Forum Discussion
Bad performance RLS for non-admin users: what can be done?
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"
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?
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.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.