Source Qualifier Transformation

Source Qualifier Transformation

By Unknown - Saturday, May 21, 2016 - No Comments

Source Qualifier Transformation in Informatica

The source qualifier transformation is an active,connected transformation used to represent the rows that the integrations service reads when it runs a session. You need to connect the source qualifier transformation to the relational or flat file definition in a mapping. The source qualifier transformation converts the source data types to the Informatica native data types. So, you should not alter the data types of the ports in the source qualifier transformation.

• It is only transformation that is not reusable.
• Default transformation except in case of XML or COBOL files.

The source qualifier transformation is used to do the following tasks:

• Joins: You can join two or more tables from the same source database. By default the sources are joined based on the primary key-foreign key relationships. This can be changed by explicitly specifying the join condition in the "user-defined join" property.
• Filter rows: You can filter the rows from the source database. The integration service adds a WHERE clause to the default query.
• Sorting input: You can sort the source data by specifying the number for sorted ports. The Integration Service adds an ORDER BY clause to the default SQL query
• Distinct rows: You can get distinct rows from the source by choosing the "Select Distinct" property. The Integration Service adds a SELECT DISTINCT statement to the default SQL query.
• Custom SQL Query: You can write your own SQL query to do calculations.

Creating Source Qualifier Transformation:

The easiest method to create a source qualifier transformation is to drag the source definition in to a mapping. This will create the source qualifier transformation automatically.

Follow the below steps to create the source qualifier transformation manually.

• Click Transformation -> Create.
• Select the Source Qualifier transformation.
• Enter a name for the transformation
• Click on create.

• Select a source, click OK and then click Done.

Now you can see in the below image how the source qualifier transformation is connected to the source definition.

Source Qualifier Transformation Properties:

We can configure the following source qualifier transformation properties on the properties tab. To go to the properties tab, open the source qualifier transformation by double clicking on it and then click on the properties tab.

Property	Description
SQL Query	To specify a custom query which replaces the default query.
User-Defined Join	Condition used for joining multiple sources.
Source Filter	Specifies the filter condition the Integration Service applies when querying rows.
Number of Sorted Ports	Used for sorting the source data
Tracing Level	Sets the amount of detail included in the session log when you run a session containing this transformation.
Select Distinct	To select only unique rows from the source.
Pre-SQL	Pre-session SQL commands to run against the source database before the Integration Service reads the source.
Post-SQL	Post-session SQL commands to run against the source database after the Integration Service writes to the target.
Output is Deterministic	Specify only when the source output does not change between session runs.
Output is Repeatable	Specify only when the order of the source output is same between the session runs.

Note: For flat file source definitions, all the properties except the Tracing level will be disabled.

To Understand the following, Please create the employees and departments tables in the source and emp_dept table in the target database.

create table DEPARTMENTS
(
  DEPARTMENT_ID   NUMBER(4) not null,
  DEPARTMENT_NAME VARCHAR2(15) not null,
  MANAGER_ID      NUMBER(6)
);

create table EMPLOYEES
(
  EMPLOYEE_ID   NUMBER(6) not null,
  NAME          VARCHAR2(10),
  SALARY        NUMBER(10,2),
  MANAGER_ID    NUMBER(6),
  DEPARTMENT_ID NUMBER(4)
);

create table EMP_DEPT
(
  EMPLOYEE_ID   NUMBER(6) not null,
  NAME          VARCHAR2(10),
  SALARY        NUMBER(10,2),
  MANAGER_ID    NUMBER(6),
  DEPARTMENT_ID NUMBER(4),
  DEPARTMENT_NAME VARCHAR2(15) not null
);

Viewing the Default Query or Generating the SQL query:

For relational sources, the Integration Service generates a query for each Source Qualifier transformation when it runs a session. To view the default query generated, just follow the below steps:

• Go to the Properties tab, select "SQL Query" property. Then open the SQL Editor, select the "ODBC data source" and enter the username, password.
• Click Generate SQL.
• Click Cancel to exit.

The default query generated in this case is

SELECT employees.employee_id,
 employees.name,
 employees.salary,
 employees.manager_id,
 employees.department_id 
FROM  employees

You can write your own SQL query rather than relaying the default query for performing calculations. 

Note: You can generate the SQL query only if the output ports of source qualifier transformation is connected to any other transformation in the mapping. The SQL query generated contains only the columns or ports which are connected to the downstream transformations.

Specifying the "Source Filter, Number Of Sorted Ports and Select Distinct" properties:

Follow the below steps for specifying the filter condition, sorting the source data and for selecting the distinct rows.

• Go to the properties tab.
• Select "Source Filter" property, open the editor and enter the filter condition (Example: employees.department_id=100) and click OK.
• Go to the "Number Of Sorted Ports" property and enter a value (Example: 2). This value (2) means to sort the data on the first two ports in the source qualifier transformation.
• Tick the check box for the "Select Distinct" property.

Now follow the steps for "Generating the SQL query" and generate the SQL query. The SQL query generated is

SELECT  DISTINCT employees.employee_id, 
 employees.name, 
 employees.salary, 
 employees.manager_id, 
 employees.department_id 
FROM  employees 
WHERE  employees.department_id=100 
ORDER BY employees.employee_id, employees.name

Observe the DISTINCT, WHERE and ORDER BY clauses in the SQL query generated. The order by clause contains the first two ports in the source qualifier transformation. If you want to sort the data on department_id, salary ports; simply move these ports to top position in the source qualifier transformationa and specify the "Number Of Sorted Ports" property as 2

Joins:

The SQL transformation can be used to join sources from the same database. By default it joins the sources based on the primary-key, foreign-key relationships. To join heterogeneous sources, use Joiner Transformation.
A foreign-key is created on the department_id column of the employees table, which references the primary-key column, department_id, of the departments table. 
Follow the below steps to see the default join

Create only one source qualifier transformation for both the employees and departments.
Go to the properties tab of the source qualifier transformation, select the "SQL QUERY" property and generate the SQL query.

The Generated SQL query is

SELECT employees.employee_id,
 employees.name,
 employees.salary,
 employees.manager_id,
 employees.department_id,
 departments.department_name 
FROM  employees,
 departments
WHERE  departments.department_id=employees.department_id

You can see the employees and departments tables are joined on the department_id column in the WHERE clause.

There might be case where there won't be any relationship between the sources. In that case, we need to override the default join. To do this we have to specify the join condition in the "User Defined Join" Property. Using this property we can specify outer joins also. The join conditions entered here are database specific.
As an example, if we want to join the employees and departments table on the manager_id column, then in the "User Defined Join" property specify the join condition as "departments.manager_id=employees.manager_id". Now generate the SQL and observe the WHERE clause.

Pre and Post SQL:

You can add the Pre-SQL and Post-SQL commands. The integration service runs the Pre-SQL and Post-SQL before and after reading the source data respectively.

Here i am providing some basic examples of using source qualifier transformation in your mappings. This is a continuation to my previous posts, Learn Source Qualifier Transformation,Quiz on Source Qualifier Transformation.

To solve these examples create the employees and departments tables in your database. The "create table" statements are provided below.

create table DEPARTMENTS
(
  DEPARTMENT_ID   NUMBER(4) not null,
  DEPARTMENT_NAME VARCHAR2(15) not null,
  MANAGER_ID      NUMBER(6)
)

alter table DEPARTMENTS add primary key (DEPARTMENT_ID);

create table EMPLOYEES
(
  EMPLOYEE_ID   NUMBER(6) not null,
  NAME    VARCHAR2(10),
  LAST_NAME     VARCHAR2(10),
  SALARY        NUMBER(10,2),
  MANAGER_ID    NUMBER(6),
  DEPARTMENT_ID NUMBER(4)
)
alter table EMPLOYEES add primary key (EMPLOYEE_ID);

alter table EMPLOYEES add foreign key (DEPARTMENT_ID) references DEPARTMENTS (DEPARTMENT_ID);

Table Name: Employees
EMPLOYEE_ID NAME  SALARY MANAGER_ID DEPARTMENT_ID
-------------------------------------------------
101         PAT   1000   201        10
102         KEVIN 2000   201        10
201         MIKE  5000   NULL       10
301         JOHN  7000   NULL       NULL

Table Name: Departments

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
-----------------------------------------
10            Account         201
20            HR              501

SQ PROPERTIES TAB

1) SOURCE FILTER:

We can enter a source filter to reduce the number of rows the Power Center Server queries.

Note: When we enter a source filter in the session properties, we override the customized SQL query in the Source Qualifier transformation.

Steps:

1. In the Mapping Designer, open a Source Qualifier transformation.
2. Select the Properties tab.
3. Click the Open button in the Source Filter field.
4. In the SQL Editor Dialog box, enter the filter. Example: EMP.SAL)2000
5. Click OK.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.

2) NUMBER OF SORTED PORTS:

When we use sorted ports, the Power Center Server adds the ports to the ORDER BY clause in the default query.

By default it is 0. If we change it to 1, then the data will be sorted by column that is at the top in SQ. Example: DEPTNO in above figure.

• If we want to sort as per ENAME, move ENAME to top.
• If we change it to 2, then data will be sorted by top two columns.

Steps:

1. In the Mapping Designer, open a Source Qualifier transformation.
2. Select the Properties tab.
3. Enter any number instead of zero for Number of Sorted ports.
4. Click Apply -> Click OK.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.

3) SELECT DISTINCT:

If we want the Power Center Server to select unique values from a source, we can use the Select Distinct option.

• Just check the option in Properties tab to enable it.

4) PRE and POST SQL Commands

• The Power Center Server runs pre-session SQL commands against the source database before it reads the source.
• It runs post-session SQL commands against the source database after it writes to the target.
• Use a semi-colon (;) to separate multiple statements.

5) USER DEFINED JOINS

Entering a user-defined join is similar to entering a custom SQL query. However, we only enter the contents of the WHERE clause, not the entire query.

• We can specify equi join, left outer join and right outer join only. We Cannot specify full outer join. To use full outer join, we need to write SQL Query.

Steps:

1. Open the Source Qualifier transformation, and click the Properties tab.
2. Click the Open button in the User Defined Join field. The SQL Editor Dialog Box appears.
3. Enter the syntax for the join.
4. Click OK -> Again Ok.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.

Join Type	Syntax
Equi Join	DEPT.DEPTNO=EMP.DEPTNO
Left Outer Join	{EMP LEFT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}
Right Outer Join	{EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

6) SQL QUERY

For relational sources, the Power Center Server generates a query for each Source Qualifier transformation when it runs a session. The default query is a SELECT statement for each source column used in the mapping. In other words, the Power Center Server reads only the columns that are connected to another Transformation.

In mapping above, we are passing only SAL and DEPTNO from SQ_EMP to Aggregator transformation. Default query generated will be:

• SELECT EMP.SAL, EMP.DEPTNO FROM EMP

Viewing the Default Query

1. Open the Source Qualifier transformation, and click the Properties tab.
2. Open SQL Query. The SQL Editor displays.
3. Click Generate SQL.
4. The SQL Editor displays the default query the Power Center Server uses to Select source data.
5. Click Cancel to exit.

Note: If we do not cancel the SQL query, the Power Center Server overrides the default query with the custom SQL query.

We can enter an SQL statement supported by our source database. Before entering the query, connect all the input and output ports we want to use in the mapping.

Example: As in our case, we can’t use full outer join in user defined join, we can write SQL query for FULL OUTER JOIN:

SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO WHERE SAL>2000

• We also added WHERE clause. We can enter more conditions and write More complex SQL.

We can write any query. We can join as many tables in one query as Required if all are in same database. It is very handy and used in most of the projects.

Important Points:

• When creating a custom SQL query, the SELECT statement must list the port names in the order in which they appear in the transformation.

Example: DEPTNO is top column; DNAME is second in our SQ   mapping.

So when we write SQL Query, SELECT statement have name DNAME first, DNAME second and so on. SELECT DEPT.DEPTNO, DEPT.DNAME

• Once we have written a custom query like above, then this query will Always be used to fetch data from database. In our example, we used WHERE SAL>2000. Now if we use Source Filter and give condition SAL) 1000 or any other, then it will not work. Informatica will always use the custom query only.
• Make sure to test the query in database first before using it in SQL Query. If query is not running in database, then it won’t work in Informatica too.
• Also always connect to the database and validate the SQL in SQL query editor.

Try Solving the below examples:

1. Create a mapping to join employees and departments table on " DEPARTMENT_ID " column using source qualifier transformation?

Solution:

1. Source qualifier transformation can be used to join sources only from the same database.
2. Connect the source definitions of departments and employees to the same qualifier transformation.
3. As there is a primary-key, foreign-key relationship between the source tables, the source qualifier transformation by default joins the two sources on the DEPARTMENT_ID column.

2. Create a mapping to join employees and departments table on "MANAGER_ID" column using source qualifier transformation?

Solution:

1. Connect the source definitions of departments and employees to the same qualifier transformation.
2. Go to the properties tab of source qualifier ->User Defined Join and then open the editor. Enter the join condition as DEPARTMENTS.MANAGER_ID = EMPLOYEES.MANAGER_ID. Click Ok.
3. Now connect the required ports from the source qualifier transformation to the target.

3. Create a mapping to get only the employees who have manager?

Solution:

This is very simple. Go to the properties tab of source qualifier-> Source Filter. Open the editor and enter EMPLOYEES.MANAGER_ID IS NOT NULL

4. Create a mapping to sort the data of employees table on DEPARTMENT_ID, SALARY?

Solution:

Make sure the ports order in the source qualifier transformation as shown below

DEPARTMENT_ID
SALARY
EMPLOYEE_ID
NAME
LAST_NAME
MANAGER_ID

The first two ports should be DEPARTMENT_ID, SALARY and the rest of the ports can be in any order.

Now go to the properties tab of source qualifier-> Number Of Sorted Ports. Make the Number Of Sorted Ports value as 2.

5. Create a mapping to get only distinct departments in employees table?

Solution:

1. The source qualifier transformation should only contain the DEPARTMENT_ID port from EMPLOYEES source definition.
2. Now go to the properties tab of source qualifier-> Select Distinct. Check the check box of Select Distinct option.

Share This:

• Like
• Tweet
• +
• Pin it

Post Tags:

• Prevoius
• Next

Tharun Katanguru

I'm Tharun Katanguru. A full time web designer and Data Engineer. I enjoy to make modern template. I love create blogger template and write about Web Design, Data Warehousing and Data Management. Now I'm working as a Jr Etl Developer.

• Social Links:

• Rss
• Facebook
• Twitter
• Google+
• Linkedin
• Youtube