informatica scenarios

Information Storage and Retrieval

Informatica Scenario based Questions and Solutions (Part-1)

Scenario1:

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 contain only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:

Col
-----
a
b
c

Solution: Not using a Normaliser 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.

Scenario 2:

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.

Scenario 3:

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

Solution:

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.

Scenario 4:

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

Solution:

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.

Informatica Scenario - 5

Previous Scenarios
Scenario: 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.
Solution:
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          
                   (salary,salary_prev,rank,rank+1)
     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

Router Scenario:6

Lets say  i have more then have record in source table  and i have 3 destination table A,B,C. I have to insert first 1 to 10 record 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 upto last record.

Solution:

Generate sequence number using informatica, add filter or router transformations and define the conditions accordingly…

Define group condition as follows under router groups….

Group1 = mod(seq_number,30) >= 1 and mod(seq_number,30) <= 10
Group2 = mod(seq_number,30) >= 11 and mod(seq_number,30) <= 20
Group3 = (mod(seq_number,30) >=21 and mod(seq_number,30) <= 29 ) or mod(seq_number,30) = 0

Connect Group1 to A, Group2 to B and Group3 to C

Scenario:7

How to load first and last from source

SRC QF -> SEQ -> EXP -> AGR -> RTR -> TGT

create a port in expression t/r and generate a value for 

each row using SEQ genrator, then in Agr take count of all 

th record.create 2 condition in in router one with SEQ = 1 

and another with SEQ = count of rows (this will give last 

row).connect both to target.dont forget to give reset 

option in SEQ generator