Data Warehousing

Transformations in Informatica 9

What is a Transformation?

A transformation is a repository object which reads the data, modifies the data and passes the data. Transformations in a mapping represent the operations that the integration service performs on the data.

Transformations can be classified as active or passive, connected or unconnected.

Active Transformations:

A transformation can be called as an active transformation if it performs any of the following actions.

• Change the number of rows: For example, the filter transformation is active because it removes the rows that do not meet the filter condition. All multi-group transformations are active because they might change the number of rows that pass through the transformation.
• Change the transaction boundary: The transaction control transformation is active because it defines a commit or roll back transaction.
• Change the row type: Update strategy is active because it flags the rows for insert, delete, update or reject.

Note: You cannot connect multiple active transformations or an active and passive transformation to the downstream transformation or transformation same input group. This is because the integration service may not be able to concatenate the rows generated by active transformations. This rule is not applicable for sequence generator transformation.

Passive Transformations:

Transformations which does not change the number of rows passed through them, maintains the transaction boundary and row type are called passive transformation.

Connected Transformations:

Transformations which are connected to the other transformations in the mapping are called connected transformations.

Unconnected Transformations:

An unconnected transformation is not connected to other transformations in the mapping and is called within another transformation, and returns a value to that.

The below table lists the transformations available in Informatica version 9:

Transformation	Type	Description
Aggregator	Active/Connected	Performs aggregate calculations.
ApplicationSourceQualifier	Active/Connected	Represents therows that the Integration Service reads from an application, such as an ERP source, when it runs a session.
Custom	ActiveorPassive/Connected	Calls a procedure in a shared library or DLL.
DataMasking	Passive/Connected	Replaces sensitive production data with realistic test data for non-production environments.
Expression	Passive/Connected	Calculates a value.
ExternalProcedure	Passive/ConnectedorUnconnected	Calls a procedure in a shared library or in the COM layer of Windows.
Filter	Active/Connected	Filters data.
HTTP	Passive/Connected	Connects to an HTTP server to read or update data.
Input	Passive/Connected	Defines mapplet input rows. Available in the Mapplet Designer
Java	ActiveorPassive/Connected	Executes user logic coded in Java.The byte code for the user logic is stored in the repository
Joiner	Active/Connected	Joins data from different databases or flat file systems.
Lookup	ActiveorPassive/ConnectedorUnconnected	Lookup and return data from a flat file, relational table, view, or synonym.
Normalizer	Active/Connected	Source qualifier for COBOL sources. Can also use in the pipeline to normalize data from relational or flat file sources.
Output	Passive/Connected	Defines mapplet output rows. Available in the Mapplet Designer.
Rank	Active/Connected	Limits records to a top or bottom range.
Router	Active/Connected	Routes data into multiple transformations based on group conditions.
SequenceGenerator	Passive/Connected	Generates primary keys.
Sorter	Active/Connected	Sorts data based on a sort key.
SourceQualifier	Active/Connected	Represents the rows that the Integration Service reads from a relational or flat file source when it runs a session.
SQL	ActiveorPassive/Connected	Executes SQL queries against a database.
StoredProcedure	Passive/ConnectedorUnconnected	Calls a stored procedure.
TransactionControl	Active/Connected	Defines commit and rollback transactions.
Union	Active/Connected	Merges data from different databases or flat file systems.
UnstructuredData	ActiveorPassive/Connected	Transforms data in unstructured and semi-structured formats.
UpdateStrategy	Active/Connected	Determines whether to insert, delete, update, or reject rows.
XMLGenerator	Active/Connected	Reads data from one or more input ports and outputs XML through a single output port.
XMLParser	Active/Connected	Reads XML from one input port and outputs data to one or more output ports.
XMLSourceQualifier	Active/Connected	Represents the rows that the Integration Service reads from an XML source when it runs a session.