Row-Level Security on a DirectQuery to Power BI dataset composite model: My Findings

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Row-level security in DirectQuery to Power BI dataset model

With the DirectQuery to Power BI or Azure analysis services dataset, there are some limitations on the way that RLS (Row-level security) works. Considering that I have written a book and many blogs on this subject, It was fair to have a look at how this works. In this blog and video, you will learn about my findings, limitations, and workarounds.

Prerequisite

To understand the concepts of this blog and video, it is important to know what the DirectQuery to Power BI dataset is, and also to know what the Row-level security is and how it can be configured. I recommend these resources:

Row-level security in the source Power BI dataset

One of the first thing I found was that; the RLS configuration of a Power BI dataset, remains in that dataset. It cannot be edited or changed in the chained dataset (A chained dataset is a dataset that uses another dataset as a DirectQuery).

Here I have a Power BI dataset with RLS configuration in the model:

Static RLS defined in a Power BI dataset

The above dataset is published to a workspace in Power BI service, and users configured to have access to the roles (Please note that for simplicity, I built my demos with static row-level security. Same rules applies for dynamic RLS).

users assigned to RLS roles in the Power BI service

Then I created a new Power BI dataset with a DirectQuery to the dataset above, and with another table imported.

Chained dataset: with two tables from a DirectQuery to Power BI dataset and one imported table

In the configuration above, the FactInternetSales is an imported table and the other two tables are DirectQuery to the previous Power BI dataset (Which has the RLS configuration). As you can see, in the RLS configuration, I can only configure the tables that are imported, not the tables that are DirectQuery to another Power BI dataset.

In a chained dataset (DirectQuery to Power BI dataset) you can only configure new RLS rules on the imported tables.

This means that you cannot edit the RLS configuration of the DirectQuery tables. From my point of view, this makes absolute sense. Because the configuration on the source means that users of this dataset should see a partial view of the data. If the underlying dataset can change the RLS in the source dataset, then that would make the whole security configuration useless.

The user’s view

The user’s view of this new dataset would be still filtered. Even though the user would require Build access on the original dataset, still the view access would bring RLS rules down to the user. However, for the imported tables, the story is different.

To understand how it works, let’s have a closer look at the data model:

The RLS configured in the original Power BI dataset

The RLS configuration in the original dataset, will filter the DimSalesTeritorry table. Considering that we have the relationship one-to-many and single-directional from this table to the other two tables, It means that the other two tables should get filtered too. However, the experience is different. This is what the user will see;

The view of the user on a chained Power BI dataset with the RLS configured in the source dataset

FactResellerSales table, which is a table in the source dataset, does get filtered properly, and the RLS filter propagates correctly. So both the total ($53.61M) and the dark blue column in the chart shows correct result.

FactInternetSales table, which is the imported table, acts differently. If the value of that table is used in a chart sliced and diced by a field from DimSalesTerritory, then we see the filtered data correctly. The light blue in the above chart.

However, if the value is used without something from the DimSalesTerritory, then we see the entire tables, data, which is $29.36M. This means that even though, there is a relationship between the DirectQuery table and the imported table, the relationship doesn’t pass the RLS configuration. This is, of course, along with what Microsoft’s documentation explained already.

Workaround

This limitation might be lifted in the future. However, for now, if you want to get the RLS filters to navigate down to other tables in the model even to imported one, you can force the relationship with a measure like this:

I have created a DAX measure using TREATAS function (there are other functions you can use too) as below;

internet sale meausre using treatas = 
CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    TREATAS(
        VALUES(FactInternetSales[SalesTerritoryKey]),
        DimSalesTerritory[SalesTerritoryKey]
        )
    )

The above measure if used in the report, will returns the filtered data even if used in a visual without DimSalesTerritory;

Using measures to pass the filter from RLS defined in DirectQuery tables into imported tables

This workaround however, is just changing the total number, doesn’t get the table filtered. If you want to filter the table, you might need to add some RLS roles for the imported table too. That might require a blog post for itself, this post is mostly about findings than the workaround.

This is not a solution, of course. And this is not saying the RLS doesn’t work in a chained dataset. It is just the way the relationship between a DirectQuery table and an imported table works that make things a bit different. I am hopeful that this limitation is lifted soon by the Power BI team. However, until then, you have a workaround.

Share your thoughts with me and let me know if you face any RLS scenarios that doesn’t work in the chained dataset.

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

2 thoughts on “Row-Level Security on a DirectQuery to Power BI dataset composite model: My Findings

  • Great post Reza. Just to confirm you mean putting RLS on factinternetsales will allow to filter dimsalesterritory and in turn factreseller as well which will serve our purpose.

    • Hi Triparna
      the DimSalesTerritory and FactResellerSales are already getting filtered. but not filtering the FactInternetSales
      adding an RLS filter rule in FactInternetSales will complete the solution.
      Cheers
      Reza

Leave a Reply

%d bloggers like this: