0

I have a star schema that shows employee roles in a company. At its simplest it is:

DimDate

DimPerson

DimPaygroup

DimRole

Fact table looks like: PersonID, RoleID, PaygroupID, StartDate, EndDate

If it is the current role the Enddate is set to 31-Dec-2199
Problem is I need to track Paygroup, as this can change without a person's role changing.

My initial plan was to treat it as a late arriving fact by adding an extra column to the fact table called PayGroupEndDate. This would get updated when paygroup changes and a new row would get added. I'm not sure this is the best solution though.

Could this be dealt with better by making it a slowly changing dimension?

The StartDate & EndDate are specifically for the Role, i.e. the data a person starts and leaves a role. That is the grain of the fact table.

A person can be in the same role and move between paygoups and this needs to be tracked also, but I was trying to do this as an SCD.

UPDATE: I was thinking of just creating a new dimension for the PAYGROUP, rather than leaving it as an attribute of the person dimension. That way when it changes, a new record will be entered. Is that a simple solution to the problem?

Community
  • 1
  • 1
JD_Sudz
  • 194
  • 1
  • 12
  • 1
    I think I'm missing something. On Tuesday, I am in role Manager and I'm in PayGroup A. On Wednesday, I am still a manager but now I'm in PayGroup B. My fact row for Tuesday doesn't change, so where's the disconnect? – billinkc Feb 16 '16 at 12:32
  • Agree with the above, and to put it another way, when someone's paygroup changes, but their role doesn't, what's wrong with using your existing EndDate, and creating a new row with the same RoleID but the new PaygroupID? – Tab Alleman Feb 16 '16 at 14:41

0 Answers0