Informatica: ETL (Extract-Transform-Load) tool

• Extract data from multiple sources
• Transform the data according to business logic you
   build in the client application
• Load data into a centralized location, such as a data
   mart, data warehouse, or operational data store
   (ODS)

Informatica has 4 main components

1.Informatica Repository (Server Component)

Set of metadata tables within the repository database

2.Informatica Repository Server

(Server Component)

Manages connections to the repository from

client applications

3.Informatica Server (Server Component)

  Extracts source data, performs data

transformation & load the target

4.Informatica Client (Client Component)

Manage users, build mappings and create

workflows

• Repository Manager
• Designer
• Workflow Manager
• Workflow Monitor

Components of Informatica Client

Repository Manager

Manage the Informatica repository from the client machine

Designer

The Designer helps you create source definitions, target definitions, and

transformations to build a mapping

Workflow Manager

Allows user to execute a mapping created in the designer

Workflow Monitor

Tool that allows you to monitor workflows and tasks

The Repository Manager client tool allows you to navigate 

through multiple folders and repositories and perform basic

repository tasks such as

• Create / delete repositories
• User Administration
• Monitoring locks

Demo of Repository Manager

Exercise

Create new user

Create new folder

Give access to user

The Designer provides the following tools:

• Source Analyzer: Use to import or create source
  definitions
• Warehouse Designer: Use to import or create target
  definitions
• Transformation Developer: Use to create reusable
  transformations
• Mapplet Designer: Use to create mapplets
• Mapping Designer: Use to create mappings

1.Source databases

• Relational: Oracle, Sybase, Informix, IBM DB2, MS SQL Server &Teradata
• File: Fixed and delimited flat file, COBOL file, and XML.
• Application: Additional PowerConnect products to access business sources such as PeopleSoft, SAP R/3, Siebel, IBM MQSeries, and Tibco.
• Mainframe: PowerConnect for Mainframe (IBM DB2 on MVS).
• Other: Microsoft Excel and Access.

2.Target databases

• Relational: Oracle, Sybase, Sybase IQ, Informix, IBM DB2, MS SQL Server & Teradata.
• File: Fixed and delimited flat file and XML.
• Application: Additional PowerConnect products to load data into SAP BW, IBM MQSeries
•   message queues and Tibco

Other: Microsoft Access

Demo of Informatica Designer

Exercise:

Import a source definition

Import a target definition

Create a basic mapping 

Informatica Client - Workflow Manager

To create a workflow, you first create tasks such as a session,

which contains the mapping you build in the Designer. Workflow 

Manager provides 3 tools:

• Task Developer: Use the Task Developer to create tasks you
  want to execute in the workflow.
• Workflow Designer: Use the Workflow Designer to create a
  workflow by connecting tasks with links. You can also create
  tasks in the Workflow Designer as you develop the workflow.
• Worklet Designer: Use the Worklet Designer to create a
  worklet

 Informatica Client – Workflow Monitor

The Workflow Monitor consists of the following windows:

• Navigator window: Displays monitored repositories, servers,
   and repositories objects
• Output window: Displays messages from the Informatica
  Server and the Repository Server.
• Time window: Displays progress of workflow runs.
• Gantt Chart view: Displays details about workflow runs in
  chronological (Gantt Chart) format.
• Task view: Displays details about workflow runs in a report
  format, organized by task, folder, or status

Demo of Informatica Workflow Manager and Workflow Monitor

Exercise:

Create a task

Create a workflow

Monitor a workflow

Specialized transformations

1.     Source Qualifier    
2.     Update Strategy 
3.     Lookup transformation
4.     Sequence Generator 
5.     Expression transformation
6.     Stored procedure transformation

   Transformation - Source Qualifier

The Source Qualifier represents the rows that the 

Informatica Server reads when it executes a session. 

(only 1 per mapping)

Join data originating from multiple source tables

Filter data from source tables (add WHERE clause)

Specify an outer join rather than the default inner join

Specify sorted ports: Add an ORDER BY clause to the

default SQL query

Select only distinct values from the source

Create a custom query to issue a special SELECT

Transformation – Update Strategy 

This transformation determines how to handle changes to

existing rows in the target

Within a session:

TREAT SOURCE ROWS AS

    INSERT (default)

    UPDATE

    DELETE

    DATA DRIVEN

Individual target tables

    INSERT (default)

    UPDATE AS UPDATE

    UPDATE AS INSERT

    UPDATE ELSE INSERT

    DELETE

Within a mapping:

Using Update transformation

Transformation – Lookup

Use a Lookup transformation in your mapping to look up data 

in a relational table, view, or synonym

Lookup table – Defines the database table to perform lookup operation

Ports – Define the table columns used for the lookup

Properties – Lookup policy, caching, tracing level

Condition – Lookup condition

Transformation – Sequence Generator

The Sequence Generator transformation generates 

numeric values

NEXTVAL / CURRVAL (default ports)
Properties

        Start value (default = 0)

   End value (default = 2147483647)

   Increment by (default = 1)
   Cycle

Transformation – Expression

Expression transformation are used to calculate values in a

single row before writing to the target

Character operations (Concatenation, Case, Trim etc)

Arithmetic operations (Absolute, Exponential, SQRT etc)

Conversion (TO_CHAR, TO_DATE etc)

Date operations (TO_DATE, ADD_TO_DATE etc)

Special operations (e.g. IIF / DECODE etc)

Test operations (IS_DATE, ISNULL, IS_NUMBER etc)

 Transformation – Stored Procedure

    Used to call Stored procedures within a mapping

    Connected mode: Every row of data in the Source Qualifier

 transformation passes data through the Stored Procedure transformation

    Unconnected mode: Stored procedure runs before or after the session

 and is not connected to the flow of data

    Type of Stored procedure

Normal (Row by row execution)

Pre-load of the Source: Before session retrieves data from source

Post-load of the Source: After session retrieves data from source

Pre-load of the Target: Before session loads data into target

Post-load of the Target: After session loads data into target

 Other Transformations

Aggregator Transformation: The Aggregator transformation allows you to perform 

aggregate calculations, such as averages and sums on data groups

External procedure Transformation: Use Transformation Exchange (TX) dynamic 

invocation interface to call COM components and DLLs

Filter Transformation:  Used to filter rows in a mapping

Joiner Transformation: Joiner transformation joins two related

heterogeneous sources residing in different locations or file systems

with at least one common port



Normalizer Transformation: Primarily used with COBOL sources which are

often stored in a denormalized format.

Rank Transformation: Allows you to select only the top or bottom rank of

data

 Router Transformation:Router transformation tests data for one or more 

conditionsand gives you the option to route rows of data that do not meet any

of  the conditions to a defaultoutput group.

     Sorter Transformation: The Sorter transformation allows you to sort

     data in ascending or descending order according to a specified sort key


     XML Source Qualifier:  The XML Source Qualifier represents the data       
     elements that the Informatica Server reads when it executes a session

     with XML sources.