No DRILLTHROUGH queries in Reporting Services

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.

Advertisements

7 Responses to No DRILLTHROUGH queries in Reporting Services

  1. furmangg says:

    I don’t agree with that statement Microsoft made that a drillthrough could be written in MDX. What if a fact table has more than one row for a particular slice. If you write an “equivalent” MDX statement, won’t you get only one row for that slice showing the total, not the individual fact table rows?

  2. Deepak Puri says:

    Hi Mark,

    To get around this issue, I’ve resorted to using the OLE DB for OLAP 9.0 Provider with AS 2005 Drillthrough reports. This option, though not pretty, has been discussed in the SQL Server OLAP NG:

    http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/85a65872f286686b
    >>

    This probably means that the issue is isolated to the ADO MD provider in
    SSRS. As going down a layer to OLE DB works a treat.

    >>

  3. Mark Garner says:

    furmangg,
    I second your assessment of Microsoft’s statement on two accounts. First of all, the performance is significatnly different and second, you are correct, multiple rows would be aggregated into one row if they fell in the same slice.
    Can anyone think of another way to structure the query?

  4. furmangg says:

    I’m assuming you all saw the workaround posted Darren on that bug report?

    https://connect.microsoft.com/SQLServer/feedback/Workaround.aspx?FeedbackID=126175

    If you switch the query to DMX, the supposedly it works… and I assume (though I haven’t tried it) that this will allow you to use real parameters unlike the OLE DB provider.

  5. Deepak Puri says:

    I think that you need to include the fact dimension key attribute, so that each fact table row is returned, regardless of how many contribute to each cell. Performance is another matter, though:

    >>
    SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity] } ON COLUMNS,
    NON EMPTY {
    [Internet Sales Order Details].[Internet Sales Orders].[Internet Sales Order] *
    [Customer].[Customer].[Customer] *
    [Date].[Calendar].[Date] *
    [Product].[Category].[Category] *
    [Product].[Model Name].[Model Name] *
    [Product].[Product].[Product] *
    [Product].[Product Line].[Product Line] *
    [Product].[Subcategory].[Subcategory] *
    [Promotion].[Promotion].[Promotion] *
    [Sales Reason].[Sales Reason].[Sales Reason] *
    [Sales Territory].[Sales Territory Region].[Sales Territory Region] *
    [Sales Territory].[Sales Territory Group].[Sales Territory Group] *
    [Ship Date].[Date].[Date]}
    ON ROWS
    FROM [Adventure Works]
    where [Date].[Calendar Year].&[2003]
    >>

  6. Mark Garner says:

    Thanks for the query example Deepak. I’m still disappointed that Microsoft has made a decision to not support (oficially) DRILLTHROUGH querys. Good to know that there is a workaround, though.

  7. No MDX DRILLTHROUGH queries in Reporting Services…

    I found an interesting post by Mark Garner that exposes another gap in SSAS and SSRS inter-operabiity. ……

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

%d bloggers like this: