When using the lookup transformation to load a fact table in DI 4.21, is it possible to reference the effective from and to dates in addition to the business key?
This seems to be an essential feature when using SCD Type 2 tables - possibly I'm missing something fundamental!
The where tab in lookup properties appears to only allow filtering of the dimension prior to comparison with the data to be loaded. We're currently looking at a weekly load for our fact tables, and need to be able to take account of changes to dimensions in the interim period, so filtering on the current record indicator isn't an option.
Any advice or suggestions on how to get around this issue would be welcomed!
Thanks for the response folks, I thought this might be the case, but was hoping otherwise!
It would be nice to have this functionality and keep the population of the fact table to a single transformation, rather than have to use SQL joins and data validation transformations. For our purposes, this omission means that the lookup will almost certainly never be used...
As mentioned in other posts, the current version of the Lookup transformation does not allow for finding a business key within a particular date range (to support late arriving facts). You will need to use the SQL Join to include a date range criterion.
In certain special situations, you can get around this by pre-filtering your lookup tables, but only in special situations. For instance, I had to load a fact table where the source table effective dates were always the end of a week. So, I could pre-filter my lookup tables for only rows whose begin/end effective date ranges fit that date. The filtered lookup tables were then used in the Lookup transformation. If the input to your job has effective multiple dates, then this isn't a viable strategy and you'd need to fall back to SQL Join.
Lastly, there *is* a coding approach that would allow you to use an enhancement to the Base SAS Hash object the lookup table is based on. You could create a Generated Transformation using this approach. However, I won't bore you with the details unless you're interested.
We have discussed adding support for this scenario to the Lookup transformation in a future version. If I get updated information in this regard, I can post it to this list.
Please let me know if you have any specific questions. Once you start using hash objects in your code, you'll find a million and one uses. I treat them as both hash objects and general purpose expandable arrays. The new features allow one to do in memory summarization as well.
For your problem, you'll want to take the following approach:
1. Use the business key for the "defineKey()" fields when configuring the hash
2. Store the begin/end dates along with the fields you want to retreive as
"defineData()" fields for the hash object.
3. Allow duplicates when loading the hash table, since the Type 2 records will have
repeating business keys for each data range.
4. Once you get a match on business key, use the FIND_NEXT method of the hash
object to iterate through matching records to find the correct one for your record's