informatica scenarios

1. scenarios
   

   
   

   SENDING ALTERNATE RECORD TO TARGET

   SOLUTION: Send Odd records to One target and Even Records to another Target.

   Source Table: EMP Table

   Target Tables:

       Create two Target Tables as ‘EMP_ODD’ and ‘EMP_EVEN’ as shown below.. 

   
   

       CREATE TABLE EMP_ODD

   AS

   SELECT * FROM EMP WHERE 1=2;

   
   

       CREATE TABLE EMP_EVEN

   AS

   SELECT * FROM EMP WHERE 1=2;

   
   

   Step 1: Drag the source and connect to an expression transformation.

   Step 2: Create a sequence generator transformation and add Nextval to Expression Transformation. 

   
   

   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   Step 3: In expression Transformation create the two ports as ‘ODD’ and ‘EVEN’ And parse the expression shown below..

   
   

   

   
   

   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   Step 4: Connect a Router Transformation to Expression, make two group in Router and Give condition Like Below.

   
   

   

   
   

   
   

   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   Step 5 : Now send the create two groups to the respective Target  Tables as Shown below.

   
   

   

   
   

   
   

   
   

   
   

   
   
2. 
   

   FEB

   14

   
   

   Informatica Scenario: 4

   SPLIT NON-KEY COLUMNS TO SEPARATE TABLES WITH KEY COLUMNS IN BOTH

   
   SOLUTION:
   

   Split the non-key columns to separate tables with key column in both  / How to split the data of source table column-wise with respect to primary key. See the source and target tables below.

   
   

   SOURCE:

   
   

   PROD_ID	PROD_NAME	PROD_CATEGORY
   13	5MP Telephoto Digital Camera	CAMERAS
   14	17" LCD w/built-in HDTV Tuner	MONITORS
   15	Envoy 256MB - 40GB	DESKTOP PCS
   16	Laptop carrying case	LAPTOP ACCESSORIES

   
   

   INSERT INTO SRC_PRODUCT VALUES (13, '5MP Telephoto Digital Camera','CAMERAS');

   INSERT INTO SRC_PRODUCT VALUES (14, '17" LCD w/built-in HDTV Tuner','MONITORS');

   INSERT INTO SRC_PRODUCT VALUES (15, 'Envoy 256MB - 40GB','DESKTOP PCS');

   INSERT INTO SRC_PRODUCT VALUES (16, 'Laptop carrying case','LAPTOP ACCESSORIES');

   
   

   TARGET: 

   
   

   
   

   PROD_ID	PROD_NAME
   13	5MP Telephoto Digital Camera
   14	17" LCD w/built-in HDTV Tuner
   15	Envoy 256MB - 40GB
   16	Laptop carrying case

   
   

   PROD_ID	PROD_CATEGORY
   13	CAMERAS
   14	MONITORS
   15	DESKTOP PCS
   16	LAPTOP ACCESSORIES

   
   

   Step 1: 

   Source qualifier: get the source table to the mapping area. See image below.

   Step 2:  

   Drag all the port from (from the previous step) to the Aggregator transformation and group by the key column. Since we have to split the columns to two different tables with the key column in each, so we are going use two expression transformation, each will take the key column and one non-key column. Connect  aggregator transformation with each of the expression transformation as follows.

   
   

   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   Step 3: 

   We need another set of  aggregator to be associated with each of the expression transformation from the previous step.

   Step 4: 

   In the final step connect the aggregators with the two target tables as follows.

   
   

   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

    Step 5: Here is the iconic view of the entire mapping.

   
   

   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   
3. 
   

   FEB

   14

   
   

   Informatca Scenario: 3

   HOW  TO CONVERT ROWS INTO COLUMNS
   
   SOLUTION:
   
   SOURCE:
   
   
   

      

   ID	Month	Sales
   1	Jan	100
   1	Feb	120
   1	March	135
   2	Jan	110
   2	Feb	130
   2	March	120

   
   

   TARGET:
   
   
   

   
   

   ID	Jan	Feb	March
   1	100	120	135
   2	110	130	120

   
   

   
   

   Use the Aggregator group by ID and use First function
   FIRST(AMOUNT, MONTH='JAN')
   FIRST(AMOUNT, MONTH='FEB')
   FIRST(AMOUNT, MONTH='MAR'

     
4. 
   

   FEB

   13

   
   

   Informatca Scenario: 1

   Get top 5 records to target without using rank

    

   SOLUTION:

   Source: EMP Table

   Target : EMP_BKP

   * Same structure as off EMP.

   Step 1: Drag the source to mapping and connect it to sorter transformation.

    

   
   
   
   
   
   
   
   
   
   
   
   

   Step 2: Arrange the salary in descending order in sorter as follows and send the record to expression.

    

   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   Step 3: Now create a Expression Transformation and connect it from sorter.Create a port as "NEXTVAL" with integer data type in Expression Transformation. Also Create a Sequence Transformation, start with 1 and connect nextval port to Expression's Nextval port shown below.

   
   

    

   
   
   
   
   
   
   
   
   
   
   

   

   
   
   
   
   
   
   
   
   
   
   
   

   
   Step 4: Connect the expression transformation to a filter or router. In the property set the condition as follows..

   

   

   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. 
   

   FEB

   12

   
   

   Informatica Scenario: 
   

   Extracting every nth row

   
   

   Scenario: How to load every nth row from a Flat file/ relational DB to the target? Suppose n=3, then in above condition the row numbered 3,6,9,12,....so on, This example takes every 3 row to target table.

   
   

   Solution:

   Step 1:
   Connect an expression transformation after source qualifier. 

   Add the next value port of sequence generator to expression transformation.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   
   

   
   

   
   

   Step 2:

   In expression create a new port (validate) and write the expression like in the picture below.

   
   

   

   
   

   Step 3: 

   Connect a filter transformation to expression and write the condition in property like in the picture below.

   
   

    

   
   

   Step 4:

   Finally connect to target.