PowerCenter Transformation

Transformation
 A transformation is a repository object that generates, modifies or passes
data.
 Transformations in a mapping represent the operations the Integration service
performs on the data. Data passes through transformation ports that you link
in a mapping or mapplet.
 Transformations can be active or passive.
 An active transformation can change the number of rows that pass through
it, such as Filter transformation that removes rows that do not meet the
filter condition.
 A passive transformation does not change the number of rows that pass
through it, such as Expression transformation that performs a calculation
on data and passes all rows through the transformation.
 Transformations can be connected or unconnected to the data flow.
 An unconnected transformation is not connected to other transformations in the
mapping and is called within another transformation. And returns a value to that
transformation.
Active Transformation
 Aggregator – Performs aggregate calculations
 Filter – Filters data
 Joiner – Joins data from different databases or flat-file systems
 Normalizer – Source Qualifier for COBOL sources. Can also use in the
pipeline to normalize data from relational or flat-file
sources.
 Rank – Limits records to a top or bottom range.
 Router – Routes data into multiple transformations based on group conditions
 Sorter – Sorts data based on sort key.
 Source Qualifier – Represents the rows that the Integration service reads
from a relational or flat-file source when it runs a session.
 SQL – Executes SQL queries against a database.
 Union – Merges data from different databases or flat-file systems.
 Update Strategy – Determines whether to insert, delete, update or reject
rows.
Active Transformation
 Application Source Qualifier – Represents the rows that the IS reads from an
application such as an ERP source, when it runs a session.
 Custom – Calls a procedure in a shared library or DLL
 Java – Executes user logic coded in Java. The byte code for the user logic is
stored in the repository.
 XML Generator – Reads data from one or more input ports and outputs XML
through a single output port.
 XML Parser – Reads XML from one input port and outputs data to one or
more output ports.
 XML Source Qualifier – Represents the rows that the Integration Service reads
from an XML source when it runs a session.
Passive Transformation
 Expression – Calculates a value.
 External procedure– Calls a procedure in a shared library or in the COM
layer of windows
 HTTP – Connects to an HTTP server to read or update data.
 Input – Defines mapplet input rows. Available in the mapplet designer.
 Input – Defines mapplet output rows. Available in the mapplet designer.
 Java – Executes user logic coded in Java. The byte code for the user logic is
stored in the repository.
 Sequence Generator – Generates primary keys.
 SQL – Executes SQL queries against a database.
 Stored Procedure – Calls a stored procedure.
 Unstructured Data – Transforms data in unstructured and semi-structured formats.
Creating a Transformation
You can create transformations using the following designer tools.
 Mapping Designer: Create transformations that connect sources to
targets. Transformations in a mapping cannot be used in other mappings
unless you configure them to be reusable.
 Transformation Developer: Create individual transformations, called
reusable transformations, that use in multiple mappings.
 Mapplet Designer: Create and configure a set of transformations, called
mapplets, that you use in multiple mappings.
Creating a Transformation
To create a transformation:
Use the same procedure to create a transformation in the Mapping
Designer, Transformation Developer and Mapplet Designer.

1. Open the appropriate designer tool.

2. In the Mapping Designer, open or create a Mapping. In the mapplet
designer, open or create a mapplet.
3. On the transformations toolbar, click the button corresponding to the
transformation you want to create (or) Click Transformation > create
and select type of transformation you want to create.
4. Drag across the portion of the mapping where you want to place the
transformation.

The new transformation appears in the workspace. Next you need to

configure the transformation by adding any new ports to it and setting other
properties.
Configuring Transformations
After you create a transformation, you can configure it. Every transformation
contains the following common tabs:
 Transformation: Name of the transformation or add a description.
 Port: Add and configure ports.
 Properties: Configure properties that are unique to the transformation.
 Metadata Extensions: Extend the metadata in the repository by associating
Information which individual objects in the repository.
Some transformation might includes other tabs, such as the condition tab,
where you enter conditions in a Joiner or Normalizer transformations.
Working with ports
After you create a transformation, configure ports on the ports tab.
Ports: It defines the columns of data that move into and out of the transformation.
Creating Ports: Create a port in the following ways
 Drag a port from another transformation. When you drag a port from another
transformation the Designer creates a port with the same properties, and it links
the two ports.
 Click the Add button on the Ports tab. The Designer creates an empty port you can
configure
Configuring Ports
Configure the following the properties on the Ports tab:
 Port Name: Name of the Port.
 Datatype, precision and scale: If you plan to enter an expression or
condition, make sure the datatype matches the return value of the
expression.
 Port Type: Transformations may contain a combination of input (I), output
(O),input/output and variable port types.
 Default value: The Designer assigns default values to handle null values and
output transformation errors. You can override the default value in some
ports.
 Description: A description of the port.
Linking Ports
After you add and configure a transformation in a mapping, you link it to
targets and other transformations. You link mapping objects through the
ports. Data passes into and out of a mapping through the following ports:

 Input Port: Receive data

 Output Port: Pass data
 Input/output ports: Receive data and pass it unchanged.

To link ports, drag between ports in different mapping objects. The Designer
validates the link and creates the link only when the link meets validation
requirements.
Source Qualifier Transformation
SQ Transformation
Transformation Type: (Active and Connected)
 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.
USAGE
Use the Source Qualifier transformation to complete 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.
SQ Transformation
 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.
 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.
 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.

Transformation Data Types:

 The Source Qualifier transformation displays the transformation datatypes.
 The transformation datatypes determine how the source database binds data when
the Integration Service reads it. Do not alter the datatypes in the Source Qualifier
transformation.
 If the datatypes in the source definition and Source Qualifier transformation do not
match, the Designer marks the mapping invalid when you save it.
SQ Transformation
Target Load Order: (or) Target Load Plan
 You 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.
SQ Transformation
Datetime Values:
 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.
The following table describes the datetime formats for each database type:

 Some databases require you to identify datetime values with additional punctuation, such as
single quotation marks or database specific functions.
 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’)
For Informix DATETIME ($$$SessStartTime) YEAR TO SECOND
SQ Transformation
Parameter and Variables
 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.
 Use any parameter or variable type that you can define 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, you can use a session
parameter, $ParamMyQuery, as the SQL query, and set $ParamMyQuery to the SQL
statement in a parameter file.
 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.
 When you use a datetime mapping parameter or variable, or when you use the
built-in variable $$$SessStartTime, 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.
 To ensure the format of a datetime parameter or variable matches that used by the
source, validate the SQL query.
SQ Transformation Properties
SQ Transformation Properties
SQ Transformation Properties
Default Query:
 For relational sources, the Integration Service 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 Integration Service reads only the columns that are connected to another
transformation.

 Although there are many columns in the source definition, only three columns are connected
to another transformation. In this case, the Integration Service generates a default query that
selects only those three columns:
SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.COMPANY, CUSTOMERS.FIRST_NAME
FROM CUSTOMERS
SQ Transformation Properties
Viewing the Default Query
SQ Transformation Properties

Overriding the Default Query:

 You can alter or override the default query in the Source Qualifier transformation by changing
the default settings of the transformation properties.

 Do not change the list of selected ports or the order in which they appear in the query. This
list must match the connected transformation output ports.

 When you edit transformation properties, the Source Qualifier transformation includes these
settings in the default query.

 However, if you enter an SQL query, the Integration Service uses only the defined SQL
statement. The SQL Query overrides the User-Defined Join, Source Filter, Number of Sorted
Ports, and Select Distinct settings in the Source Qualifier transformation.
SQ Transformation Properties
Joining Source Data:
 Use one Source Qualifier transformation to join data from multiple relational tables. These
tables must be accessible from the same instance or database server.
 When a mapping uses related relational sources, you can join both sources in one Source
Qualifier transformation. During the session, the source database performs the join before
passing data to the Integration Service. This can increase performance when source tables are
indexed.
 Use the Joiner transformation for heterogeneous sources and to join flat files.
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 datatypes
SQ Transformation Properties
Custom Join:
 If you need to override the default join, you can enter contents of the WHERE
clause that specifies the join in the custom query. If the query performs an outer
join, the Integration Service may insert the join syntax in the WHERE clause or the
FROM clause, depending on the database syntax.
 You might need to override the default join under the following circumstances:
 Columns do not have a primary key-foreign key relationship
 The datatypes of columns used for the join do not match.
 You want to specify a different type of join, such as an outer join.
Heterogeneous Join:
 To perform a heterogeneous join, use the Joiner transformation.
 Use the Joiner transformation when you need to join the following types of
sources:
 Join data from different source databases.
 Join data from different flat file systems
 Join relational sources and flat files
SQ Transformation Properties
Adding a SQL Query:
Points to remember:
 The Source Qualifier transformation provides the SQL Query option to override the default
query. You can enter an SQL statement supported by the source database. Before entering the
query, connect all the input and output ports you want to use in the mapping.
 When you edit the SQL Query, you can generate and edit the default query. When the
Designer generates the default query, it incorporates all other configured options, such as a
filter or number of sorted ports. The resulting query overrides all other options you might
subsequently configure in the transformation.
 You can use a parameter or variable as the SQL query or include parameters and variables
within the query. When including a string mapping parameter or variable, use a string
identifier appropriate to the source system. For most databases, you need to enclose the
name of a string parameter or variable in single quotes.
 When you include a datetime value or a datetime mapping parameter or variable in the SQL
query, change the date format to match the format used by the source. The Integration
Service converts a datetime value to a string based on the source system.
 When creating a custom SQL query, the SELECT statement must list the port names in the
order in which they appear in the transformation.
SQ Transformation Properties
Adding a SQL Query:
Steps to override Default Query:
 Open the Source Qualifier transformation, and click the Properties tab.
 Click the Open button in the SQL Query field. The SQL Editor dialog box appears.
 Click Generate SQL - The Designer displays the default query it generates when querying rows
from all sources included in the Source Qualifier transformation.
 Enter a query in the space where the default query appears.
 Every column name must be qualified by the name of the table, view, or synonym in which it appears.
For example, if you want to include the ORDER_ID column from the ORDERS table, enter
ORDERS.ORDER_ID. You can double-click column names appearing in the Ports window to avoid typing
the name of every column.
 You can use a parameter or variable as the query, or you can include parameters and variables in the
query.
 Enclose string mapping parameters and variables in string identifiers. Alter the date format for datetime
mapping parameters and variables when necessary.
 Select the ODBC data source containing the sources included in the query.
 Enter the user name and password to connect to this database and click Validate. The Designer
runs the query and reports whether its syntax was correct.
 Click OK to return to the Edit Transformations dialog box. Click OK again to return to the
Designer.
SQ Transformation Properties
Entering a User-defined Join:
Points to remember:
 Entering a user-defined join is similar to entering a custom SQL query. However, you only
enter the contents of the WHERE clause, not the entire query.
 When you perform an outer join, the Integration Service may insert the join syntax in the
WHERE clause or the FROM clause of the query, depending on the database syntax.
 When you add a user-defined join, the Source Qualifier transformation includes the setting in
the default SQL query. However, if you modify the default query after adding a user-defined
join, the Integration Service uses only the query defined in the SQL Query property of the
Source Qualifier transformation.
 You can use a parameter or variable as the user-defined join or include parameters and
variables within the join. When including a string mapping parameter or variable, use a string
identifier appropriate to the source system. For most databases, you need to enclose the
name of a string parameter or variable in single quotes.
 When you include a datetime parameter or variable, you might need to change the date
format to match the format used by the source. The Integration Service converts a datetime
parameter and variable to a string based on the source system.
SQ Transformation Properties
Entering a User-defined Join:
Steps To create a user-defined join::
 Create a Source Qualifier transformation containing data from multiple sources or associated
sources.
 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.
 Do not enter the keyword WHERE at the beginning of the join. The Integration Service adds
this keyword when it queries rows.
 Enclose string mapping parameters and variables in string identifiers. Alter the date format
for datetime mapping parameters and variables when necessary.
 Click OK to return to the Edit Transformations dialog box, and then click OK to return to the
Designer.
SQ Transformation Properties
Entering a Source Filter:
Points to remember:
 You can enter a source filter to reduce the number of rows the Integration Service queries. If
you include the string ‘WHERE’ or large objects in the source filter, the Integration Service fails
the session.
 The Source Qualifier transformation includes source filters in the default SQL query. If,
however, you modify the default query after adding a source filter, the Integration Service
uses only the query defined in the SQL query portion of the Source Qualifier transformation.
 You can use a parameter or variable as the user-defined join or include parameters and
variables within the join. When including a string mapping parameter or variable, use a string
identifier appropriate to the source system. For most databases, you need to enclose the
name of a string parameter or variable in single quotes.
 When you include a datetime parameter or variable, you might need to change the date
format to match the format used by the source. The Integration Service converts a datetime
parameter and variable to a string based on the source system.

Note: When you enter a source filter in the session properties, you override the customized SQL
query in the Source Qualifier transformation.
SQ Transformation Properties
Entering a Source Filter:
Steps To enter a Source Filter:
 Open the Source Qualifier transformation, and click the Properties tab.
 Click the Open button in the Source Filter field. The SQL Editor dialog box appears..
 Enter the syntax for the source filter.
 Include the table name and port name. Do not include the keyword WHERE in the filter.
 Enclose string mapping parameters and variables in string identifiers. Alter the date format
for datetime mapping parameters and variables when necessary.
 Click OK to return to the Edit Transformations dialog box, and then click OK to return to the
Designer.
SQ Transformation Properties
Using Sorted Ports:
Points to remember:
 When you use sorted ports, the Integration Service adds the ports to the ORDER BY clause in
the default query.
 The Integration Service adds the configured number of ports, starting at the top of the Source
Qualifier.
 You might use sorted ports to improve performance when you include any of the following
transformations in a mapping:
 Aggregator. When you configure an Aggregator transformation for sorted input, you can
send sorted data by using sorted ports. The group by ports in the Aggregator
transformation must match the order of the sorted ports in the Source Qualifier
transformation.
 Joiner. When you configure a Joiner transformation for sorted input, you can send sorted
data by using sorted ports. Configure the order of the sorted ports the same in each
Source Qualifier transformation.
 You can also use the Sorter transformation to sort relational and flat file data before
Aggregator and Joiner transformations.
SQ Transformation Properties
Using Sorted Ports:
Steps To use sorted ports:
 Open the Source Qualifier transformation, and click the Properties tab.
 Click in Number of Sorted Ports and enter the number of ports you want to sort.
 The Integration Service adds the configured number of columns to an ORDER BY clause,
starting from the top of the Source Qualifier transformation. The source database sort order
must correspond to the session sort order.
 Click OK to return to the Edit Transformations dialog box, and then click OK to return to the
Designer.

Tip: Sybase supports a maximum of 16 columns in an ORDER BY clause. If the source is Sybase, do
not sort more than 16 columns.
SQ Transformation Properties
Select Distict:
Points to remember:
 If you want the Integration Service to select unique values from a source, use the Select Distinct
option.
 You might use this feature to extract unique customer IDs from a table listing total sales. Using Select
Distinct filters out unnecessary data earlier in the data flow, which might improve performance.
 By default, the Designer generates a SELECT statement. If you choose Select Distinct, the Source
Qualifier transformation includes the setting in the default SQL query.
To use Select Distinct:
 Open the Source Qualifier transformation in the mapping, and click on the Properties tab.
 Check Select Distinct, and Click OK.

Overriding Select Distinct in the Session

 You can override the transformation level option to Select Distinct when you configure the session in
the Workflow Manager.
To override the Select Distinct option:
 In the Workflow Manager, open the Session task, and click the Mapping tab.
 Click the Transformations view, and click the Source Qualifier transformation under the Sources
node.
 3.In the Properties settings, enable Select Distinct, and click OK.