Performance Tuning OLAP Reports

Reports that use an Analysis Services cube as a data source can often have performance problems during pre-execution.  Before you even see the classic Report is being generated sign the report is slow to start.

The most common reason I’ve found for this performance lag has to do with parameters that are filtering the main dataset.  For example, you have a report that uses a sales cube and you want the user to be able to select the date range that the report will return results for.  This would mean you would add a filter to the dataset using a date dimension and check the box to make it a parameter.

The reason this can be a performance problem is because you’ve now added a parameter that includes every date that exist in your date dimension.  So before the report can even begin to generate it has to populate all the records in the parameter first.  Depending on how many dates you’re storing in the dimension this could be a major issue.  Not to mention if you are using cascading parameters that depend on the previous parameters results. 

It’s likely the end users of the report aren’t interested in seeing 70 years worth of data.  It’s much more likely that the user would want a rolling years worth of dates to choose from.  So here’s the solution:

Step One

Show Hidden Datasets by right clicking on the report Data Source and clicking Show Hidden Datasets.  When you check to include parameters on a dataset it creates hidden datasets behind the scenes. 

 

Step Two

Open the properties to the newly shown dataset that is used for the date parameter and select Query Designer.  Add the following MDX where clause to the query.  This query may vary greatly depending on the design of your cube.  This will just give you a starting point.

 

Now when I view this report I will only have the past years worth of dates available in the date parameter.  Lag(366) is bringing back all dates more than a year old to Lag(0), which is the current date.  Again this could be very different for you depending on how the date dimension is setup in your cube