07 October, 2020
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
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.
Create the following source definition
[empno, ename, job, sal, deptno, dname, loc]
[empno, ename, job, sal, deptno,deptno]
[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
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
It can receive the data from source pipe line
It provides the data further processing (or) loading.
Procedure:
Create the following source definition
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
Create a target definition with name EMP_DEPT_Hetrogenious
Empno,ename,sal,job,deptno,dname,loc
For indepth understanding of Informatica, click on
TekSlate
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .