How to handle employee changing jobs in dimension

I'm not sure how to deal with historical employee data, so hoping to get some help here.

I have a data source which contains all employees and a history of their job changes throughout the organisation.
It basically has: Employee ID, Name, Job Title, Job Expiry Date
So 1 employee for example can appear as follows in the table:

Employee ID_____Name___________Job Title_________Job Expiry Date
12345__________Fred Smith_______Software Dev_____1 June 2010
12345__________Fred Smith_______BI Consultant_____3 November 2010
12345__________Fred Smith_______Manager__________NULL

So this particular employee was a software dev up until 1 June 2010. Then he was a BI Consultant up until 3 November 2010. And currently is a Manager.

Now in my Employee dimension, I'm trying to model it as a SCD Type 2 dimension. I'm planning to have EffectiveDate and ExpiryDate columns in my Employee dimension.
My first option is to model it so that my EffectiveDate is simply the date that I have loaded the record into the dimension and thus my ExpiryDate would initially be NULL or 99991231:
So it would look like this:
Employee ID_____Name___________Job Title_________Job Expiry Date________EffectiveDate____ExpiryDate
12345__________Fred Smith_______Software Dev_____1 June 2010__________10 Jan 2011_____99991231
12345__________Fred Smith_______BI Consultant_____3 Nov 2010___________10 Jan 2011_____99991231
12345__________Fred Smith_______Manager__________31 Dec 9999_________10 Jan 2011_____99991231

Or should I get rid of Job Expiry Date and use the EffectiveDate and ExpiryDate columns to account for the Job Expiry Date as in:
Employee ID_____Name___________Job Title_________EffectiveDate____ExpiryDate
12345__________Fred Smith_______Software Dev_____(Date Joined)____1 June 2010
12345__________Fred Smith_______BI Consultant_____1 June 2010_____3 Nov 2010
12345__________Fred Smith_______Manager_________3 Nov 2010______99991231

There is a reporting requirement that I need to map a Sale to the Employee who sold it "as-at" the time of sale. So for example if Fred Smith made a sale of a product in 5 July 2010, then I would need to match the sales record to the 2nd employee record in my example. If he sold a product in 1 Sep 2009 then the sale would need to map to the 1st record.

I'm not sure which method would best suit this requirement or indeed if both of them or none of them suit?

Thanks for the help.
Cheers

I would suggest the following model for your requirements

Emploee dimension:
Empl Key____Employee ID____Name____________JobTitle__________StartDate_______EndDate
1__________12345__________Fred Smith_______Software Dev_____(Date Joined)____31 May 2010
2__________12345__________Fred Smith_______BI Consultant_____1 June 2010_____2 Nov 2010
3__________12345__________Fred Smith_______Manager_________3 Nov 2010______null

The fact table will have
Emp Key ( FK column)
Date Key( FK column)
Fact data…

This means that the sale row made on 5 July 2010 will be connected to to the 2nd dimension row ( with Emp Key: 2) and the sale row in 1 Sep 2009 will be connected to the first row.

If you will report the fact data e.g. for July 2010 , Fred Smith will be shown as a BI consultant.

Usually employment changes (position, job, transfer, etc) are handled in an employee action fact table, not as dimensional history.

But there is a reporting requirement "that I need to map a Sale to the Employee who sold it "as-at" the time of sale".

If so and you have to report for instance how many sales were made by BI consultants ( or various job titles) in a given period, then job title changes can be modelled as SCD2 type.

The last option works the best. Set that sales date between the Effective date and expire date when assigning the key.

Hi ngalemmo,

Are you able to give me an example of what you mean by modeling employee transfers and changes in a fact table?

Thanks.

You've already modeled it. Make the text columns dimensions and your done.