Informatica Reference

Archive for the ‘Informatica Scenario’ Category

Suppose the source table has values like this
10 A
10 B
10 C
20 X
20 Y
20 Z
30 D
30 E
30 F

And the target should be
10 A*B*C
20 X*Y*Z
30 D*E*F

How will you do the mapping?

Answer: This can be done using an expression transf and Aggregator transf.
In expression transf declare 2 variables ID_V and NAME_V
NOW pass the output ports to aggregator GROUP BY ID and connect to target.

A source table contains emp_name and salary columns. Develop an Informatica mapping to load all  records with 5th highest salary into the target table.


The mapping will contain following transformations after the Source Qualifier Transformation:

1. Sorter : It will contain 2 ports – emp_name and salary. The property ‘Direction’ will be selected as ‘Descending’ on key ‘Salary’

2. Expression transformation: It will 6 ports as follows –
a> emp_name : It will be an I/O port directly connected from previous sorter transformation
b> salary_prev : It will be a variable type port. Give any vriable name e.g val in its Expression column
c> salary : It will be an I/O port directly connected from previous transformation
d> val : It will be a variable port. The expression column of this port will contain ‘salary’
e> rank: It will be a variable type port. The expression column will contain decode
f> rank_o : It will be an output port containg the value of ‘rank’.

3. Filter Transformation : It will have 2 I/O ports emp_name and salary with a filter condition rank_o = 5

The ports emp_name and salary from Filter Transformation will be connected to target

Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.


You will have to assign a row number with each record. To achieve this, either use Oracle’s psudo column rownum in Source Qualifier query or use NEXTVAL port of a Sequence generator. Lets name this column as rownumber.

From Source Qualifier, create 2 pipelines:

First Pipeline:
Carry first port Col1 from SQ transformation into an aggregator transformation. Create a new output port “tot_rec” and give the expression as COUNT(Col1). Do not group by any port. This will give us the total number of records in Source Table. Carry this port tot_rec to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Second Pipeline:
from SQ transformation, carry all the ports(including an additional port rownumber generated by rownum or sequence generator) to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Join these 2 pipelines with a Joiner Transformation on common port DUMMY. carry all the source table ports and 2 additional ports tot_rec and rownumber to a router transformation. Add 2 groups in Router : FIRST_HALF and SECOND_HALF. Give condition rownumber<=tot_rec/2 in FIRST_HALF. Give condition rownumber>tot_rec/2 in SECOND_HALF. Connect the 2 groups to 2 different targets.

There is a source table containing 2 columns Col1 and Col2 with data as follows:

Col1   Col2
 a          l
 b         p
 a         m
 a         n
 b         q
 x          y

Design a mapping to load a target table with following values from the above mentioned source:

Col1    Col2
  a        l,m,n
  b       p,q
  x        y


Use a sorter transformation after the source qualifier to sort the values with col1 as key. Build an expression transformation with following ports(order of ports should also be the same):

1. Col1_prev : It will be a variable type port. Expression should contain a variable e.g val
2. Col1 : It will be Input/Output port from Sorter transformation
3. Col2 : It will be input port from sorter transformation
4. val : It will be a variable type port. Expression should contain Col1
5. Concatenated_value: It will be a variable type port. Expression should be decode(Col1,Col1_prev,Concatenated_value||’,’||Col2,Col1)
6. Concatenated_Final : It will be an outpur port conating the value of Concatenated_value

After expression, build a Aggregator Transformation. Bring ports Col1 and Concatenated_Final into aggregator. Group by Col1. Don’t give any expression. This effectively will return the last row from each group.

Connect the ports Col1 and Concatenated_Final from aggregator to the target table.

There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.

Solution :

Bring all the columns from source qualifier to an Aggregator transformation. Check group by on the key column. Create a new output port count_col in aggregator transformation and write an expression count(key_column). Make a router transformation with 2 groups:Dup and Non-Dup. Check the router conditions count_col>1 in Dup group while count_col=1 in Non-dup group. Load these 2 groups in different targets.

We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:

Col1 Col2 Col3
  a       b       c

There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:


Solution: Not using a Normalizer transformation:

Create 3 expression transformations exp_1,exp_2 and exp_3 with 1 port each. Connect col1 from Source Qualifier to port in exp_1.Connect col2 from Source Qualifier to port in exp_2.Connect col3 from source qualifier to port in exp_3. Make 3 instances of the target. Connect port from exp_1 to target_1. Connect port from exp_2 to target_2 and connect port from exp_3 to target_3.