BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
7 REPLIES 7
LinusH
Tourmaline | Level 20
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
Data never sleeps
DaveR_SAS
SAS Employee
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
Not applicable
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
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
Not applicable
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
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
SAS Employee
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2538 views
  • 0 likes
  • 5 in conversation