How to use SSIS to populate a fact table that needs to reference multiple fields in SCD2?
-
I've seen various SSIS examples of a basic scenario of populating a fact table by using a Lookup to substitute a natural key for a surrogate key found in a relevant dimension. What I haven't really seen, nor figured out how to do, is how to create an SSIS package that references a SCD2 where date ranges play a role in knowing which surrogate key to retrieve -- i.e., if the natural key = "X" and the datestamp in the OLTP for "X" is between RowStartDate and RowEndDate of the SCD2, then get the appropriate SK. Using an IsRowCurrent field won't be enough in my situation.
Performing this task in SQL is fairly straight forward, but I don't see an obvious solution in SSIS.
To make this even more complex, the lookup is dependent on a total of four fields within the dimension: the natural key, a product type, and RowStartDate & RowEndDate. I'm working with healthcare data with a type of code called "DRG". A given DRG can mean completely different things depending on the product type (Medicare vs. Commercial). Additionally, DRG codes have expiration dates per the federal government; in fact, in Oct 2009, the DRG code set was completely reworked. Thus, some DRGs have expired, some new ones created, some DRG codes have been repurposed to mean something completely different: all dependent on dates.
So, given that a Lookup performs equi joins, how the heck do you perform some sort of lookup based on a comparison of a value that fits within a range of values?
Thanks in advance -- Pete
-
Soon after posting my question, I stumbled upon the following links that may provide a solution:
http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range
http://blogs.msdn.com/mattm/archive/2008/11/25/lookup-pattern-range-lookups.aspx
If anyone has additional suggestions, please feel free to share.
-
peterzeke (5/14/2010)
Soon after posting my question, I stumbled upon the following links that may provide a solution:http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range
http://blogs.msdn.com/mattm/archive/2008/11/25/lookup-pattern-range-lookups.aspx
If anyone has additional suggestions, please feel free to share.
CozyRoc has implemented data flow destination script, which creates memory-efficient range dictionary object. The dictionary object can then be used in CozyRoc Lookup Plus component. For more information and demonstration how to use the script, check here.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply