cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dobregon
Post Prodigy
Post Prodigy

RLS produces bad performance

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!



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
5 REPLIES 5
mwegener
Super User
Super User

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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.



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Try DAX Studio
https://daxstudio.org
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Hi @dobregon ,

 

try this...

Copy the DAX query from the performance analyzer.

PerformanceAnalyzer.png

Connect DAX Studio to the model using the RLS

Connect.png

Run the DAX Query with Server Timings

ServerTiming.png

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!