Performance Issues when to many RLSs are defined

[teodorajuduc]

Hi Everyone,

I am debating with the client to create RLS for each program portfolio and they do not know yet how many portfolios they will be having in a long run. I know that Microsoft says that too many RLS defined can lead to performance issues, but I cannot find anywhere what "too many" really is. I think that the client should not have more than 50 portfolios.

Does anyone know how many RLS defined is too many? What are the performance issues?

Thanks in advance,

Teodora

[v-janeyg-msft · Accepted solution]

Hi, @teodorajuduc 

If your RLS is too complicated and affects more dax functions, then performance may be affected. It also depends on the performance of your data model. If only 50 users, it should be no problem.You can access as a user, it doesn’t matter if it’s not slow. 

But in the dashboard tile refresh, it will be affected. You can refer to the content of the document:

By default, Power BI maintains a single cache for every tile, but if you use dynamic security to restrict data access based on user roles, then Power BI must maintain a cache for every role and every tile. The number of tile caches multiplies by the number of roles.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[teodorajuduc]

Thanks for your answer. The RLS are simple DAX filters on a single column. I will keep your advice in mind and try to test the performance by creating a few RLS and check the behaviour.

[lbendlin]

what are the rules behind these roles? simple filters on a single column, or very complex DAX expressions?

If your client doesn't know the eventual size of the list then you may want to consider outsourcing the RLS computations to a reference table that you then incorporate into your data model as a top level dimension. That will allow you to drastically reduce the number of Roles required.

[Don-Bot]

@lbendlin 

I know this comment is a few years old but I hope you see this.  

How would I reference a table that has RLS applied to it?  I have a model with 25 dimensions and if I put RLS on all of them it fails.  However, this idea seems like a great one I just can't get it to work.  I created a dimension that is joined to the RLS table.  I then referenced that table to join to my fact table.  Problem is my dimension isn't being filtered by the RLS...

[lbendlin]

Here's the preference of RLS implementation

Best:  No RLS.  Do you really, really need it? Is your data very sensitive?

Good: Dynamic RLS, with rules outsourced to reference dimension table that controls the protectables.

Not so good: Static RLS with rules defined in the semantic model.  Too much maintenance effort.

[Don-Bot]

@lbendlin  Do you have any examples of the Good scenario above?  I've tried googling for it but I'm not sure if I'm finding the relevant articles.

[lbendlin]

Dynamic Row Level Security with Power BI Made Simple - RADACAD

[Don-Bot]

Thanks @lbendlin been through that which works great for a simple model.  Not so much for a model with 20 dimensions and a fact where the main RLS table can't be joined to the other dimensions due to "ambiguity" errors.  

[teodorajuduc]

These are basic rules, a filter on a single column.

The scenario is more like: a user is part of 1 or mutiple portfolios and when he logs in he should see only the entities which are part of his portfolio. The client already has configured CWS groups which works more or less like AAD groups. By having a RLS for each portfolio we can assign a CWS group to each role. Is there any other way to implement this scenario?