Informatica : Multiple Targets based on Source Order.

One of the frequently asked questions on the Informatica forums at Informatica forums is how to load data into different target tables based on the order of the source row data.

Eg..If you have an employee table that has 14 rows, the requirement is to load data into three different target tables in the following way.(The three target tables have the same description ).

Record 1 —————> Target table 1
Record 2 —————> Target table 2
Record 3 —————> Target table 3
Record 4 —————> Target table 1
… and so on…..

The number of target tables for this example is 3. This, of course, varies based on the question. I haven’t come across this requirement before, but since this is so frequently asked, I am writing this post. If you have come across a business reason for this, Please post it in the comments.

Here are the three transformations that you can use to implement this.

a) Sequence generator : to generate a series of sequence numbers.
Even if you have an ID as a primary key and it is incremented by 1 every time, I’d suggest you create a sequence to make sure there is no gap in the sequence numbers generated. This will ensure the distribution is uniform.

b) Router : To generate the groups for each of the rows to go into.

c) Expression Transformation : This is optional. You can write the same condition directly in the Router expression if you want to, thereby avoiding this transformation. Please see below how the mapping and condition would look in that case.

For my example, I’ll be using the emp table from oracle’s scott schema.
If you dont have oracle installed, you can get it from here : demobld.sql

Create the three new target tables with the same structure as the source.

With an Expression Transformation:

>Import the source scott_emp, the three targets, sequence generator and expression transformation.
> Specify a start value of 1 for sequence transformation and increment by value of 1. Connect “next value” to the expression transformation.
> To get the group into which a record would go, use the mod function to get the reminder. If you have three targets, use mod(seq_num,3) to get reminders 0,1,2. If you have 2 targets, use mod(seq_num,2) to get two groups 0,1.

> Specify a start value of 1, increment by 1 and end value of 3 and check the Cycle Option. (so the output values would be 1,2,3 and then 1,2,3 and so on.)
> Pass these records to the router and route these records to the target tables as needed based on the group.

Here is how the mapping would look and the data after the load. Please note that I used the override query in the source qualifier to get the data ordered by empno. This may or may not be necessary based on your requirement.

Multiple Target Tables : Expression Transformation

You can of course implement the same thing without using the expression transformation. In that case, the ports from source qualifier and the sequence transformation would be passed directly to the the router and the router groups would be like this.

Hope this helps..
Rajesh.

  • Tanima

    Thankx Rajesh..nice explanation!!! It helped me a lot….

    • Hi Rajesh,

      Thanks a lot, it has helped a lot and would request you to share ur knowledge on Informatica for interested Ppl.

      Let the almighty give good health for ur furture good works on Informatica.

      Thanks Chap !!
      Jagadesh

    • Shankar

      wow,,thanks a lot.In my recent interview with accenture,they asked me this question first,but i could not answer.Then i came home and searched for it,,by god i got your post…i would like to ask you,,what i will do if i have to pass 2 rows to first table?is my condition is router be like dis : seq_generator >1 && <3???

      • Rajesh

        I think that logic should work. Please create a test mapping to be sure, though.

  • Bala

    Hello Rajesh,

    Great work! Excellent explanations.

    But still no updates on the actual Business reason of such task from any one. Lets wait for that.

    Thanks,
    Bala

    • Rajesh

      Thanks Bala.

      This question has the same response in the forums too. No one gives the business case for this, but it keeps coming up every now and then.. 😉

      Cheers,
      Rajesh.

  • G.RajarameshReddy

    Thank you a lot. It helped me a lot.

  • Sandhya

    With out Sequence generator may i know the process

    • rajesh kumar

      WHY? The sequence generator in Informatica is the both easy and the correct way of implementing things. Theoritically, You could change the query to use the database sequence numbers (select emp_seq.nextval, emp_id …..) and use that when inserting a new row. But this would be a very inefficient approach since you’d be wasting most of the sequence numbers for all rows where you are doing an update. .

      • Sandhya

        Thanks a lot Rajesh .. I will try and let you know the results

  • Rediranch

    IT reason, not necessarily business owner reason –

    We have a process running 4-20M rows, 600 bytes wide, through heavy lookup, rankers, mapplets, which takes a lot of time.

    We employed a UNIX task that splits up the input into 8 files, then an Informatica reusable session (8 of them) run simultaneously processing those 8 files.

    This post gave me an idea on how to use Infa, not UNIX, to do the split. This is especially helpful since just before the UNIX split we have a session reading the input and writing it out (scrubbing data prior to processing).