Performance in complex RLS scenario

[rspijker]

Hello,

I have a dataset containing invoice information for multiple customers. The eventual goal is an embedded scenario, where users belonging to a customer can see only their data. The central fact table represents the items on an invoice (millions of records). Each of these items is on an invocie, and each invoice belongs to a customer. Furthermore, these items have additional properties, modeled through dimensions. As an example, say a category and a location where the item was sold (the actual model contains many properties, they are confidential however so I can't share the exact contents here). These properties contain customer-specific information as well. So I want to ensure through the model that only users associated with the correct customer can see the data in these dimensions.

The example model then looks like this:

In the backing database these relationships exist and the model is ensured to be consistent. I've introduced RLS rules on the "Customers", "Locations", and "Categories" tables. This works as expected, data is filtered correctly and everything works.

However, the problem is performance. Due to the number of dimensions we see a very large number of joins in every query, which are unneeded and slow down reports to a crawl. Ideally, we'd only filter the Items through the RLS on the "Customers" table. However, that makes it so that if we display the contents of, say, "Locations" in a slicer all items are shown and not only the customer-specific ones. What I would really want is to have PowerBI not apply the RLS constraints over some of the relationships. For instance, I want RLS to apply on the "Locations" table, and I want *selections* in that table to filter the "Items" table. I do not need the RLS constraints to filter "Items" on every single query though, as this is already accomplished by the RLS constraint on "Customers".

Any ideas on how to handle this?

[lbendlin]

---

For instance, I want RLS to apply on the "Locations" table, and I want *selections* in that table to filter the "Items" table. 

---

yep, that's what is actually happening based on where you apply RLS and how your filter arrows move.  RLS is applied to the outer rim of your data model and flows inwards towards the fact table all by itself.

Have you considered creating a Frankentable "CustomerLocationCategories" where you could then apply RLS only once?

[rspijker]

I understand the default flow of RLS. I'd like to be able to override it because it doesn't "add any security" in my case. As for the combination table, the issue is that my actual model is far more complex. There's about 12 different dimensions akin to Location and Category. Combining them into a single table would add a significant amount of data duplication, this table would grow very large due to the number of combinations. It also feels like it goes against best practices in terms of having a star model and separating our dimensions. I'll give it some additional thought though, thank you for the suggestion.