Bad performance RLS for non-admin users: what can be done?

[dpollozhani]

Hi all!

I believe I have an RLS performance problem and hope someone can help.

Users subject to RLS are experiencing very long waiting times for simple actions like expanding the hierarchy of a matrix, whereas for me as admin these actions complete virtually instantanously. There are no network differences or hardware differences - so it has to boil down to RLS.

I've been looking through the community and have found that others also have had this same problem, most recently this post.

However, this post doesn't explain how the problem was solved, only that the issue is related to the RLS structure.

What I'm trying to understand is therefore if there is any way to improve the performance of RLS in my model, given the constraints that I will now present.

• We are running Power BI Pro, so shared capacity. Premium is in our plans, but not in the short term.
• Dynamic RLS on UserPrincipalName.
• I have a natural many-to-many relationship between my users and the fact table: a user can have access to multiple companies and multiple business areas. 

I've tried a few different constellations, but this is how my model currently looks:

For a user that has access to all companies and business areas the table "RLS" looks like this:

Therefore, the size of this table (# of rows) is proportional to the #of companies x #of business areas x #of users. At the moment it is not that big (~2.5K rows), but of course it will grow non-linearly over time. Still, I'm not filtering the fact table, which is close to 30M rows, directly.

The RLS rule checks for UserPrincipalName and then propagates the resulting set of "Company business area key" to the "RLS bridge" which just contains the distinct set of "Company business area key". I'm not really sure that this bridge table helps with performance or whether a direct many-to-many cardinality relationship between "RLS" and "Facts" would be practically the same performance-wise. 

I should mention that I have looked at the Performance analyzer with/without RLS applied, and while in some runs it seems that performance is slower with RLS on, (10-50 ms) it does not seem significant. I'm guessing whatever differences I see on my machine, they are magnified in the PBI service.

With all this said, do you believe anything in the model can be changed to improve performance for users? I'm open for all kinds of suggestions, as long as I can achieve a data reduction on company and business area (as there are no other options).

Thanks in advance!

[GilbertQ]

Hi @dpollozhani 

I am going to assume that is those other tables with the calendar and item which are causing the issues. I have had large models over 50GB in size with many RLS roles that perform really fast. Which to me would indicate that it has to do with the way your data is modelled which is causing the performance issue.

I was also with the understanding that the RLS is applied when the user logs in (And why my models perform really fast). It would come down to the DAX having to evaluate the calendar and item table when they are used in the DAX measures or columns put into visuals

[dpollozhani]

Perhaps you are right, and I might have to try removing those snowflakes just to test.

The calendar snowflake is however something I need because it solves a specific issue. The item snowflake is in all honesty unneccessary, just due to me not having bothered joining the tables beforehand.

Regarding your fast performing models with RLS, have you also had this natural M:M relationship between Facts and RLS like I do?

[GilbertQ]

Hi @dpollozhani 

Looking at your image it would certainly be the way the dataset is created with the cross filter direction set to both for most relationships.

This means that when RLS is applied each and every query has to the evaluate both sides of each relationship along with the RLS which would slow it down significantly. I would suggest changing the data model to use the star schema having a many -> one and the corss filter direction = "single"

[dpollozhani]

I've tried changing all model relationships between Facts and Dimensions to unidirectional:

In the .pbix file I'm setting up a matrix with 8 measures (identical to the one I have in the actual report file), and open the Performance analyzer.

First with RLS activated, for a user that should see all companies and business areas. Then no RLS. I realize my initial tests were not thorough enough, because the difference is huge:

Just to confirm that changing crossfilter direction indeed doesn't seem to be the decisive factor, here is the test with all original bidirectional relationships:

The performance is basically indistinguishable. 

So this makes me wonder, is RLS intrinsically yielding bad performance, or is there something I can change in the RLS structure itself to improve this?

[GilbertQ]

Hi @dpollozhani 

Yeah for sure it is the RLS which is causing the slow performance.

This is because the RLS has to filter across all the tables, which have multiple joins and evaluations which is then causing the DAX to take so long.

When there is no RLS those filters are not applied and that is why it is quicker.

As mentioned before I would suggest changing your model to a star schema with a single fact able and dimension tables all having a one to many relationship to the fact table.

[dpollozhani]

@GilbertQ  It's perplexing to me that the RLS filter should be applied by the engine continously. I expected that the filter were applied once either during reload of the dataset or on opening (like is the case with QlikView/Qlik Sense, and perhaps other tools?) - and when the relevant set for a certain user has been established, there should be no need to reapply RLS filters.

Regarding your comment on the model: it is clear from my images that I only have a single fact table, and that all tables linking to it have a 1:M relationship. The only exceptions are the two snowflakes connecting to the calendar and item table respectively. I also just showed that crossfilter direction doesn't affect RLS performance significantly.