Dynamic Row Level Security with Organizational Hierarchy Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-11-23_23h59_24

In previous posts, I covered some methods of Dynamic Row Level Security including Row Level Security user based, With Manager Level Access, and also with User and Profiles as a many to many relationships. In this post, I’m going to cover another common scenario for row level security; Organizational hierarchy for security. Through organizational hierarchy the approach is that each employee should have access to his/her own data, and manager should have access to employee’s data, there might be another higher level manager as well. every person should have access to all employees under him or her. In this post, we are going to cover this method of security in details with Power BI. 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

Every company has an organizational hierarchy, in many scenarios employees needs to be authorized to their data records only, and to the data of people whom they are managing. Here is an example of organization chart;

2017-11-23_23h59_24

In the organization chart above; Bill should see only one record of data. Mark should see three records; 2 records for himself, and 1 record from Bill (because Bill is reporting directly to Mark). Leila should see 4 records; one record for herself, 2 records for Mark, and one record for Bill. This is how the hierarchical organizational row level security required to work.

User Table

Here is the sample data in User table;

2017-11-24_00h04_38

as you can see we have two main columns; ID of the employee, and the Manager ID which points to the record which is the manager’s record.

Sales Transaction Table

For every employee there might be one or more sales transactions in the transactions table;

2017-11-24_00h06_10

Diagram of the model

two tables here create a very simple diagram as below;

2017-11-24_00h07_31

 

Sample Report

Here is a glance of sample report; it has all records from all employees as well as the organizational hierarchy;

2017-11-24_08h00_37

Path Functions in DAX

For implementing row level security in this scenario, one of the most common ways is using Path functions in DAX. Path functions are powerful functions that can navigate through an unknown level of hierarchy based on an ID, and Parent ID structure. the structure of your data table usually is constructed based on two columns; ID and Manager ID as below;

2017-11-24_00h14_49

To learn how path functions are working, let’s explore couple of these functions;

Path()

This function will go through an ID, and parent ID structure, and reveals the whole hierarchical path into a string type delimited style. to use this function you can simply create a calculated column in the user table with below expression;

2017-11-24_00h18_22

This function will give you the whole path for the hierarchy with a delimited text value. The id of every employee in the path is separated in this text by a vertical line (|).

PathItem()

The PathItem() function will give you the specific item in a path. if you want to see who is the manager level 1, or level 2 or 3, you can use PathItem to fetch it. Here is an example;

In the code above; 2 is the index of the level (2 means the second level of management), and 1 is the data type of output (1 means integer).

You can then combine this method with LookupValue function to find out name of the person in that level;

This means you can create calculated columns for every level of organization hierarchy;

2017-11-24_00h41_49

PathContains()

Now the important function of this post is PathContains. PathContains will check if an ID exists in the path or not. This is the function that you need to implement row level security. All you need to find out is the ID of the person who is logged in. We already know how to get the email address of the person who is logged in, we use UserName() or UserPrincipalName() function for it.

Find out the ID of person Logged in

You can use a Filter function and Iterator function in DAX to find out who logged in to the system, filter function for filtering the user with the PrincipalUserName() function, and Iterator function fetched the ID of that remaining record.

Here is how Filter Function used to fetch the logged in user’s record;

After finding the record for current user, you can use MaxX or MinX to find out the ID of that user;

and finally you can now use this ID in a PathContains functions to check if user’s ID exists in a path or not;

You need to add this logic as a role in User table;

2017-11-24_08h03_54

This DAX expression will check the full path of organization hierarchy to see if there is any records in user table which has this user ID in their Path column or not.

Testing Result

As a result if you switch to that user, you will see only logged in user with records related to him/her

2017-11-24_08h05_56

 

There are other methods of implementing such logic, you can use other functions and expressions to find the current records ID. This post explained one way of doing this. As you can see in the above screenshot, Reza only has access to see records for himself, Amy, and David (his direct reports), and Lindsay (who reports directly to Amy).

Summary

Applying row level security has many variations. In this post, you’ve learned about how to use organization hierarchy and Path functions in DAX to implement row level security based on a hierarchy. 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;

Dynamic Row Level Security with Users and Profiles

When each user can be part of multiple profiles, or each profile can have many users. This many to many situation creates a different approach for row level security. This post explains in details how that method of security can be implemented.

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 *