how to add department AND city (both with different start date) to Employee dimension
Hello, I'm using SQL ser er 2008 R2 Standard
I have created an employee dimension table, with history for department changes SCD:
ID employee_nr Dept_startdate Deparment
With a lookup I add the correct employee record to the fact table.
Now I want to add the employee city to this employee dimension, but it has it's own start date, city_startdate, so i can track city changes.
How can I add this to the Employee dimension, and how can I make the lookup find the correct dimension record?
Example: Suppose I have employee with ID 12345
City history of employee 12345:
City_startdate city
2008-01-01 Boston
2010-01-01 New York
Department history of employee 12345:
Dept_startdate department
2004-12-22 Sales
2006-11-30 Marketing
2010-02-01 Finance
2011-06-01 Marketing
The desired result should be this:
id date city department
1 2004-12-22 Boston Sales
2 2006-11-30 Boston Marketing
3 2010-01-01 New York Marketing (here you see that city changed to New york from 2010-01-01, when he still worked at marketing)
4 2010-02-01 New York Finance
5 2011-06-01 New York Marketing
Another problem is that city history is started from 2008-01-01, but department history is older, so I want for those older records to have the first (oldest) city record.
Regards, Hennie
August 17th, 2012 2:58am
use 2 lookups instead of 1 and for the 2nd Lookup where you do not find a match, use the top first, ordered by ASC on City start date.Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
August 17th, 2012 7:00am
I disagree. I would simply have all changes tracked by the one set of Start/End dates inside the same Dimension table, and let the rows reflect that:
Key ID City Department Start
End
1 12345 Boston Sales 12/22/2004
11/30/2006
2 12345 Boston Marketing 11/30/2006 1/01/2010
3 12345 New York Marketing 1/01/2010 2/01/2010
...
Now, when you match your facts to the dimension, DON'T do it with a SSIS Lookup. Instead, load facts into a Staging table. Then do a JOIN to your Dimension such as:
UPDATE FACT
SET FACT.Key = DIM.Key
FROM dbo.FactTable AS FACT
INNER JOIN dbo.DimensionTable DIM
ON FACT.ID = DIM.ID
AND FACT.City = DIM.City
AND FACT.Depoartment = DIM.Department
AND FACT.Date >= DIM.Start
AND Fact.Date <= ISNULL(Dim.End, '1/01/3000')
Such a SQL statement will identify the correct Key row based on the matching City and Department, AND the correct date range.
Such an operation CANNOT be done in SSIS because SSIS Lookups ONLY allow EQUAL JOINS (<sigh>).Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.
August 17th, 2012 11:57am
I disagree. I would simply have all changes tracked by the one set of Start/End dates inside the same Dimension table, and let the rows reflect that:
Key ID City Department Start
End
1 12345 Boston Sales 12/22/2004
11/30/2006
2 12345 Boston Marketing 11/30/2006 1/01/2010
3 12345 New York Marketing 1/01/2010 2/01/2010
...
Now, when you match your facts to the dimension, DON'T do it with a SSIS Lookup. Instead, load facts into a Staging table. Then do a JOIN to your Dimension such as:
UPDATE FACT
SET FACT.Key = DIM.Key
FROM dbo.FactTable AS FACT
INNER JOIN dbo.DimensionTable DIM
ON FACT.ID = DIM.ID
AND FACT.City = DIM.City
AND FACT.Depoartment = DIM.Department
AND FACT.Date >= DIM.Start
AND Fact.Date <= ISNULL(Dim.End, '1/01/3000')
Such a SQL statement will identify the correct Key row based on the matching City and Department, AND the correct date range.
Such an operation CANNOT be done in SSIS because SSIS Lookups ONLY allow EQUAL JOINS (<sigh>).Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
August 17th, 2012 12:02pm