How to use cell data security with visual totals?

Hi,

We are working on a project with SQL Server 2005 and SSAS.

We created several roles using dimension security and one special role that requires cell data access permissions (i.e. cell security).

This last role is giving us trouble since we do not know how to implement visual totals on cell security.

A problem description follows:

1) We have a budget and expenses cube with a) Account, b) Time and c) Cost Center dimensions
2) The special role was created for a group of users who have the following requirement for data access permission:
- for some cost centers, restrict access to some accounts
- and for the rest of cost centers see all accounts
3) When looking at data at the cost center and account level, the role works fine. Restrictions are applied correctly.
4) However when looking at the data at the cost center level ONLY, the aggregated total shows a sum for all accounts, regardless of restrictions.

We know that if you use regular dimension security, you can enable Visual Totals in order to have aggregated values that reflect
the restrictions applied at the atomic level.

However, we do not know how to apply visual totals on cell data security. If anyone has faced similar situation,
we would appreciate any help on how to do this.

Another way to put it:

1) In a cube with a Product dimension (Family > Category > Product) and a Geography dimension (Country > State > City)
2) You can set dimension security to restrict access to certain products.
3) If you enable Visual Totals when creating the role, the aggregations at the Category level will show total values excluding amounts for those restricted products.
4) If you do not enable Visual Totals, the aggregated totals at the Category level will add up all products, regardless of access restrictions at the product level.

We are trying to achieve the same with Cell Data Access Permissions, but there is no option to Enable Visual Totals. For instance:

1) Same cube as above
3) Your create a role with cell data access permission; for some cities, you restrict acess to some products, and for all other cities allow access to all products.
4) When you look at data at the City level, you want to aggregate totals not for all products, but excluding amounts for those restricted ones.

We would appreciate your help on this enormously!

Cheers!
jbadillo@.streamnova.com

Cell security was not designed to support visual totals as you described.|||

Hi,

Is there a way around this? Instead of using visual totals, is there some other way to get the right totals?

Cheers

|||

One approach, which seems to work in limited testing with Adventure Works, would involve introducing cell security MDX conditions into the cube MDX script. Basically, a scoped calculation sets the secured cell values to NULL, which will cause the aggregation of those values up the hierarchy to adjust accordingly. However, when cube cells are browsed, the secured cells will still show "#N/A". The limitation with this approach, of course, is that user and/or role logic will be needed, so that unrestricted users are unaffected.

The simplified scenario in Adventure Works was as follows:

For user XYZ, secure the data for Bikes in CY 2003, and rollups in the Sales Summary measure group should reflect this.

The Cell Read Permission expression (for a role with user XYZ as member) is:

Not ([Date].[Calendar].CurrentMember is [Date].[Calendar].[CY 2003]
and [Product].[Product Categories].CurrentMember is [Product].[Product Categories].[Bikes])

And the cube calculation MDX script is something like:

Scope(MeasureGroupMeasures("Sales Summary"),

[Date].[Calendar Year].&[2003],

[Product].[Category].[All Products].[Bikes]);

IF UserName = "XYZ"

THEN this = NULL END IF;

End Scope;