What is the best way to configure a dimension model (preferably star schema) when we have the following requirements?
- There is an Employees table (25 attributes) where we are required to make some of the attributes to SCD2. For e.g. Salary, LastSalaryIncreaseDate, LastBonusAmount, LastBonuesDate, Designation. We don't have to maintain the reporting hierarchy.
- There are different Departments. Every Department is head by exactly a single Department head (Employee).
- An Employee may belong to multiple Departments and vice versa.
- Monthly payroll information is required to maintain for every Employee.
Understanding and Questions
- Should we split the Employees entity into two considering only 5/25 of the attributes are SCD2 based (historical)?
- I suppose there is a bridge table required for the Employee and the Departments. So every employee must have an attribute (e.g. DepatementGroupCode) showing multiple departments in the bridge table. Correct?
- There is a direct relationship between employees and the Department. So Department will have the attribute EmployeeKey in it. How do I deal with SCD2 changes of employees with respect to the Department entity?
- The payroll periodic Fact entity will be linked only with the Employee and the date dimension. This should not be linked with the Department because it is already linked with the Employee entity...Please correct my understanding.