Analysis Services security model is role based. Within a role, the cube data can be secured by either Cell data or Dimension data. In this post, I compare the relative merits of each method.
Cell Data Access
- In this method, a set (or range) of cells is granted or denied access based on an MDX expression that gets evaluated for every cell in the slice.
- Cell Data access enables writing generic MDX expression to secure cube data. For example, Adventure Works cube can be secured in such a way that the Reseller measures are available only at the Country and State level but not at city level (Screenshot 1). By writing generic MDX expression, cube security is unaffected by data changes when the cube is re-processed.
Screenshot 1 - Cell Data MDX Expression
MDX Expression:
[Reseller Geography].[Geographies].CurrentMember.Level .Ordinal <=2
Screenshot 2 - Cell Data Security
- Cells that are denied access are masked and appear as #NA in cube browser.
- In this method, Visual Totals gets masked if the cell has no access and displays as #NA
- Cell Data access degrades response time of MDX queries (and hence cube browsing) .This is because the MDX expression that determines whether the cell has access/deny permission gets executed for each cell returned by the query.
Dimension Data Access
- In this method, the cube data is secured by using MDX expressions that returns a set for specific members of a dimension.
- The MDX Expression used to grant or deny permissions in Dimension Data access must return specific member(s) of an attribute. For example, Reseller data can be secured in such a way that only the data for NSW can be browsed (Screenshot 3). This means the MDX expression cannot be generalized.
Screenshot 3 - Dimension Data MDX Expression
MDX Expression:
{[Reseller Geography].[State-Province].&[New South Wales]}
- Dimension Data Access hides the cells that do not have access (Screenshot 4) unlike Cell Data access which displays access denied cells as #NA.
Screenshot 4 - Dimension Data Access
- Dimension Data access provides flexibility to toggle Visual Totals. If the Visual Totals is turned-on, then the totals are calculated only for the visible cells at different levels of the hierarchy. . If the Visual Totals is turned-off, then the aggregated total from the cube is displayed at different levels of the hierarchy (Screenshot 5)
- Screenshot 5 – Visual Totals
- Dimension Data Access has better query response time when compared to Cell Data Access.
Benny Austin
