PowerCenter - Transformations

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

3 - 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.
dit:powercenter: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.
dit:powercenter: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.
dit:powercenter:mq_series_source_qualifier Represents the rows that the Integration Service reads from an MQSeries message queue source when it runs a workflow.
dit:powercenter:normalizer Source qualifier for COBOL sources. Can also use in the pipeline to normalize data from relational or flat file sources.
dit:powercenter: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
dit:powercenter: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.

4 - Manage Transformation

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

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

4.3 - Expression Editor

4.4 - Reusable

Check the check box :-)

5 - Documentation

  • “Working with Transformations” in the Transformation Guide.

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap