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.
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 Configuration in Power BI Desktop
- DirectQuery for Power BI datasets and Analysis Services. The composite model with Analysis Services. What is it and why it is a big deal?
- DirectQuery for Power BI dataset: How does it work?
- Dynamic Row Level Security with Power BI Made Simple
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:
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).
Then I created a new Power BI dataset with a DirectQuery to the dataset above, and with another table imported.
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.
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 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;
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.
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;
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.
6 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.
the DimSalesTerritory and FactResellerSales are already getting filtered. but not filtering the FactInternetSales
adding an RLS filter rule in FactInternetSales will complete the solution.
The composite model which I am using doesn’t have RLS applied in direct query tables (coming from Power BI dataset). The requirement is that RLS should be applied to the fact table(imported) which will filter all other tables (even tables coming from Power BI dataset), how will this arrangement work?
although you can set RLS rules on the fact table. But I do recommend doing it on the dimension tables. when you apply the rule on the dimension tables they will automatically filter the fact table, but not necessarily the other way around (mainly because of the direction of relationships)
To put more context- I have regions in my fact table on which RLS is applied. I don’t have region on my dim table (direct query). I also have around 20 other dim tables (imported) which is connected to the fact table with One to Many relationship. My queries-
My main table is the fact table, I think in your model the main table is the dim table? DimSalesTerritory? So w.r.t your model, I should apply RLS in DimSalesTerritory and it will filter the rest of the other 2 fact tables as well (imported and direct query)?
ideally, the Region should be a dim table too. Any descriptive information is better to be stored outside of the fact table. That is a modeling best practice.
but for the RLS part; yes, you can apply it on the fact table too. It may not filter other tables that have their directions of relationship to this table. but you can use a method like what I described here to fix that (instead of making them both-directional)
That said, when it comes to DirectQuery to Power BI dataset model, the RLS is not fully supported by switching between tables of different sources. that still has to come later.