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]

Horizontal partitioning can be a great benefit to data warehouses, as it greatly facilitates the management of very large databases. Of course, querying on partitions can also have performance benefits, especially when only a small percentage of the database is queried and partition elimination can occur. SQL Server 2005, however, can have some unexpected behavior when querying few partitions.

In SQL Server 2005, one thread is allocated per partition when multiple partitions are queried. These threads then work in parallel to retrieve the rows to satisfy the query. If only one partition is queried, however, SQL Server 2005 allocates as many threads as are available to the one partition. As you can quickly see, there is a vulnerability when it comes to queries that query very few, but more than 1 partition.

The common scenario is when only 2 partitions are queried. For example, let’s say you have a data warehouse database that is partitioned on a sales date by month. You write a query to compare last month’s sales to this month’s sales. Now, let’s say this runs on a 32-processor server. How many threads get allocated to this query? The answer is 2, one per queried partition.

This last scenario can become even more complicated when a window of time causes the query to fluctuate between 1 and multiple partitions. Let’s say we compare today’s sales to the same day of the week last week. If both weeks are in the same month, we get full thread allocation. If the weeks span two months (= 2 partitions), however, we only get 2 threads allocated and the query can appear to slow down considerably, for no apparent reason (at least, probably not to the issuer of the query).

 

Note: All of these scenarios assume that the queries are able to eliminate partitions due to a direct search on the partitioning key. Be sure partition elimination is occurring in order to gain the best partitioned table query performance.

SQL Server 2008 behavior changes the way in which threads are allocated. It allocates all available threads to each partition relevant to the query in a round robin fashion. This results in fast and consistent query execution, no matter how many partitions are queried.

Note that this makes it all the more important to allocate partitions to filegroups that are spread across many disk spindles, allowing the query on a given partition to be as efficient as possible.

But wait, there’s another improvement to partition query behavior in SQL Server 2008. SQL Server 2005 only allows lock escalation to the table level. Let’s say your query eliminates 75% of a table’s partitions, but scans all of the remaining partitions (1/4 of the table). If SQL Server decides to escalate the lock, all other queries will be locked out even if they are querying completely different partitions. SQL Server 2008 provides a table option to override this default behavior. Note that the default is still to escalate to table locks (at least for now) so this option will need to be changed to take advantage of partition-level lock escalation. Find out more about this, like I did, on über blogger Paul Randall’s post.

I sent this to our local user group when I installed Vista Ultimate on my new laptop a few months ago.  Yesterday, I did a quick search to see if this information was readily available and found that it still was not (at least not in one place), so I decided to post it here.  I hope it helps!

·       You must install SQL Server 2005 SP2.  I was able to successfully install RTM, then followed by SP2, but an attendee at our last user group meeting had no such luck.  One of our local Microsofties mentioned there is an SQL install that combines the core install of SQL with SP2.

·       OWC 11 component install fails.  This has a domino effect on the installation of most of the other SQL components.  You know, the important ones, like database services, SSIS, SSAS and SSRS.  The newsgroup solution was to uninstall OWC 11 through Add/Remove Programs.  This solution worked for me, as well.

·       IIS is required for Reporting Services.  Nothing new here, but the configuration of IIS was not as simple as I recall it on XP.  Be sure the following options are selected (Start --> Control Panel --> Programs and Features --> Turn Windows features on or off):

o   Web management tools

§  IIS 6 Management Compatibility

·         IIS 6 WMI Compatibility

·         IIS Metabase and IIS 6 configuration compatibility

o   World Wide Web Services

§  Application Development Features

·         ASP.NET

·         ISAPI Extensions

·         ISAPI Filters

§  Common Http Features

·         Default Document

·         Directory Browsing

·         HTTP Redirection

·         Static Content

§  Security

·         Windows Authentication

·       You must install Visual Studio SP1, then another patch for Vista.  I initially tried installing the patch for Vista first but it wanted SP1.  I was unable to run BIDS until I installed these.  Here are the download files (available for download at Microsoft.com):

o   VS80sp1-KB926601-X86-ENU (VS SP1)

o   VS80sp1-KB932232-X86-ENU (patch for Vista)

There was mention of having to turn off UAC at one of our user group meetings.  I did not have this issue.  During the install, I was asked if I wanted to be added to the sysadmin role during the SQL SP2 setup, because Builtin\Administrators is not automatically added to sysadmin like it used to be.