Informatica Powercenter – Source Qualifier Trasformation

Here’s what the official documentation says about Source Qualifier transformation:

The Source Qualifier transformation represents the rows that the Integration Service reads when it runs a session. The Source Qualifier transformation is an active transformation.

So, Source Qualifier happens when you drag a source to the mapping designer.

Here’s our source:

Here’s what happens when I drag this source inside a mapping:

I dragged the source, the green one, and Powercenter created the yellow one, the Source Qualifier. With source qualifier, we can define and override how the data is fetched from the source.

Here’s a list of what you can do using Source Qualifier, as seen in Informatica Powercenter official docs:

Source Qualifier Transformation Datatypes

As you can see, here all our SQ datatype is nstring and the mapping is valid.

The transformation datatypes determine how the source database binds data when the Integration Service reads it.

Only change the datatype when required. If the datatypes in the source definition and Source Qualifier transformation do not match, the Designer marks the mapping invalid when you save it.

Check the image below. The mapping is invalid after I changed Source Qualifier column CustomerID datatype to binary.

Source Qualifier SQL Joins

Instead of using join transformations you can use Source Qualifier to join all tables if they are related within the same source, meaning, if your tables have relationships they can be used as once using Source Qualifier transformation.

Here’s what the official doc says: “You can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.”

To do this you must first have some tables related as source. Like this:

Here’s a view of those tables in my SQL database:

Once you’ve done that you can create a new mapping and drag all those tables within.

Remove all Source Qualifiers.

Now, add a new SQ.

This is the result:

A quick trick, you can right click your SQ and ask it to generate a target based on itself.

Source Qualifier SQL Query

So, you now have all columns/attributes you need from your joined table. In this next step you can either write a SQL statement or delete the ports you don’t want.

I selected those duplicated columns for CustomerID and removed it by clicking the “cut” button.

Once again I right click my SQ and choose “create and add target”. I also drag each field from SQ to target to create the final mapping.

Since I will need a table like the one in the target (the purple table) to receive the result data, I will then create this table in my database. To do that I will go to: target > select the table that I want >

Then go to the menu targets > Generate/Execute SQL >

Make sure the be connected to the correct database, the image below shows SQL_DW > hit Generate and Execute.

Here’s the table created in the database.

After that, if we want to get data from our mapping to input data in our new table we need to create and run a workflow.

To create a workflow simply right click your mapping and choose > generate workflow > next next finish.

Choose your worfklow and run/start it.

Here’s the result once the worflow is done:

Source Qualifier Properties

Let’s explore some of SQ properties.

SQL Query: here you can paste/write your own SQL query. It is much like what we did with the column deletion on our previous example.

User Defined Join: Entering a user-defined join is similar to entering a custom SQL query. However, you only enter the contents of the WHERE clause, not the entire query. More about this here: https://docs.informatica.com/data-integration/powercenter/10-4-0/transformation-guide/source-qualifier-transformation/entering-a-user-defined-join.html

Source Filter: just like a where clause. You can enter a source filter to reduce the number of rows the Integration Service queries. If you include the string ‘WHERE’ or large objects in the source filter, the Integration Service fails the session.

The two images above shows a Source Filter being used and the result in our database after the workflow was executed.

Number of sorted ports: When you use sorted ports, the Integration Service adds the ports to the ORDER BY clause in the default query. The Integration Service adds the configured number of ports, starting at the top of the Source Qualifier transformation. Meaning, in our example, if 1 is the value for sorted ports, then CustomerID will be sorted.

Select Distinct: the name speaks for itself.

Pre and Post SQL: The Integration Service 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.

Output is deterministic: A transformation generates deterministic output when it always creates the same output data from the same input data. https://docs.informatica.com/data-integration/powercenter/10-2/advanced-workflow-guide/workflow-recovery/working-with-repeatable-data/transformation-repeatability/output-is-deterministic.html

Output is repeatable: A transformation generates repeatable data when it generates rows in the same order between session runs. Transformations produce repeatable data based on the transformation type, the transformation configuration, or the mapping configuration. More here: https://docs.informatica.com/data-integration/powercenter/10-5/advanced-workflow-guide/workflow-recovery/working-with-repeatable-data/transformation-repeatability/output-is-repeatable.html

References

https://www.guru99.com/source-qualifier-transformation-informatica.html

https://docs.informatica.com/data-integration/powercenter/10-4-0/transformation-guide/source-qualifier-transformation/source-qualifier-transformation-overview.html

https://youtu.be/Dg6BmLQLoGI – Source Qualifier Transformation in informatica | Informatica Tutorial