Tutorial corner

Informatica,ETL,oracle,sql/plsql

Informatica Transformation – Definition and Types

Spread the love

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.

 

Informatica transformation
Informatica transformation

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

Check here for difference between active and passive transformation in informatica

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:

Transformation Type Description
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.

Updated: December 6, 2015 — 3:23 pm

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme