2

I am currently building up a new data model in the area of sales. In the old data model, which I want to replace, I had very specific row-level security settings for each user: Sometimes, settings can be done with an "AND" filter:

UserXY should have access to all customers from China selling Toys

Sometimes, they are defined as "OR":

UserXYZ should have access to all customers from Europe and to all food products

As the access rights differ from one customer to another, one or more role per user had to be created (if the rights are "AND" based, a additional role has to be created).

In the future, we would like to control the data access trough a table, where the respective objects the user has access to are defined. But I am afraid of possibly not be able to cover AND/OR logic on different dimensions.

Of course MS also has concepts about dynamic RLS (https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-tutorial-row-level-security-onprem-ssas-tabular). But as this is just for one dimension, it would not cover our requirements.

Did someone once stumbled upon a similar problem inside the data models? Is there any best practice solution for a better handling of individual RLS access rights in tabular models?

Regards, Ivo

Ivo
  • 303
  • 2
  • 15

1 Answers1

2

Whenever you have complex RLS requirements, there's a simple pattern to follow in your model.

Introduce and populate user entitlement tables of the form (UserName,DimensionKey) that flow filters to the target dimension (or perhaps (GroupId, DimensionKey) ).

So for

UserXY should have access to all customers from China selling Toys

So ahead of time run a query that calculates "all customers from China selling Toys" an insert into the CustomerEntilement table all the

insert into CustomerEntitlement (UserName, CustomerId)
select 'UserXY', CustomerId
from DimCustomer c
join FactSales s 
  on s.CustomerID = c.CustomerID
where s.ProductType = 'Toys'

Then you simply put an RLS filter on CustomerEntitlement.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Interesting. Let's assume toys are an attribute of the dimension "DimProduct" and I have more than just one fact table. Then I would have to loop trough all users and all fact tables and calculate all the possible matches between DimCustomer and DimProduct right? I am afraid of not be able to compute this in a useful time because of the number of users and the size of our fact tables. – Ivo Nov 18 '21 at 14:55
  • 1
    If you have a large number of users, they usually can be grouped to reduce the number of combinations. And a fact table contains all the relevant combinations of dimensions. And you shouldn't actually "loop through" anything. It's just a SQL Query with some joins. – David Browne - Microsoft Nov 18 '21 at 18:14