Forum Discussion
Data Modeling Question: Handling Type 4 Changes
I'd break out product and employee hierarchy to separate dimensions. Your fact table can capture a foreign key for product at time of transaction and a separate foregin key for employee hierarchy at time of transaction.
If your employee hierarchy is rapidly changing, then break out each management level to its own dimension. DimProduct, DimSeniorManager, DimManager, DimEmployee.
The fact table just records the keys that are accurate as of transaction time.
To me, product and employee are pretty orthogonal business concepts, so capturing them in a single dimension seems guaranteed to lead to issues.
greggyb thank you for the suggestion. I will look into breaking out the employee categories into separate dimensions. We only use the Senior Manager, Manager, Employee hierarchy to record who is responsible for that product so this is not a normal Employee information table where it lists all employee history.
I will report back the results after branching out to add more dimensions.
Thanks