Row and Column (Cell) based security in SSAS Tabular Model

I am working on a BI System for a social care project in a local government authority where I need to achieve cell based security in SSAS Tabular Model.

Problem

• One semantic model needs to be published for all the reporting/analytics needs of the project
• No security is required for measures. So everyone (who ever has access to the cube) can see all the actual figures.
• There are end users (reports consumers) and there is a separate reporting team who is responsible for building/publishing ad-hoc reports as the business needs. For some very sensitive records only few in the reporting team has view permission.
• So on certain attributes of selected dimensions, security can be applied. E.g. If Person is a dimension then all the measures related to Person will be unaffected but if certain persons are restricted then every user can’t see their name and say Ethnicity of the persons and it’s OK to see the Person ID.
• Performance can’t be severely compromised because of security.

Solution

Clearly, SSAS Tabular Model out of the box can’t fulfil all the requirements.

If you apply simple role based security then they can see the all or nothing.

If you apply one of the classic solutions like row based filtering then problem would be:

1. You can’t even know that there exists a record because they are filtered out.
2. Measures would be affected because the records filtered won’t be counted.

Both of the consequences are not acceptable. After a bit of digging around I figured out a solution which perfectly fits for purpose. There has to be a bit of work in the ETL and rest of existing security provisions in the Tubular Model.

A bit of background before we get into details: In the BI system for social care system, consider an episode as an incident or case which always involves a person/child. Some episodes can’t be displayed to all and it depends on the episode type. Again, curtain person data is sensitive and can only be visible to specific people. The measures are mostly counts e.g. Count of episodes.

The pain of deciding who (which user) can see what records is done in the ETL. So considering the episode type in the ETL a user mapping table to the dimension records are written. So there is a mapping table per dimension table on which there are restriction to view certain attributes.

Again there is a duplicate table per dimension table in which security is imposed. Now the trick is to be performed. On the original dimension table for all the records which at least one user can’t see, the restricted attributes are filled with “XXXXX”, basically masked. And for each restricted record there is an entry for duplicate table. Something like below:

We have two tables, Dim_Episode and Dim_RestrictedEpisode.

Dim_Episod

Dim_RestrictedEpisode

As you may have noticed, the dates are being stored as string. This may not be workable if you need to have counts based on dates. In those cases consider having separate date dimensions and have link from fact table. Well, what have been presented here are much simplified ones just to convey essence of the approach. The real scenario as in our case is bound to be more complicated.

Below is the model:

We have user and mapping tables along with fact and dimension tables and those are hidden. Notice that only normal dimension tables are linked (got relationship) with fact tables whereas restricted table which are duplicate tables got no relationship.

Now the last step in regard to building the tabular model: the dynamic role filter. As you might have guessed the filters are on the restricted table rather than on normal dimension tables.

Invoke Role manager and write DAX filter expression which will return True or False based on if the current user can view the row in the table.

There can be 2 ways you may populate and write filter expression accordingly:

1. Make an entry for each user and each viewable record
2. Make an entry for each user and each record which is not viewable

This would be optimised based on the way you will end up making less number of entries in the mapping table.

With this when you access the model from a client; you can have all the counts (measures) irrespective of what permissions you have and at the same time when you are on the drill through report you will see records for which you have permission to.

Well, it’s not the end of the story yet! As in our case most of the reports will be built through Report Builder/Designer, for every report and for every restricted dimension you need to perform an additional step. This is not the only way but one easy way considering the fact that users who are building reports are not technical enough and have no knowledge of MDX/DAX.

Let’s take a scenario – you are building a report for count of episodes based on episode type. Then you need a drill thru report to display all the episode IDs along with contact reason for a give episode type.

These are the steps.

1. Build your overall count report normally.
2. For the drill through report
   1. Build the data set for your drill through with all the logic
   2. Then just pull table records from Dim_RestrictedEpisode into another data set.
   3. For all the restricted columns where masking is applied lookup from the second dataset based on Episode ID. Lookup function in report builder comes handy.

What you have achieved is column based security, so ultimately cell-based security:

• Overall counts are unaffected because the Dim_Episode dimension has no (row based) filter applied.
• You can only view value for restricted columns for the ones you have permission. You don’t have permission to view all the rows in Dim_RestrictedEpisode table.

Now you must be asking me, ‘Report Builder is fine but what about other clients?’ Well, if you want to use PivotTable in Excel then mostly you are interested in counts and not in drill thru stuff. If at all you need then build drill through action using BIDS Helper Tabular Action Editor in the tabular model itself. For the details on other clients potentially in another blog post ☺. By the way, my users are happy with Report Builder.

Obviously there are pros and cons of the approach. In my case there are considerably less restricted records so the solution is fit for the purpose without any performance glitch. Let me know your thoughts…