THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Erin Welker

SQL Server and Business Intelligence

Trials in reporting on cubes

I'll admit that I haven't spent a lot of time in Reporting Services.  And it's been quite awhile since I've written a report against an Analysis Services cube - since SQL Server 2000, in fact.  I was kind of looking forward to using the MDX designer in Reporting Services 2005 but soon found it's limitations (which are well-documented).  I needed to implement drilldown on the Account hierarchy, which required pulling the preceding parent levels in a parent-child dimension.  This quickly moved me to the OLE DB data source type and out of the nifty query designer that is only available when using the Analysis Services type :-(  As such, I was back to implementing parameters by concatenating them in the query string - it was looking like SQL Server 2000 all over again.

But that's not the jist of this blog entry.

I got a nice looking report that drilled down a parent-child Account hierarchy on the rows and displayed various time periods on the columns.  Yes, it looked good, but it took, on average, 1 minute 37 seconds to refresh!  Every time the user would select a new job to display they'd have to wait over a minute and 1/2 for the report to come up.  Unacceptable!  What's worse is that the MDX was only taking 7 seconds to execute.  I can tune the MDX/cube but I had no idea what Reporting Services was doing that resulted in such a long render time.

My first resort was to google on the problem.  I found very little information on the topic.  One useful blog by Chris Webb suggested that I remove the cell formatting options.  Alright, that was nice - it removed 15 seconds and my report now refreshed in 1 minute 23 seconds (average).  Still unacceptable.

In thinking about the problem it seemed strange that RS required such a flat query that took an aggregated source, required you to unaggregate it, so that RS could re-aggregate it itself.  Yet all examples I could find put all measures (and only measures) on the columns axis, and all remaining dimension members on the remaining axes.  This returns a resultset that is long and narrow.  The report is short (relatively) and wide.  Why can't I make the query look just like the report?

So that's what I tried.  I put the column header dimension members on the column axis.  Bingo - the report rendered in 15 seconds - now that's a significant improvement!

Note that this solution will not fit the need of all reports.  In placing dimension members on the column axis I lose the ability to drill down or up that dimension - the column headers become static.  In my case, that's precisely what I wanted since that happened to be the design of the report.  It also is a little more tedious to manually enter the values associated with 10 static columns compared to the 1 column in the original report.  I felt this was a small price to pay for over 5x performance improvement.

               SSRSDesignerFast

 

MDX Before (condensed)

SELECT NON EMPTY {[Measures].[Value]} ON COLUMNS, 

  {([Time].[Fiscal Month].[Current Month], {[Scenario].[All Members].[Actual], [Scenario].[All Members].[Forecast]}),
    ([Time].[Fiscal Month].[ITD], {[Scenario].[All Members].[Actual], [Scenario].[All Members].[Forecast]}),
    ([Time].[Fiscal Month].[EAC], {[Scenario].[All Members].[EAC], [Scenario].[All Members].[Budget]})}
ON ROWS,

NON EMPTY  ([Version].[All Members].[Version].[0], [Job].[Job_Hierarchy].[Level 03].[1234],
   { [MeasureType].[All Members].[Units] * DESCENDANTS([Account].[Account Hierarchy].[Total Costs],, LEAVES),
     [MeasureType].[All Members].[Dollars] * {DESCENDANTS([Account].[Account Hierarchy].[Total Costs],, LEAVES), [Account].[Account Hierarchy].[Total Revenue]}}) ON PAGES

FROM [Job Planning]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

MDX After

SELECT  
  {([Time].[Fiscal Month].[Current Month], {[Scenario].[All Members].[Actual], [Scenario].[All Members].[Forecast]}),
    ([Time].[Fiscal Month].[ITD], {[Scenario].[All Members].[Actual], [Scenario].[All Members].[Forecast]}),
    ([Time].[Fiscal Month].[EAC], {[Scenario].[All Members].[EAC], [Scenario].[All Members].[Budget]})}

  * {[Measures].[Value]} ON COLUMNS, 

NON EMPTY  ([Version].[All Members].[Version].[0], [Job].[Job_Hierarchy].[Level 03].[1234],
   { [MeasureType].[All Members].[Units] * DESCENDANTS([Account].[Account Hierarchy].[Total Costs],, LEAVES),
     [MeasureType].[All Members].[Dollars] * {DESCENDANTS([Account].[Account Hierarchy].[Total Costs],, LEAVES), [Account].[Account Hierarchy].[Total Revenue]}}) ON ROWS

FROM [Job Planning]

Published Wednesday, April 30, 2008 9:46 PM by ErinW

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris Webb said:

Hi Erin,

I share your pain! In fact I got so frustrated I went ahead and did something about it, resulting in this:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1797.entry

http://www.it-workplace.co.uk/IQ.aspx

Feel free to download an eval version and tell me what you think!

Chris

May 1, 2008 1:55 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement