2

Source Qualifier Transformation in Informatica

Source Qualifier Transformation in Informatica

Source Qualifier Transformation Overview

The Source Qualifier Transformation in Informatica is an active, connected transformation. It selects records from flat files and relational sources. Attributes or columns used in the Source Qualifier output connections are then passed to additional mapping transformations. Additionally, it converts data from the source’s native datatype to a compatible PowerCenter transformation datatype. For relational sources, if we do not code a custom SQL statement, SQL is generated dynamically to extract data.

Business Purpose

The Source Qualifier Transformation in Informatica provides an efficient way to filter input fields/columns. Many times we do this through performing homogeneous joins, a join made on the same data source (Ex: Oracle + Oracle, DB2 + DB2, Teradata + Teradata).

Ports Tab Example

Source Qualifier Transformation in Informatica Ports Tab

Source Qualifier Transformation in Informatica Ports Tab

Properties Tab

Source Qualifier Transformation in Informatica Properties Tab

Source Qualifier Transformation in Informatica Properties Tab

Below we describe the different properties within the properties tab:

Property Description
SQL Query Allows you to override the default SQL query that PowerCenter creates at runtime.
User Defined Join Allows you to specify a join that replaces the default join created by PowerCenter.
Source Filter Allows you to create a where clause that will be inserted into the SQL query that is generated at runtime. The “where” portion of the statement is not required. For example: Employee.ID = Person.ID
Number of Sorted Ports PowerCenter will insert an order by clause in the generated SQL query. The order by will be on the number of ports specified, from the top down. For example, in the SQ_SALES Source Qualifier, if the number of sorted ports = 2, the order by will be:
ORDER BY SALES.SALES_ID, SALES.AGENT_ID.
Tracing Level Specifies the amount of detail written to the session log.
Select Distinct Allows you to select distinct values only.
Pre SQL Allows you to specify SQL that will be run prior to the pipeline being run. The SQL will be run using the connection specified in the session task.
Post SQL Allows you to specify SQL that will be run after the pipeline has been run. The SQL will be run using the connection specified in the session task.

Custom SQL Query

We touched on the SQL Query property in the last section, but there are some extra tips and tricks I thought needed some more attention. As previously stated, this property lets us code a custom SQL statement into the Source Qualifier. Instead of the default SQL statement, the Integration Service uses our custom SQL to extract data from a data source.

SQL Example

SELECT FIRST_NAME, LAST_NAME, FULL_NAME
FROM PERSON

One tip when overriding SQL is to make sure the columns in your SELECT statement align with the ports you map from the Source Qualifier to your next transformations. Using the example SQL statement above, we want to make sure FIRST_NAME, LAST_NAME, and FULL_NAME are the only ports in our Source Qualifier transformation. If we have more ports, we have to be careful to connect sort these ports in the same order as the SELECT statement. The point is, the number of lines we map and the order we map them need to align with the number and order in our SELECT statement.

Velocity Best Practice

Velocity recommends the following naming standard for the Source Qualifier Transformation in Informatica.

SQ_{FUNCTION}

Example: SQ_PERSON

Summary

The Source Qualifier Transformation in Informatica helps us select records from flat files and relational database sources. In my experience, the SQL Query property is heavily used to code custom SQL statements. These simple to complex custom SQL statements help us extract specific data according to our individual mapping needs.

Aaron Gendle

Aaron Gendle

Senior Consultant & Data Architect at Eccella
As a data architect, I have the privilege of solving challenging data problems on a daily basis.

I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.

Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.

Find Aaron Gendle on LinkedIn -
Aaron Gendle

Comments 2

  1. Nice post on source qualifier transformation. This is very helpful for learning informatica , great work! Looking forward to more tutorial based posts such as these covering more informatica related concepts.

    1. Post
      Author

Leave a Reply

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