No DRILLTHROUGH queries in Reporting Services

June 30, 2006

I was needing to do an mdx DRILLTHROUGH report in Reporting Services and got this strange error: “Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)”.  Went looking and came across Nick Barclay’s blog entry that talks about how you can’t do mdx DRILLDOWN queries in RS and that Darren Gosbell had written a bug report about it.  Microsoft responded that they decided not to support it since you could easily perform the same query in 2005 w/o using a drilldown statement.

It took me a little while to work out all the syntax of the different queries and thought I’d share it here as well as some performance differences that I found.

First of all, here is the DRILLTHROUGH query that I wrote.

DRILLTHROUGH
SELECT NON EMPTY { [Measures].[Internet Sales Amount]} ON 0
FROM [Adventure Works]
WHERE ( [Date].[Calendar Year].&[2003])
RETURN
     [$Customer].[Customer]
     ,[$Date].[Calendar Year]
     ,[$Product].[Category]
     ,[$Product].[Model Name]
     ,[$Product].[Product]
     ,[$Product].[Product Line]
     ,[$Product].[Subcategory]
     ,[$Promotion].[Promotion]
     ,[$Sales Reason].[Sales Reason]
     ,[$Sales Territory].[Sales Territory Region]
     ,[$Sales Territory].[Sales Territory Group]
     ,[$Ship Date].[Date]
     ,[Internet Sales].[Internet Sales Amount]
     ,[Internet Sales].[Internet Order Quantity]

Here is the equivalent query written in a way that will work in Reporting Services 2005.

SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY {
     [Customer].[Customer].[Customer].ALLMEMBERS *
      [Date].[Calendar].[Date].&[550] : [Date].[Calendar].[Date].&[914] *
      [Product].[Category].[Category].ALLMEMBERS *
      [Product].[Model Name].[Model Name].ALLMEMBERS *
      [Product].[Product].[Product].ALLMEMBERS *
      [Product].[Product Line].[Product Line].ALLMEMBERS *
      [Product].[Subcategory].[Subcategory].ALLMEMBERS *
      [Promotion].[Promotion].[Promotion].ALLMEMBERS *
      [Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
      [Sales Territory].[Sales Territory Region].[Sales Territory Region].ALLMEMBERS *
      [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS *
      [Ship Date].[Date].[Date].ALLMEMBERS
} ON ROWS
FROM [Adventure Works]

I have found (very unscientifically) that on my server, there is a significant difference in performance.  The DRILLTHROUGH query ran in 14-16 seconds on average and the CROSSJOIN query ran in 23-25 seconds.  When the queries were smaller and included fewer dimensions and fewer rows because of the WHERE clause, they converged and there wasn’t a significant difference in performance – although on a small query I was finding that the CROSSJOIN query took 3 seconds and the DRILLTHROUGH took 1 second – a 200% difference.

Now I’m not an expert in how the query engine works, but it isn’t a surprise to me that the CROSSJOIN query would be slower than the DRILLTHROUGH query.  Maybe someone out there can detail why the DRILLTHROUGH is faster or if maybe there is another way to write the query to get the same results into RS without using a CROSSJOIN.

This seems to me that we have another instance of the integration of Reporting Services and Analysis Services getting shortshrifted again (read here).  Hopefully Microsoft will see this as an opportunity to improve the integration of OLAP into the Reporting Services product.


Role Playing Dimensions Materialized and Aggregated? Yes.

June 27, 2006

Ok. We have an official word and it is good news.  Role playing dimension are materialized and aggregated and there was just an error in the Project REAL docs. You can read about it in the comments to this post and also on Chris Webb’s blog.

I have to admit that I probably should have thought twice about this statement and had a little more doubt.  Not being able to aggregate on a role-playing dimension would be a big blow to the product.

Thanks,

Mark

Role playing dimensions are dimensions that are defined once and then used multiple times in a cube.  One example might be a date dimenion.  You create one date dimension on top of one relational table and add that to your dimensions folder in your project.  In your cube you have two roles for this dimension to play, sales date and ship date.  You add the dimension to your cube and for each one, you select the key that it will join to in the fact table.

There is one thing that should be noted when doing this.  If you create your dimensions this way, they are not materialized.  This means that no aggregations will be stored for that dimension.  Since the dimension plays two roles, we can’t store two sets of aggregates for it.  This does save space, but at a performance cost.

One way to materialze your dimensions is to create a named query in the data source view.  You can select * from your table and then rename the table and the key column so that it matches your fact table.  This will also create the primary key on the named query.  Then go through and re-create your dimensions but create one dimension for each new named query.

Mark


MDX Solutions

June 6, 2006

I have just gotten done with reading (most) of the book MDX Solutions by Spofford, et al – Second Ed.
 
What a great book.  If you have questions about how to get something done in MDX, the answer is probably there.
 
Good job guys.
 
Mark


NULL = 0?

May 11, 2006

Does everyone know that NULL is converted to 0 in Analysis Services before it is used in a comparison in MDX?

If you need to check to see if something is null, don't compare it to null, use the ISEMPTY() function.

Good Luck,

Mark