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

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.