Microsoft KB Archive/306191

= PRB: Drillthrough Queries Return More Rows than Expected If Cube Schema Is Optimized =

Article ID: 306191

Article Last Modified on 2/21/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q306191



SYMPTOMS
Drillthrough queries in Analysis Services may return more rows than expected if the cube schema is optimized and the drillthrough query data set includes columns from a dimension table that had it's join removed by the schema optimization process.



CAUSE
Missing joins in the WHERE clause cause the drillthrough query to return more rows than expected.



WORKAROUND
To work around this problem, explicitly specify the missing joins on the Filter tab of the Drillthrough options for the cube in the Cube Editor or for the partition in the Partition Wizard.



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.