Dave Rodabaugh's Cube

Parts I and II of this series demonstrated the differences in drillthrough from MSAS to SSAS, and listed design implications for the entire DW/BI manufacturing pipeline. This section discusses ways to write effective drillthrough queries.

The introduction of this series stated that web research did not provide a single, conclusive way to handle drillthrough in SSAS. As noted, the first hint of trouble was the inability of SSRS to use DRILLTHROUGH in an MDX query. SSRS returned the following error: "Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)".

A Web search reveals two workarounds.

So, apparently there are three ways to write drillthrough when integrating SSRS with SSAS.

• Use the OLE DB for OLAP 9.0 provider instead of a native provider. This approach is undesirable if the source is SQL Server, since the native provider probably performs better OLE DB. It may also affect parameterization in SSRS, and it makes little sense to use a query that limits reporting functionality. This is "the old way" complete with the drawbacks of "the old way."
• Use a DMX query with the DRILLTHROUGH keyword. As noted, this works quite well but it requires any new report writer to know the workaround.
• Write a crossjoin MDX query that returns the correct rowset.

Option (1) is discarded. That leaves options (2) and (3). The following examples illustrate how they work.

Example for Option (2): DRILLTHOUGH with an MDX query.

The following example illustrates how the DRILLTHROUGH keyword works with an MDX query (and also with a DMX query in SSRS). Like Parts I and II, this example uses the Adventure Works DW cube and the Reseller Sales measure group. (All of these queries may be run in SQL Server Management Studio.)

The following is the most basic drillthrough MDX query:

It returns the following columns:

Several observations emerge from the results set:

1. The query returns 80,655 rows. This is the exact number of rows in the fact table underneath the Reseller Sales measure group, and is the correct answer. Since SSAS answers all drillthrough queries and does not pass the request on to the data source, the one-to-one relationship between detail cell and fact row is evidenced.
2. Dimension columns are prefixed with a "$". Measure columns have no such prefix.
3. Ten measures are returned by the query. These are the ten measures with their Visible property set to True. Those measures with their Visible property set to False are not returned by default. They can be returned if the optional RETURN keyword is used and the columns are explicitly listed therein.
4. Both the Reseller Sales Order Details and Geography dimension returned non-key attributes. This is because their key attributes have their AttributeHierarchyVisible property set to False.
5. All other dimensions have key attributes with AttributeHierarchyVisible property set to True. These dimensions return only their key attribute.

Additional attributes and hidden measures may be returned with this query if the RETURN keyword is used. However, this will require all return columns to be explicitly listed in the RETURN clause. RETURN also permits column aliasing, which may particularly useful to comply with organizational standards for SSRS data set column names.

Additional considerations apply.

1. Of course, a WHERE clause may be added to this query as required.
2. It does not matter which measure is called in AXIS (0); the query always returns the same set of columns (absent the RETURN clause).
3. The query sends CPU utilization for msmdsrv.exe to maximum capacity. This test was conducted on a laptop with a P4 HT, and only one of the virtual CPU’s answered the query.
4. There appears to be some caching effect. The first query took about four minutes to run. Repeating the query reduced completion time to 10’s of seconds.
5. DRILLTHROUGH will not return calculated members. Anything returned by DRILLTHROUGH must be tied directly to an attribute or measure, which in turn relies directly upon a DSV column.

Example for Option (3): Emulating DRILLTHROUGH with an MDX Query.

The following example illustrates how MDX may be used to emulate a DRILLTHROUGH query. This example assumes that the proper design criteria have been met to emulate drillthrough with MDX. (See part II of this series.) This example uses the Adventure Works DW cube and the Reseller Sales measure group. (All of these queries may be run in SQL Server Management Studio.)

Deepak Puri correctly assessed the lone requirement to emulate a drillthrough query with MDX when he said, "…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." Any other attribute or measure in the query may add content, but it will not change the number of rows returned by the query.

The following query returns 60,855 rows from Reseller Sales in Adventure Works DW.

Observations about this query include the following:

1. The query returns 60,855 rows. This is the exact number of fact rows and provides evidence that MDX queries can be used to emulate drillthrough so long as the proper design techniques are employed.
2. This query returned all physical measures in the cube. Since not all measures originate with the Reseller Sales measure group, not all measures can be sliced by the Reseller Sales Order Details dimension. The values returned by those measures will be for the All member in that dimension and will have the same large value in every row. Be sure to explicitly list the measures to be returned in the query.
3. This query returned no calculated members.
4. A WHERE clause may be added as required.
5. Caching effect is large. The first time the query was run execution time was 10:12. The second time the query was run execution time was 3:06.
6. Performance conditions were identical to the previous example since the same machine was used, in the same configuration.

Additional attributes may be added as desired to the previous query.

This query returns 1,159 rows and clearly does NOT emulate drillthrough.

This particular query takes a very long time to run but it returns 60,855 rows, as expected. Specifying a single measure instead of [Measures].Members on Axis 0 would make the query much faster.

So, Microsoft really DID know what they were talking about.  You can either use the DRILLTHROUGH keyword, or model UDM so that MDX produces a granular rowset.