Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
martinriddar
Frequent Visitor

Need help understanding extra queries generated by enabling RLS

I'm very new to power bi so I'm hopefully just missing something obvious but here we go...

 

I'm developing a report where I get data from a single SQL Server database, some tables are imported and some are DirectQuery (BIG fact tables, import is not an option).

I've got one fact table which (among other things) contains the columns system, device, date, timestamp, event and event_value. I have a line graph where I put the timestamp on the x-axis and the event_value on the y-axis and I have single select slicers for the system, device and date columns. This works fine when I don't enable row level security. The queries sent to the database contains the correct filters and get good execution plans.

 

If I enable row level security, an extra query is sent to the database which (to me) makes no sense. The query is fetching all distinct values from the timestamp column and it has no filters except one on the system column which comes from the RLS rule so it is querying the table from the beginning of time until now. Below is an edited version of the query that is sent:

 

SELECT
TOP (1000001) [t16].[timestamp]
FROM
(
(
select [$Table].[system] as [system],
[$Table].[device] as [device],
[$Table].[date] as [date],
[$Table].[timestamp] as [timestamp],
[$Table].[event] as [event],
[$Table].[event_value] as [event_value],
from [fact].[dv] as [$Table]
)
)
AS [t16]
WHERE
(
([t16].[system] IN (15,30,16,31,17,1,18,33,2,34,3,19,35,20,36,4,21,37,5,22,6,23,7,24,9,11,25,26,12,27,13,29))
)
GROUP BY [t16].[timestamp]

 

The RLS rule is on another table "access_list" which contains two columns, user and system, there are multiple rows per terminal and multiple rows per user. The rule is 'admin access_list'[user] = USERPRINCIPALNAME(), this table is in turn linked via the system column to the dimension table "system" (imported) which in turn is linked to the fact table.

 

I've tried to have to have the access_list as DirectQuery, imported and dual, I've tried to connect it directly to the fact table, nothing makes a difference.

I've seen that the same thing happens for table visuals where I show a column from the fact table which isn't aggregated, for example if I have a table with the columns system, device, event and sum(event_value), a query runs that gets all distinct values for the "event" column.

 

I'm wondering if the cause if this is the relation between the system table and the access_list table. Since the cardinality is "many to one" from access_list to system I need to set cross filter direction to both in order for the RLS filter to propagate down to the fact table.

1 ACCEPTED SOLUTION

@martinriddar Yeah, try switching that table to Dual mode. Generally in these scenarios you need to set your dimension tables to Dual mode when your fact table is DirectQuery.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@martinriddar There is also a setting to enable security in both directions if you set the relationship to Both. Not sure I 100% understand your data model, can you post a pic of the data model?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Sorry forgot about the security filter in both directions setting, it is activated.

Here are two screenshots, one of the model and one of the line chart setup, I've tried to clear out the clutter to only show the relevant parts.

2023-12-19 08_49_31-.png2023-12-19 08_46_31-Untitled - Power BI Desktop.png.

One thing that I noticed now is that the line between the "access_list" and "system" tables is missing these little symbols, at first I thought that it had to do with the RLS rule on access_list but nothing happens if I remove the rule.

My Google skills are failing me on this one, can anyone shed som light on this too? 2023-12-19 08_46_31-Untitled - Power BI Desktop zoomed in.png

@martinriddar Yeah, try switching that table to Dual mode. Generally in these scenarios you need to set your dimension tables to Dual mode when your fact table is DirectQuery.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler that worked so I'll accept this as a solution 🙂

However I would still like to really understand WHY it needs to do that query when I turn on RLS. Since the RLS filter is applied "further up the chain" so to speak I don't understand the necessity to run anything more against a table that is at the bottom of the filter chain.

@martinriddar Well, assuming your RLS rule is on a dimension table and it is in import mode then you have a weak relationship between the two tables. This weak relationship means that while the RLS rule can process the row set for the import table down, it's not really certain about how that really relates to the DirectQuery table so it has to make an additional query to be certain. This is ulimately exactly why Dual storage mode was invented. What you are saying essentially by flagging your table as Dual is allowing the RLS rule to be processed in import mode but you are effectively saying as well is that there is a strong relationship between those two tables for DirectQuery purposes as well. Thus, the semantic model is confirming that any filter on the dimension table is valid on the fact table. That's sort of the jist of it. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors