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.
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
Below we describe the different properties within the properties tab:
|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.
SELECT FIRST_NAME, LAST_NAME, FULL_NAME
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.
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.
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 -
Latest posts by Aaron Gendle (see all)
- 5 Reasons to Quit Your Job Today - June 20, 2017
- The 2017, Top 50
Data Driven CEO’s - March 1, 2017
- 25+ Advanced Informatica Interview Questions and Answers - February 7, 2015