SSIS: Lookups with no exact match
I wrote about that some weeks ago (http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx). Using a OLE DB command transform to find out a matching record instead of a lookup transform when you don’t have an exact match but i.e. a range to compare is very slow. Using a cross join might be a solution (I still didn’t have time to test it…), but produces huge datasets to load into memory. So no perfect solution…
Some days ago my colleague Markus was in the BI Migration Lab in Redmond. It was a great event and we got some very valuable input there. Runying Mao came up with a very neat solution for this issue…
Back to the example of the employee<->department relationship. You have a fact table with employee IDs and another table having the employee ID and the department the employee is working in. Since employees can change their department you have a start and end date for this employee<->department assignment. Now you want to know for each fact record in which department the employee was at the given time.
Runying’s solution is to do an inner join between the fact and the relationship table. If you have an employee who was in different departments you get the same fact record for each department the employee ever was. So the only thing you have to do is filter out (by a conditional split) the “non fitting” records, what means the records where the date from the facts doesn’t fit into the data range coming from the relationship table.
Cool solution… Perhaps this is a good scenario for my next snippet…
Thomas