Forum Discussion
User changing role from A to B
Hi,
I have a report that has RLS implement and working fine but the issue I have now is how to implement change if the user changes role. For example manager, A change from A to manage B and I want him to see details of B and A( where he stopped last at A only)
that is he will see details of A up to the last date he managed A.
I will really appreciate your help. Thanks in advance.
So that requirement is possibly to achieve, but it will mean restructuring your model and data load routines. What you need to do is to implement surrogate keys (so that you can have different versions of each manager) and maintain effective dates for each "version" of a manager and employee.
What you would end up with is a table like the following. The first 3 rows are where "Manager A" managed Employees B & C, Then on 24-Mar he moved and started managing Employees D & F. If you insert fact records using the EmpID and then apply RLS to a column that identifies the employee (like the "Employee" column in the example below) then you should achieve the result you are after.
EmpID ManagerID Employee StartDate EndDate 1 Manager A 01-October-2019 23-March-2020 2 1 Employee B 01-October-2019 23-March-2020 3 1 Employee C 01-October-2019 23-March-2020 4 Manager A 24-March-2020 31-December-2999 5 4 Employee D 24-March-2020 31-December-2999 6 4 Employee E 24-March-2020 31-December-2999 7 Manager A2 24-March-2020 31-December-2999 8 7 Employee B 24-March-2020 31-December-2999 9 7 Employee C 24-March-2020 31-December-2999 You just need to be very careful to test your data load routines very carefully, because when a manager moves you need to generate new "versions" of every employee underneath them.
2 Replies
- d_gosbell
Super User
So that requirement is possibly to achieve, but it will mean restructuring your model and data load routines. What you need to do is to implement surrogate keys (so that you can have different versions of each manager) and maintain effective dates for each "version" of a manager and employee.
What you would end up with is a table like the following. The first 3 rows are where "Manager A" managed Employees B & C, Then on 24-Mar he moved and started managing Employees D & F. If you insert fact records using the EmpID and then apply RLS to a column that identifies the employee (like the "Employee" column in the example below) then you should achieve the result you are after.
EmpID ManagerID Employee StartDate EndDate 1 Manager A 01-October-2019 23-March-2020 2 1 Employee B 01-October-2019 23-March-2020 3 1 Employee C 01-October-2019 23-March-2020 4 Manager A 24-March-2020 31-December-2999 5 4 Employee D 24-March-2020 31-December-2999 6 4 Employee E 24-March-2020 31-December-2999 7 Manager A2 24-March-2020 31-December-2999 8 7 Employee B 24-March-2020 31-December-2999 9 7 Employee C 24-March-2020 31-December-2999 You just need to be very careful to test your data load routines very carefully, because when a manager moves you need to generate new "versions" of every employee underneath them.