• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

SQL –override –joins and aggregation in Informatica

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

SQL –override –joins and aggregation in Informatica

Cache is created only for master source

SQL –override –joins and aggregation in Informatica

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

  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

  1. 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

Summary
Review Date
Reviewed Item
SQL –override –joins and aggregation in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on SQL –override –joins and aggregation in Informatica"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.