Inconsistencies when using Drillthrough MDX with Perspectives
Author: Denny Lee
Contributors: John Desch, Kevin Cox
Reviewers: Robert Zare
DRILLTHROUGH MDX Overview
DRILLTHROUGH MDX is a very powerful way to drillthrough to your original data source of detail data that make up the aggregations that you are looking at. For more information on enabling drillthrough, please refer to T.K. Anand’s great article on Enabling Drillthrough in Analysis Services 2005 (https://technet.microsoft.com/en-us/library/ms345125(SQL.90).aspx). Using a real example, if you were to query the Adventure Works OLAP database for the [Internet Sales Amount] measure:
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS
FROM [Adventure Works]
The result would be that of: $29,358,677.22. You can also get the same result when you query the [Direct Sales] perspective (change the [Adventure Works] cube with the [Direct Sales] perspective in the above MDX statement).
With DRILLTHROUGH MDX statement, you can get the details that make up the above value of $29,358,677.22. Specifically, the MDX statement
DRILLTHROUGH
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS
FROM [Adventure Works]
will provide the dimension attributes and measure details that make up this aggregate value. Due to the large number of columns and rows included in the output, only the dimension members Promotion and Sales Territory Region and the Internet Sales Amount measure will be shown below:
[Promotion], [Sales Territory Region], …., [Internet Sales Amount], …
-------------------------------------------------------------------------
No Discount, Australia, …, 3399.99, …
No Discount, Australia, …, 3374.99, …
…, …, …, …, …
DRILLTHROUGH MDX ON PERSPECTIVES
But when you run the same drillthrough statement against the [Direct Sales] perspective,
DRILLTHROUGH
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS
FROM [Direct Sales]
you’ll notice that while all of the dimension attributes are included in the output, none of the measures are listed.
Yet, this output behavior is not consistent; if you were to run the query below against the [Sales Summary] perspective,
DRILLTHROUGH
SELECT { [Measures].[Sales Amount]} ON COLUMNS
FROM [Sales Summary]
The output is includes only four of the ten available measures in the perspective. To prove this latter point, execute the below statement against the [Sales Summary] perspective and you will see the ten measures listed.
select { [Measures].members} on columns from [Sales Summary]
As you can see from the MDX statements above, there is inconsistent output from the DRILLTHROUGH MDX statements in which some or none of the measures are included in the output. Yet, this issue does not come up when you query the cube directly. Does this mean you will have to run all of your drillthrough queries through the cube?
WORKAROUND TO SOLVE THIS
You certainly can do this and avoid the use of perspectives altogether will drilling down to the details. But if you want to use perspectives, the workaround to this problem is to list out all of the measures and dimension attributes you want to see within that perspective explicitly. An example in reference to the [Direct Sales] perspective is:
DRILLTHROUGH
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS
FROM [Direct Sales]
RETURN [Internet Sales].[Internet Sales Amount],[Internet Sales].[Internet Order Quantity],[Internet Sales].[Internet Extended Amount],[Internet Sales].[Internet Tax Amount],[Internet Sales].[Internet Freight Cost],[Internet Sales].[Internet Unit Price],[Internet Sales].[Internet Total Product Cost],[Internet Sales].[Internet Standard Product Cost],[$Internet Sales Order Details].[Carrier Tracking Number],[$Internet Sales Order Details].[Customer PO Number],[$Internet Sales Order Details].[Sales Order Number],[$Internet Sales Order Details].[Sales Order Line],[$Promotion].[Promotion],[$Delivery Date].[Date],[$Sales Territory].[Sales Territory Region],[$Product].[Product],[$Ship Date].[Date],[$Source Currency].[Source Currency Code],[$Date].[Date],[$Sales Reason].[Sales Reason],[$Customer].[Customer],[$Destination Currency].[Destination Currency Code]
By explicitly stating what is to be returned, the “missing” measures such as [Internet Sales Amount] will now show up with in your drillthrough to the [Direct Sales] perspective.
Please note, this is by design and will not change within Analysis Services 2008.