Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am not sure how to model this.
I have 3 tables.
Contract | Manager1 | Manager2 |
1 | Peter | Rob |
2 | Peter | Tim |
3 | Tim | James |
4 | Peter | James |
Client | C. Manager1 |
1 | P |
2 | NM |
Fact:
Sale | Contract | Client |
10 | 1 | 1 |
10 | 1 | 2 |
10 | 2 | 2 |
10 | 3 | 2 |
10 | 4 | 2 |
Then, the access with RLS will be based on contract. If you are manager1 or manager2 for that contract you shall see it (also if you are c. manager1 on that row you shall see it too).
So, for the table for access, I will do an Unpivot like:
SqlTable | ||
Contract | Client | |
1 | 1 | Manager1 |
1 | 1 | Manager2 |
1 | 1 | C. Manager1 |
1 | 2 | Manager1 |
1 | 2 | Manager2 |
1 | 2 | C. Manager1 |
2 | 2 | Manager1 |
2 | 2 | Manager2 |
2 | 2 | C. Manager1 |
3 | 2 | Manager1 |
3 | 2 | Manager2 |
3 | 2 | C. Manager1 |
4 | 2 | Manager1 |
4 | 2 | Manager2 |
4 | 2 | C. Manager1 |
I have tried this model but it doesnt work:
Is there a way to achieve this functionality?
Solved! Go to Solution.
Try combining Client and Contract into 1 table e.g ClientContract, and create a composite key that consist of client and contract ID. Use that to connect to Fact ( it means you have to have the same composite key in the Fact )
Try combining Client and Contract into 1 table e.g ClientContract, and create a composite key that consist of client and contract ID. Use that to connect to Fact ( it means you have to have the same composite key in the Fact )