Need help understanding extra queries generated by enabling RLS

[martinriddar]

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.

[Greg_Deckler]

@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?

[martinriddar]

@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.

.

[martinriddar]

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? 

[Greg_Deckler · 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.

[martinriddar]

@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.

[Greg_Deckler]

@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.