Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Improving Performance of Analysis Services-Sourced Reporting Services Reports

with 3 comments

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

Written by Chris Webb

May 31, 2006 at 5:41 pm

Posted in Reporting Services

3 Responses

Subscribe to comments with RSS.

  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

    Dan Meyers

    May 31, 2006 at 9:59 pm

  2. Is this right? 60 to 50 seconds – makes only 16,7% speed up. It\’s a right slight performance benefit.

    chtepa

    June 26, 2006 at 10:59 am

  3. 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.

    Chris

    June 27, 2006 at 9:45 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,070 other followers

%d bloggers like this: