Source Qualifier Transformation

The Source Qualifier is an Active and Connected Transformation.

Source Qualifier: When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation. The Source Qualifier transformation represents the rows that the Integration Service reads when it runs a session. The Source Qualifier Transformation convert relational or flat file datatypes into Informatica datatypes.

Source Qualifier transformation perform the following tasks:

• Join data originating from the same source database: You can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.
• Filter rows when the Integration Service reads source data: If you include a filter condition, the Integration Service adds a WHERE clause to the default query.
• Specify an outer join rather than the default inner join: If you include a user-defined join, the Integration Service replaces the join information specified by the metadata in the SQL query.
• Specify sorted ports: If you specify a number for sorted ports, the Integration Service adds an ORDER BY clause to the default SQL query.
• Select only distinct values from the source: If you choose Select Distinct, the Integration Service adds a SELECT DISTINCT statement to the default SQL query.
• Create a custom query to issue a special SELECT statement for the Integration Service to read source data: For example, you might use a custom query to perform aggregate calculations.

Sample Mapping:

1. Connect and Open the folder if not already opened.

2. Select Tools --> Mapping Designer

3. Select Mappings --> Create

It will pop-up "Mapping Name". Enter the mapping name of your choice" and Click on 'OK'. Example: m_emp_dept_to_flatfile

4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Table definitions.

The Source Qualifier appearing in the above snapshot is created by default when you drag any source definition into mapping designer.

  Note: You can create source qualifier by:

• By dragging source definition into mapping designer as mentioned above
• By click on SQ button appearing in the below snapshot, then it will pop-up the "Select Sources for Source Qualifier Transformation" where you can select the Source definition which are apart the Source Qualifier Transformation and Click on 'OK'.

    

It will appear 'Select Source for Source Qualifier Transformation'

• By selecting 'Transformation' from menu -> Create. Which will pop-up "Select the Transformation type to Create:" from the dropdown list select the "Source Qualifier" and Entry a new to the transformation

• Click 'Create' and 'Done'

5. Link all port from Source to Target definition as show in below

 

6. Right Click on Source Qualifier --> Edit --> Properties Tab.

To defined SQL Override, User Defined Join, Source Filter, Number Of Sorted Ports and Select Distinct.

Properties Tab:

1) SQL Override:

By default Integration Service generates a query based on the ports that are mapping from SQ (Source Qualifier) to Next Transformation, including if any constraints defined at Source Analyzer after source definition imported.

Note: Constraints copied from database while importing definitions.

A custom query that replaces the default query the Integration Service uses to read data from sources represented in this Source Qualifier transformation. A custom query overrides entries for a custom join or a source filter or a Union or Aggregate function or Sub Query.

Steps:

1. Right Click on SQ --> Edit --> select 'Properties Tab'

2. Click on the "Open Button" highlighted above snapshot. To customize the SQL Override as below:

I. Click on Generate SQL (will give you default query) modify the query as required.

II. By Passing "ODBC Connect, User Name and Password". You can validate the query for any error detection.

III. Click on 'OK' to close SQL Editor.

3. Click 'Apply' and Click 'OK'.

Note: Validate the mapping and save it.

2) User Defined Join:

By user defined join we are added 'WHERE' clause to the default/customize query by specifies the condition used to join data from multiple sources represented in the same Source Qualifier transformation. We can have 3 types of joins: equi join, left outer join and right outer join.

Steps:

1. Right Click on SQ --> Edit --> select 'Properties Tab'

2. Click on the 'Open Button' highlighted below and the SQL Editor will be appear

3. Entry the join condition as below.

 4. Click 'OK' will close SQL Editor.

5. Click 'Apply' and Click 'OK'.

Join Types and Syntax:

Equi Join: DEPT.DEPTNO=EMP.DEPTNO

Left Outer Join: EMP LETF OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO

Right Outer Join: EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO

Note: Validate the mapping and save it.

3) Source Filter:

By specifies the filter condition we are adding it to/as 'WHERE' clause to the default/customize query to which the Integration Service applies when querying rows.

Steps:

1. Right Click on SQ --> Edit --> select 'Properties Tab'

2. Click on the 'Open Button' highlighted below and the SQL Editor will be appear

3. Entry the Filter condition as below.

Example Filter Condition:     EMP.DEPTNO = 10

                                      either

                                      EMP.SAL > 1500

4. Click 'OK' will close SQL Editor.

5. Click 'Apply' and Click 'OK'.

Note: Validate the mapping and save it.

4) Number of Sorted Ports:

If you select this option, the Integration Service adds an ORDER BY to the default/customize query when it reads source rows queried from relational sources. The ORDER BY includes the number of ports specified, starting from the top of the transformation. By default it is 0. If you change it to 1 then it will sort the data based on the column in the SQ. In our case 'EMP.EMPNO', if you change it to 2 then it will sort the data based on 1st two columns EMP.EMPNO, EMP.ENAME.

If you want the specific sort order then you need to modify SQ port order in case default query and in case customize query you need to modify SQL Override query.

Steps:

1. Right Click on SQ --> Edit --> select 'Properties Tab'

2. Entry the number to sort the no of ports.

 3. Click 'Apply' and 'OK'

Note: Validate the mapping and save it.

5) Select Distinct:

If you want to select only unique rows (non duplicate). The Integration Service includes a SELECT DISTINCT statement if you choose this option.

Steps:

1. Right Click on SQ --> Edit --> select 'Properties Tab'

2. Just check 'Select Distinct' box

6) Pre-SQL:

I. Pre-session SQL commands to run against the source database before the Integration Service reads the source.

II. It can have multiple statements separated by (;) with commit;

7) Post-SQL:

I. Post-session SQL commands to run against the source database after the Integration Service writes to the target.

II. It can have multiple statements separated by (;) with commit;

8) Select 'Mapping' from Menu --> Validate.

9) Select 'Repository' from Menu --> Save.

Note: The SQL Override, User Defined Join, Source Filter, Number Of Sorted Ports and Select Distinct are not applicable for Flat File Source.

To know how to work with Flat File click here.

What is Target Load Order:

We specify a target load order based on the Source Qualifier transformations in a mapping. If you have multiple Source Qualifier transformations connected to multiple targets, you can designate the order in which the Integration Service loads data into the targets.

If one Source Qualifier transformation provides data for multiple targets, you can enable constraint-based loading in a session to have the Integration Service load data based on target table primary and foreign key relationships.

Using Parameters and Variables in Source Qualifier:

You can use parameters and variables in the SQL query, user-defined join, source filter, and pre- and post-session SQL commands of a Source Qualifier transformation which you have pre-defined in the mapping and which is defined in the parameter file.

You can enter a parameter or variable within the SQL statement, or you can use a parameter or variable as the SQL query.

For example:

I want to pass “DeptNo” on the run time. So I have a mapping parameter/variable defined at mapping level.

From Menu -->Mapping --> Parameter and Variable.

Note: All the mapping variable/parameter will start prefix with “$$”, Name the parameter as “$$DeptNo”, Type “Parameter”, Datatype “integer” and IsExpr “FALSE”.

1. Sample SQL Override:

SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, EMP.HIREDATE, EMP.SAL, EMP.COMM

FROM

 EMP, DEPT

WHERE

 EMP.DEPTNO=DEPT.DEPTNO

AND DEPT.DEPTNO=$$DeptNo.

Another way to is to use session parameter which will start with prefix ‘$’ $ParamDeptNo and define it in the parameter file.

2. Sample SQL Override:

SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, EMP.HIREDATE, EMP.SAL, EMP.COMM

FROM

 EMP, DEPT

WHERE

 EMP.DEPTNO=DEPT.DEPTNO

AND DEPT.DEPTNO=$ParamMyQuery

The Integration Service first generates an SQL query and expands each parameter or variable. It replaces each mapping parameter, mapping variable, and workflow variable with its start value. Then it runs the query on the source database.

The parameters datatypes can be string, integer and datetime. It’s depending on your requirement.

TIPS:

1. To ensure the format of a datetime parameter or variable matches that used by the source, validate the SQL query.
2. When you override the default SQL query, you must enclose all database reserved words in quotes.
3. If the source table has more than 1,000 rows, you can increase performance by indexing the primary key-foreign keys. If the source table has fewer than 1,000 rows, you might decrease performance if you index the primary key-foreign keys.
4. You can resize the Expression Editor. Expand the dialog box by dragging from the borders. The Designer saves the new size for the dialog box as a client setting.
5. When you enter a source filter in the session properties, you override the customized SQL query in the Source Qualifier transformation.
6. You can also use the Sorter transformation to sort relational and flat file data before Aggregator and Joiner transformations.
7. Sybase supports a maximum of 16 columns in an ORDER BY clause. If the source is Sybase, do not sort more than 16 columns.
8. You can also enter pre- and post-session SQL commands on the Properties tab of the target instance in a mapping.

Warning:

If you configure a transformation as repeatable and deterministic, it is your responsibility to ensure that the data is repeatable and deterministic. If you try to recover a session with transformations that do not produce the same data between the session and the recovery, the recovery process can result in corrupted data.

Disadvantages:

1. You cannot directly connect sources to targets. Instead, you need to connect them through a Source Qualifier transformation for relational and flat file sources, or through a Normalizer transformation for COBOL sources. Because the Source Qualifier converts all database types into Informatica datatypes.
2. The Designer does not allow you to connect multiple Source Qualifier transformations to a single target. There are two workarounds:

·         Reuse targets. Since target definitions are reusable, you can add the same target to the mapping multiple times. Then connect each Source Qualifier transformation to each target.

·         Join the sources in a Source Qualifier transformation. Then remove the WHERE clause from the SQL query. 

Click here to know how to create a session for a mapping.