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..