FIX: Analysis Services Always Uses Visual Totals for Restricted Dimensions (298443)

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:

	1999
NorthWest	119,450
US Total	500,000

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:

	1999
NorthWest	119,450
US Total	119,450

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.