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

Source Qualifier Transformation in Informatica

Source qualifier transformation

It is of type an active transformation which reads the data (or) extract the data from data base and flat files. You can find Interview Questions about Source Qualifier Transformation in the homepage.

SQL override

It is a process of changing th default SQL generated by source qualifier transformation.

Source filter

If the source is a database then Informatica recommends to use source filter condition to filter the data rather than using filter transformation. It improves the performance of data extraction.

Procedure

Create a source definition with EMP meta data

Create a target definition with the name EMP_ SQLfilter

Create a mapping with the name M_ source qualifier filter

Drag the source and target definitions , drop  on mapping designer work space

Connect the ports from source qualifier transformation to the target.

Double click on the header of source qualifier T/R click on properties tab

T/R Attribute Value
Source filter
Emp .sal>2000(or)

Emp. Dept no in(10,20)

Screenshot_13

Add the following order by clause to the SQL query generated by source qualifier

Click on ok

Click on apply, click on ok

Click on save

Create the session and work flows

User defined joins:

If both the source coming from the same data base then Informatica recommends to use userdefined joins rather then using joiner transformation.

Procedure

Create 2 source definition with EMP, dept

create a target definition with the name EMP_SQ_EMP_DEPT_SQJOIN

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

drag the both the source definition emp, dept on to the designer work space, drag the target definition, drop on designer workspace

right click on SQ-dept, click on delete from mapping connect the ports from dept to source qualifier T/R to target double click on the header of source qualifier T/R click on properties tab.

Transformation Attribute Value
User defined join Emp.Deptno = Dept.Deptno

Click on ok

Save mapping, create session and work flow

Source qualifier transformation SQL override

An SQL override is a process of writing user defined SQL statements.

The SQL source qualifier T/R supports SQL over rider when the source is a data base system.

SQL –over ride –joins and aggregation

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

Interested in mastering Informatica Training? Enroll now for FREE demo on Informatica Training Online.

Joiner transformation in Informatica

This is the type an active transformation which allows you to combine the data records horizontally based on join condition.

Joiner transformation combines the data from 2 different sources having different meta data.

A joiner transformation is created with following types of ports

  1. Input port (I)
  2. Out put port (O)
  3. Master port(M)

The joiner transformation can only join 2 input data stream per joiner.

The sources to the joiner transformation designated as master source and detailed sources

A source which is having the lesser no of records is designated as master source and other source is designated as detailed

A master source is the source which occupy least amount of space (or) memory in the cache

Joiner transformation supports homogeneous joins and heterogeneous joins.

A join which is made on the same data source is known as heterogeneous join.

Ex:  oracle + SQL server

  • The joiner transformation is created with the following types of joins
  1. Normal join
  2. Master outer join
  3. Detailed outer join
  4. Full outer join

The default join type is normal join.

The joiner transformation does not support non equi join (<=,>=)

The joiner transformation supports only AND operator but it doesn’t support OR operator.

For indepth understanding of Informatica click on

Summary
Review Date
Reviewed Item
Source Qualifier Transformation 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 Source Qualifier Transformation in Informatica"

Leave a Message

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

Site Disclaimer, Copyright © 2016 - All Rights Reserved.