- Active and Connected Transformation.
- The Source Qualifier transformation represents the rows that the Power Center Server reads when it runs a session.
- It is only transformation that is not reusable.
- Default transformation except in case of XML or COBOL files.
Tasks performed by Source Qualifier:
- Join data originating from the same source database: We 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 Power Center Server reads source data: If we Include a filter condition, the Power Center Server adds a WHERE clause to the Default query.
- Specify an outer join rather than the default inner join: If we include a User-defined join, the Power Center Server replaces the join information Specified by the metadata in the SQL query.
- Specify sorted ports: If we specify a number for sorted ports, the
- Power Center Server adds an ORDER BY clause to the default SQL query.
- Select only distinct values from the source: If we choose Select Distinct,the Power Center Server adds a SELECT DISTINCT statement to the default SQL query.
- Create a custom query to issue a special SELECT statement for the Power Center Server to read source data: For example, you might use a Custom query to perform aggregate calculations. The entire above are possible in Properties Tab of Source Qualifier t/f.
SAMPLE MAPPING TO BE MADE:
- Source will be EMP and DEPT tables.
- Create target table as showed in Picture above.
- Create shortcuts in your folder as needed.
Creating Mapping:
- Open folder where we want to create the mapping.
- Click Tools -> Mapping Designer.
- Click Mapping-> Create-> Give mapping name. Ex: m_SQ_example
- Drag EMP, DEPT, Target.
- Right Click SQ_EMP and Select Delete from the mapping.
- Right Click SQ_DEPT and Select Delete from the mapping.
- Click Transformation -> Create -> Select Source Qualifier from List -> Give Name -> Click Create
- Select EMP and DEPT both. Click OK.
- Link all as shown in above picture.
- Edit SQ -> Properties Tab -> Open User defined Join -> Give Join condition EMP.DEPTNO=DEPT.DEPTNO. Click Apply -> OK
- Mapping -> Validate
- Repository -> Save
- Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
- Make sure to give connection information for all tables.
SQ PROPERTIES TAB
1) SOURCE FILTER:
We can enter a source filter to reduce the number of rows the Power Center Server queries.
Note: When we enter a source filter in the session properties, we override the customized SQL query in the Source Qualifier transformation.
Steps:
- In the Mapping Designer, open a Source Qualifier transformation.
- Select the Properties tab.
- Click the Open button in the Source Filter field.
- In the SQL Editor Dialog box, enter the filter. Example: EMP.SAL)2000
- Click OK.
Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.
2) NUMBER OF SORTED PORTS:
When we use sorted ports, the Power Center Server adds the ports to the ORDER BY clause in the default query.
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: DEPTNO in above figure.
- If we want to sort as per ENAME, move ENAME to top.
- If we change it to 2, then data will be sorted by top two columns.
Steps:
- In the Mapping Designer, open a Source Qualifier transformation.
- Select the Properties tab.
- Enter any number instead of zero for Number of Sorted ports.
- Click Apply -> Click OK.
Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.
3) SELECT DISTINCT:
If we want the Power Center Server to select unique values from a source, we can use the Select Distinct option.
- Just check the option in Properties tab to enable it.
4) PRE and POST SQL Commands
- The Power Center Server 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.
- Use a semi-colon (;) to separate multiple statements.
5) USER DEFINED JOINS
Entering a user-defined join is similar to entering a custom SQL query. However, we only enter the contents of the WHERE clause, not the entire query.
- 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.
Steps:
- Open the Source Qualifier transformation, and click the Properties tab.
- Click the Open button in the User Defined Join field. The SQL Editor Dialog Box appears.
- Enter the syntax for the join.
- Click OK -> Again Ok.
Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.
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}
|
6) SQL QUERY
For relational sources, the Power Center Server generates a query for each Source Qualifier transformation when it runs a session. The default query is a SELECT statement for each source column used in the mapping. In other words, the Power Center Server reads only the columns that are connected to another Transformation.
In mapping above, we are passing only SAL and DEPTNO from SQ_EMP to Aggregator transformation. Default query generated will be:
- SELECT EMP.SAL, EMP.DEPTNO FROM EMP
Viewing the Default Query
- Open the Source Qualifier transformation, and click the Properties tab.
- Open SQL Query. The SQL Editor displays.
- Click Generate SQL.
- The SQL Editor displays the default query the Power Center Server uses to Select source data.
- Click Cancel to exit.
Note: If we do not cancel the SQL query, the Power Center Server overrides the default query with the custom SQL query.
We can enter an SQL statement supported by our source database. Before entering the query, connect all the input and output ports we want to use in the mapping.
Example: As in our case, we can’t use full outer join in user defined join, we can write SQL query for FULL OUTER JOIN:
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO WHERE SAL>2000
- We also added WHERE clause. We can enter more conditions and write More complex SQL.
Important Points:
- When creating a custom SQL query, the SELECT statement must list the port names in the order in which they appear in the transformation.
Example: DEPTNO is top column; DNAME is second in our SQ mapping.
So when we write SQL Query, SELECT statement have name DNAME first, DNAME second and so on. SELECT DEPT.DEPTNO, DEPT.DNAME
- Once we have written a custom query like above, then this query will Always be used to fetch data from database. In our example, we used WHERE SAL>2000. Now if we use Source Filter and give condition SAL) 1000 or any other, then it will not work. Informatica will always use the custom query only.
- Make sure to test the query in database first before using it in SQL Query. If query is not running in database, then it won’t work in Informatica too.
- Also always connect to the database and validate the SQL in SQL query editor
