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.
It is a process of changing the default SQL generated by source qualifier transformation.
If the source is a database, then Informatica recommends using source filter condition to filter the data rather than using filter transformation. It improves the performance of data extraction.
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 a source definition with EMP metadata
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 workspace
Connect the ports from source qualifier transformation to the target.
Double click on the header of source qualifier T/R click on the properties tab
| T/R Attribute | Value | |
| Source filter |
|
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 workflows
User-defined joins:
If both the source coming from the same database then Informatica recommends using user-defined joins rather then using joiner transformation.
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 workflow
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 database system.
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 the 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 |
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
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 another source is designated as detailed
A master source is a source which occupies the 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 a heterogeneous join.
Ex: oracle + SQL server
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 in-depth understanding of Informatica click on
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
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 in the market.
