RLS produces bad performance

[dobregon]

Hi guys,

I'm experiencing performance issues in the bi when I applied the RLS to the report. As an example i have 3 tables:
1. Daily values of sales, estimations, etc per market (million of rows)
2. List of markets with some metadata (not much rows)

3. Users of my platform

In the second table that it is the list of markets i have a column with the concatenate of UsersIds that can access to the market, example Usersenabled (1,3,5,6,9,15,58,...)

So, in the report I have a RLS that taking the USERPRINCIPALNAME it filters the 3º table, showing only the user logged in the report and then in the 2nd table we check in the RLS if the userenabled contains the userid that is filtered in the user table.

Everthing is ok and it works but the problem that i have is that this RLS produces an underperformance that it is impossible to work with this. As an example, the main matrix table with KPIs,values, etc takes 1-2 seconds to refresh the values when i dont apply the RLS but if we use the RLS the same matrix table takes 40 seconds to refresh that it not a solution. someone now a better solution of this?

or anyobody has information about how affect the RLS to the performance of the query or what i'm doing wrong? or other solution that cooul help me?

I'm thinking to create a column with USERPRINCIPALNAME and filter with the userid in the projects as a column like (if the userids is inside the userenabled take 1 and if not is 0) and then put as an filter of the report and with that remove the RLS becasue it will works for me but i have foudn the problem that the userprincipalname and other type of commands can't be used as a columns only measures.

can someonehelp me on this?
Kind regards!

[mwegener]

Hi @dobregon ,

I think the problem is your RLS DAX, which has to filter the UserId from the column with the concantinated UserIds.

Could you share your RLS DAX or a sample pbix?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[dobregon]

hi @mwegener ,

I was looking some weeks about a solutions and i have re done my RLS in order to be more simple that i had month ago. Now i have the question if exists the posibility to check the query of a visual (i know in the performance analyser) but i need to know the memory that the query produces in order to can check the problem.

As i can see, when i use without RLS in the powerbi service normally i dont receive any error in the visuals but when i use RLS some visuals (the biggest visuals) produces the memory problem. With that analysis i will be able to know if the RLS is possible for me or not becasue my dataset of values is very big.

[mwegener]

Try DAX Studio
https://daxstudio.org

[dobregon]

i'm trying with DAX Studio and Tabular Editor but they dont give me the KB that a visuals produces; the only way is to checking the Task manager from windows but it is not a correct way to know the RAM memory that bi uses when i refresh a visual. In addition with that i cant compare with and without RLS becasue the memory problem appears when i acctive the RLS

[mwegener]

Hi @dobregon ,

try this...

Copy the DAX query from the performance analyzer.

Connect DAX Studio to the model using the RLS

Run the DAX Query with Server Timings