1 etl informatica

DATA WAREHOUSE Cognos PPES

New Concept Training Center 1

1

2

Informatica PowerCenter



3

Connectivity Overview

4

Informaticas Architecture

DesignerWorkflow Manager

Workflow MonitorRepository Manager

Server

Repository

Targets 1-nSources 1-nPowerPlugs

Data Models



5

Connectivity Overview

6

Informatica Design Process

1. Create Repository2. Import Source Definitions3. Import/Create Target Schema4. Create Mappings5. Load Data

1.

2.

Source Def

3. Target Def

Mapping

4.

Sessions

5.



7

Repository

8

Metadata Repository

Information about the target systemCatalogs the repositoryDirects the serverContains record of user accessCan be sharedCan be searched and reportedBridged through Metadata Exchange



9

Repository Manager

10

Repository Manager Navigator



11

Create Repository

Launch the Repository Manager Repository Connect to Repository Server

12

1. Create the configuration

In Manage Repository window Configuration - Create



13

2. Create Repository

In Manage Repository window Repository Create

14

Informatica Server can not start up without repository

NO REPOSITORY



15

Create a group

Connect to the repository using repository manager SecurityManage Groups

16

Assign Privileges to a Group



17

Creating a new user

18

Object Locking(applied at object level)

Lock - automaticUnlock - automatic, manual

Permissions(issued at folder level)

ReadWriteExecute

Privileges(issued per login ID and/or group)

Use Designer *Browse Repository *Create Sessions and Batches *Session OperatorAdminister Repository Administer ServerSuper User

User Management

* Default privileges to each new user and group



19

Groups and Privileges

Create users Create groups Assign privileges to

groups

Move users into groups Assign additional privileges to

users

20

Folder Permissions

Assign one user as the folder owner for first tier permissions

Select one of the owners groups for second tierpermissions

All users and groups in the repository will be assigned the third tier permissions



21

Designer Tools

22

Designer Windows



23

Working with Sources

Relational SourceOracleSybaseInformixSQL ServerDB2

MS Excel Source Cobol Source Flat files XML sources

24

Working with Relational source



25

Import from Database Import from FilesFlat FileExcel fileXML file

Import from Cobol File Create manually Transfer from data modeling

tools via PowerPlugs

Analyze Sources

Repository

Relational Diff Type Of files

COBOL file Data Model

26

Relational Source Analysis

TableViewSynonym

ODBC

Relational Source

DEF

DEF

Repository

Source Analyzer

ODBC



27

Import from relational database

28

Import from relational database After import, chose Repository Save



29

Updating a relational source definition

Edit existing source definitionRe-import source definition

30

Flat File Analysis

DEF Fixed Width Delimited

Mapped DriveNFS MountLocal Directory

Flat File

DEF

Repository

Source Analyzer

ODBC



31

Flat File Wizard

Three-step wizard

Can rename columns within wizard

Only datatypes allowed are text or numeric

Wizard guesses datatype

32

Flat File Analysis

Edit Source Definition



33

Import source from MS Excel

To import source from MS Excel Install the MS Excel ODBC driver Create a MS Excel ODBC Data Source for each

source filePrepare MS spreadsheet by definition ranges

and formatting columns of numeric data import source definitions to the designer

34

Import source from XML



35

COBOL File Analysis

DEF

Mapped DriveNFS Mounting

Local Directory

.CBL File

DATA

Supported Numeric Storage Options

DEF

Repository

Source Analyzer

ODBC

36

Cobol File Analysis

Edit Source Definition



37

Working with Target Warehouse Designer

38

Importing a relational target definitions



39

Import from Database

Reverse engineer existing object definitions from a database into Informatica repository

TableViewSynonym

ODBC

Repository

DatabaseWarehouse Designer

DEF

ODBC

40

Import target from a relational database



41

Import target from a flat file

42

Import target from XML



43

Manual Target Definition

1. Create empty definition2. Add desired columns

3. Finished target definition

44

Target Table Definitions



45

Create Physical Tables

InformaticaRepository

logical target table definitions

DEF

DEF

DEF

TargetDatabases

physicaltables

Execute SQLvia

Designer

46

Create Physical Tables

Create tables that do not already exist in target databaseConnect - connect to the target databaseGenerate SQL file - create DDL in a script fileEdit SQL file - modify DDL script as neededExecute SQL file - create the physical tables in target database

Connect



47

Mappings and Mapplets

48

Sample Mapping



49

Steps to develop a mapping

Verify that all source, target, and reusable objects are created

Create the mapping (Open the Mapping Designer, chose MappingCreate or drag a repository object into the workspace, enter a name for the new mapping e.g. m_PromotionItems)

Add sources and targets Add transformations and transformation logic Connect the mapping Validate the mapping Save the mapping

50

Mapping Object Edit View



51

Connecting mapping objects

52

Working with source in a mapping



53

Working with transformation in a mapping

54

Working with Mapplets in a mapping



55

Working with Targets in a mapping

56

Working with Targets in a mapping



57

Relational Target

58

Mapping Validation



59

Mapplets Overview

60

Sample Mapplet



61

Sample Mapplet in a mapping

62

Expanded Mapplet in a mapping



63

Transformation

64

Transformation Type -1



65

Transformation Type -2

66

Creating a transformation



67

Configuring a transformation

68

Working with Expression



69

Using Local Variables

70

Using Local Variables sample



71

Using Default Value

72

Configuring Tracing levels Session log



73

Linking Port

74

Auto-Link by position



75

Auto-Link by Name

76

Auto-Link by Prefix/Suffix

Layout Autolink



77

Link one to many / many to one

78

Create a reusable transformation



79

Source Qualifier Transformation

Active TransformationConnected

Ports All input/output

Usage Modify SQL statement* Sorted ports* Select DISTINCT* Convert datatypes*relational sources only

Represents the source record set queried by the server. Mandatory in mappings using relational or flat file sources.

80

Source Qualifier Transformation

For relational sources SQL Query replaces the default query

User Defined Join replaces the WHERE clause of the default querySource Filter is added to the WHERE clause of the default query

Number of Sorted Ports adds an ORDER BY to the default querySelect Distinct adds a DISTINCT to the default query



81

Expression Transformation

Passive TransformationConnected

Ports Mixed Variables allowed

Create expression in an output port

Usage Perform majority of

data manipulation

Perform calculations using non-aggregate Informaticafunctions.

82

Aggregator Transformation


Ports Mixed Variables allowed Group By allowed

Create expression in an output port

Usage Standard

aggregations Group By

Performs aggregate functions.



83

Sorted Ports

Source QualifierNumber of Sorted Ports property - will create an ORDER BY

statement in the SQLIncreases performance - for rank, joiner, and aggregator

AggregatorThe Sorted Ports property ONLY works if data has been sortedGroup By portsMUST be pre-sorted in SQL MUST be in same order as in SQL

Server will store data for a group into memory, and when it reaches the first record of the next group, it can release data from the cache

84

Working with Transformations

Aggregation Mapping



85

Rank Transformation


Ports Mixed One predefined output

port RANKINDEX Variables allowed Group By allowed

Specify the column to be ranked

Usage Select top/bottom

number of records

Filters the top or bottom range of records.

86

Update Strategy Transformation

Used to specify how each individual row will be used to update target tables (insert, update, delete, reject).



Specify the Update Strategy Expression

Usage Slowly changing

dimension tables IIF or DECODE logic

determine how to handle the record



87

Lookup Transformation

Looks up values from database objects and provides to other components in a mapping.

Passive TransformationConnected/Unconnected

Ports Mixed L denotes Lookup port R denotes port used as

a return value in unconnected lookup

Specify the Lookup condition

Usage Get related values Update tables

88


TARGETUPDATE

STRATEGYBased upon match of Job_IDs, update the target T_JOBS table

LOOKUPLook up source Job_IDs in targetT_JOBS

SOURCESOURCE

QUALIFIER

Update Strategy Mapping



89

Filter Transformation



Specify a Filter condition

Usage Filter rows from flat

file sources Single pass source(s)

into multiple targets

Limits rows sent to targets or other transformations.

90

Stored Procedure Transformation

Calls a database stored procedure.

Passive TransformationConnected/Unconnected

Ports Mixed R denotes port used as

a return value (from a stored function)

Usage Perform transformation

logic outside Informatica



91

Stored Function vs.Stored Procedure Transformations

ORACLESTORED FUNCTION

ORACLESTORED PROCEDURE

Can only return one value(connected or unconnectedmode)

Can return multiple values (inconnected mode)

Must use the Return (R) portoption for the output port thatprovides the return value,regardless of whether thetransformation is unconnectedor connected.

In a connected mode, use links todesignate return values. Inunconnected mode, usePROC_RESULT in the parameter listof the stored procedure call todesignate the return value.

92

Sequence Generator Transformation

Generates unique keys for records.

Passive TransformationConnected

Ports Two predefined output

ports, NEXTVAL and CURRVAL

No input ports allowed

Usage Generate sequence

numbers Shareable across

mappings



93


Filterfilters outdiscontinued items

Stored Procedurecalls stored procedure to calculate the number of times an item has been ordered

Sequence Generatorgenerates uniqueID numbers

Star Schema Mapping

94

Workflow Manager



95

Workflow Monitor

1 etl informatica

Documents