dynamic lookup property in datastage

[mads_a]

Hi All,

We do have a dynamic lookup concept in informatica. Can you please let me know if the same property is available in Datastage?

Regards
Madhavi Ancha

[ray.wurlod]

Welcome aboard.

The answer is to be found via Search - it has been asked and answered previously.

[mads_a]

Hi,

I was not able to find it in search. That's the reason why I have posted it again.

[ray.wurlod]

Odd.

I got 16 hits searching for "dynamic lookup".

The Slowly Changing Dimension stage has the equivalent of Informatica's dynamic lookup built into it, though it performs the lookups against an in-memory copy of the (dimension) table.

There are other techniques, and others pertintent to server jobs, that a search for "dynamic lookup" reveals.

[mads_a]

Hi,

Well I have seen all the search conditions. They are useful, but my scenario is a bit different one. Let me explain the same.

lookup flatfile
.
. not present surrogate key
. .
fact file................................................>dimension table (netezza)

I have to do a lookup on a lookup file set (lookup flatfile). If the record is not present a record need to be inserted in the dimension table along with the surrogate key. So, if a new record with the same unique key column cobinations comes from the fact table (more than one transaction record for a customer, whose record is not present in dimension table, first time it shld get inserted in both dimension table and even lookup flatfile, such that a new record won't be generated when the same combination comes in nth row.

Hope I am clear. If not, please let me know.

Regards[/img]

[ray.wurlod]

The Slowly Changing Dimesion stage does all that for you, except that you don't need to use a flat file. It loads an image of the Dimension table into a virtual Data Set (similar to a Lookup File Set, but without the need to import anything into internal format). It's used like this:

Code: Select all

               DimTable
                  |
                  |     
                  V
Source  ---->  SCDStage  ----->  FactTable
                  |
                  |
                  V
               DimTable

You string together as many SCD stages as there are dimension tables for the fact table. It automatically handles Type 1 and Type 2 changes, as well as having different mechanisms for identifying current and expired records in the dimension table.

[pbhasker.rao]

- All the dimensions are of TYPE-1 (SCD is not implemented).
- Lookup file set needs to be used for lookup

Now when we load the facts (from source file), we need to do a lookup on lookup file set for resolving the natural keys and populate the surrogate key in fact table.

However, for the case of early arriving facts (missing parent keys - dimension record not available in lookup file set), we need to generate a new surrogate key and insert the same into dimension table & also to lookup file set which is being used for lookup. This is required to ensure that we dont generate new surrogate key if the missing parent key repeats in the source file for fact data.

*************************************************************
Considering loading account-bal fact table having account_key & balance amount.

Fact source file format:
Account Number|acct balance
A000|99
A001|29 (missing parent key)
A003|40
A007|40
A001|39 (missing key repeating)

Account dimension has the mapping for Account number & Account_key. This information is present in the lookup file set before the start of job for loading fact table.

Given the above scenario, A001 doesnot exist in Lookup file set. So we need to generate a new surrogate key corresponding to this Account number
- update lookup file set
- insert in the dimension table
- load fact table with the generated surrogate key

However when we start loading the 5th record into the fact table, we need not generate the surrogate key again as its already done while processing for 2nd record.

*************************************************************
We are using parallel edition 8.01

Please advice how this can be acheived.

Thanks!
Bhasker

[ray.wurlod]

Slowly Changing Dimension stage does all that.

[Althaf6553]

The Slowly Changing Dimesion stage does all that for you, except that you don't need to use a flat file. It loads an image of the Dimension table into a virtual Data Set (similar to a Lookup File Set ...

Hi Ray,

We have achieved the functionaliy of Dynamic Look up Using SCD stage

But we were looking up the Dimension Table to match the Natural keys ,But Our customer wants us to look up on the lookup fileset only
When we do that we are getting a error as

main_program: Data set, file set, or file "UdmPolDim:DSLink12.v": Non-pipe (or non-virtual data set) with .v in its name


Can the SCD Stage be used to lookup on the Look Up Filesets???

Please suggest

[ray.wurlod]

Your customer lacks knowledge. It does not matter what stage type is in the design, the lookup is ALWAYS done against a virtual data set (except in the case of sparse lookup direct to database, which is not supported by using a Lookup File Set).

Resist stupid requirements!