I have an Employee dimension that I am using SCDs and Surrogate keys to track changes over time.
Employee's business system key: EmployeeID
Employee Surrogate key: EmployeeSCDKey
I would like to have Manager information tracked over time as well. The managers are employees like everyone else and as such, I was thinking about having a ManagerSCDKey column in my Employee dimension like so:
Example:
This is the problem I am facing though. The arrow shows the boundary from one transform to the next. In the event that a Manager changes jobs (or some other type 2 SCD field) and a new surrogate key is created for them, that change won't be recognized until the next time the dimension is transformed.
By this I mean that the row in red won't appear until the second transformation, so any fact rows associated with Joe for this time will have outdated manager information.
I guess it boils down to this:
Is there a way to make this pattern work? (dimension with a key into itself?)
Or is there a better practice way to accomplish the same task? I would prefer to not maintain a manager dimension that is extremely similar to the employee dimension, but if that's best practice then so be it.