Latest versions of Informatica

Below listed are the versoins of Informatica in the order of there launch

Versions

Informatica Powercenter 4.1

Informatica Powercenter 5.1

Informatica Powercenter 6.1.2

Informatica Powercenter 7.1.2

Informatica Powercenter 8.1

Informatica Powercenter 8.5

Informatica Powercenter 8.6

Informatica Powercenter 9

List of Popular ETL Tools in Market

ETL Tools

Oracle Warehouse Builder (OWB) 11gR1 Oracle

Data Services XI 3.2 SAP Business Objects

IBM Information Server (Datastage) 9.1 IBM

SAS Data Integration Studio 4.21 SAS Institute

PowerCenter Informatica 9.0 Informatica

Elixir Repertoire 7.2.2 Elixir

Data Migrator 7.7 Information Builders

SQL Server Integration Services (SSIS) 10 Microsoft

Talend Open Studio & Integration Suite 4.0 Talend

DataFlow Manager 6.5 Pitney Bowes Business Insight

Pervasive Data Integrator 9.2 Pervasive Software

Open Text Integration Center 7.1 Open Text

Transformation Manager 4.1.4 ETL Solutions Ltd.

Data Manager/Decision Stream 8.2 IBM (Cognos)

Clover ETL 3.0.1 Javlin

Centerprise 5.0 Astera

DB2 Warehouse Edition 9.1 IBM

Pentaho Data Integration 4.1 Pentaho

Adeptia Integration Suite 5.1 Adeptia

DTM-Data Transformation Manager Process

While running a Workflow,the PowerCenter Server uses the Load Manager process and the Data Transformation Manager Process (DTM) to run the workflow and carry out workflow tasks.When the PowerCenter Server runs a workflow, the Load Manager performs the following tasks:  

• Locks the workflow and reads workflow properties. 
• Reads the parameter file and expands workflow variables. 
• Creates the workflow log file. 
• Runs workflow tasks. 
• Distributes sessions to worker servers. 
• Starts the DTM to run sessions. 
• Runs sessions from master servers. 
• Sends post-session email if the DTM terminates abnormally. 

After the load manager performs validations for the session, it creates the DTM process. The DTM process is the second process associated with the session run. The primary purpose of the DTM process is to create and manage threads that carry out the session tasks.When you run a session, the DTM process reads source data and passes it to the transformations for processing.When the PowerCenter Server runs a session, the DTM performs the following tasks: 

• Fetches session and mapping metadata from the repository. 
• Creates and expands session variables. 
• Creates the session log file. 
• Validates session code pages if data code page validation is enabled. Checks query conversions if data code page validation is disabled. 
• Verifies connection object permissions. 
• Runs pre-session shell commands. 
• Runs pre-session stored procedures and SQL. 
• Creates and runs mapping, reader, writer, and transformation threads to extract,transform, and load data. 
• Runs post-session stored procedures and SQL. 
• Runs post-session shell commands. 
• Sends post-session email.

The DTM allocates process memory for the session and divide it into buffers. This is also known as buffer memory. It creates the main thread, which is called the master thread. The master thread creates and manages all other threads.If we partition a session, the DTM creates a set of threads for each partition to allow concurrent processing.. When Informatica server writes messages to the session log it includes thread type and thread ID. Following are the types of threads that DTM creates:

Master Thread :Main thread of the DTM process. Creates and manages all other threads.
Mapping Thread : One Thread to Each Session. Fetches Session and Mapping Information.
Pre and Post Session Thread :One Thread each to Perform Pre and Post Session Operations.
Reader Thread:One Thread for Each Partition for Each Source Pipeline.
Writer Thread :One Thread for Each Partition if target exist in the source pipeline write to the target.
Transformation Thread :One or More Transformation Thread For Each Partition.

DTM Buffer Memory
The DTM allocates buffer memory to the session based on the DTM Buffer Size setting in the session properties. By default, the Integration Service determines the DTM buffer size at run time. The Workflow Manager allocates a minimum of 12 MB for DTM buffer memory.You can specify auto or a numeric value. If you enter 2000, the Integration Service interprets the number as 2000 bytes. Append KB, MB, or GB to the value to specify other units. For example, you can specify 512MB.
Increase the DTM buffer size in the following circumstances:

• A session contains large amounts of character data and you configure it to run in unicode mode. Increase the DTM buffer size to 24MB.
• A session contains n partitions. Increase the DTM buffer size to at least n times the value for the session with one partition.

Informatica Basic Features- Power Centre ,Power Mart

Informatica is one of the most powerful ETL tool with a simple visual interface. You do most of the work by simply dragging and dropping with your mouse in the Designer. This graphical approach makes it also very easy to understand what is going on (it is "self-documenting" in a sense).

It can communicate with all major databases, can move/transform data between them. It can move huge volumes of data in a very effective way. It can throttle the transactions (do big updates in small chunks to avoid long locking and filling the transactional log). 

It can effectively do joins between tables in different databases on different servers. The tasks are performed by Informatica Server (Unix or MS Windows). You get a client application called "Server Manager" to work with the server.

You design your processes in a client application called "Designer". This is where you you tell what the source databases and tables will be, what will be the targets, and how you move/transform the data.

Informatica uses its own database called "Metadata Repository Database", or simply a Repository. Repository stores the data (rules) needed for data extraction, transformation, loading, and management. You get a client application "Repository Manager" to work with the repository.

 

General Layout of mapping :

SOURCE --> SOURCE QUALIFIER -->TRANSFORMATIONS ---> TARGET

 

Products:

Comprised of the flagship Informatica PowerCenter® and Informatica PowerMart® software products, as well as expanded connectivity solutions that extend even across firewalls, this data integration platform helps companies integrate and analyze data from virtually any corporate system, including ERP, CRM, supply chain, procurement, eBusiness, mainframe and legacy systems. The Informatica platform provides real-time performance, scalability and extensibility to handle the analytic requirements of even the largest enterprises.

They have different products.

• Power Center
• Power Mart
• Power Connect
• Power Channel
• Power Plug

Power Center & Power Mart :

The most 2 important products are Power Center & Power Mart. Power Mart is a subset of Power Center. Power Center has better load balancing.

Power Center : is the world-standard enterprise data integration platform for the creation and real-time management of data warehouse infrastructures and analytic applications.

• Distributed metadata (Repository)
• Global & Local Repositories (can register more than one Informatica server & can share the information in the global repository). Multiple repositories, many servers.
• Can connect to varied sources like Peoplesoft, SAP etc.
• Cost is more
• Has bridges which can transport meta data from other tools (like Erwin)

Power Mart : is a departmental data integration platform and can be expanded as a company's needs grow to an enterprise level with Informatica PowerCenter 5

• Only Local Repository. (Can’t register other repositories). Single repository, one server.
• Cannot connect to varied sources like Peoplesoft, SAP etc.
• Cost is less

Mappings : defines the flow of data from source to target database. A mapping contains more than one transformation. After getting the Source & Target, we define the mappings. The definitions of the mappings are stored in the repository.

Informatica Repository Server : A server used for managing the information with in the meta data repository. All the clients will be communicated with the Repository server. The information in the repository is managed by Repository Server.

When ever we are working with client tool, the first task is logon to repository. We need to have a valid user to logon. The communication between the client tools & Repository Server is TCP/IP

Informatica Server : is responsible for loading of data.Parts of the Informatica Server :

1.. Load Manager

2. Data Transformation Manager

3. Reader

4. Writer

Informatica Repository : There are 2 types of repositories. Global & Local repositories(with reference to Power Center). With Power Mart only one local repository. The information of all local repositories are located in Global repository. Shared information can be put in global repository.

Folders : To organize the files (like containers). Use to organize the information with in the repository. There can be one or more folders with in a repository. Each folder is independent of the other. Whenever we are going to work, work with in the folder. A folder contains a pre-defined set of sub folders.

 

• Sources (Contains the source database object definitions)
• Targets (Target db object definitions)
• Dimensions (Dimension definitions)
• Cubes (Cube definitions)
• Transformations (contains reusable transformation definitions)
• Mapplets (A mapplet is a reusable part of a mapping which contains one or more reusable transformations. Contains the mapplet definitions)
• Mappings (Mapping definitions)
• Business Components (Business is classified as far as the business usage)
• Sessions (contains session definitions. A session is a task based on a mapping)

Informatica PowerCenter : has different tools

 Repository Manager

Can create and manage repository and repository objects(Users& Folders). Can take back up and perform recoveries. Can perform security related issues. Permissions etc.

Two different admin users created for repository are Administrator and the database user of repository schema

Two default user groups created in the repository are Administrator and Public

 

Designer

• define / create the source database objects
• define / create target database objects
• define transformations
• define mapplets
• define mappings
• validate the mappings & transformations
• debug the sessions
• compare different objects
• define the dimensions & cubes

Source Database : Basically from where we are going to retrieve the data. Source is an existing one. The source is read only. Ex : Flat files, XML files, Cobol files, Oracle, SQL Server, Sybase, Informix, DB/2, ERP(SAP, People Soft), CRM, ODBC.From the client we connect to the source through ODBC and get the meta data definitions of the source and then store in the repository. If a connection is not possible, we has to be manually defined.

Target Database : Actual Warehouse database. Objects may/may not exists. Can have read, write access to the target database.

Connection : defines the communication path. (ODBC, TCP/IP, Native)

Transformations : are the programs which are used to transform the data from one form to other. 14/15 transformations.Here are some transformations:

Aggregator - to do things like "group by".

Source Qualifier - ...

Expression - to use various expressions.

External procedure - ...

Filter - to filter data.

Joiner - to make joins between separate databases, file, ODBC sources.

Lookup - to create local copy of the data.

Normalizer - to transform denormalized data into normalized data.

Rank - to select only top (or bottom) ranked data.

Sequence Generator - to generate unique IDs for target tables.

Source Qualifier - to filter sources (SQL, select distinct, join, etc.)

Stored Procedure - to run stored procedures in the database - and capture their returned values.

Update Strategy - to flag records in target for insert,delete, update (defined inside a mapping).

Meta Data : Data about data / information about the data.

MetaData Repository : basically where the meta data definitions are stored. It can be located in any RDBMS (Oracle, SQL Server, DB/2, Sybase, Informix, ODBC).We call this Repository as Power Center / Power Mart / Informatica Repository).