Browsed by
Category: SSAS

SSRS: Using date pickers to filter SSAS cube data

SSRS: Using date pickers to filter SSAS cube data

When you have a report that pulls data from an SSAS cube, you have most likely used a date range filter on which time series you would like to display. It is not so straightforward though, to create date-parameters in SSRS and in your MDX query you refer to these queries. Because MDX needs to interpret the date values in a completely different syntax, we have to format the data. This post shows you how to accomplish this.┬áThis post does…

Read More Read More

Various SQL date formats to use with SSIS, SSAS and/or SSRS

Various SQL date formats to use with SSIS, SSAS and/or SSRS

While designing data warehouses, SSIS packages, etc., like myself you’ve probably ran into the issue of having to format different date and time values that ends up in a long string of SQL code or expressions in SSIS. The purpose of this post is to collect some links to various pages where these kind of date and time formatting can be found. Getting only date part of a datetime field First of all, the function that I used the most…

Read More Read More

Report on your reports

Report on your reports

When you deploy your SSRS reports, you might want to know about how they perform. Or maybe your reports perform allright, but there might be improvements on how the end users can be presented with the reports even faster. Also, you might have deployed a bunch of reports that nobody reads but they’re just sitting there… To get insight into these things, the ReportServer database has a few tables that contain log records of your reports. First of all, by…

Read More Read More

Avoid SSRS to change your customized MDX datasets

Avoid SSRS to change your customized MDX datasets

I’ve experienced many times that when I changed the main dataset in SSRS (that reads from an SSAS cube), that the datasets of the parameters are changed again automatically. This has usually been a very annoying thing because I change the MDX in the parameter datasets very frequently. To avoid this automatic change, you can add SuppressAutoUpdate=true in the code of your report that suppresses the change of this MDX. Right click the report and choose View Code. Find the…

Read More Read More

Error message explained: The ‘Role’ with ‘ID’ = ‘Role 1’ doesn’t exist in the collection.

Error message explained: The ‘Role’ with ‘ID’ = ‘Role 1’ doesn’t exist in the collection.

It could happen that you move around your cube files and with the deployment, you run into some errors. A common error that I’ve encountered again is the following. The ‘Role’ with ‘ID’ = ‘Role 1’ doesn’t exist in the collection. This happens mostly when you have copied a .cube and .partitions file to another project and added this to the new SSAS project/solution. The underlying meaning of this error is that in your cube file, you have a role…

Read More Read More

Dimensional model in SCD2 (part 2)

Dimensional model in SCD2 (part 2)

Continued from part 1: In addition to my previous post, I thought it would be helpful to display screenshots of how I designed my dimension. This is the attributes tab: … and this is how the attribute relationships look like: With these attributes, I would like to be able to do analyses like the number of resources per gender, per country, per cost center, per language, etc…

Dimensional model in SCD2 (part 1)

Dimensional model in SCD2 (part 1)

This post is dedicated to an issue I’ve been dealing with for a while now and until so far I haven’t found a solid solution yet. Since I’ve been explaining and asking this to several experts over and over again, I’ll dedicate a blog post to it. I’m a big fan of using Kimball’s Slowly Changing Dimensions, especially since it keeps track of historical data and it doesn’t explode your database. But… mapping this data into an SSAS dimension is…

Read More Read More

Welcome to my blog!

Welcome to my blog!

Welcome to my blog! The purpose of this blog is to publish information that I have been collected over the past years to make life easier in the world of Microsoft’s BI platform. It has occurred to me many times that I asked myself the question “Now how did I do this-and-this again?”. After some extensive searches on Google I usually found an answer, but I thought to myself why not collect this information and share it with the rest…

Read More Read More