Using effective dates in lookup transformation

[deleted_user]

Hi all,

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!

Mike

[LinusH]

As far as I know, you can't use values in your source table in the subset/where criteria in the Lookup transform.
On other way to do it might be using the SQL Join transform instead?

/Linus

[DaveR_SAS]

I think Linus is correct: you'd have to use the SQL Join transformation to do what you are talking about.

Dave in SAS Publications

[deleted_user]

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...

Maybe this should be in a future version of DI!

Mike

[TimStearn_SASProductManagement_]

Mike,

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.

Thanks,

Tim Stearn

[deleted_user]

Thanks Tim,

I've actually been looking into the generated transformation approach myself. It would be a good opportunity to get to grips with hash tables, which is something I've been meaning to do for a while...

If you have any pointers on the best approach for this they would be welcome!

Mike

[TimStearn_SASProductManagement_]

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
object.
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
effective date.

If you run into issues, just give us a shout.

Thanks,

Tim Stearn

[Michael_SAS]

Just to further the hash discussion along, there are dozens of papers on the hashing but here are some good ones:

The SAS® Hash Object: It's Time To .find() Your Way Around
http://support.sas.com/resources/papers/proceedings10/151-2010.pdf

Getting started with hash object
http://support.sas.com/rnd/base/datastep/dot/hash-getting-started.pdf


How Do I Love Hash Tables? Let Me Count The Ways!
http://www2.sas.com/proceedings/forum2008/029-2008.pdf

mike