Thoughts Regarding “Kimball University: Five Alternatives for Better Employee Dimension Modeling”
Today I read the article "Kimball University: Five Alternatives for Better Employee Dimension Modeling"
The basic employee dimension structure is quite common. However my personal opinion is that in most cases, employees change a lot while roles (company structure) are relatively stable. Hence, I prefer at the DW layer, separate the employees from the roles and make roles a self-referencing table. e.g. DimEmployee, DimRoles (or even DimEmployee, BridgeEmployeeRoles,Dimroles if you need many to many relationships). By doing this, when an SCD type 2 update from the top to bottom (let’s say CEO is changed) happens, you don’t have to replicate the whole tree. As even the person of CEO is changed, the position is not changed. People below CEO now is still under CEO. The only thing changed is that now the CEO is not XXX. He is YYY at the moment.
By the way, the Alternative 5: The PathString Attribute is quite interesting.
