Introduction to Informatica PowerCenter

1. Introduction

Before introduce Informatica PowerCenter, we need to know below concepts:

a. Data Warehouse(DW): Data warehouse is the repository to store or archive the historical data of the organization. A data warehouse is subject oriented, integrated, Non volatile and time variant collection of data. Data analyst can perform various operation to analyze data, such as data mining, data mart without slowing down the operation systems. Data warehouse support managers to take business decisions.

b. Data Mart: The data mart, like the data warehouse, can also provide a picture of a business organization's data and help the organizational staff in formulating strategies base on aggregated data and statistical analysis of industry trends and patterns. Data mart is also known as local data warehouse.

c. ETL: Extract data from multiple sources. Transform the data according to business logic and need. Load the transformed data into file and relational DB. Extraction of data from source, carrying out the necessary transformations & loading the data into the target database.

2. ETL Tool

As ETL is the most challenging task and the most time consuming process in the data warehouse project, we need to use of commercially available tools make the ETL esay and fast.

Toos available: Informatica PowerCenter, Data Stage, Business Objects.

Informatica PowerCenter is an ETL tool that allow you to load data into a centralized place, such as data mart,data warehouse or optional data store.

Why Informatica?

• Addressing the ETL process in a data warehouse development.
• Ability to source large volumes of data.
• Ability to source from multiple platforms.
• Can handle complex transformations: Filters, Lookups, Joiners, Math functions etc.
• Support high speed loads.

3. Informatica Architecture

Informatica consists of the following integrated components:

• Informatica Repository: Informatica Repository is the center of Informatica. You create a set of metadata tables within repository database that the Informatica application and tools access. Informatica Client and Server access the repository to save and access metadata.
• Informatica Client: Informatica Client is used to manage users, define sources and targets, build mappings and mapplets with the transformation logic, and create sessions to run the mapping logic. Informatica client consists of Repository Manager, Designer and Server Manager.
• Informatica Server: Informatica Server extracts data from source, transforms data and load-transformed data into targets.

4. Sources

Informatica access the following sources:

• Relational - Oracle, Sybase, Informix, IBM DB2, Microsoft SQL Server and Teradata.
• File - Fixed and delimited flat file, COBOL file and XML.
• Extended - PeopleSoft, SAP R/3, Siebel and IBM MQSeries (need to purchase additional products for these sources).
• Mainframe - Need to purchase additional products.
• Other - Microsoft Excel and Access.

5. Targets

Informatica can load data into following targets:

• Relational - Oracle, Sybase, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server and Teradata.
• File - Fixed and delimited flat files and XML.
• Extended - SAP BW and IBM MQSeries (need to purchase additional products for these targets).
• Other - Microsoft Access.

6. Informatica Products

• Repository Server Administration Console: Used to connect/disconnect to the Repository Server
• Repository Manager: Used to create/organize/manage the Repository
• Designer: Used to create mappings that contain transformation instructions
• Workflow Manager: Used to create and run workflows & tasks
• Workflow Monitor: Used to monitor scheduled & running workflows

7. Informatica Design Process

Informatica design process mainly consists of five different steps:

• Create Repository – Repository will hold all metadata and thus drive extraction and transformation process of Informatica.
• Import Source Definitions – Source Analyzer in Designer is used to import or create source definitions.
• Create Target Schema – Warehouse Designer in Designer is used to import or create target definitions.
• Create Mappings – Mapping Designer in Designer is used to link source to target with the required transformations.
• Load Data – Server Manager is used to create and schedule sessions and batches to run the mappings. Based on the information in transformation and repository metadata Informatica Server loads data into targets.