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.
MDX Expression:
[Reseller Geography].[Geographies].CurrentMember.Level .Ordinal <=2
- 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 
hi, I am new in ssas development.
I want to block a user group from viewing the all measures.
For this i had created a role and in role’s –> Cell Data –> in allow reading cube contents, I put the MDX as,
{NOT Measures.CurrentMember IS [MeasureNameHere]}
It works fine at summery level but when i view detailed report. It took so much time and performs the join operation and shows the repeated values in report.
I might be missing something in my query. Please guide.
Thank you in advance
Vijay, in this scenario instead of securing cell data try dimension data security. Go to Roles->Dimension Data. In the Dimension drop down you will see the measure group name listed beneath cube. Select the measure group and tick the measures you would like to block or allow.
Hi Benny, I got a question on Default Member MDX expression in Dimension Security. From what I saw when implemented, this behaves like a slicer on the data being browsed. Example, when i set the slicer as Queensland in the above example and browse the fact on some other dimension, I would see the fact corresponding to Queensland. But this flexibility is actually kind of mixed up, since we have access to NSW member, but Fact corresponds to Queensland. So i think it would make sense for MS to restrict the Default Member to allow members only which are within the Allowed set. What is your view on this?
I guess the default member as such is much more relevant if using the denied set option instead of the allowed set option. Allowed set in most cases resolve to a single member like an employee, a facility, an office or a region and default member is not required in such cases. I would agree with you that it would be good to enforce that default member (if used) has to be a member that is either in the allowed set or not in denied set.
Hi, I have big problem understanding these different features. I have a role that should only see three countries (EST, LIT, LAT). Then I started there was a Dimension Data set and that looks good (we use Excel to browse the cube). But the problem is that I would like it to be more like a filtering, even if All is choosen or the dimension is not used the result should only be calculated from these three countries. Have tried Cell Data but with no success. Could you please explain these things some more for me.
Hi, Cell Data Access and Dimension Data Access defines security at cell and member level respectively based on the MDX expression used in their security definition. Since they are at cell/member level, they define the granularity of the data that a user can drill-down. They do not function as cube level filters. That’s why you will notice that they function only when secured slicers are used for browsing. Try using SCOPE sub-cube function in your measures if you like to restrict them to different countries.
Hi Benny,
I am implementing a excel analysis tool but i need to implement something like the Dimension Data Access but i need to pass the hierarchy member by parameter. For example, i have a parentchild hierarchy by user, this parent child has managers, supervisors and employe. If one supervisor of Canada download the cube file he only can view the sum of measures of his employers. In general i need to implement a parametric Dimension Data Access. Do you know how can i achieve that ?
Thanks
Carlos
Hi Carlos, if you include country as attribute to user dim and define dimension data access on it – does that meet your requirement ?
Hi Benny,
One simple question. What’s de diference between setting Dimension Data Access on a Shared Dimension vs Dimension Inside the Cube??? I’m getting in trouble when Setting Visual Totals only at Shared Dimension. So I need to replicate dimension data access mdx expression at Dimension Cube level.
Is your dimension role playing ? Role Playing dimensions can be secured only at cube level https://bennyaustin.wordpress.com/2009/12/29/securing-role-playing-dimensions-in-analysis-services