learninformatica

Basic information on informatica 

What is Informatica?
Informatica is a tool, supporting all the steps of Extraction, Transformation and Load process.
When u build a data warehouse (DWH) u need to :
1) Extract data from different data sources (oracle, SQL Server, Flat files, XML etc).
2) Transform this data i.e make it consistent, eg; Oracle uses data type number for a field and SQL server uses integer. If data from both theses fields is to be transferred to DWH, one needs to ensure that this data has the same data type as that of the target DWH.
3) Once data has been extracted and Transformed it is Loaded in DWH. We use the word LOAD instead of Insert because this is not new data, it is already present somewhere. We are simply removing it from operational Data store and putting into DWH.
Informatica ETL product, known as Informatica Power Center consists of 3 main components.

1. Informatica PowerCenter Client Tools:

These are the development tools installed at developer end. These tools enable a developer to

• Define transformation process, known as mapping. (Designer)
• Define run-time properties for a mapping, known as sessions (Workflow Manager)
• Monitor execution of sessions (Workflow Monitor)
• Manage repository, useful for administrators (Repository Manager)
• Report Metadata (Metadata Reporter)

2. Informatica PowerCenter Repository:

Repository is the heart of Informatica tools. Repository is a kind of data inventory where all the data related to mappings, sources, targets etc is kept. This is the place where all the metadata for your application is stored. All the client tools and Informatica Server fetch data from Repository. Informatica client and server without repository is same as a PC without memory/harddisk, which has got the ability to process data but has no data to process. This can be treated as backend of Informatica.

3. Informatica PowerCenter Server:

Server is the place, where all the executions take place. Server makes physical connections to sources/targets, fetches data, applies the transformations mentioned in the mapping and loads the data in the target system.

Informatica Product Line

Informatica is a powerful ETL tool from Informatica Corporation, a leading provider of enterprise data integration software and ETL softwares.
The important products provided by Informatica Corporation is provided below:

• Power Center
• Power Mart
• Power Exchange
• Power Center Connect
• Power Channel
• Metadata Exchange
• Power Analyzer
• Super Glue

Informatica Transformations

A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. For example, an Aggregator transformation performs calculations on groups of data.
Transformations can be of two types:
Active Transformation
An active transformation can change the number of rows that pass through the transformation, change the transaction boundary, can change the row type. For example, Filter, Transaction Control and Update Strategy are active transformations. The key point is to note that Designer does not allow you to connect multiple active transformations or an active and a passive transformation to the same downstream transformation or transformation input group because the Integration Service may not be able to concatenate the rows passed by active transformations However, Sequence Generator transformation(SGT) is an exception to this rule. A SGT does not receive data. It generates unique numeric values. As a result, the Integration Service does not encounter problems concatenating rows passed by a SGT and an active transformation.
Passive Transformation.
A passive transformation does not change the number of rows that pass through it, maintains the transaction boundary, and maintains the row type. The key point is to note that Designer allows you to connect multiple transformations to the same downstream transformation or transformation input group only if all transformations in the upstream branches are passive. The transformation that originates the branch can be active or passive.
Transformations can be Connected or UnConnected to the data flow.
Connected Transformation
Connected transformation is connected to other transformations or directly to target table in the mapping.
UnConnected Transformation
An unconnected transformation is not connected to other transformations in the mapping. It is called within another transformation, and returns a value to that transformation.

Informatica Transformations

Following are the list of Transformations available in Informatica:

• Aggregator Transformation
• Application Source Qualifier Transformation
• Custom Transformation
• Data Masking Transformation
• Expression Transformation
• External Procedure Transformation
• Filter Transformation
• HTTP Transformation
• Input Transformation
• Java Transformation
• Joiner Transformation
• Lookup Transformation
• Normalizer Transformation
• Output Transformation
• Rank Transformation
• Reusable Transformation
• Router Transformation
• Sequence Generator Transformation
• Sorter Transformation
• Source Qualifier Transformation
• SQL Transformation
• Stored Procedure Transformation
• Transaction Control Transaction
• Union Transformation
• Unstructured Data Transformation
• Update Strategy Transformation
• XML Generator Transformation
• XML Parser Transformation
• XML Source Qualifier Transformation
• Advanced External Procedure Transformation
• External Transformation