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
Desired to gain proficiency on Informatica? Explore the blog post on Informatica training in Hyderabad to become a pro in Informatica.
Homogeneous join:
Create the following source definition
- EMP(oracle)
- 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
- Input group:
It can receive the data from source pipe line
- 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
- EMP(oracle)
- 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
- EMP (oracle)
- 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