About
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.
Articles Related
Transformation Toolbar
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.
Manage Transformation
Create transformation
You can create transformations using the following Designer tools:
- PowerCenter - 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.
- PowerCenter - Transformation Developer. Create individual transformations, called reusable transformations, that use in multiple mappings.
- PowerCenter - Mapplet Designer. Create and configure a set of transformations, called mapplets, that you use in multiple mappings.
Open the appropriate designer tool and :
- On the Transformations toolbar, click the button corresponding to the transformation you want to create.
- Click Transformation > Create and select the type of transformation you want to create.
Edit/Configuration transformation
Every transformation contains the following common tabs:
- Transformation. Name 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 with individual objects in the repository.
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:
- Add ports. Define the columns of data that move into and out of the transformation.
- Add groups. In some transformations, define input or output groups that define a row of data entering or leaving the transformation.
- Enter expressions. Enter SQL-like expressions in some transformations that transform the data.
- Define local variables. Define local variables in some transformations that temporarily store data.
- Override default values. Configure default values for ports to handle input nulls and output transformation errors.
- Enter tracing levels. Choose the amount of detail the Integration Service writes in the session log about a transformation.
Expression Editor
Reusable
Check the check box 
Documentation
- “Working with Transformations” in the Transformation Guide.