Look up and SCD

Munish · Post by **Munish** » Thu Jan 11, 2007 8:49 pm

Hi Everybody,
In one of my Load job (to Oracle)
I need to do lookup to find the key (from dimension table)
and load the target table with the key values
ie I am getting Account_Num as input and I need Account_Key for output.

Now if Account_Num does not exist, I do not want to reject my rows but continue the job with Default value for key.
ie
If Account_Num does not have respective Account_Key in the DB, I want to send -1 as Account_Key which stands for unkown in our DB.

I am using Lookup stage and Lookfilesets.

Please advice how this scenario should be handled.

Thanks,
Munish

DSguru2B · Post by **DSguru2B** » Thu Jan 11, 2007 9:09 pm

Perform the lookup, if there is a hit, send it down one link, if it does not, send it down the other. Merge the both before doing upsert or send both the links directly to the same database.

Munish · Post by **Munish** » Thu Jan 11, 2007 9:13 pm

Thanks for the advice.
I am doing like this only.
I am just wondering that is there any property where I could put this defalut value and do not have to split and merge data.

Regards,
Munish

ray.wurlod · Post by **ray.wurlod** » Thu Jan 11, 2007 9:16 pm

Make the "lookup failed" rule in your Lookup stage "Continue", and the fields derived from the reference input link Nullable. Downstream add a Modify or Transformer stage to handle the null, replacing it with -1.

You are still thinking like a server job developer. In parallel jobs the rule (mainly) is "one task, one stage". Or "one stage, one task". You can not substitute the in-band null within the Lookup stage. You need an extra stage.