PRB: Drillthrough Queries Return More Rows than Expected If Cube Schema Is Optimized (306191)

MORE INFORMATION

One of the performance enhancing features in the Analysis Services product is the Optimize Schema functionality that is built into the Cube Editor. The Optimize Schema feature analyzes the joins between the dimension and fact tables and eliminates any joins that it does not need to properly process and aggregate the cube data. The joins are considered unnecessary if the column in the fact table, on which the join to the dimension table is performed, is the same as the member key column of the dimension members. In that instance, you can use the value in the fact table to look up the member information that is stored by Analysis Services in the dimension structure. When Analysis Services eliminates the joins, it removes the dimension table from the FROM clause and removes the join from the WHERE clause of the SQL statement it uses when processing the cube. The benefit of this behavior is that it reduces the work required by the relational data provider when processing of the cube takes place.

You can see a potential drawback with the use of optimized schema when you use the drillthrough feature of Analysis Services. When you use a drillthrough query, you can select columns from the source tables for all the dimensions in the cube. Drillthrough inherits the joins for the drillthrough SQL statement from the WHERE clause on the cube. However, in the case of an optimized schema, the joins to the dimension table may have been removed from the WHERE clause on the cube. As a result of the missing joins in the WHERE clause, the drillthrough query may return more rows than expected.

For example, suppose you have a cube with two dimension tables, named Dim1 and Dim2. Enable drillthrough for the cube and request that the FactTbl.Fact1, FactTbl.Fact2, Dim1.Description and Dim2.Manager fields be returned.

The joins for the cube are:

FactTbl.Dim1Key -> Dim1.Key
FactTbl.Dim2Key -> Dim2.Key
				

In the preceding case, the WHERE clause for the cube and drillthrough query are:

WHERE FactTbl.Dim1Key = Dim1.Key
AND FactTbl.Dim2Key = Dim2.Key
				

Suppose that the keys in the member key column for the lowest level of Dim1 is unique but that the keys of Dim2 are not unique. Run Optimize Schema on the cube and it replaces the member key column for Dim1 in the cube with FactTbl.Dim1Key. The WHERE clause for the cube and drillthrough are updated to:

WHERE FactTbl.Dim2Key = Dim2.Key
				

The missing join between the FactTbl and Dim1 tables cause every row from the fact table to join to every row in the Dim1 table when the drillthrough query executes.

REFERENCES

For more information regarding the Filter options for a drillthrough query, see the "Filters-Analysis Services, drillthrough" topic in Microsoft SQL Server Books Online.