By: Koen Verbeeck | Comments | Related: > Analysis Services Development
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).
The list of distinct users is simply extracted from the bridge table using a 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:
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:
In the members tab, we add the users to the role.
If we would use a front-end tool to analyze the data using UserA, we get the following results:
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.
When we the relationship is properly configured, we get the results we were looking for (screenshot shows data for UserA):
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.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
