Informatica transformation is one of the building block of Informatica mapping. Informatica Transformation is informatica object which is used to read, manipulate and load data. Each transformations perform specific functions. Data pass in and out from the transformation through ports.
What is Informatica Transformation ?
Informatica Transformation is one of the key Informatica repository object , which read data from one or more input , process the data and move it to target or other transformation for further processing.
Check here for Informatica mapplet tutorial
Example : Rank Transformation , which give the rank to the input data based on some key fields. Joiner transformation join the data from multiple source.
Types of Informatica Transformation :
Mainly Informatica Transformation can be divided into two categories
- Active Transformation:
All those Informatica Transformation which change the number of rows passing through it or change the transaction boundary or it changes the row type. For Example filter is active transformation , as it pass only those records which fulfil its filtering condition.
- Passive Transformation:
All those Informatica Transformation which doesn’t change the number of rows and maintain the transaction boundary and row type.
Example : Expression transformation , as it just change field data , but not the number of rows
Classification of Informatica Transformation based on connection :
All Informatica Transformation can be connected or unconnected type to the dataflow
- Connected Type Transformation: All those Transformation which are connected to other transformation via port link are connected transformation . Example Expression,Router , Sorter
- Unconnected Type Transformation : All those transformation which are not connected to any other transformation via port link , rather it is called within from transformation in form of some function.Example, unconnected Lookup. which is called as LKP.lookup_name(input paramete) from some expression’s field.
For more details on it , please check “Connected Transformation vs Unconnected Transformation”
List of Informatica Transformation:
|Aggregator Transformation||Active/Connected||Performs aggregate calculations. It changes the no of rows|
|Application Source Qualifier||Active/Connected||Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a session.|
|Custom||Active or Passive/Connected||Calls a procedure in a shared library or DLL.|
|Data Masking||Passive/Connected||Replaces sensitive production data with realistic test data for non-production environments.It changes the value only not rows itself|
|Expression Transformation||Passive/Connected||Calculates a value.|
|External Procedure||Passive/Connected||Calls a procedure in a shared library or in the COM layer of Windows.|
|Filter Transformation||Active/Connected||Filters data based on condition|
|HTTP||Passive/Connected||Connects to an HTTP server to read or update data.|
|Mapplet Input||Passive/Connected||Defines mapplet input rows. Available in the Mapplet Designer.|
|Java||Active or Passive/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 Transformation||Active or Passive/Connected Or Unconnected||Look up 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.|
|Mapplet 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.|
|Sequence Generator||Passive/Connected||Generates primary keys.|
|Sorter||Active/Connected||Sorts data based on a sort key.|
|Source Qualifier||Active/Connected||Represents the rows that the Integration Service reads from a relational or flat file source when it runs a session.|
|SQL||Active or Passive/Connected||Executes SQL queries against a database.|
|Stored Procedure||Passive/Connected Or Unconnected||Calls a stored procedure.|
|Transaction Control||Active/Connected||Defines commit and rollback transactions.|
|Union Transformation||Active/Connected||Merges data from different databases or flat file systems.|
|Unstructured Data||Active or Passive/Connected||Transforms data in unstructured and semi-structured formats.|
|Update Strategy||Active/Connected||Determines whether to insert, delete, update, or reject rows.|
|XML Generator||Active/Connected||Reads data from one or more input ports and outputs XML through a single output port.|
|XML Parser||Active/Connected||Reads XML from one input port and outputs data to one or more output ports.|
|XML Source Qualifier||Active/Connected||Represents the rows that the Integration Service reads from an XML source|
Key points to be noted:
- Informatica designer doesn’t allow to connect multiple Active Transformation (or Passive transformation with previous Transformation) to the same data flow. For example we cannot connect two filtered data to same target definition. As informatica process the data row by row , it don’t concatenate multiple rows directly under the same dataflow
- Sequence Generator is an exception for this rule .As we can connect multiple sequence generator to same transformation(say expression). As it don’t receive any input , just create numeric sequence , hence it can be concatenated with coming rows.
- We can combine two passive data flow ( which contain only passive transformation ) to same downstream data flow
So , In this article ,we started with small introduction of Informatica transformation and their purpose in Informatica mapping. Later on we will go through various type of Informatica transformation with example.