Direct Query with Dynamic RLS

[dpartha]

Hello Everyone,

I have created a Power BI data model in Direct Query mode. The data source doesn't have any RLS, but user email is present as an attribute in fact table. Hence created one role in PBI to implement dynamic RLS using USERPRINCIPALNAME() mapping to email attribute in fact table. While I tested this from PBI Desktop via View AS option, it is working as expected, showing records for the user. Now, from PBI service, it kept on loading and timed out. I made sure the user is added to the role and has only viewer access to the dataset. I am bit surprised to see the difference in behaviour.

For this scenaio, we have few 100 GB of data the table. My question is, does Power BI query (via direct query) the datasource to bring all the data first and then filter in memory for logged in user as per RLS definition? If yes, then how it is working in PBI desktop with such a large volume of data?

Any thoughts or insight on this is welcome.

Many thanks

Regards

Partha

[Anonymous]

Hi @dpartha ,

If you need per-user security implemented with DirectQuery sources, either use RLS or configure Kerberos-constrained authentication against the source. Kerberos isn't available for all sources. 

To troubleshoot this issue, you can try the following steps:
Check the SQL query performance of your data source and optimize it if possible. You can use tools like SQL Server Profiler or Azure Data Studio to capture and analyze the queries generated by Power BI.
Use indexing to improve the query performance and reduce the memory consumption.
Reduce the number of fields or filters in your report to decrease the amount of data queried.
Enable the “Aggregate in storage” option for your report if you are using DirectQuery or composite models.

Solved: RLS with Direct Query - Microsoft Fabric Community

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn

If I have misunderstood your meaning, please provide more details.

Best Regards
Community Support Team _ Rongtie

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

[dpartha]

Hi @Anonymous ,

Thank you for your response. I am already connecting the data source (GCP BigQuery) in Direct Query mode. Also implemented RLS with USERPRINCIPALNAME() and mapped to emailID attribute present in source table. It is working and showing the appropriate results when testing thru PBI Desktop using "View As" feature. However it didn't work in PBI service while replicating the same scenario for the same user. The visuals keep on loading and timed out evetually.

The user has been provided only Viewer access in service and added to the RLS role as per standard procedure. I am curious to know in PBI service, whether the direct query carries the user email to data source thru the RLS or it loads all the data in memory first, and then filters the data as per dynamic RLS? And finally, why this experience differs with PBI Desktop and Service?

Hope I was able to describe the problem statement / questions appropriately. Kindly help to resolve the issue.

P.S. The source table is quite big, it will never work if all the data needs to be loaded.

Thanks in advance.

Best Regards

Partha