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.
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.
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:
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.
Security Table = SUMMARIZECOLUMNS('DimEmployee'[SalesTerritoryKey],
DimEmployee[EmployeeKey], DimEmployee[EmailAddress],
FILTER(DimEmployee,DimEmployee[SalesTerritoryKey]<>11))
A quick look at the data model
Now, simply go to your “Manage Roles” option on the modeling tab of Power BI Desktop:
…and for the DimSalesTerritory table enter the following DAX in the Table Filter DAX Expression
DimSalesTerritory[SalesTerritoryKey] = CALCULATE(
VALUES('Security Table'[SalesTerritoryKey]),
SUMMARIZE(
'Security Table',
'Security Table'[EmployeeKey]
),'Security Table'[EmailAddress]=USERPRINCIPALNAME()
)
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”
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
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)
When we remove the Role filter, we can see all of the geographies re-appear
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.
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:
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
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”.
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.
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.
To get the CountryKey to exist in the FactResellerSales table, add the following calculated column using LOOKUPVALUE() function
CountryKey = LOOKUPVALUE(xCountrySalesTerritory[CountryKey],xCountrySalesTerritory[SalesTerritoryKey],[SalesTerritoryKey])
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.
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.
Country[CountryKey] = LOOKUPVALUE(
'Security'[CountryKey],
'Security'[EmailAddress],USERPRINCIPALNAME(),
'Security'[CountryKey],Country[CountryKey]
)
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.
DimSalesTerritory[SalesTerritoryKey] = LOOKUPVALUE(
'Security'[SalesTerritoryKey],
'Security'[EmailAddress],USERPRINCIPALNAME(),
'Security'[SalesTerritoryKey],DimSalesTerritory[SalesTerritoryKey]
)
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.
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.
Now Bob sees his Country information plus an additional sales territory.
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.
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 🙂
Thx for sharing.
LikeLike
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.
LikeLike
thank you for pointing that out. I have corrected it. appreciate you taking time to notify me. 🙂
LikeLike
Thanks a lot for the blog post. Really looking forward to reading more.
LikeLike
Good article. Is it also possible to dynamically show either the country or sales territory name based on the role?
LikeLike
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?
LikeLike
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.
LikeLike
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. 😦
LikeLike
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.
LikeLike
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).
LikeLike
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.
LikeLike
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
LikeLike
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. ….
LikeLike
It was FALSE() added to the table role not BLANK()
LikeLike
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).
LikeLike
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.
LikeLike
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 .
LikeLike
Fantastic article, really coming handy for what I needed to do. Thanks for sharing
LikeLike
Super Article ! Vraiment très instructif… 🙂
LikeLike
Great Dynamic RLS tutorial! This is the first useful Dynamic RLS guide I’ve come across.
LikeLike
Hello,
I’ve implemented RLS successfully.
I was wondering if there is a way to have the Basic Security we’ve created applied at all times, hide “Stop Viewing as” in order to achieve that anyone that logs in with his mail and tries to view the report only sees what he is enabled to.
So we want to have one report, having security applied at all times and according to one’s mail he’s able to see only his territory or district etc, without the option to view the entire database.
LikeLike
Great article. I will be experiencing a few of these issues as well..
LikeLike
Thanks for finally writing about >Dynamic RLS via Hierarchy in Power BI – Angry Analytics
Blog <Loved it!
LikeLike
Does this work in Analysis Services?
Did anyone test if this will work with Direct Query?
LikeLike
I hope I can repeat Ryan’s question. Does this pattern work for Analysis Services as well? Do I need two separate roles with Read permissions (Sales Territory Security and Country Security) and adding all members twice under both created roles? Could you please describe how it will work in SSAS?
LikeLike
As security is dynamic, and handle via a DAX measure, there is only one security table with all the users. You don’t need to add users twice. You will have two roles in SSAS just like in the Power BI solution, but they both point to dynamic dax measure to determine access.
LikeLike