Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need help to understand RLS filters

Hi Experts,

I'm using Direct Query mode with SQL Server in my reports. There is Big Data in future. I'm working on APP's owns data. I've been applied RLS based built-in a function where email = USERPRINCIPALNAME().

 

My question is:

when specific use gets logged in, let's say [email protected] so behind scene Power BI RLS brings the filtered data from the database (like select * from table where UserEmail = '[email protected]')

or

it will 1st bring all users data into the model first then apply the user filter on data then show on app?

 

As I'm seeing queries running on the SQL server analyzer it is querying for all users. which seems a big issue for me. Shouldn't it query the filtered data from DB?

 

 

 

Need Help
Show and Tell
Tips and Tricks

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks

      I can't install SQL Profiler on the organizational system.
      Simply answer my question

      How Power BI RLS filters apply to tables? 

      Is it 1st load into the model and then do RLS filter?

      or

      Does it send a filtered query to SQL Server?

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        Hi Anonymous 

         

        I ran a test on my local installation of SQL Server with direct query and RLS, running SQL Profiler and this is the query sent from Power BI Desktop to the SQL Server:

         

        SELECT 
TOP (501) [t0].[EmailAddress]
FROM 
(
(select [$Table].[BusinessEntityID] as [BusinessEntityID],
    [$Table].[EmailAddressID] as [EmailAddressID],
    [$Table].[EmailAddress] as [EmailAddress],
    [$Table].[rowguid] as [rowguid],
    [$Table].[ModifiedDate] as [ModifiedDate]
from [Person].[EmailAddress] as [$Table])
)
 AS [t0]
WHERE 
([t0].[EmailAddress] = N'[email protected]')

GROUP BY [t0].[EmailAddress]
ORDER BY [t0].[EmailAddress]
ASC

         

         

        The role was set up to filter on email address and I viewed the report as [email protected]. So with this setup, the filtering is done at the server. I get the same result when testing from Power BI Service. 

         

        Cheers,
        Sturla

        If this post helps, then please consider Accepting it as the solution. Kudos are nice too.