Forum Discussion

gabriel23's avatar
gabriel23
Frequent Visitor
1 year ago
Solved

Row Level Security with Partitions

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

audreygerred's avatar
audreygerred
Super User
1 year ago

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.