Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

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.

Comments

Posted by John Stevens-Taylor on 11 November 2009

a simpler solution would be to use the SSRS date picker and format the date selected to match your key (eg yyyymmdd). this can then be used directly to filtger your main data query. no trips to the cube required to populate the date parameter.

obvoiusly this will not work if you absolutly have to have dates that are in your cube, but in most cases this would simply result in an empty result set.

Posted by kpatrick on 11 November 2009

One other performance tip I've seen: the StrTo functions like StrToMember can also cause performance issues. One trade-off you might consider: creating your entire query as a VB string, which would reduce the need for the StrToMember. The trade-off part is that you can no longer use the easy-to-user query builder screen to modify your query.

Posted by knight_devin@hotmail.com on 11 November 2009

John

Yep you're right. In my case I did need it all done in the cube though.