Lookup Stage. Lookup failure records to Rejected file.

edwds · Post by **edwds** » Mon Sep 11, 2006 8:35 am

I am using a SINGLE Lookup stage, which does lookups into 5 different tables and gets the corresponding values for each record. Now, I wanted to send each lookup fail record, into a different output link. So, I am trying to have 5 different output links coming from the Lookup stage and route the records to different outputs based on the lookup value. But the Lookup stage allows me to output to only one Output link.

Any help is appreciated.

Thanks

meena · Post by **meena** » Mon Sep 11, 2006 8:56 am

Hi,

As we know that the Lookup stage can have reference links, a single input link, a single output link, and a single rejects link.

According to your specifications:Can you try to use join stage and pass them to transformer and then to output files.You can write constraints based on your specification too,if you need them.(else if the source is also a database and all tables are in same schema then we can write sql query and can pass them to transformer).

kris007 · Post by **kris007** » Mon Sep 11, 2006 9:49 am

You can always use Merge Stage where you can create reject links as many as your update links.

edwds · Post by **edwds** » Mon Sep 11, 2006 10:50 am

Thanks for your responses. But. let me frame my question much better.
I am actually loading my FACT table. In this process, I have 5 Dimension tables linked to the Fact table(PKs of the DIM tables as FK's in the Fact).

Before loading records to the Fact, I do a Lookup to each of the Dimension tables to see if the parent record exists in my DIM tables or not. If it does not exist, then I want to send them to the REJECT table and an Error message to notify that the Parent record does not exist.

I guess if DS had allowed me to define multiple Reject links, I guess my problem would have been solved. Any ideas..

Thanks

meena · Post by **meena** » Mon Sep 11, 2006 11:00 am

Hi,

Kris007 gave you the solution. Merge stage allows you to give multiple rejects links.

I guess if DS had allowed me to define multiple Reject links, I guess my problem would have been solved. Any ideas..

kris007 · Post by **kris007** » Mon Sep 11, 2006 11:04 am

IMO, your problem could still be solved if you use Merge Stage. Read the documentation for more help.

prabu · Post by **prabu** » Mon Sep 11, 2006 11:10 am

edwds wrote:Thanks for your responses. But. let me frame my question much better.
I am actually loading my FACT table. In this process, I have 5 Dimension tables linked to the Fact table(PKs of the DIM tables as FK's in the Fact).

Before loading records to the Fact, I do a Lookup to each of the Dimension tables to see if the parent record exists in my DIM tables or not. If it does not exist, then I want to send them to the REJECT table and an Error message to notify that the Parent record does not exist.

I guess if DS had allowed me to define multiple Reject links, I guess my problem would have been solved. Any ideas..

Thanks

Hi Ed,

have 5 transformers one after the other and have rejects define for each of them[you are the "Boss"].

regards,
Prabu

meena · Post by **meena** » Mon Sep 11, 2006 11:13 am

Hi prabu,

Using five transformers may have performance issue and of more cost.

prabu · Post by **prabu** » Mon Sep 11, 2006 11:25 am

meena wrote:Hi prabu,

Using five transformers may have performance issue and of more cost.

Hi Meena,
that is more towards answering , "is there any way" [ instead of is it the best way]

emma · Post by **emma** » Mon Sep 11, 2006 12:36 pm

Try another approach.
Are you doing your lookup against entire dimension table ? Maybe creating your lookup files one step before might be more performing and gives you the possibility to have your rejects per table.

prabu · Post by **prabu** » Mon Sep 11, 2006 12:53 pm

emma wrote:Try another approach.
Are you doing your lookup against entire dimension table ? Maybe creating your lookup files one step before might be more performing and gives you the possibility to have your rejects per table.

that's exactly what i meant to say ,ie., to have lookup filesets and do sequential lookups on them. but i have used transformer instead of lookupfile sets

edwds · Post by **edwds** » Mon Sep 11, 2006 2:28 pm

I am trying to use the Merge Stage.

I am getting the Master Input data for my Fact table from a Table in Oracle.
I also have 5 additional Input streams coming from Oracle tables(each table is an Input from the DIMENSION table). So the MERGE stage is getting 6 Input streams(1 Master and other 5 Input data sets from Dimension tables).

The master table has a PK, which is a combination of 5 columns, all FK's from the DIM tables(5 columns).

And each other Input stream has only ONE column as the key.

Looks like its not possible to use a Merge stage, when you DON'T have the same Keys for all imput streams.

kris007 · Post by **kris007** » Mon Sep 11, 2006 3:37 pm

In that case, how do you intend to use LookUp Stage? How do you join the your Primary key(combination of 5 keys) to a single key from each lookup dimension table?

ray.wurlod · Post by **ray.wurlod** » Mon Sep 11, 2006 3:45 pm

The only solutions are:

(1) five separate Lookup stages (only one reject link allowed)

(2) one Merge stage (one reject link per reference input)

(3) six-table Join and check for nulls (cumbersome!)