Source Qualifier and Expression Transformation

Source Qualifier Transformation

This is Active and Connected Transformation. The Source Qualifier transformation represents the rows that the Power Center Server reads when it runs a session. Source Qualifier Transformation is not reusable. This is 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 transformation.                                                            

Source Qualifier Properties Tab:
1) SOURCE FILTER:

We can use a source filter to reduce the number of rows the Power Center Server queries.

Note: When we use a source filter in the session properties, we override the customized SQL query in the Source Qualifier transformation.

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 Source If we change it to 2, then data will be sorted by top two columns.

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-SESSION and POST-SESSION 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.

6) SQL QUERY:

For RDBMS 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

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.

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

Expression Transformation

Expression Transformation is a connected passive transformation (i.e.) Number of input and output Rows is the same), you can modify individual ports of a single row, or add or suppress them. It helps implement the complicated data transforms, applies business logic and performs checks and validations. You can use various function according your business logic.

Built-in Functions:

Data Manipulation: concatenation (CONCAT or ||), Case change (UPPER, LOWER) truncation, InitCap (INITCAP)

Data type conversion: (TO_DECIMAL, TO_CHAR, TO_DATE, TO_FLOAT, and TO_NUMBER)                

Data cleansing: GREATEST, IN, INSTR, IS_DATE, IS_NUMBER, IS_SPACES, ISNULL, LEAST, LTRIM, RTRIM

Manipulate dates: GET_DATE_PART, IS_DATE, and DIFF_DATES

Scientific calculations and numerical operations: exponential, power, log, modulus (LOG, POWER, SQRT)

Informatica Function: IIF, DECODE

Properties of Expression Transformation:

Active /Passive: Expression Transformation is a Passive transformation as it only modifies the incoming port data, but it won’t affect the number of rows processed.

Connected/Unconnected Transformation: Expression Transformation is a connected   Transformation

Types of ports in Expression Transformation: Input, Output, Variable port.

Expression transformation in Informatica:

1. Steps to create Expression transformation:In the Mapping Designer, open a Mapping.
2. Click Transformation > Create. Select Expression transformation.
3. You can also select Transformation by clicking function button on Informatica Designer.
4. Enter a name and click done (ex: EXP_DIM_BUN_EMEA).
5. Select and drag the ports from the source qualifier or other transformations to add to the Expression transformation. You can also open the transformation and create ports manually.
6. Double-click on the title bar and click on Ports tab. You can create output and variable ports Within the transformation.
7. In the Expression section of an output or variable port, open the Expression Editor.
8. Enter an expression. Click Validate to verify the expression syntax.
9. Click OK.
10. Assign the port data type, precision, and scale to match the expression return value.
11. To make it reusable, check the reusable option in the edit properties.
12. Configure the tracing level on the Properties tab.
13. Click OK.
14. Connect the output ports to a downstream transformation or target.

Expression transformation Tabs:

• Transformation:  Enter the name and description of the transformation. The naming convention              for an Expression transformation is EXP_meaning_full_name. You can also make the transformation reusable.
• Ports: Create port and configure them.
• Properties: Configure the tracing level to determine the amount of transaction detail reported in the session log file.
• Metadata Extensions:  Specify the extension name, data type, precision, and value. You can also create reusable metadata extensions.
• Configuring Ports: You can configure the following components on the ports tab
• Port name:  Add the name of the port.
• Data type, precision, and scale:  Configure the data type and set the precision and scale for each port.
• Port type: A port can be input, output, input/output, or variable. The input ports receive data and output ports pass data. The input/output ports pass data unchanged. Variable ports   store data temporarily and can store values across the rows.
• Expression:  Use the Expression Editor to enter expressions. Expressions use the transformation language, which includes SQL-like functions, to perform calculations.
• Default values and description:  Set default value for ports and add description.

Purpose of Expression Transformation: The Expression transformation is use to perform non-aggregate calculations for each data. Data can be modified using logical and numeric operators or built-in functions.

Performance Tuning on Expression Transformation:

• Try to use numeric operation instead of string operations.
• Use of operators are faster than functions (i.e. || vs. CONCAT).
• Use transformation variables to break down complex transformation logic into smaller parts.        You can define variable port by clicking on V check box while defining the port. In the expression editor of it, it can all input port and other variable port for calculation.
• It is highly recommended to define a naming convention for the input and output ports for expressions. For example, all input ports have an in_ prefix for Input port, out_ for output   ports and var_ for variables port.
• Ports are evaluated in the following order: input ports first, then variable ports and then output ports.