How did I build Employee dimension using historical data?

I never ever had to this in the past. On my current project I had to build the employee dimension based on opportunity table in CRM system, not from an employee reference table. Opportunity table holds the following information OpportunityID, AccountManagerID, ActualCloseDate, ClosedDivision and ClosedRegion along with many other fields.

UserInfo table is the table which holds Employee details, but that table only holds current date. Going forward we will be OK as will do the type 2 on Region and Division.

The requirement was from the client is to map the Employee to an opportunity, which is fine, except the employee need to be mapped to the Division and Region which they closed the deal at. That’s why the ClosedDivision and ClosedRegion from an opportunity table comes handy.

I brought the data from source system into Stage database using query similar to the one below
SELECT o.accountmanagerid,
u.username,
o1.closed_region,
o1.closed_division,
MIN(o.actualclose) actualclose
FROM sysdba.opportunity o
INNER JOIN sysdba.opportunity_extra_1 o1
ON o.opportunityid = o1.opportunityid
INNER JOIN sysdba.userinfo u
ON o.accountmanagerid = u.userid
WHERE status LIKE 'Closed - %'
AND actualclose IS NOT NULL
AND closed_division IS NOT NULL
AND closed_region IS NOT NULL
GROUP BY o.accountmanagerid,
u.username,
o1.closed_region,
o1.closed_division

The above query will bring all the AccountManagerID (NK), Name, ClosedDivision and Region along with MINIMUM actual close date of an opportunity.

Source table example

Opp Emp ClosedDate ClosedRegion ClosedDivision
1234 ABC 01/04/1990 London South
2345 ABC 09/04/1990 Mids North
4567 ABC 06/08/2003 Mids North
3456 ABC 05/07/2007 Scotland Scotland

The Stage table will look like this

Emp ClosedDate ClosedRegion ClosedDivision
ABC 01/04/1990 London South
ABC 09/04/1990 Mids North
ABC 05/07/2007 Scotland Scotland

Now I have got the stage table populated, now how can I build the warehouse dimension table.

This package only to run one time, as it is going to build the employee dimension using historical data. This is how I built the dimension.

1. I read the stage table and assigned it to an object variable using the following query
SELECT DISTINCT AccountManagerId, ActualClose
FROM scbEmployeeHistory
ORDER BY 1,2
2. Please note that I am only selecting AccountManagerID (NK) and ActualClose (date).
3. Using Foreach Loop Container I loop through the Foreach ADO Enumerator and mapping the results to 2 variables.
4. Then I will build the dimension usual way, but the key is to pass the AccountManagerID and ActualCloseDate as parameter to the source querry.

If anyone would like to see an example feel free to drop me a mail and I will send it to you.

I would be interested to hear from others if you have done it differently.

Thanks
Sutha