TRANSFORMATON 3

1. Expression Transformation
   

 

We can use Expression transformation to calculate values in a single row.

 

1. Filter Transformation
   

 

A filter transformation test data for one condition and drops the rows that do not meet the condition.

 

Filter condition:

 

Filter condition is an expression that returns TRUE or FALSE. Any expression that returns a single value can be used as a filter.

 

If the filter condition evaluates to NULL, the row is treated as FALSE.

 

The filter condition is case-sensitive.

 

à To maximize the session performance, keep the Filter transformation as close as possible to the sources in the mapping.

 

à The SQ transformation provides an alternative way to filter rows. Rather than filtering rows within a mapping, the SQ filter rows when it reads from source.

 

SQ transformation only lets us filter rows from relational sources, while the filter transformation filter rows from any type of source.

 

Filter condition in the SQ transformation only uses standard SQL. The filter transformation can define a condition using any statement or transformation function that returns either TREU or FALSE.

 

 

 

 

1. Source Qualifier
   

 

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

 

Tasks:

 

à 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 Integration Service reads source data

 

à Specify an outer join rather than the default inner join.

 

à Specify sorted ports.

 

à Select only distinct values from the source

 

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

 

 

Properties

 

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.

 

User-Defined Join -- Specifies the condition used to join data from multiple sources represented in the same Source Qualifier transformation.

 

Source Filter -- Specifies the filter condition the Integration Service applies when querying rows.

 

Number of Sorted Ports --Indicates the number of columns used when sorting rows queried from relational sources.

 

Select Distinct --Specifies if you want to select only unique rows.

 

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

 

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

 

 

 

Default Join

 

 

When you join related tables in one Source Qualifier transformation, the Integration Service joins the tables based on the related keys in each table.

This default join is an inner equijoin, using the following syntax in the WHERE clause:

Source1.column_name = Source2.column_name

 

The columns in the default join must have:

 

--> A primary key-foreign key relationship

--> Matching data types

 

You might need to override the default join under the following circumstances:

 

--> Columns do not have a primary key-foreign key relationship.

--> The data types of columns used for the join do not match.

--> You want to specify a different type of join, such as an outer join.

 

 

Creating Key Relationships

 

 

You can join tables in the Source Qualifier transformation if the tables have primary key-foreign key relationships. However, you can create primary key-foreign key relationships in the Source Analyzer by linking matching columns in different tables. These columns do not have to be keys, but they should be included in the index for each table.

 

 

For example, the corporate office for a retail chain wants to extract payments received based on orders. The ORDERS and PAYMENTS tables do not share primary and foreign keys. Both tables, however, include a DATE_SHIPPED column. You can create a primary key-foreign key relationship in the metadata in the Source Analyzer.

 

The primary key-foreign key relationships exist in the metadata only. You do not need to generate SQL or alter the source tables.

 

 

Outer Join

 

Use an outer join when you want to join two tables and return all rows from one of the tables.

 

 

 

The Integration Service supports two kinds of outer joins:

 

--> Left. Integration Service returns all rows for the table to the left of the join syntax and the rows from both tables that meet the join condition.

 

--> Right. Integration Service returns all rows for the table to the right of the join syntax and the rows from both tables that meet the join condition.

 

Normal Join Syntax

 

To create a normal join, use the following syntax:

{ source1 INNER JOIN source2 on join_condition }

 

Left Outer Join Syntax

 

To create a left outer join, use the following syntax:

{ source1 LEFT OUTER JOIN source2 on join_condition }

 

 

Right Outer Join Syntax

 

To create a right outer join, use the following syntax:

{ source1 RIGHT OUTER JOIN source2 on join_condition }

 

 

 

Important Points

 

 

--> When you use a datetime value or a datetime parameter or variable in the SQL query, change the date format to the format used in the source. The Integration Service passes datetime values to source systems as strings in the SQL query. The Integration Service converts a datetime value to a string, based on the source database.

 

For example, to convert the $$$SessStartTime value for an Oracle source, use the following Oracle function in the SQL override:

to_date ('$$$SessStartTime', 'mm/dd/yyyy hh24:mi:ss')

 

 

--> 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, you can use a session parameter, $ParamMyQuery, as the SQL query, and set $ParamMyQuery to the SQL statement in a parameter file.

 

-->When you use a string mapping parameter or variable in the Source Qualifier transformation, use a string identifier appropriate to the source system. Most databases use a single quotation mark as a string identifier.

 

--> When output is deterministic and output is repeatable, the Integration Service does not stage source data for recovery.

 

--> You must connect the columns in the Source Qualifier transformation to another transformation or target before you can generate the default query.

 

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

 

 

--> When you override the default SQL query for a session configured for pushdown optimization, the Integration Service creates a view to represent the SQL override. It then runs an SQL query against this view to push the transformation logic to the database.