Optimal Data model with RLS

[D_G]

I have data model with multiple dimensions and two Fact tables

I want to set up Role level secturity on Company dimension and also Limit item and Customer list so that company user would see only Items and Customers which are relevant for them. Items and customers can be local and global:

In data model we have more than 100 K items, around 50 K customers and 20 mio transaction lines. 

What would be best option to apply RLS in this case?

[Anonymous · Accepted solution]

Hi All,
Firstly  sanalytics  thank you for your solution!
And @D_G ,Because Items and Customers are associated with specific companies, applying RLS filters to the COMPANYID in the Company table propagates to those tables, automatically limiting the visible Items and customers to items and customers associated with the user's company.

We have divided the RLS into two categories, one that restricts a certain type of company to see only its own projects and amounts, and another that is similar to a global administrator that can see any data

Then publish it to our service for testing and you can see that we are able to filter by company.

If you have more recent questions you can check out my pbix file that was created 1 to 1 based on your relationship view, hope that helps!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[D_G]

thanks. this was exactly what I was looking for

[sanalytics]

Hello @D_G 

The best approach should be to apply the dynamic row level security in your case.

1)Use dynamic RLS based on the company dimension.
2)Apply filter propagation from the company to the items and customers, ensuring that users only access the relevant data.

There are tons of material avialable regarding dynamic rls in google and youtube. please go though this.

Regards,

sanalytics

If it is your solution then please like and accept it as solution