Informatica ETL(Extract, Transform and Load) Tool

Here,we will see how the tool INFORMATICA is used to extract data from Source(s), transform it and then load it into the Target. Data transformation is done to eliminate any erroneous or redundant data. This ensures that only the correct data is loaded into the Target (OLAP), which will be used for analysis / reporting.

Informatica PowerCenter architecture is used to achieve the extract, transform and load of data. PowerCenter provides an environment that allows you to load data into a centralized location, such as a datamart, data warehouse, or operational data store (ODS). You can extract data from multiple sources, transform the data according to business

logic you build in the client application, and load the transformed data into file and relational targets. PowerCenter provides the following integrated components

• PowerCenter repository. The PowerCenter repository is at the center of the PowerCenter suite. You create a set of metadata tables within the repository database that the PowerCenter applications and tools access. The PowerCenter Client and Server access the repository to save and retrieve metadata.
• PowerCenter Repository Server. The PowerCenter Repository Server manages connections to the repository from client applications. It inserts, updates, and fetches objects from the repository database tables. It also maintains object consistency.
• PowerCenter Client. Use the PowerCenter Client to manage users, define sources and targets, build mappings and mapplets with the transformation logic, and create workflows to run the mapping logic. The PowerCenter Client has the following client applications: Repository Manager, Repository Server Administration Console, Designer, Workflow Manager, and Workflow Monitor.
• PowerCenter Server. The PowerCenter Server extracts the source data, performs the data transformation, and loads the transformed data into the targets.

Sources

PowerCenter accesses the following sources:

• Relational. Oracle, Sybase, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
• File. Fixed and delimited flat file, COBOL file, and XML.
• Application. You can purchase additional PowerCenter Connect products to access business sources, such as PeopleSoft, SAP R/3, Siebel, IBM MQSeries, and TIBCO.
• Mainframe. You can purchase PowerExchange for faster access to IBM DB2 on MVS.
• Other. Microsoft Excel and Access.

Targets

PowerCenter can load data into the following targets:

• Relational. Oracle, Sybase, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
• File. Fixed and delimited flat file and XML.
• Application. You can purchase additional PowerCenter Connect products to load data into SAP BW. You can also load data into IBM MQSeries message queues and TIBCO.
• Other. Microsoft Access.

You can load data into targets using ODBC or native drivers, FTP, or external loaders. 

Repository

The PowerCenter repository resides on a relational database. The repository database tables contain the instructions required to extract, transform, and load data. PowerCenter Client applications access the repository database tables through the Repository Server.

You add metadata to the repository tables when you perform tasks in the PowerCenter Client application, such as creating users, analyzing sources, developing mappings or mapplets, or creating workflows. The PowerCenter Server reads metadata created in the Client application when you run a workflow. The PowerCenter Server also creates metadata, such as start and finish times of a session or session status.

You can develop global and local repositories to share metadata:

• Global repository. The global repository is the hub of the domain. Use the global repository to store common objects that multiple developers can use through shortcuts. These objects may include operational or Application source definitions, reusable transformations, mapplets, and mappings.
• Local repositories. A local repository is within a domain that is not the global repository. Use local repositories for development. From a local repository, you can create shortcuts to objects in shared folders in the global repository. These objects typically include source definitions, common dimensions and lookups, and enterprise standard transformations. You can also create copies of objects in non-shared folders.
• Version control. A versioned repository can store multiple copies, or versions, of an object. Each version is a separate object with unique properties. PowerCenter version control features allow you to efficiently develop, test, and deploy metadata into production.

You can connect to a repository, back up, delete, or restore repositories using pmrep, a command line program.

You can view much of the metadata in the Repository Manager. The Informatica Metadata Exchange (MX) provides a set of relational views that allow easy SQL access to the Informatica metadata repository.

Repository Server

The Repository Server manages repository connection requests from client applications. For each repository database registered with the Repository Server, it configures and manages a Repository Agent process. The Repository Server also monitors the status of running Repository Agents, and sends repository object notification messages to client applications.

The Repository Agent is a separate, multi-threaded process that retrieves, inserts, and updates metadata in the repository database tables. The Repository Agent ensures the consistency of metadata in the repository by employing object locking. 

PowerCenter Client

The PowerCenter Client consists of the following applications that you use to manage the repository, design mappings, mapplets, and create sessions to load the data:

• Repository Server Administration Console. Use the Repository Server Administration console to administer the Repository Servers and repositories.
• Repository Manager. Use the Repository Manager to administer the metadata repository. You can create repository users and groups, assign privileges and permissions, and manage folders and locks.
• Designer. Use the Designer to create mappings that contain transformation instructions for the PowerCenter Server. Before you can create mappings, you must add source and target definitions to the repository. The Designer has five tools that you use to analyze sources, design target schemas, and build source-to-target mappings:
• Source Analyzer. Import or create source definitions.
• Warehouse Designer. Import or create target definitions.
• Transformation Developer. Develop reusable transformations to use in mappings.
• Mapplet Designer. Create sets of transformations to use in mappings.
• Mapping Designer. Create mappings that the PowerCenter Server uses to extract, transform, and load data.
• Workflow Manager. Use the Workflow Manager to create, schedule, and run workflows. A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data. The PowerCenter Server runs workflow tasks according to the links connecting the tasks. You can run a task by placing it in a workflow.
• Workflow Monitor. Use the Workflow Monitor to monitor scheduled and running workflows for each PowerCenter Server. You can choose a Gantt chart or Task view. You can also access details about those workflow runs.

PowerCenter Server

The PowerCenter Server reads mapping and session information from the repository. It extracts data from the mapping sources and stores the data in memory while it applies the transformation rules that you configure in the mapping. The PowerCenter Server loads the transformed data into the mapping targets.

The PowerCenter Server can achieve high performance using symmetric multi-processing systems. The PowerCenter Server can start and run multiple workflows concurrently. It can also concurrently process partitions within a single session. When you create multiple partitions within a session, the PowerCenter Server creates multiple database connections to a single source and extracts a separate range of data for each connection, according to the properties you configure. 

Connectivity

PowerCenter uses the following types of connectivity:

• Network protocol
• Native drivers
• ODBC

The PowerCenter Client uses ODBC and native drivers to connect to source and target databases. It uses TCP/IP to connect to the Repository Server. The Repository Server uses native drivers to connect to the repository database. The Workflow Manager and the PowerCenter Server use TCP/IP to communicate with each other.

The PowerCenter Server uses native drivers to connect to the databases to move data. You can optionally use ODBC to connect the PowerCenter Server to the source and target databases. It uses TCP/IP to connect to the PowerCenter Client. 

Database Connections

The Repository Server maintains a pool of reusable database connections for serving client applications. The server generates a Repository Agent process for each database. The Repository Agent creates new database connections only if all the current connections are in use.

For example, if 10 clients send requests to the Repository Agent one at a time, the agent requires only one connection. It reuses the same database connection for all the requests. If the 10 clients send requests simultaneously, the Repository Agent opens 10 connections. You can set the maximum number of open connections using the DatabasePoolSize parameter in the repository configuration file.

For a session, a reader object holds the connection for as long as it needs to read the data from the source tables. A writer object holds a connection for as long as it needs to write data to the target tables.

The PowerCenter Server maintains a database connection pool for stored procedure or lookup databases in a workflow. You can optionally set the MaxLookupSPDBConnections parameter to limit connections when you configure the PowerCenter service. The PowerCenter Server allows an unlimited number of connections to lookup or stored procedure databases. If a database user does not have permission for the number of connections a session requires, the session fails.

For pre-session, post-session, and load-stored procedures, consecutive stored procedures reuse a connection if they have identical connection attributes. Otherwise, the connection for one stored procedure closes and a new connection begins for the next stored procedure. 

Designer Windows

You can display the following windows in the Designer:

• Navigator. Connect to repositories, and open folders within the Navigator. You can also copy objects and create shortcuts within the Navigator.
• Workspace. Open different tools in this window to create and edit repository objects, such as sources, targets, mapplets, transformations, and mappings.
• Output. View details about tasks you perform, such as saving your work or validating a mapping.
• Status bar. Displays the status of the operation you perform.

• Overview. An optional window to simplify viewing a workspace that contains a large mapping or multiple objects. Outlines the visible area in the workspace and highlights selected objects in color.
• Instance data. View transformation data while you run the Debugger to debug a mapping.
• Target data. View target data while you run the Debugger to debug a mapping.

All About Informatica Transformations

In Informatica, Transformations help us to transform the source data according to the requirements of target system and thereby ensuring the quality of the data being loaded into target.

Transformations mainly are of two types: Active and Passive.

Active Transformation

An active transformation can change the number of rows that pass through it from source to target i.e it eliminates rows that do not meet the condition in transformation.

Passive Transformation

A passive transformation does not change the number of rows that pass through it i.e it passes all rows through the transformation.

Transformations can be Connected or UnConnected.

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.

List of Transformations

Below are the list of Transformations available in

Informatica PowerCenter:

• Aggregator Transformation
• Expression Transformation
• Filter Transformation
• Joiner Transformation
• Lookup Transformation
• Normalizer Transformation
• Rank Transformation
• Router Transformation
• Sequence Generator Transformation
• Stored Procedure Transformation
• Sorter Transformation
• Update Strategy Transformation
• XML Source Qualifier Transformation
• Advanced External Procedure Transformation
• External Transformation
• Union Transformation

Now we will discuss in detail, about the Informatica Transformations and their significances in the ETL process.

Aggregator Transformation

Aggregator transformation is an Active and Connected transformation. This transformation is useful to perform calculations such as averages and sums (mainly to perform calculations on multiple rows or groups). For example, to calculate total of daily sales or to calculate average of monthly or yearly sales. Aggregate functions such as AVG, FIRST, COUNT, PERCENTILE, MAX, SUM etc. can be used in aggregate transformation.

Expression Transformation

Expression transformation is a Passive and Connected transformation. This can be used to calculate values in a single row before writing to the target. For example, to calculate discount of each product or to concatenate first and last names or to convert date to a string field.

Filter Transformation

Filter transformation is an Active and Connected transformation. This can be used to filter rows in a mapping that do not meet the condition. For example, to know all the employees who are working in Department 10 or to find out the products that falls between the rate category $500 and $1000.

Joiner Transformation

Joiner Transformation is an Active and Connected transformation. This can be used to join two sources coming from two different locations or from same location. For example, to join a flat file and a relational source or to join two flat files or to join a relational source and a XML source. In order to join two sources, there must be at least one matching port. While joining two sources it is a must to specify one source as master and the other as detail. The Joiner transformation supports the following types of joins:

• Normal
• Master Outer
• Detail Outer
• Full Outer

Normal join discards all the rows of data from the master and detail source that do not match, based on the condition.

Master outer join discards all the unmatched rows from the master source and keeps all the rows from the detail source and the matching rows from the master source.

Detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.

Full outer join keeps all rows of data from both the master and detail sources.

Lookup Transformation

Lookup transformation is Passive and it can be both Connected and UnConnected as well. It is used to look up data in a relational table, view, or synonym. Lookup definition can be imported either from source or from target tables.

For example, if we want to retrieve all the sales of a product with an ID 10 and assume that the sales data resides in another table. Here instead of using the sales table as one more source, use Lookup transformation to lookup the data for the product, with ID 10 in sales table.

Difference between Connected and UnConnected Lookup Transformation:

• Connected lookup receives input values directly from mapping pipeline whereas UnConnected lookup receives values from:LKP expression from another transformation.
• Connected lookup returns multiple columns from the same row whereas UnConnected lookup has one return port and returns one column from each row.
• Connected lookup supports user-defined default values whereas UnConnected lookup does not support user defined values.

Normalizer Transformation

Normalizer Transformation is an Active and Connected transformation. It is used mainly with COBOL sources where most of the time data is stored in de-normalized format. Also, Normalizer transformation can be used to create multiple rows from a single row of data.

Rank Transformation

Rank transformation is an Active and Connected transformation. It is used to select the top or bottom rank of data. For example, to select top 10 Regions where the sales volume was very high or to select 10 lowest priced products.

Router Transformation

Router is an Active and Connected transformation. It is similar to filter transformation. The only difference is, filter transformation drops the data that do not meet the condition whereas router has an option to capture the data that do not meet the condition. It is useful to test multiple conditions. It has input, output and default groups. For example, if we want to filter data like where State=Michigan, State=California, State=New York and all other States. It’s easy to route data to different tables.

Sequence Generator Transformation

Sequence Generator transformation is a Passive and Connected transformation. It is used to create unique primary key values or cycle through a sequential range of numbers or to replace missing keys.

It has two output ports to connect transformations. By default it has two fields CURRVAL and NEXTVAL(You cannot add ports to this transformation). NEXTVAL port generates a sequence of numbers by connecting it to a transformation or target. CURRVAL is the NEXTVAL value plus one or NEXTVAL plus the Increment By value.

Stored Procedure Transformation

Stored Procedure transformation is a Passive and Connected & UnConnected transformation. It is useful to automate time-consuming tasks and it is also used in error handling, to drop and recreate indexes and to determine the space in database, a specialized calculation etc.

The stored procedure must exist in the database before creating a Stored Procedure transformation, and the stored procedure can exist in a source, target, or any database with a valid connection to the Informatica Server. Stored Procedure is an executable script with SQL statements and control statements, user-defined variables and conditional statements. In case of stored procedure transformation procedure will be compiled and executed in a relational data source. You need data base connection to import the stored procedure in to your mapping.

Sorter Transformation

Sorter transformation is a Connected and an Active transformation. It allows to sort data either in ascending or descending order according to a specified field. Also used to configure for case-sensitive sorting, and specify whether the output rows should be distinct.

Source Qualifier Transformation

Source Qualifier transformation is an Active and Connected transformation. When adding a relational or a flat file source definition to a mapping, it is must to connect it to a Source Qualifier transformation. The Source Qualifier performs the various tasks such as overriding default SQL query, filtering records; join data from two or more tables etc.

Update Strategy Transformation

Update strategy transformation is an Active and Connected transformation. It is used to update data in target table, either to maintain history of data or recent changes. You can specify how to treat source rows in table, insert, update, delete or data driven.

XML Source Qualifier Transformation

XML Source Qualifier is a Passive and Connected transformation. XML Source Qualifier is used only with an XML source definition. It represents the data elements that the Informatica Server reads when it executes a session with XML sources.

Advanced External Procedure Transformation

Advanced External Procedure transformation is an Active and Connected transformation. It operates in conjunction with procedures, which are created outside of the Designer interface to extend PowerCenter/PowerMart functionality. It is useful in creating external transformation applications, such as sorting and aggregation, which require all input rows to be processed before emitting any output rows.

Union Transformation

The union transformation is used to merge multiple datasets from various streams or pipelines into one dataset. This transformation works similar to the UNION ALL, it does not remove any duplicate rows. It is recommended to use aggregator to remove duplicates are not expected at the target.

External Procedure Transformation

External Procedure transformation is an Active & Connected/UnConnected transformations. Sometimes, the standard transformations such as Expression transformation may not provide the functionality that you want.In such cases External procedure is useful to develop complex functions within a dynamic link library (DLL) or UNIX shared library, instead of creating the necessary Expression transformations in a mapping.

Differences between Advanced External Procedure and External Procedure Transformations:

• External Procedure returns single value, where as Advanced External Procedure returns multiple values.
• External Procedure supports COM and Informatica procedures where as AEP supports only Informatica Procedures.

Please Check out our Latest Post: How Nested Tables can bestored and retrieved in a Normal table-ORACLE PL/SQL

What is Business Intelligence?

Business Intelligence (BI) is a term commonly associated with data warehousing. Business intelligence (BI) is defined as the ability of an organization to take all its capabilities and convert them into knowledge, ultimately, getting the right information to the right people, at the right time, via the right channel.This will produce huge amounts of information that can lead to the development of new opportunities for the organization. Through these opportunities & through effective

Business Intelligence Tools

Business Intelligence(BI) tools are a type of application software designed to retrieve, analyze and report data. These tools generally read data that have been previously stored in a data warehouse or data mart. There is a broad range of BI tools that range in purpose from data acquisition to data processing. The most common tools used for business intelligence are as follows.

ETL/DW

Informatica

Oracle Warehouse Builder

Ab Initio

Microsoft SSIS

IBM DataStage

ErWin Data Modeler

ER Studio

Kettle

Talend

Control-M

AutoSys

Reporting/Analytics

BiRT

Jasper Reports

Pentaho

OBIEE

BusinessObjects

Cognos

Altio

MicroStrategy

Cystal Reports

MS SSRS

Benchmarking/Integration

Informatica

MicroStrategy

Cognos

Data Stage

Tableau

Kettle

ER Studio

Kettle

BO

Enterprise Management

Cognos

BO

Open source tools

Relational Database Management

Oracle

Sybase

MS SQL Server

MySQL

Customer Relationship Management

Siebel CRM