Forum Discussion
RLS on a dimension
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.
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.
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.
5 Replies
Thanks a lot Rita for your suggestion. I am able to handle this scenario with the bridging table design. Thanks again.
Happy to help😊
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.
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.
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.
