Dynamic RLS via Hierarchy in Power BI

I recently was working with a client and we were trying to implement row level security for a sales territory hierarchy. I am posting this blog because it wasn’t quite as intuitive as I would have thought.

hierarchy

For this blog post, I will work with the very simple AdventureWorksDW2012 (download here) DimSalesTerritory table. This sales territory dimension only has 11 rows but imagine if the hierarchy breakdown had 10k or more. I will explain a couple of iterations I took on the problem.

02-DimSalesTerritory

This blog post will be more “terse” than usual. So some of you may be very happy to not have to endure my hyperbole on the topic at hand 🙂

One mention I will make is that if you are trying to implement Dynamic Security, the “Securing the Tabular BI Semantic Model” white paper provides an in-depth look at the patterns being shown below.

GitHub Repo

If you want the two PBIX files I use below to more easily follow this solution, please find them on my GitHub repo

https://github.com/realAngryAnalytics/angryanalyticsblog/tree/master/DynamicRLSwithHierarchy

Simple RLS Example

So, the issue we were trying to solve was the fact that we had a table that showed the employee relationship to their respective Sales Territory. This is a quite simple problem to solve if there is a 1 to 1 relationship between employee and Sales Territory. If an employee can only access Sales Territories they are associated with, then the “security” table would look like this:
03-Security

For this table I simply used a calculated table derived from the AdventureWorksDW DimEmployee table. I purposely excluded all the records that had a Sales Territory of N/A. See the DAX for this table below.

A quick look at the data model

04-datamodel1

Now, simply go to your “Manage Roles” option on the modeling tab of Power BI Desktop:

05-manageroles_nav

…and for the DimSalesTerritory table enter the following DAX in the Table Filter DAX Expression

Name the above role as “Basic Security”.

The above pattern is easy to pick out in the white paper mentioned above. The USERPRINCIPALNAME() expression ensures that DAX picks up the email address of the individual logged into powerbi.com

When we select “View as Role”, check the “Other user” box to use a user other than who is logged into the machine  (because your email address is likely not in the adventure works DW sample database). Enter “david8@adventure-works.com”

06-view_as_role_dialog

When back on DimSalesTerritory in the table view, we can now see that the only territory allowed in this view is the Northwest United States

07-DimSalesTerritory_filtered

We can also review some simple visuals including a geography hierarchy bar chart, total orders over time line chart and a card. (This is all using the FactResellerSales data)

08-visuals_filtered_01

When we remove the Role filter, we can see all of the geographies re-appear

09-Visuals_unfiltered01

Employee Hierarchy RLS example

The above is a pretty straightforward dynamic row level security example.

Now let’s assume an example that all the employees in the United States territory have a manager. Let’s call him Bob. Bob should be able to see all the records for the United States. This includes the Northwest (1), Northeast (2), Central (3), Southwest (4), and Southeast (5) territories.

If we do a simple right click “Copy Table” on the Security Table to Excel, we can add the necessary rows for Bob to have the access he needs to see all rows for the United States.

10-excel-security

What is the problem with this approach?

For our simple AdventureWorks database, this is fine because of the small number of records. If we consider a real world retail store example, each sales territory may be broken down into 30 districts, and each district may have 20 stores. We may have 20k employees as well. So our Hierarchy would look like this:

Country -> Region -> District -> Store

6 Countries -> 10 Regions -> 300 Districts -> 6000 stores

The CEO of the company would have to have 6000 entries in the security table, a region manager would have to have 600 entries and so on. This table starts to get extremely bloated. In my real life client example we estimated that the security table would have between 10-50 million records in it.

A better approach

Ideally, I want the CEO of the company should only have to have 6 records for the highest level of the hierarchy. In our example above of Bob that should have access to everything in the United States, he would only have to have 1 record for the United States. This greatly collapses our bloated security table and if the company has 20k employees, if you include some multiple entry managers (someone who may manage 2 districts, or someone who manages 2 or 3 stores), you would only have around 25k records.

A security table would now look like this:

11-excel-security-2

We have added a column for CountryKey (100 represents the United States) and we only have one record for Bob instead of 5.

Applying this Solution

keepcalm

A few things need to be done to our PBIX file to implement this. First, we need an additional dimension table for each additional hierarchy level added to our security table above. I have replaced the Calculated Security Table and a new Country table with an RLS_tables.xslx spreadsheet that I have included in the GitHub resources above. It has a second sheet for “Country”.

13-excel-country

Note: We would need a sheet/dimension for District and Store if we were to implement a larger hierarchy as I discussed above

To make this new method work, the FACT table (in this case the FactResellerSales) needs to include a column for each level in the hierarchy that may need to be used to filter. Therefore, CountryKey has to be added to the FactResellerSales table. This is best served from your underlying database, but to keep this entire solution in Power BI Desktop, I have added a cross reference table to the RLS_tables.xslx spreadsheet that links SalesTerritoryKey and CountryKey.

14-excel-xCountrySalesTerritory

Pay attention to the new data model. Note that the relationship from DimSalesTerritory to the Security table has been removed. This change will be discussed a little later.

15-datamodel2

To get the CountryKey to exist in the FactResellerSales table, add the following calculated column using LOOKUPVALUE() function

With CountryKey in the FACT table, now create single direction relationship from Country table to FactResellerSales via the newly added column. You would repeat this process for additional dimensions such as District and Store in a real world scenario.

16-datamodel-CountryKey

Handling the Dynamic RLS

For this most important part, I have to give credit to Kasper De Jonge (@Kjonge) for helping me. Removing the relationship between the Security table and DimSalesTerritory was key. We now have two paths to securing the FACT table and trying to navigate Active/Inactive relationships got too messy and I was unable to resolve.. This is where using LOOKUPVALUE() and MULTIPLE active ROLES at the same time worked.

In Manage Roles, create a new role named “Country Security” and use the following DAX.

 

The LOOKUPVALUE() function searches the email addresses in the security table for the currently logged in person and will find what CountryKey that they have access.

Create another Role named Sales Territory Security and use the following DAX.

 

Quite similarly, this is finding the SalesTerritoryKey that the person has access.

Now when we view the report as Bob, we are seeing all of the data for the United States with a single CountryKey in the security table instead of having to have a row for every territory.

17-viewasrole-02

18-visuals-filtered-final01

What makes this especially powerful is that having multiple roles (one for Country and one for DimSalesTerritory) creates an “OR” condition between them… I am going to modify the Security table in my excel spreadsheet to include for Bob the ability to see data for the United Kingdom sales territory also.

19-excel-security-option2

Now Bob sees his Country information plus an additional sales territory.

20-visuals-filtered-final02

Finishing Up

To round this out, we probably want to hide the Security table from being viewed in the report and also add a FALSE() DAX expression on the Security table in the two roles that we created so that if someone were to do an “Analyze in Excel” there would be no exposure to the table.

21-hidetables

22-manageroles-end

 

Conclusion

That is it! we have just solved a hierarchical Security structure being used with Dynamic RLS in Power BI. I didn’t feel that this topic of using a hierarchy in dynamic security was well covered in other posts so I hope that this helps.

On a side note, if anyone has a good tutorial on how to better format wordpress sites (I feel like i am SHOUTING with this font) and restrict the numerous spam i get, please tweet me at @angryanalytics 🙂

 

 

20 thoughts on “Dynamic RLS via Hierarchy in Power BI”

  1. Great article – and very useful for the work I am doing – thanks so much!
    One minor point: you list the code for the DAX expression for DimSalesTerritory – but it seems incomplete…? Of course you can pick it up in full on the Manage Roles screen.

  2. Good article. Is it also possible to dynamically show either the country or sales territory name based on the role?

  3. Hi, Good article.

    Taking advantage of your example, it is possible after that I have already applied the RLS to restrict access to data only for a specific customer (MOBILE), i can show in the another chart, your sales position in relation to the others clients?

    1. With DAX nearly anything is possible. You may want to create a calculated table using SUMMARIZCOLUMNS that contains all the sales people and their rankings compared to their peers. That table you would not restrict access to. If you don’t want them to be able to see that much information, you could hide that table and then create a measure on top only giving them their ranking compared to others. I have not tried it, so i can’t say this with certainty, but something like that should work.

  4. This is Great! Is there a way that this can be extended to direct query mode? the Lookupvalue doesn’t work in direct query mode. 🙁

    1. hmmm.. good question. Are you able to go to “Options and Settings” -> “Options” -> “DirectQuery” and check the box to allow unrestricted measures? Depending on how big your data set is, this may work just fine.

  5. Thanks for the DAX expression. I almost have it working. I just have two questions:

    1) When I apply this method and view a test Dashboard in My Workspace the RLS is not being applied. If I go to Dataset > Security and use “Test as Role”, I can see the report being filtered, but it doesn’t do that in My Workspace. Why is this?

    2) Is it possible to use multiple fields with an AND operator instead of OR? For example: Only show Sales where Business Unit=1 AND Territory=100 (because there might be sales in Territory 100 for a different Business Unit that this user shouldn’t see).

    1. You can ignore the second question. I figured it out. You can create a table with all of the RLS data in it and just make a single security Role on your Dataset that checks against every field that you want to have RLS for.

      For example, a table called tblRLS that looks like this…

      Email Ter BU
      user1@site.com 100 1
      user2@site.com 101 1
      user2@site.com 102 1
      user3@site.com 2

      …could be used with a query called “MasterData” in a single Security Role with DAX expression of…

      AND(
      MasterData[Ter]=LOOKUPVALUE(‘tblRLS'[Ter],’tblRLS'[Email],USERPRINCIPALNAME(),’tblRLS'[Ter],MasterData[Ter])
      ,
      MasterData[BU]=LOOKUPVALUE(‘tblRLS'[BU],’tblRLS'[Email],USERPRINCIPALNAME(),’tblRLS'[BU],MasterData[BU])
      )

      …assuming that each of those fields (Ter, BU) exist in the MasterData query as well. There’s no need to setup relationships with tblRLS table in PowerBI in order for this to work.

      Obviously you could switch out the AND() function for the OR() function and get the same functionality with a different logical operator (if suit your needs better). Or you can use a combination of them.

      I still don’t know why this doesn’t get applied when I view a dashboard in My Workspace, though.

      1. hey Jason, yes the AND option is very interesting to use with multiple RLS rules as you demo. But to which attribute in which tables did you apply the rule ? As i understand the rule must be applied at attribute (column) granularity ? David

        1. Hey JAson in a similar manner as you answered your own question i too managed to answer mine. The answer is the AND statement is a table role and not added at attribute level. (a bit like the blank() command was also added to the security table in the blog demo).
          Creating this RLS dynamic role on the fact table with a non connected RLS-Security table using AND with LOOKUP solves the problem…great. This technique by-passes the RLS role filters on the dimensions.
          Would be keen to hear if others agree this is a good technique as i had always thought it was best to filter on the dimensions but if we need to AND the RLS filters it must be done on the Fact table. ….

      2. This is great.

        I suppose there will be a performance impact with using LOOKUPVALUE, in the absence of relationships.

        Can this be achieved with relationships? In my case, I have multiple security tables (at different grains). One security table with one attribute to filter on and the other with 2 attributes (not hierarchical and hence need a AND condition).

        1. It has been awhile, but i think i had to do lookupvalue because i had multiple dimensions being associated with the security table. This created inactive relationships that could not be transversed properly.in my case because i was potentially saving millions of entries in the security table (each region manager may have 500 rows of stores if i did everything at the lowest grain) the lookupvalue is likely faster. If you only have one dimension per security table, a relationship should work.

  6. How to apply RLS on multiple fileds say Business_Segment,Business_Sub_Segment,Region,Country at once ?
    1.My excel has all the above fields with level of access in it .
    2.Report has all the above fileds in it
    so now i want to filter the data Dynamically based on login of user .

    I tried to write the DAX as per below but no luck (only for two fields i have tested )

    [Region] =
    LOOKUPVALUE(SER_SA[Region],
    SER_SA[Region],
    [Region],
    SER_SA[Employee],USER())

    && [Business_Segment] =
    LOOKUPVALUE(SER_SA[Business_Segment],
    SER_SA[Business_Segment],
    [ASI_Business_Segment_Description],
    SER_SA[Employee],
    USERNAME() )

    Here fields starting with SER_SA is a table from Excel and others are from report query table .

    Please suggest me on this .

Leave a Reply

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