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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics