Dynamic Row Level Security for hierachy over multiple columns

[timtack]

Hello,

i have a task within my company to set up a PowerBI workspace. I taught myself the knowledge and learned a lot about measures. Before I start with the dashboards, I need to check if our requirement regarding row level security is suitable with PowerBI.

The data model should be adapted to the requirements. At the moment I got 2 fact tables:

1. The product structure with a hierarchy of 5 levels (level 2:highest with whole product; level 5 lowest hierarchy with each individual product) => columns (Level1, Level2, Level3, Level4, Level5). There is also a column for the product name. A row has a product name, when the smallest hierarchy is reached ( can be a level 3, but most common a level 5 => Level 1 in the hierarchy is the company itself, so no relevance
2. The sales numbers regarding Level 5 items, associated with the Financial Year, regions and different cost categories (product name, different cost categories, sales, Financial Year)

These two tables are published once a year within my company. I only get the result and don’t have access to the roots of the informations.

For the data model, I created a fact table for:

• Financial year

I know this could be better, but I don’t see possibilities. Do you have suggestions here?

To come back with the RLS requirement:

Hundreds of users will have access according to their USERPRINCIPALNAME(). Users should have different permissions, e.g.

• User1 (e.g. Manager of product "A") is allowed to see all Data of Level 2 of product “A” an all related sub hierarchy items until Level 5.
• Another case: User 2 is  allowed to see only Level4 of Product “C” an all related sub items of this specific hierarchy.
• There should also be Users to see different items in different hierarchies, e.g. User 98 has permission to view Level 2 of Product “A”, Level 4 of Product “C”, and Level5 of product “D” and Product “F”.

There are many different Level 2 Items with individual Level 3 items and so on untill the lowest hierarchy Level 5.

I have to set up the permission table as well as the RLS. I can built the data model to fit the requirements.

The questions:

• How should I set up a dynamic RLS with a excel? What columns are needed/ do you recommend ? (I though dynamic RLS is a good way to go according to the users amount who have access an our company uses microsoft)
• How should I set up the row level securities to meet the requirements?
• Is there a possibility to have a concatenated path function for my hierarchy from level1 to level 5? Or which way should I go?

I researched a lot, but rarely found a solution that I can apply to my case with my actual knowledge.

Thanks!

[timtack]

Thank you very much for your response and help. I tried to make it more complicated then needed, because our internal requirement is that our PB workspace should run as independet as possible.

Sorry for my late response, i was out of office for more than a week.

[lbendlin · Accepted solution]

what you describe may be better served by static RLS.  Create a number of roles with their access patterns, and then assign users (distribution lists) to these roles. That way you  don''t have to chase the permissions for every single user.