employment fact table

Hi

I have the following dimensions

Member and Member Account, the dimensions are linked via Member_Member_Account as a Member can have one or more Member Accounts

Each account is associated with employment (employer, salary, employment status, occupation etc) and I was thinking of having an employment fact table to track the employment history (i.e salary changes)

This fact table will

Member Account Key
Employer Key
Effective Date
Occupation Type Key
Salary
Working Status Key ( Employed Full/Part Time, Unemployed Ceased/Retired/Other)


Is the best way to go

Regards

Tim

From the sound of your requirements, it seems a snapshot is in order. You will want to take a snapshot of your source data (weekly or monthly) and load your fact. Your fact definition is going in the right direction as well, with some small changes:

Fact Snapshot Date Key
Member Account Key
Employer Key
Effective Date
Working Status Key ( Employed Full/Part Time, Unemployed Ceased/Retired/Other)
Occupation Type Key
Employee Start Date Key
Employee End Date Key
Employee Retirement Date Key
Salary