RLS on a dimension

[vp84]

Hi all,

I have a requirement to implement row level security in my model. The scenario is like this:

I would like to first know the Business Unit (BU) of whoever the logged in user is (which I can get from Userprincipalname()).

Please note one user can only belong to one BU and both the user email and BU are the columns in my table.

Once I have the name of logged in user's BU I would like to filter down the entire dataset to their BU which would have other users as well obviously.

I am not able to figure out how to do this. Please suggest or point me to the right direction/link.

Thanks.

[Ritaf1983 · Accepted solution]

Hi @vp84 
You need to create a table with roles by BU .
The columns: User Name, Bu
And to create a relationship by BU with your data tables ( it will be many to many and its ok in this case).

For more information please refer :

https://community.fabric.microsoft.com/t5/Desktop/Dynamic-RLS-by-group/td-p/1378126

(in your case BU is like school in the linked discussion)

Or you can create a BUs table with unique values and use it as a "bridge"

:https://www.youtube.com/watch?v=Z3oLWtWABfo

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

[vp84]

Thanks a lot Rita for your suggestion. I am able to handle this scenario with the bridging table design. Thanks again.

[Ritaf1983]

Happy to help😊

[Ritaf1983 · Accepted solution]

Hi @vp84 
You need to create a table with roles by BU .
The columns: User Name, Bu
And to create a relationship by BU with your data tables ( it will be many to many and its ok in this case).

For more information please refer :

https://community.fabric.microsoft.com/t5/Desktop/Dynamic-RLS-by-group/td-p/1378126

(in your case BU is like school in the linked discussion)

Or you can create a BUs table with unique values and use it as a "bridge"

:https://www.youtube.com/watch?v=Z3oLWtWABfo

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

[vp84]

Thanks Rita for your reply. I can explore this solution. My only doubt is since I already have User Name and BU columns in my existing dataset, what is the use of creating a separate table and then add its relationship?

Kindly clarify.

[Ritaf1983 · Accepted solution]

Hi again 🙂

This functionality is the same as all other dims, it gives you the flexibility to connect multiple tables and filter them in an efficient way + If we are talking about data that can be changed like employees who can leave it is much easier to manage roles table.

(like other RLS systems not just PBI )

If you have just 1 static table you can try to use its columns, but it is not the best practice.

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