ETL and Data base topics

Informatica Scenario:-

Let’s say I have 200 record in source table and I have 3 Target table A,B,C. I have to insert first 1 to 10 records in ‘A’ , then 11 to 20 in ‘B’ and 21 to 30 in C. Then again from 31 to 40 in A, 41 to 50 in B and 51 to 60 in C. So on up to last record.

Solution:-

In the above requirement we need to Informatica and load it into 3 targets (each group with 10 Records).

The below is the mapping. Let’s see in steps.

STEP1:- First let’s see how to create sequence and reset it, after it reaches to 30 count (because of the above Requirement 10 records as a set for 3 targets).

As you can see this can be done in one Variable port. Let’s see how this works

IIF(v_SEQ=30

(this means the variable port reaches to 30),then set the variable port back to 1, else v_SEQ+1 increment the variable).

Lets parameter the value ‘30’ to $$SPLT_LIMT. And go to Mappings –> then Parameter and variable click it and declare the variable/parameter there.

Why we have to use Mapping Parameters:-
what if tomorrow requirement changes to (load 1st records to target A, Second records to target B and finally 3 records to target C). In this case if we don’t use mapping parameters we have to modify mapping.

In this case Just change in Param File:- Assign 3 to the variable $$SPLT_LIMT in the parameter file, no need to change anything in the mapping.

Step3:-
Create 3 groups and divide the records as per the requirement in RTR (Router
Transformation).

As we did for Expression transformation we can parameterize the router Group conditions as follows.

o_SEQ >0 and o_SEQ <11 (change the hard coded values 0 and 11)
TO
o_SEQ> $$GRP1_LOW_LMIT AND o_SEQ <$$GRP1_UP_LMIT (declare this in Mapping parameter and variables and add it to your parameter file). Similarly do it for rest of the groups as well.
So let’s run our mapping and see the output.