Microsoft KB Archive/834285

= Incorrect data appears in a cube when you select multiple dimensions that have the same level in an SSABI application =

Article ID: 834285

Article Last Modified on 4/25/2007

-

APPLIES TO


 * Microsoft Solution Offering - Microsoft SQL Server Accelerator for Business Intelligence

-





SYMPTOMS
When you define multiple dimensions that all have the same level for a Microsoft SQL Server Accelerator for Business Intelligence (SSABI) application, and the aggregation that is chosen for a measure causes SSABI to generate custom rollup formulas for the aggregation, incorrect values may appear in the Analysis Services cube.



CAUSE
When you define multiple dimensions and when you set aggregation, SSABI generates the appropriate custom rollup formula for the dimension. However, when a cube has multiple dimensions that have custom rollup formulas, Analysis Services resolves the formulas in the order that the dimensions are added to the cube.

This behavior of Analysis Services is documented in the SQL Server Books Online topic &quot;Custom Rollup Formulas and Custom Member Formulas&quot;. The following is an excerpt from the topic:

If a cube contains multiple dimensions with custom rollup formulas or custom member formulas, the formulas are resolved in the order that the dimensions were added to the cube.

In other words, the custom rollup formula that Analysis Services uses to calculate is the custom rollup formula of the first dimension that is created in the cube.



This behavior is by design.



Steps to reproduce the behavior

 * 1) Open the Analytics Builder workbook.
 * 2) Click the Autogen Time worksheet, and then select the Marketing check box.
 * 3) Click the pCubes worksheet, and then add the [Time].[Marketing] dimension to the Backlog cube. To do this by using the Analytics Builder Workbook (ABW) toolbar, add an additional row under the Backlog cube, and then select the [Time].[Marketing] dimension in the Dimension-Hierarchy column.
 * 4) Click the Processing worksheet, and then click Generate Application. Wait for the processing to finish.
 * 5) Open Analysis Manager.
 * 6) Process the Backlog cube, and then edit the cube.
 * 7) Notice the following:
 * 8) * The custom rollup formulas for the Time.Marketing dimension and the Time.Standard dimension are different.
 * 9) * The values for the following times are the same:
 * 10) ** Time.Marketing = All and Time.Standard = 2001
 * 11) ** Time.Marketing = 2001 and Time.Standard = All

