| athena no seinto 2006-10-25, 6:01 am |
| 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
|