Normalizer transformation scenario

How can we do this:
StudentName Subject1 Marks1 Subject2 Marks2
Sam Maths 100 English 80
John Maths 85 English 75

to the target:

StudentName Subject Marks
Sam Maths 100
Sam English 80
John Maths 85
John English 75

Answer :

In this way we can get the above output

Create a Stored Procedure in Oracle

Please comment if you like the video

Import Stored procedure in Informatica

Please comment if you like the video.. Thanks!!!

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.