Dave Rodabaugh's Cube

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Drillthrough is changed substantially in SSAS because Microsoft rearchitected it. SSAS will no longer prepare an SQL statement to pass through the data provider to the data source, and pass along a rowset returned by the data source. All drillthrough queries must be answered from SSAS, using the information present in UDM.

The goal of drillthrough in SSAS should be the same as it was for MSAS: return one row for each row in the fact table, regardless of how many detail cells are underneath the cell upon which drillthrough occurs. (NOTE:  I am aware that drillthrough is actually more sophisticated than this and can return rowsets at nearly any grain, but if it will not produce the fact row set, then flexibility is limited.)  Since SSAS now answers all drillthrough queries directly, savvy DW/BI designers must adhere to several prominent design elements, most notably enforcement of fact row uniqueness. The goal is to produce a one-to-one relationship between fact rows and detail cube cells. One cell should have one and only one fact row.

While SSAS will work fine without unique fact rows for aggregate and analytical queries, it simply cannot produce the most granular drillthrough query without fact row uniqueness. Decide whether drillthrough requirements require fact row uniqueness before ETL and cube builds, because it may be very difficult to add once a cube is in production.

Add a dimension with an attribute to hold the column or columns that make fact rows unique. This attribute will have as many members as rows in the fact table, so it could be very large. Take steps to control it. Techniques include making it a ROLAP dimension and perhaps hiding it if it is useful only to drillthrough queries. This dimension ties into a measure group by using the fact dimension relationship.  CAUTION:  ROLAP dimensions must be processed the first time they are queried, which could result in extraordinarily long query times.  Cnosider MOLAP first.

Once the one-to-one relationship between fact rows and detail cells is enforced, drillthrough queries can be written with two divergent techniques. The first is to use the DRILLTHROUGH keyword in an MDX query. The DRILLTHROUGH keyword requires an MDX query that returns a single cell, and then returns one row for each detail cell under the single cell. The second is to use an MDX statement to emulate drillthrough and produce a cell set. This is done with an inordinate number of crossjoins, but it works.

Even though these two query options produce the same answer, choose one or the other carefully based on performance. The query using the DRILLTHROUGH keyword does not execute as an MDX query and basic testing shows it is faster than its crossjoin counterpart. Indeed, be very wary of the crossjoin alternative. Crossjoin queries slow down considerably as the number of members being crossed increased, and since the fact dimension contains one member for each fact row, the crossjoin approach can become unwieldly very quickly.  Remember, the point of this series is to integrate SSRS with SSAS using drillthrough, and drillthrough returns a nice, flattened rowset convenient for SSRS.

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.