How to manage attribute permissions on SSAS Tabular model

Question

We have a Tabular model with several Fact tables and several Dim tables. We would like to manages roles so that specific roles will not be able to see members of a certain attribute within a dimension.

So in an HR cube with a "Work Hour" measure - i would like to block a specific role from seeing the "Employee Name" attribute but still show the sum of "Work Hours" to the total employee.

While using multidimensional, i simply used an MDX expression which filters on the "All" member of the dimension thus showing the total but not the members of an attribute. Don't know how to do so in Tabular Model.

Did someone encounter a similar request? Thank you!

GregGalloway GregGalloway · Accepted Answer · 2016-02-07T21:12:32

Yes, Tabular models don't give you the option of disabling "visual totals". So this isn't easy to do. However if you get creative you can do it. If you remember that calculated columns are calculated at processing time without security then you can store the rollups you need ahead of time. Store those rollups somewhere users can read them from even with security in effect. In this case you may need to put the rollups in a separate table, separate from the employees since all rows in that table will be hidden. Here is a full write up: http://cathydumas.com/2012/05/19/row-security-and-hierarchiespart-1/

However in your case since you want to hide all Employee table rows that will cause all related fact table rows to disappear due to security. So here is what I would suggest. First, disable the relationship to the Employee table. Second, pattern your measures after this pattern:

Work Hours := IF(
 COUNTROWS(Employee)>0,
 CALCULATE(
  SUM(FactHours[Work Hours]),
  USERELATIONSHIP(FactHours[EmployeeKey], Employee[EmployeeKey])
 ),
 SUM(FactHours[Work Hours])
)

The logic here is that if your user can't see any employees then don't enable that relationship. If your user can see employees, then enable the relationship.

How to manage attribute permissions on SSAS Tabular model

1 Answers