29 Nov 2023 Hierarchical Row Level Security in Azure Analysis Services Tabular Models

In the data sector, it’s common to restrict access to dashboards to prevent unauthorised users from seeing sensitive data. However, it’s sometimes necessary to further restrict data within the dashboard based on the specific user accessing it. 

To achieve this, Row Level Security (RLS) is often sufficient, but there are cases where managers need access to their workers’ data while being restricted from accessing data belonging to other managers’ workers. In such cases, a hierarchical RLS must be implemented.  

But first let’s explain what RLS is. 

What is Row Level Security in Tabular Models? 

Row Level Security (RLS) can be defined as a security system that limits the rows in a tabular model based on the user’s permission to access the data. It enables the application of dynamic row filters that vary depending on who is consulting the tabular model. 

This is really useful in scenarios where models contain information that needs to be accessible only to certain users, such as sales made by different departments. 

Use Case 

To illustrate the implementation process, let’s create a dummy model for an international company, containing information about sales and the company’s users. 

In this use case, users should be able to access their own sales data and that of their subordinates, whilst being unable to access data belonging to other managers’ staff. 

First, we’ll create a simple RLS and demonstrate why it’s insufficient for this use case, and then show how a hierarchical RLS meets all the requirements. 

Implementation of Row Level Security 

This model will be created in Azure SQL, from which a tabular model in Azure Analysis Services (AAS) is made, using Power BI for data visualisation: 

The model has 2 tables: 

• Sales: A fact table with all the company sales orders, each assigned to a sales user. 
• Users: A dimension table with company user information, like username, email, role, and parent role, the latter identifying each user’s manager. 

To implement simple RLS in a tabular model, first identify the user information table, in this case, Users.  

Next, create a role in the tabular model, for instance using Visual Studio 2019, by right-clicking on the Roles folder in the Tabular Model Explorer window and selecting Roles, as shown below:  

The next window will open: 

Here we create a new role and define its permissions. For the Users table, apply the following DAX code as a filter: 

'Users'[User_mail] = USERNAME () 

This code filters rows where the User_mail column matches the email of the current model consultant. Without RLS, a user could see all the data in both tables, but with RLS in place they only see the relevant data.

So, without RLS, the user with id 5 (Alvaro Borreguero) can see all the data in both tables: 

But with RLS, he can only see his row in the Users table and none of the rows in the Sales table because there are no sales orders assigned to him: 

Implementation of Hierarchy Row Level Security 

Continuing with the previous scenario, suppose the head of sales, Alvaro Borreguero, needs access to all data, but other users, like Julie Chan, should only see sales from their subordinates, John Smith and Mary Oldman. 

The solution is to implement hierarchical RLS. We build a hierarchy in the Users table using the Path DAX function, which creates a recursive lookup between two fields, returning each user’s full hierarchy with the different roles separated by “|”: 

Now we’ll adapt the DAX code to filter the Users table, checking if the user’s role is included in the path field of the users linked to a sale. 

To accomplish this, we use the Pathcontains function, which effectively searches within a given path to determine if a specific value is present the Users table. We will introduce a revised DAX code snippet, which is designed to implement this refined filtering logic: 

Now, the user Alvaro Borreguero can see all the sales as he is at the top of the hierarchy: 

Conclusion 

Row Level Security allows for data access restriction in tabular models, maintaining data confidentiality within a company. It’s also possible to restrict access using a user hierarchy, enabling managers to view their subordinates’ information; this functionality is available in Power BI as well. 

Here at ClearPeaks our advanced Row Level Security solutions, complete with hierarchical access, are designed to protect your crucial data whilst ensuring easy access for authorised team members. Streamline your data management and empower your managers with the tools they need for success! Don’t hesitate to contact us to discover how our solutions can transform your data strategy.