Forum Discussion

jenesis's avatar
jenesis
Frequent Visitor
3 years ago

PATH-function for user/manager relation in combination with SCD2

Hello together, i have some example data like the following:   scdID userID userIDsupervisor csdStartDate scdEndDate 1 1 2008-02-02 2 2 ...
Path
SCD2
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

Hello jenesis,

 

Can you please try the following:

 

  1. Enhance Your Data Model: It's important to enhance your data model to include unique identifiers for users. You could create a new column that concatenates the userID and scdID or another unique identifier.

  2. Create a User Hierarchy: With the enhanced data model, create a new hierarchy based on the newly generated unique identifier column

  3. Calculate Unique Manager Paths: Create a measure to calculate unique manager paths using the newly created unique identifier column.

ManagerPath =
VAR SelectedUser = SELECTEDVALUE('dim_user'[UniqueUserID])
RETURN
CALCULATE(
    PATH('dim_user'[UniqueUserID], 'dim_user'[UniqueUserIDsupervisor]),
    FILTER(
        ALL('dim_user'),
        [isInDateRange] == 1 &&
        'dim_user'[UniqueUserID] = SelectedUser
    )
)

Should you require further details or information, please do not hesitate to reach out to me.

jenesis's avatar
jenesis
Frequent Visitor
3 years ago

Hello Sahir_Maharaj,

thank you for your answer, but there is a problem why this solution will noch work perfectly (I think):

Let's say we have a user "X" and he has the manager "Y" at point in time 1

And this user "Y" has manager "Z" at this point. So the hirachy at point 1 would be: X -> Y -> Z

So now at point in time 2 the manager of user "Y" gets changed to "K". So the hirachy for X from point 2 should be X -> Y -> K.

But this will not trigger any new scd record for the user "X" so looking from the perspective of the old record of user "X" it will result in a wrong (old) PATH (X -> Y -> Z) even after point 2.

Or do you see an error in my thinking in some way?

I tried now another aproach which involves more data preparartion in our data factory. So my data model will now result in one table holding the scd-records for users and one additional table called scd-hirachy, which holds scd2 records for each user, updated whenever anything in the complete hirachy of one user changes.

So this table looks like:

scdIDhirachy   userID    hirachy   csdStartDate   scdEndDate
1              1         1         time1          null
2              2         2-1       time1          time3
3              2         2-3-1     time3          null
4              3         3-2-1     time2      	  time3
5              3         3-1       time3          null

 

Now i now there is all information correctly available in the data model at each point in time but I am not sure how the RLS query should loook like which checks if a user should the a specific record at a specific point in time which is determined by the date-slider. 
Do you think you can help me to get further with this?