Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Bi-Directional Cross-Filtering in Analysis Services Tabular 2016 for Dynamic Row Level Security - Part 2


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

With the release of SQL Server 2016, Analysis Services Tabular introduces a very important new feature: Bi-direction cross-filtering. This feature allows us to support more complex models much easier without the need of creating extra - often complicated - DAX measures. In part 1 of the tip, we introduced how you can set-up many-to-many relationships using bi-directional cross-filters. Now we'll discuss a slight alternative use cases: dynamic row-level security.

Solution

Dynamic Row-level Security

A common scenario is to secure the data according to the privileges of the user viewing the data. For example, users of a specific department can only see sales data for Europe, while users from another department can only view sales data for the United States. When a user views the data, it will be already pre-filtered by the model. If another user takes a look at the same report, he/she will see another set of data, depending on the privileges. In order to set this up we need to add two tables to our model. The first table is a distinct list of the users who have access to the data. The other table is a bridge table containing a list of all of the categories a specific user can view, along with the domain login for the user. Keep in mind the Power BI service uses the email address (the UPN) instead of the traditional domain login. The domain login works fine for local SSAS Tabular instances or when you are working with local users (as I do in this example).

m2m security bridge table

The list of distinct users is simply extracted from the bridge table using a view:

security users view

After importing the bridge table and the view into the model, we now have the following structure:

Let's find out how we can deal with this issue. The entire model has the following structure:

security model

Since those two tables are helper tables to implement row-level security, they are both hidden from client tools. It is now easy to see the set-up for dynamic row-level security is a variation on the many-to-many relationship pattern we discussed in part 1 of the tip. Here we also need to set the relationship to bi-directional. If we don't, row-level security won't take effect since the fact table isn't filtered. Let us configure the security first. In the menu, click on Model and then on Roles... This will bring us to the Role Manager. Here we can create a new role and specify a DAX filter on top of the SecurityUsers table:

role manager

In the members tab, we add the users to the role.

role manager add members

If we would use a front-end tool to analyze the data using UserA, we get the following results:

security fail

We need to change the relationship between the Product Category table and the bridge table to bi-directional and make another additional change as well: we need to configure the relationship to apply filters from row-level security.

security relationship config

When we the relationship is properly configured, we get the results we were looking for (screenshot shows data for UserA):

Accurate results with the correct security

You can read more about row-level security in the blog post Dynamic security made easy with SSAS 2016 and Power BI by Kasper de Jonge.

Conclusion

In this tip, we have shown how the bi-directional cross-filtering feature of Analysis Services Tabular 2016 makes implementing dynamic row-level security easy. In the next part, we'll take a look at another use case for bi-directional cross-filtering: measures on top of dimensions.

Next Steps
  • If you want to check out the Tabular model shown in the tip, you can download it here. Make sure to change the connection strings to the source data.
  • Check out the first part of this tip, where we implemented many-to-many relationships.
  • You can find more Analysis Services tips in this overview.
  • For more SQL Server 2016 tips, you can use this overview.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools