Informatica Real Time Scenarios - Solutions

Informatica Real Time Scenarios:

Q1) Alternate Target Loading
My source is a flat file which contains N number of records. I want to load the source data into two targets such that first five records should loaded into the first target, next five records into the second target table. Again the next source five records into the first target table and so on. How to implement a Informatica mapping logic for this?
Solution:

• Connect the source qualifier transformation to the expression transformation. In the expression transformation, create the below additional ports:

v_cnt (variable port) = v_cnt+1
o_cnt (output port) = v_cnt

• Connect the expression transformation to the router transformation. Create two output groups in the router transformation and specify the following filter conditions:

--Filter condition for first output group
DECODE(substr(o_cnt,-1,1),1,TRUE,2,TRUE,3,TRUE,4,TRUE,5,TRUE,FALSE)
--Filter condition for second output group
DECODE(substr(o_cnt,-1,1),6,TRUE,7,TRUE,8,TRUE,9,TRUE,0,TRUE,FALSE)

• Connect the router transformation output groups to the appropriate targets.

Informatica online training - Informatica Jobs

Q2) Load source data in multiple session run.

I have flat file as a source which contains N number of records. My requirement is to load half of the source data into the target table in the first session run and the remaining half of the records in the second session run. Create Informatica mapping to implement this logic? Assume that the source data does not change between session runs.
Solution:

• Create a mapping to find out the number of records in the source and write the count to a parameter file. Let call this parameter as $$SOURCE_COUNT.
• Create another mapping. Go to the mapping parameters and variables, create a mapping variable ($$VAR_SESSION_RUNS) with integer data type.
• Connect the source qualifier transformation to the expression transformation. In the expression transformation, create the below additional ports.

v_Count (variable port) = v_Count+1
O_Run_flag (output port) = IIF($$vAR_SESSION_RUNS=0, 
                                  setvariable($$vAR_SESSION_RUNS,1), 
      IIF( !ISNULL($$vAR_SESSION_RUNS) 
                                          and v_Count=1,
                                              2, 
                                              $$vAR_SESSION_RUNS) 
          )
O_count (output port) = V_Count

• Connect the expression transformation to the filter transformation and specify the following filter condition:

IIF (O_Run_Flag =1, v_count<= $$SOURCE_COUNT/2,
IIF (O_Run_Flag =2, v_count > $$SOURCE_COUNT/2))

• Connect the filter transformation to the target.
• Here i am assuming that you know how to use a parameter file. That is why I did not specify the complete details.

Informatica Problems With Solutions - Part 1

1. In this problem we will see how to implement the not equal operator, greater than, greater than or equal to, less than and less than or equal to operators when joining two tables in informatica.

Consider the below sales table as an example?
Table name: Sales

product, prod_quantity, price , Year
A         , 10                 , 100  , 2010
B         , 15                 , 150  , 2010
A         , 8                   , 80    , 2011
B         , 26                 , 260  , 2011

Now the problem is to identify the products whose sales is less than in the current year (In this example: 2011) when compared to the last year.

Here in this example, Product A sold less in 2011 when compared with the sales in 2010.
This problem can be easily implemented with the help of SQL query as shown below

SELECT  cy.*
FROM    SALES cy,
SALES py
WHERE   cy.product = py.product
AND        cy.year=2011
AND        py.year=2010
AND       cy.prod_quantity < py.prod_quantity;

In informatica, you can specify only equal to condition in joiner. Now we will see how to implement this problem using informatica.

Solution:
Informatica online training - Informatica Jobs
STEP1: Connect two source qualifier transformations to the source definition. Call the first source qualifier transformation as sq_cy  (cy means current year) and the other as sq_py  (py means previous year).
STEP2: In the sq_cy source qualifier transformation, specify the source filter as price=2011. In the sq_py, specify the source filter as price=2010
STEP3: Now connect these two source qualifier transformations to joiner transformation and make sq_cy as master, sq_py as detail. In the join condition, select the product port from master and detail.
STEP4: Now connect all the master ports and only the prod_quantity port from detail to the filter transformation. In the filter transformation specify the filter condition as prod_quantity < prod_quantity1. Here pord_quantity port is from master port and prod_quantity1 is from detail port.
STEP4: Connect all the ports except the prod_quantity1 of filter transformation to the target definition.
2. How to implement the not exists operator in informatica which is available in database?
Solution:
Implementing the Not Exists operator is very easy in informatica. For example, we want to get only the records which are available in table A and not in table B. For this use a joiner transformation with A as master and B as detail. Specify the join condition and in the join type, select detail outer join. This will get all the records from A table and only the matching records from B table.
Connect the joiner to a filter transformation and specify the filter condition as B_port is NULL. This will give the records which are in A and not in B. Then connect the filter to the target definition.