SQL –over ride –joins and aggregation in Informatica

Step1: creation of source definition:

From tools menu select source analyzer

From source menu click on create

Enter the source name select the data base type oracle

Click on create and done

Double click on source definition select the columns tab:

Column S Name	Data type	Precession	Scale
Dept no	Number(p,s)	10	2
Dname	Varchar2(20)
Sum(SAL)	Number(p,s)	10	2

Click apply ,click ok

From repository menu click on save

Step:2

Create a target definition with the name TRG (dept no, dname, sum (sal))

Step3:

Create a mapping with the name  M_SQL_Aggrigation_join

Drop source and target definition

From source qualifier connect the ports to target

Double click on source quantifier T/R select properties tab

Transformation Attribute	Value
SQL Query	Select Emp. Deptno, Dept. Dname, Sum(SAL) From emp, Dept where emp.deptno= dept.deptno group by emp.deptno , dept.Dname order by emp.Deptno

Click apply and click ok

Types of joins:

Normal join:

It combined the data records based on equality match (=) (Emp .dept No= dept. dept No)

Master join:

It combines all the records from detailed source + mach rows from master source

Detailed outer join:

It combines all the rows from master source + matching records from detailed source

Full outer join:

It combines matching + non matching records from both master and detailed source

Joiner cache – how it works:

There are 2 types of cache memory, index and data cache.

All rows from the master source and loaded into cache memory

The index cache contains all ports values from the master source where the port is specified in the join condition.

The data cache contains all port values not specified in the condition.

After the cache is loaded the detail source is compared row by row to the values in the index cache.

Up on match the row from the data cache are included in the stream.

Cache is created only for master source

Cache is created only for master source

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

Homogeneous join:

Create the following source definition

1. EMP(oracle)
2. Dept(oracle)

• Create a target definition with the name Emp – Dept

[empno, ename, job, sal, deptno, dname, loc]

• Create a mapping with the name M_Emp_data_ Join
• Drop the sources and target definitions
• Create the transformation type joiner
• From SQ_Emp copy the following ports to joiner transformation

[empno, ename, job, sal, deptno,deptno]

• From SQ- Dept copy the following ports to joiner T/R

[deptno, dname,loc]

Double click on joiner transformation select the condition tab

From tool bar click on add a new condition.

MASTER	Operator	Detail
Dept no1	=	deptno

Click apply ,click ok

• From joiner transformation connect the ports to the target from repository menu click on save

Union transformation:

This is the type of an activity transformation which combined the data records vertically from multiple source having same meta data

The union transformation also supports heterogeneous data sources.

Ex1:

Emp(oracle), employee (SQL server)

Ex2:

Emp(oracle), emp.txt(flat file)

The union transformation is created with the 2 groups

1. Input group:

It can receive the data from source pipe line

2. Output group:

It provides the data further processing (or) loading.

• The union transformation function as ”UNION ALL” set operator

Procedure:

Create the following source definition

1. EMP(oracle)
2. Employee(oracle)

Create a target definition with name emp_union

Empno,ename,job,sal,deptno

Create a mapping with the name M_data _ union

Drop the source and target definition

Create the transformation type union

Double click on union transformation select the groups tab

From tool bar click on add a new group

Input group name:

Emp – input

Employee – input

Select the group port tab

Port Name	Data type	precession	Scale
Empno	Decimal	7	2
Ename	Varchar2	7	2
Job	Varchar2	7	2
Sal	Decimal	7	2
dept	decimal	7	2

Click apply, and click ok

From SQ- EMP connects to first input group

From SQ- Employee  connects to 2nd input group

From output group connects the ports to the target

From repository menu click on save

Hydrogenous joins:

Procedure:

Create the following source definition

1. EMP (oracle)
2. Dept(SQL server)

Create a target definition with name EMP_DEPT_Hetrogenious

Empno,ename,sal,job,deptno,dname,loc

For indepth understanding of Informatica, click on

• Rank Transformation in Informatica
• Aggregator Transformation in Informatica
• Expression Transformation in Informatica
• Lookup Transformation in Informatica
• ETL Project Architecture
• Interview Questions