CODEWITHSUNDEEP
data-warehousedimensionsurrogate-keywarehouse
user7600460
user7600460

Reputation: 11

Dimension with a surrogate key into itself (Data Warehouse)

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:

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.

Upvotes: 1

Views: 341

Answers (1)

Rich
Rich

Reputation: 2279

Here's a good discussion of some alternatives, I'm sure you'll find something that matches what you need: http://www.informationweek.com/software/information-management/kimball-university-five-alternatives-for-better-employee-dimension-modeling/d/d-id/1082326?page_number=1

I'd likely opt for some kind of 'reports to' bridge table, perhaps having natural keys rather than surrogate keys depending on how you want it to behave (and to solve your type 2 SCD table). You wouldn't need to have a separately created manager dimension, only have employee pointing to the bridge table twice.

Upvotes: 1

Related Questions