Improving Performance of Analysis Services-Sourced Reporting Services Reports

This is something I picked up on the other week, when I was doing a job tuning some Reporting Services reports which were running off Analysis Services (there were other, more interesting findings but I’ll leave them for a later date). When you create MDX queries using the RS query builder, the reports look something like this:

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 
My customer was creating some very large reports that were up to several hundred pages long, and in some cases they took over a minute to render. What I realised was that the above query contains a number of cell properties that aren’t actually needed, such as BACK_COLOR, FORE_COLOR, FORMAT_STRING, FONT_NAME, FONT_SIZE and FONT_FLAGS. They can safely be removed from the query, as follows:

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, FORMATTED_VALUE

 

There’s no change to the results returned or how most reports will behave (assuming you’re not somehow referencing these properties somewhere), and in my case it made a noticeable difference in the amount of time taken to render the reports – reports that previously took 60 seconds to run now took 50 seconds. I would guess that this is because for very large queries, there’s a significant overhead involved with including all these unnecessary property values in the resultset

3 thoughts on “Improving Performance of Analysis Services-Sourced Reporting Services Reports

  1. Good posting.  I am working on some performance tuning of some MDX queries myself and found this to help out as well.  I have posted a reference to your post on my Blog here (http://spaces.msn.com/danmeyers).  I just got it up and going and I am hoping to add some good content to it over the next few weeks here. 
     
    Dan Meyers

  2. I\’ve just noticed that this was fixed in SP1 (or the hotfix rollup) – you only seem to get the VALUE property in MDX queries now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s