How will you approach this : 3

Input :

Student Name	Subject Name	Marks
Srivatsan	Maths	100
Sendil	Maths	95
Ragu	Physical Science	96
Muthu	Maths	75
Uma	Life Science	98
Raja	Life Science	92
Jothi	Physical Science	97
Karthick	Life Science	91

Output :

The output is to team up based on top marks . Each team should have 2 members

Students Name	Team Name	Marks
Srivatsan/Uma	Team1	100/98
Sendil/Raja	Team2	95/92
Ragu/Jothi	Team3	96/97
Muthu/Karthick	Team4	75/91

How will you approach this : 2

Input :

Student Name	Maths	Life Science	Physical Science
Sam	100	70	80
John	75	100	85
Tom	80	100	85

Output :

Student Name	Subject Name	Marks/Avg Marks
Sam	Maths	100/85
Sam	Life Science	70/90
Sam	Physical Science	80/83.33
John	Maths	75/85
John	Life Science	100/90
John	Physical Science	85/83.33
Tom	Maths	80/85
Tom	Life Science	100/90
Tom	Physical Science	85/83.33

PL/SQL : Simple Procedure Example

CREATE OR REPLACE PROCEDURE EXAMPLE_PROC
IS
CURSOR EMP_CUR IS
SELECT *
FROM EMP;

EMP_REC EMP_CUR%ROWTYPE;

BEGIN

OPEN EMP_CUR;

LOOP

FETCH EMP_CUR INTO EMP_REC;
EXIT WHEN EMP_CUR%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(EMP_REC.ENAME);
END LOOP;

CLOSE EMP_CUR;
END;



COMMAND USED TO EXECUTE PROCEDURE :
EXEC EXAMPLE_PROC;

OUTPUT :

JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL : Simple Explicit Cursor Example

DECLARE

CURSOR EMP_CUR IS
SELECT *
FROM EMP;

EMP_REC EMP_CUR%ROWTYPE;

BEGIN

OPEN EMP_CUR;

LOOP

FETCH EMP_CUR INTO EMP_REC;
EXIT WHEN EMP_CUR%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(EMP_REC.ENAME);
END LOOP;

CLOSE EMP_CUR;
END;


INPUT :
NA

OUTPUT :

JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL : Simple Query with two sections

DECLARE                     -- Declaration section
v_ename emp.ename%type ;

BEGIN                        -- Execution section
Select ename into v_ename
from emp
where empno = '7219';

DBMS_OUTPUT.PUT_LINE(v_ename);

END;


INPUT : NA
OUTPUT : John (the corresponding ename for empno given will be displayed)

Connected Lookup vs Unconnected Lookup

Connected Lookup :

1) Connected Lookup can return more than one value.The number of Output port can be more than one.

2) The Cache will store all the lookup columns.

3) Can use both Static and dynamic cache.

4) As name suggests , these lookups are connected with the flow and they get their input from pipeline.



Unconnected Lookup :

1) They can return only one value.

2) The cache will store the return port and output port.

3) Can use only static cache.

4) These lookups are not connected and get their input values through expression (:LKP)

Source Qualifier Transformation and its Properties

Source Qualifier Transformation is an active Transformation. The reason is the output rows count can be modified by using option like "Select Distinct".

There are various properties in Source Qualifier transformation which are very useful for us like

1) We can get the Output from Source Qualifier as Sorted by using the property called "Number of Sorted Ports".

2) We can filter in the Source level itself so that unnecessary records wont be taken into for further process using the Filter property.

3) We can use the "Select Distinct" property to Avoid the Duplicate output from Source Qualifier.

4) We have option to write Pre and Post Sql ., Which will be executed before and after the main Query.

5) We can use the Custom / User defined Sql property to write our Own Sql , So that output is generated based on that.
Note : When we use a user defined/Custom Sql , the Select distinct , Sorter , filter ..etc wont work as it will be overridden by the Custom Sql.

Aggregating without using Aggregator Transformation

In informatica level the aggregator transformations output can be obtained by replacing it with the following transformations

1) Sorter transformation -- Used to sort the data asc/dec

2) Expression Transformation -- basic transformation used almost in all the mappings

3) Filter Transformation -- To obtain the records with specific condn.

Aggregator transformation will reduce the number of records by aggregating on some specific columns given by us .

The Reverse transformation of Aggregator is Normalizer Transformation which will increase the number of Records.

Constraint Based Loading

Whenever we have only one Source Qualifier transformation and multiple targets , our approach in specifying the order of the target is by using the property called "Constraint based loading".

By enabling the Check box the targets will be loaded in a way like the master table is loaded first and then the depending tables .

If we have more than one Source Qualifier transformation then we have to use Target Load plan.

Target Load Plan

Target Load plan is used to load the targets in whichever order we require .

Target Load plan can be used when the mapping has more than one Source Qualifier transformation.
The Order can be specidfied by going to mappings -> Target load plan.

Here we have to order the various Source Qualifier transformations used in our mapping. Thereby it will load the corresponding Target.



What if our mapping has only one Source Qualifier t/f and has to load many targets ?
Please check the following link

Scenario 3 - Aggregator Transformation Example

Input :

Student Name	Subject Name	Marks
Sam	Maths	100
Tom	Maths	80
Sam	Physical Science	80
John	Maths	75
Sam	Life Science	70
John	Life Science	100
John	Physical Science	85
Tom	Life Science	100
Tom	Physical Science	85

Output :

Student Name	Maths	Life Science	Physical Science
Sam	100	70	80
John	75	100	85
Tom	80	100	85

Answer :

Here the transformation which we have to use is Sorter and Aggregator .

Lets see the approach :

1) The Input from the Source Qualifier is sent into the Sorter transformation , And Sorted based on Student name and Subject.
The following output is obtained

Student Name	Subject Name	Marks
Sam	Maths	100
Sam	Life Science	70
Sam	Physical Science	80
John	Maths	75
John	Life Science	100
John	Physical Science	85
Tom	Maths	80
Tom	Life Science	100
Tom	Physical Science	85

2) The output from Sorter transformation is sent into the aggregator transformation and Group by Student name is selected . As we need the final output grouped with student name.
And also in the aggregator transformation three columns are created like
1) Maths --- with formula -- Max(Marks, Subject = 'Maths')
2) Life Science --- with formula -- Max(Marks, Subject = 'Life Science')
3) Physical Science --- with formula -- Max(Marks, Subject = 'Physical Science')

3) Now Student name and Maths , Life Science , Physical Science are connected to the target.
The output obtained is as follows :

Student Name	Maths	Life Science	Physical Science
Sam	100	70	80
John	75	100	85
Tom	80	100	85

Joining Heterogeneous Sources

Joining Heterogeneous Sources can't be done using the Source Qualifier Transformation.

Source Qualifier transformation is used to join homogeneous sources.Whenever we are using a flat-file the Source Qualifier properties gets disabled and we cant use it to join the Sources.

This is one of the reason why we have Joiner transformation , Also when we have Sources which don't have a common column / primary-foreign key relationship , we have to go to joiner transformation.

Joiner T/f is used for following

1) Join data from different Relational Databases.

2) Join data from different Flat Files.

3) Join relational sources and flat files.


Sources which don't have common column can be joined by creating a dummy column using Sequence generator transformation.

How will you approach this : 1

Input :

Name	Year	Transport	House Rent	Food
Sri	2000	200	1500	500
Uma	2000	300	1200	300
Ragu	2000	300	1350	350
Sri	2001	300	1550	450
Uma	2001	350	1200	290
Ragu	2001	350	1400	350

Output :

Name	Year	Expense Type	Expense
Sri	2000	Transport	200
Sri	2000	House rent	1500
Sri	2000	Food	500
Uma	2000	Transport	300
Uma	2000	House rent	1200
Uma	2000	Food	300
Ragu	2000	Transport	300
Ragu	2000	House rent	1350
Ragu	2000	Food	350

Scenario 2

Here we have a scenario to get the below mentioned output from the given input. Let us see how to solve this scenario :

Input

Employee name              Basic            DA             HRA          MedicalA             FoodA
John                              5000             200             1000               500                     500                                  
George                          6000             100               800               500                     200                              
Carol                             3000             700              700                800                     900      

Output Required :

Employee name          Salary_type       Salary_Amt

John                             Basic                  5000                         
John                             DA                     200                         
John                             HRA                  1000                           
John                             MedicalA            500                                 
John                             FoodA                500                                   
George                         Basic                  6000                              
George                         DA                     100                             
George                         HRA                   800                                  
George                         MedicalA            500                                          
George                         FoodA                200                                 
Carol                            Basic                   3000                           
Carol                            DA                      700                        
Carol                            HRA                   700                         
Carol                            MedicalA             800                                   
Carol                            FoodA                 900                   



Answer :

1) The input from the Source has to be connected to Normalizer transformation  .

2) In the normalizer transformation we will get two outputs ., One is Employee name and other is the Amount. The GCID number is also generated , which will give the salary type.

3) In this way the normalizer transformation generates transpose of the input(table).

4) The output from normalizer can be given to an expression transformation to get the Salary type which can be generated from GCID number.
It will be like

Employee name          GCID           Salary_Amt

John                             1                      5000                         
John                             2                      200                         
John                             3                      1000                           
John                             4                      500                                 
John                             5                      500                                   
George                         1                      6000                              
George                         2                      100                             
George                         3                      800                                  
George                         4                      500                                          
George                         5                      200                                 
Carol                            1                      3000                           
Carol                            2                      700                        
Carol                            3                      700                         
Carol                            4                      800                                   
Carol                            5                      900 


4) Its an active transformation and the reverse of aggregator transformation which reduces the number of output.                     

Scenario 1

Suppose we have to load three targets in such a way like

1) the first target should be loaded with 1,4,7... records
2) the second target should  be loaded with 2,5,8... records
and
3) the third target should be loaded with 3,6,9 ... records

How can this be achieved?


Answer :

1) Lets have a expression transformation and load all the data from Source Qualifier to it.

2) Now we will create a Sequence generator transformation to generate the Sequence for identifying the records to be loaded for various targets.

3) Let the Connect the Sequence generator to the expression transformation

4) Lets use a router transformation to distribute the records to the three targets by creating three groups in it. Namely target1 , target2, target3

5) The following conditions are given on the three targets in router transformation to properly send the records to respective targets.
target1 --- mod(Sequence_no,2) = 1
target2 --- mod(Sequence_no,2) = 2
target3 --- mod(Sequence_no,2) = 0   

6) The Outputs of router is connected to respective targets.

How to avoid Duplicate Rows in Informatica

Duplicate Rows in informatica can be avoided by selecting the distinct Check box in the Source Qualifier Transformation.

If  the Source is a Flatfile then it cannot be Prevented by Source Qualifier to give Distinct Data.
For avoiding the duplicate rows we have to use a transformation called Sorter transformation and Select the option called as "Distinct".

One more option to avoid the duplicate rows is by using an aggregator transformation . A groupby in any one of the duplicate column will provide us a result of non duplicate records.