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