Dimension modelling for HR with Employee Dimension and multiple departments in a Data warehouse

Question

What is the best way to configure a dimension model (preferably star schema) when we have the following requirements?

1. 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.
2. There are different Departments. Every Department is head by exactly a single Department head (Employee).
3. An Employee may belong to multiple Departments and vice versa.
4. Monthly payroll information is required to maintain for every Employee.

Understanding and Questions

1. Should we split the Employees entity into two considering only 5/25 of the attributes are SCD2 based (historical)?
2. 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?
3. 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?
4. 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.

Answer 1

Answering this older question since it's getting views & upvotes, even though I know the original poster is inactive.

Q1: Employee Dimension with Mixed SCD Attributes

You don't need to split the Employee entity into two because you're tracking historical changes in some attributes but not others (SCD 1 & 2). This is quite common, especially for larger dimensions.

You should be able to adapt your ETL to treat some attribute changes as an overwrite and others as a "expire old row and insert new row" SCD2. You should even be able to adapt your ETL to have the overwrite attributes either overwrite just the current row or all historical rows too.

Q2 & Q4: Employees & Departments

A many-to-many relationship (using a bridge table) does sound appropriate here. In some ways it depends on what you want to do with the model. Without knowing that, I can only summarize what this solution will do. Another question would have to be asked if this isn't the solution you want.

By having a bridge table, any facts associated to employee would be associated to each department that employee is in. For example, if Person A is in Department Y and Department Z, then the full monthly payroll amount for person A would show up when you're filtered to Department Y or Department Z. However, when looking at both departments together, the monthly payroll amount would only be counted once. E.g.,:

Employee | Department   | Payroll $
-----------------------------------
Person A | Department Y | $800
Person A | Department Z | $800
TOTAL    |                $800

It looks a little odd because the total isn't the sum of the individual rows. But it makes sense if you know a person can be in more than one department (but isn't paid twice).

As you note in Q4, you could only associate a fact to an employee OR a department, but never to both at the same time. (As associating it to both would mean 2 competing ways to slice the same fact into department.)

Q3: Department Head

Even though the department head is an employee, it exists here as a descriptive attribute of the department. Therefore, I would recommend treating Department Head like any other attribute and populating it with the name of the department head rather than the EmployeeKey. This will make the Department dimension much easier to manage. (When the department head name changes, you can do an overwrite or an SCD2 in the Department dimension, but that would be independent from anything happening in the Employee dimension).

Hope this helps someone.