Source Qualifier Transformation

Source Qualifier Transformation

Source Qualifier is an :

(a) Active Transformation 

(b) Connected Transformation

(c) The Source Qualifier transformation represents the rows that the Integration Service reads when it runs a session. 

(d) It is only transformation that is not reusable. 

(e) Default transformation except in case of XML or COBOL files

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.

Perform the following tasks with a Source Qualifier transformation :-

1. 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.

2. 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.

3. 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.

4. Specify sorted ports. If you specify a number for sorted ports, the Integration Service adds an ORDER BY clause to the default SQL query.

5. 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.

6. 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.

Source Qualifier Transformation Sample :-

Source Qualifier Joining data originating from the same source database :-

Source Qualifier Properties :-

1) SOURCE FILTER: 

Specifies the filter condition the Integration Service applies when querying rows. 

2) NUMBER OF SORTED PORTS: 

Indicates the number of columns used when sorting rows queried from relational sources. If you select this option, the Integration Service adds an ORDER BY to the default query when it reads source rows.The ORDER BY includes the number of ports specified, starting from the top of the transformation.

When selected, the database sort order must match the session sort order. 

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: 

SELECT 

EMPNO,

EMP_NAME,

DEPTNO,

DEPT_NAME 

FROM EMPLOYEE

in above SQL, present in default query.

- If we want to sort as per EMP_NAME, then we need to change it to 2. 

- If we change it to 2, then data will be sorted by top two columns. Fisrt it will be sorted by EMP_NAME then if a conflict occurs it will be sorted by EMPNO.

3) SELECT DISTINCT: 

Specifies if you want to select only unique rows. The Integration Service includes a SELECT DISTINCT statement in default query if you choose this option. 

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

4) PRE and POST SQL Commands :

- Pre-session SQL commands - are the commands that run against the source database before the Integration Service reads the source. 

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

- Use a semi-colon (;) to separate multiple statements. 

5) USER DEFINED JOINS :

Specifies the condition used to join data from multiple sources represented in the same Source Qualifier transformation 

- 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. 

SQL Join syntax you can use 

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

Informatica Join Synatx you can use

Equi Join   --> { source1 INNER JOIN source2 on join_condition } e.g. { CUSTOMER INNER JOIN PURCHASES on CUSTOMER.CUST_ID = PURCHASES.CUST_ID }

Left Outer Join   --> { source1 LEFT OUTER JOIN source2 on join_condition } e.g. { CUSTOMER  LEFT OUTER JOIN PURCHASES on CUSTOMER.CUST_ID = PURCHASES.CUST_ID }

Right Outer Join --> { source1 RIGHT OUTER JOIN source2 on join_condition } e.g. { CUSTOMER  RIGHT OUTER JOIN PURCHASES on CUSTOMER.CUST_ID = PURCHASES.CUST_ID }

Note: Brackets {} must be included while using infa syntax .

Both Synatx are valid and works in query overvride in Informatica.

6) SQL QUERY :

Defines 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.

SAMPLE MAPPING USING SOURCE QUALIFIER

Mapping Creation

1. Open folder where we want to create the mapping by double clicking it or by right click and select open option. 

2. Click Tools -> Mapping Designer. 

3. Click Mapping-> Create-> Give mapping name. Ex: m_source_qualifier_demo 

4. Drag EMPLOYEE soucre and SALES Target. 

5. Drag all the port from source qualifier to Expression tranformation. 

6. Create an output port OUT_SALES and give the expression IIF(TOTAL_SALES>10,TOTAL_SALES+0.5,TOTAL_SALES). 

7. Drag all port from expression except total sales and put it in target SALES 

8. Link all as shown in above picture.

9. Mapping -> Save. 

10. Mapping -> Validate 

11. Repository -> Save 

12. Create Session and Workflow

13. Run session and see execution in Monitor.

Troubleshooting Source Qualifier Transformations:-

Problem 1. I cannot perform a drag and drop operation, such as connecting ports.
Solution Review the error message on the status bar for details.

Problem 2. I cannot connect a source definition to a target definition.
Solution 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.

Problem 3. I cannot connect multiple sources to one target.
Solution The Designer does not allow you to connect multiple Source Qualifier transformations to a single target. There are two workarounds:
(a) 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.
(b) Join the sources in a Source Qualifier transformation. Then remove the WHERE clause from the SQL query.