Row Level Security with Partitions

[gabriel23]

I have a table FactUsers. with fields for Date, CompanyID, GrossCash and 20 other fields. and a measure named TotalGrossCash which is the sum of all the grosscash.

• this table is partitioned by year-month (202401, etc) based on the Date field
• i have row level security set up on CompanyID.
• i have a dimdate table. this is linked to FactUsers based on Date field
• I have a dimcompany table
• i have a role  - this has an RLS filter on dimcompany
• Import mode (not directquery)

when a user belonging to companyID 1 queries the table for April 2024 data, what exactly happens behind the scene?

• does the engine have to look at the entire table to first filter for records belonging to companyID 1 (the RLS part) or does it immediately start looking at the Apr 2024 partition? Basically, is it capable of still quickly going to the single partition even though RLS needs to be applied?

• Is there a way I can easily verify this in DaxStudio or other? 

[Anonymous · Accepted solution]

Hi, @gabriel23 

In Power BI's import mode, when a user queries a specific partition data and applies row-level security (RLS), the Power BI engine applies partition filtering (such as Date = '2024-04') before applying RLS's CompanyID filtering. The engine directly targets the 202404's partition and scans only the data for that partition. RLS filtering to further filter CompanyID = 1 in the partition data.
Because partitions have physically split the data over time, the engine doesn't need to scan the entire table, only a subset of the target partitions, and performance is still efficient even if RLS is present.
If you want to use DAX Studio to analyze queries and observe the number of rows and partition filters for VERTIPAQ SCAN, you can capture queries in DAX Studio. Run the following DAX query:

EVALUATE
SUMMARIZE(
FILTER(FactUsers, FactUsers[Date] >= "2024-04-01" && FactUsers[Date] <= "2024-04-30"),
FactUsers[CompanyID],
"TotalGrossCash", [TotalGrossCash]
)

You can check the number of rows in VERTIPAQ SCAN in DAX Studio to see if only the rows of the target partition are scanned. Check the WHERE condition to confirm that the partition filtering (Date) and RLS filtering (CompanyID) are in effect at the same time. Check the SE CPU Time in Server Timings to check whether the scan time meets the partition data volume expectations.

Alternatively, you can use DAX Studio's View AS function to simulate RLS to observe the situation. You can check the following link:

View As | DAX Studio

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gabriel23]

where can i see how many rows or segments or bytes it  scanned? I only see the number of rows returned which doesn't tell me if it scanned the entire table or not. 

[audreygerred]

Hi! When a user logs in and filters the data to April 2024 in a model with Row-Level Security (RLS), the process typically involves the following steps:

1. Partition Pruning: The engine first identifies the relevant partition based on the Date field. In this case, it will quickly locate the partition for April 2024.
2. RLS Application: After identifying the partition, the engine applies the RLS filter to restrict data access based on the user’s CompanyID.  This will further filter the data within April 2024 partition to include only the records where CompanyID is 1.

   So, the partition pruning happens first, followed by the application of the RLS filter.

   To verify this in DAX Studio or another tool, you can follow these steps:

   1. Run a Query: Execute a DAX query that retrieves data for the specific user and date range. For example:

      EVALUATE
      FILTER(
          FactUsers,
          FactUsers[Date] = DATE(2024, 4, 1) && FactUsers[CompanyID] = 1)
      

      Check Query Plan: Use the “Query Plan” and “Server Timings” features in DAX Studio to analyze how the query is processed. Look for steps indicating partition pruning and RLS filter application.

   2. Analyze Execution Details: In the “Server Timings” pane, you can see the detailed execution steps, which will show the order of operations, including partition pruning and RLS application.

   3. Analyze Execution Details: In the “Server Timings” pane, you can see the detailed execution steps, which will show the order of operations, including partition pruning and RLS application.