Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Greetings!
I´m struggling with setting up RLS for two different scenarios. First of I have two tables, UserTable and FactTable:
The tables can be connected by SupplierId or StoreId. I need it to be dynamic depending on who logs in. Which is normally achived by roleplaying dimensions (USERELATIONSHIP). Per my understanding it is not supported with RLS. Im looking for other alternatives.
Scenario 1 - MrX@email.com logs in
The report needs to be filtered on his associated StoreId,10.
Scenario 2 - MrA@email.com logs in
The report needs to be filtered on his associated SupplierId, 1. He should see all Stores with the same SupplierId. In this case, 10 and 20. (The "*" can be changed)
The benefit of a solution would be that we will not need to create up to 500 rows for each User, as some Suppliers are located in up to 500 Stores.
Any suggestions are welcome!
You could potentially do the following:
1. Create 2 different User security tables.
1.a. For users that have no filter on one of the tables, include a row with "All" for that user.
2. Create a bridge table between each security table and the fact table.
2.a. In the bridge table, "All" can be associated with all of the Stores / Suppliers.
3. Apply RLS to both User security tables.
Hope this helps,
Nathan
Thank you for your fast reply @Anonymous !
Are you suggesting splitting the Users into two Security Groups?
Security Group1
MrA@email.com
Security Group 2
MrX@email.com
MrY@email.com
MrZ@email.com
Noone is suppose to see everything. Security Group 1(SG1) should see all stores under a certain Supplier.
SG2 should only see specifik Stores.
@Anonymous - I was thinking of a single security group, with every user in both tables, but I like your idea better. Then you can eliminate the suggested bridge tables and only apply the RLS on the relevant User Security table for each security group.
Cheers!
Nathan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |