User changing role from A to B

[Anonymous]

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.

[d_gosbell · Accepted solution]

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.

[d_gosbell · Accepted solution]

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.

[Anonymous · Accepted solution]

Thanks, a lot that was a great relief.