A transformation is a part of a mapping that generates or modifies data. Every mapping includes a Source Qualifier transformation, representing all data read from a source and temporarily stored by the integration service.
In addition, you can add transformations that calculate a sum, look up a value, or generate a unique ID before the source data reaches the target.
The table below lists the transformations displayed in the Transformation toolbar in the PowerCenter - Designer:
Transformation | Description | Naming Convention | Remarks |
---|---|---|---|
PowerCenter - Aggregator Transformation | Performs aggregate calculations | AGG_TransformationName | |
PowerCenter - Application Source Qualifier Transformation | Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a workflow. | ||
Application Multi Group Source Qualifier | Represents the rows that the Integration Service reads from an application, such as a TIBCO source, when it runs a workflow. Sources that require an Application Multi-Group Source Qualifier contain multiple groups. | ||
PowerCenter - Custom Transformation | Calls a procedure in a shared library or DLL. | ||
PowerCenter - Expressions | Calculates a value. | EXP_TransformationName | You cannot enter expressions in input/output ports. |
External Procedure | Calls a procedure in a shared library or in the COM layer of Windows. | ||
PowerCenter - Filter Transformation | Filters data. | FIL_TransformationName | |
PowerCenter - Input Transformation (for Mapplet) | Defines mapplet input rows. Available in the Mapplet Designer. | ||
PowerCenter - Joiner Transformation | Joins data from different databases or flat file systems. | ||
Powercenter - Lookup | Looks up values. | LKP_TransformationName | By default, the Lookup transformation queries and stores the contents of the lookup table before the rest of the transformation runs, so it performs the join through a local copy of the table that it has cached. |
Mq Series Source Qualifier | Represents the rows that the Integration Service reads from an MQSeries message queue source when it runs a workflow. | ||
Normalizer | Source qualifier for COBOL sources. Can also use in the pipeline to normalize data from relational or flat file sources. | ||
Output | Defines mapplet output rows. Available in the Mapplet Designer. | ||
PowerCenter - Rank Transformation | Limits records to a top or bottom range. | ||
PowerCenter - Router Transformation | Routes data into multiple transformations based on group conditions. | ||
PowerCenter - Sequence Generator Transformation | Generates primary keys. | SEQ_TransformationName | |
Powercenter - Sorter | Sorts data based on a sort key. | ||
source qualifier | Represents the rows that the integration service reads from a relational or flat file source when it runs a workflow. | ||
stored procedure | Calls a stored procedure. | SP_ProcedureName | |
Transaction Control | Defines commit and rollback transactions. | ||
PowerCenter - Union Transformation | Merges data from multiple databases or flat file systems. | ||
PowerCenter - Update Strategy (Insert, Update, Delete) | Determines whether to insert, delete, update, or reject records. | ||
PowerCenter - Xml Source Qualifier Transformation | Represents the rows that the Integration Service reads from an XML source when it runs a workflow. |
The Advanced Transformation toolbar contains transformations such as Java, SQL, and XML Parser transformations.
You can create transformations using the following Designer tools:
Open the appropriate designer tool and :
Every transformation contains the following common tabs:
Some transformations might include other tabs, such as the Condition tab, where you enter conditions in a Joiner or Normalizer transformation.
When you configure transformations, you might complete the following tasks:
Check the check box