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 🙂

 

 

Restricting Access in Power BI

“Let your data be free, man!”… Power BI is truly enabling a data culture in organizations across the world. It is allowing every business analyst and data savvy end user to get access to the data they need and allow them to share it easily with others. This gold mine of capability however presents challenges for security administrators that are heroically trying to protect Power BI users from themselves… maybe using the word “heroic” is a bit of an overstatement, but they really are trying to protect YOU from doing something that could in some cases maybe even get YOU fired!

information-security

Power BI default settings are intended for openness of your data… So, if you are tasked with being a Power BI administrator or simply a general security administrator for an organization that needs to protect sensitive data, below are the 5 things you will want to consider doing immediately when starting your Power BI journey.

There is a lot of good documenation available on these topics beyond what i have posted below, but this is a good starting point.

#1 – Publish to Web

publishtoweb

When your first user from your email domain (let’s assume contoso.com) logs into http://powerbi.com they have the ability to Publish any content to the web to be publicly/anonymously available so that it can be easily accessed from a blog or news site. This is a really cool feature for individuals or small businesses but for companies trying to protect PII or PHI data, you need to turn this feature off unless you want each individual to have this responsibility to understand and protect the content they are sharing.

There are several global settings/switches available in Power BI from the Power BI administrator Portal. You can access the administrator portal by clicking the gear icon in the upper right of Power BI after you are logged in.

adminportal

NOTE: this is only available to global administrators of your Office365 or Power BI PRO deployment. If you do not see this option after logging into powerbi.com, then you are not a global administrator or you have not bought any PRO licenses for your deployment of Power BI. Please see the bottom of this blog post on how to enable non global administrators to become Power BI administrators

Once you are in the Admin Portal, there are several switches in the “Tenant Settings” section. Setting the “Publish to Web” setting to “No” will ensure sensitive data cannot be unintendedly leaked to anonymous web connectivity…

tenantsettings

 

#2 – External Sharing

Many ISVs and Client Services organizations want to easily share content with their partners. This allows someone within the contoso.com domain to share data with someone from the northwinds.com domain. If you do not plan to entrust each powerbi.com user to understand what data they can share and what data they cannot, then you will want to turn off the “Allow sharing content to external users” switch that is also shown in the above screen shot.

#3 – Viral Power BI Sign Up

It pains me to even write this, but some organizations that have sensitive data such as PII or PHI data may not want to take the risk that an “un-authorized” or “un-vetted” user could put content in Power BI. Power BI tries to enable users to  be more self-sufficient. Power BI will allow any user within a domain (such as contoso.com) to login to Power BI and automatically be granted a FREE Power BI license. To ensure ONLY users that have been vetted to use Power BI can upload content and use the tool, a global admin will want to run the following PowerShell command to disable viral sign up option for Power BI…

Set-MsolCompanySettings -AllowAdHocSubscriptions $false

For a full description on this feature, please refer to the following article:

https://powerbi.microsoft.com/en-us/blog/ability-to-disable-free-sign-ups-for-the-free-power-bi/

#4 – Conditional Access

Power BI is software as a service meaning it is an application that is fully hosted on the Azure Cloud. By default, you can login to the service from anywhere in the world. For some organizations, this may cause uneasiness. With Power BI (and other Office 365 apps) you can manage where people can login from and also ensure that people are challenged with a multi-factor authentication such as getting a phone call or entering a code prior to allowing access to Power BI. An organization could completely lock down access from any location other than their network. This could prevent anyone from logging into Power BI at home or abroad. Note that this type of strong handed approach could prevent users from utilizing the Mobile App to get access to their data.

For more information on conditional access, please refer to the Power BI Blog post on it here

#5 – Auditing

You may want to turn auditing on so that you can track activity. This is actually done through the Office365 portal and the Power BI admin portal simply redirects you there.

audit

Documentation describing this feature can be found here

 

The above 5 items I feel are the essential Power BI items to consider when starting to allow users into Power BI. Below are a few more items to consider which are more geared towards data governance than security.

Tenant Settings

Two of the tenant settings were called out above. There are several other settings that you may choose to turn on/off depending on your organization.

“Publish content packs to the entire organization”  – this is turned on by default. It allows any person in the organization to affectively share a dashboard or report with the entire organization. You may choose to turn this off so people have to be very deliberate with which groups/individuals that content is shared with. If you keep an active directory group setup for the entire organization, then sharing with everyone is still possible, but more deliberate.

“Export Data” – turned on by default, this allows users to export data from visuals in Power BI to an Excel spreadsheet (CSV). Most of the time, users are already performing this from other reporting tools within the organization, but it can be turned off.

There is also a “Data Classification” setting to allow you to classify dashboards as being “High Business Impact”, “Low Business Impact”, or custom to your desired classification such as “Confidential” or “PHI”.

dataclassification

When an administrator has this turned on, users that publish dashboards have the option to specify the classification and it will show up in the upper “bread crumb trail”

breadcrumb

Users should be trained from the beginning to look for these tags so they can be careful about how they share their content within the organization.

 

Making Power BI Administrators that are not Global Office365 Administrators

If your organization already has Office365 and you are not a global administrator, you will not have access to all the features I have pointed out above. With the below PowerShell command however your global administrator can grant you access to the Power BI Admin Portal to manage the tenant switches.

Add-MsolRoleMember -RoleMemberEmailAddress “tim@contoso.com” -RoleName “Power BI Service Administrator”

This command also requires the Azure Active Directory Module

 

Conclusion

I am an advocate for an open data culture that allows users to take responsibility for the content that they publish, however, I also know that not every organization is ready to entrust that responsibility upon all of their users. For organizations with highly sensitive data the above controls can reduce the security burden of moving to a self-service environment like Power BI.