Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-11-10_13h39_07

The Dynamic RLS topic is getting a good response in my blog post, so I intend to write about other types of scenarios that a security would be required. In this post, we are going to look at another type of security which deals with users and their profiles. Sometimes you have a user part of multiple group (or profile), and also a profile contains multiple users. This Many-to-Many relationship needs to be also incorporated in the way that security works in Power BI. You want all users to see data related to their own profiles. Because this model includes a Many-to-Many relationship, the implementation of that is a bit different from the normal Dynamic RLS. to learn more about Power BI; read Power BI book from Rookie to Rock Star.

Prerequisite

For reading this post, it would be much better to have an understanding of the simple dynamic row-level security with Username or UserPrincipalName function beforehand. please read this post.

Scenario

Imagine Mark is working in a company. Mark is part of the Sales Group in Seattle. He should see all sales transactions related to Seattle. Also; he is part of the sales group in Portland, and should see the transactions details of Portland too. On the other hand side; there are other people which their access is different. David is part of sales group of Chicago, and also Seattle. This situation creates a Many-to-Many relationship between user table and profile table such as below;

2017-11-10_12h22_54

To be able to follow the example, here is sample data sets for each table;

User table

2017-11-10_12h34_13

It is important that user table includes a column for their Power BI accounts (in this example; email).

Profile table

2017-11-10_12h35_29

You might call it branch or store or group as well.

User Profile table

2017-11-10_12h36_14

This table holds the relationship or link between users and their profiles.

Transactions table

2017-11-10_12h38_32

Every transaction is related to a profile. All users under that profile should have access to the transaction which marked for that profile.

Full Diagram of the Model

2017-11-10_12h39_47

The difference between this model and a simple dynamic row-level security is that in this model we have a Many-to-Many relationship, and filter propagation would not be as easy as the simple model.

Filtering Users doesn’t work

Filtering users itself won’t be a solution in this case. Here is a role defined in a user table with DAX filter below;

2017-11-10_13h51_38

This DAX filter on the User table would filter the User Profile, but it won’t filter the Profile table, so as a result, it won’t filter the transaction table.

2017-11-10_13h39_07

The direction of the relationship between Profile table and User Profile table doesn’t allow the filter to propagate from User Profile to the Profile table. As a result, the Sales transactions table’s data won’t be filtered with the DAX filter defined in the user table. There are many methods that you can get this solution working. In this post, I’ll explain two methods;

Cross Filter; or Both Direction Relationship

This method is not recommended especially because of the performance issue, However, I like to explain it as the first method, because it helps to understand how relationships work in DAX. For using this method; you need to change the direction of the relationship between Profile table, and User Profile table to both directional, and also you need to check the option for “apply security filter in both directions”.

2017-11-10_13h48_05

 

After this change your row level security configuration will work perfectly fine as below;

2017-11-10_13h52_42

The diagram of model is now passing the filter all the way to sales transactions table;

2017-11-10_13h53_35

This method is easy to implement. However is not recommended. Applying both direction relationship and the security filter will slow down the performance of your Power BI solution significatnly. On the other hand side; if you have many tables related to each other; depends on the circular dependency, you might not be able to apply both directional relationship always. So I do recommend the next method.

Filtering Data through DAX

Because of issued mentioned in the previous method; I do recommend using this method. This method is basically passing filters through writing some logic within DAX. There are many methods of implementing this logic with DAX. You can write a LookupValue function, Filter function, or many other ways. I’ll explain it with the Filter function.

In this method; you don’t need to change the direction of relationship to be both directions. In this method, you need to find a way to filter data in the Profile table. If you filter the data in Profile table, then the data in Sales Transactions table will be filtered automatically. One way of filtering is to find out first all profile IDs from the User Profile which is related to the logged in user;

Step 1: Find all rows in User Profile for the logged in User

You can use a simple FILTER() function to get all rows from User Profile for the logged in user;

This code will return a sub table from User Profile table which are only rows that is related to the logged in user. from this list, let’s get only their Profile ID in the next step;

Step 2: Get Profile IDs from the List

You can use SelectColumns() DAX function to only select Profile column (which is the ID of the profile);

Now this DAX code will return a table with a single column which is the ID of the profiles.

Step 3: Filter Profile table where the ID matches the table above

we can now use IN keyword to filter data of Profile table just for the logged in user;

This filter should be written in Profile table as below;

2017-11-10_14h10_34

As a result, this method works perfectly as expected;

2017-11-10_14h12_11

Diagram of this method is a single directional relationship. However, the DAX Filter propagates all the way with the logic we’ve written in the role.

2017-11-10_14h13_24

Summary

Applying row level security has many variations. In this post, you’ve learned about how to use Users and Groups (or Profiles) concept and overcome the Many-to-Many challenge for the row-level security. You’ve learned about two methods; which one of them was the recommended approach. There are other ways to implement this scenario as well (with LookupValue and many other functions). Please share your row-level-security scenario down in the comment below if it is different from what discussed. to learn more about other ways of row-level security, read the post series here:

Static Row Level Security

The logic of security is static in the role definition, we call it Static Row Level Security. to learn more about it, read this blog post.

Row Level Security in SSAS Live Connection

In this case, the Power BI Report connected live to an SSAS model. the username will pass through effective username connection information, to learn more about it, read this post.

Dynamic Row Level Security

When you have too many roles, then implementing static roles is not an option. You need to create one role and maintain the logic of security within the data model. This is called Dynamic row level security. To learn more about this, read this blog post.

Manager Level Access in Dynamic RLS

The dynamic RLS example that I explained in this post, does not include the manager level access. Sometimes you need the manager to have access to all data. This post explains how to do it;

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">