Microsoft KB Archive/298443

= FIX: Analysis Services Always Uses Visual Totals for Restricted Dimensions =

Article ID: 298443

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q298443



BUG #: 11251 (Plato7.x)



SYMPTOMS
Analysis Services 2000 always displays Visual Totals for restricted dimensions that contain custom rollup formulas, unary operators, or custom members, even if Visual Totals are not enabled.



RESOLUTION
To resolve this problem, obtain the latest service pack for the Microsoft SQL Server 2000 (the Analysis Services Components - Sql2kasp1.exe). For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000 Analysis Services version 8.0. This problem was first corrected in the Analysis Services Components of Microsoft SQL Server 2000 Service Pack 1.



MORE INFORMATION
Analysis Services 2000 introduces custom security restrictions for dimensions. Dimension level security allows the Analysis Services Administrator to alter the members and levels of a dimension that are visible to specific users and groups. Because some members or levels of a dimension may be hidden from a user, the preaggregated totals that are stored in the cube may not reflect the total of the dimension members that the user is able to see. For example, if the total sales quantity for the United States is 500,000 units, but the user only has access to the NorthWest region, they might see data displayed as follows:

To solve this apparent problem, Analysis Services introduces a feature called Visual Totals which can be enabled for dimensions where custom security is defined. The Visual Totals feature causes the client to calculate the total for the visible members of the restricted dimension. When Visual Totals are not enabled, the client queries the Analysis Server for the value of the parent member to determine the total to be displayed.

If Visual Totals are enabled for the Geography dimension in the example above, the data is displayed as follows:

For restricted dimensions that use custom rollups, unary operators, or custom members, Analysis Services always behaves as if Visual Totals are enabled. This is because the totals for dimensions using these features are not stored in the cube, but are calculated when the value is requested. Because the restricted dimension members are not available to the client, they cannot be included in the calculation of the totals.

Prior to Analysis Services 2000 Service Pack 1, the Visual Totals option can be enabled and disabled through the custom dimension security dialog box but will have no effect for dimensions using custom rollups, custom members, or unary operators. In Analysis Services 2000 Service Pack 1, the Visual Totals option is enabled for dimensions using custom rollups, custom members, or unary operators, and cannot be disabled.

Additional query words: Visual Totals OLAP

Keywords: kbbug kbfix kbssas800sp1fix KB298443

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.