Informatica PowerCenter 8.

6
Basics Training Course

Introduction
At the end of this course you will Understand how to use all major PowerCenter 8.6 components Be able to perform basic administration tasks Be able to build basic ETL Mappings and Mapplets Understand the different Transformations and their basic attributes in PowerCenter 8.6 Be able to create, run and monitor Workflows Understand available options for loading target data Be able to troubleshoot common development problems

ETL Basics

This section will include Concepts of ETL PowerCenter 8.6 Architecture

Connectivity between PowerCenter 8.6 components

Extract, Transform, and Load

Operational Systems
RDBMS Mainframe Other

Decision Support
Data Warehouse

Cleanse Data Transaction level data Apply Business Rules Optimized for Transaction Aggregate Data Response Time Consolidate Data De-normalize Current Normalized or DeTransform Normalized data

Aggregated data Historical

Extract

ETL

Load

PowerCenter 8.6 Architecture

PowerCenter 8.6 Components

PowerCenter 8.6 Components

PowerCenter Repository Repository Service Application Services Integration Service Core Services PowerCenter Client
Administration Console Repository Manager Designer Workflow Manager Workflow Monitor

External Components Sources Targets

Introduction To PowerCenter Repository and Administration

This section includes The purpose of the Repository and Repository Service The Administration Console

The Repository Manager

Administration Console maintenance operations Security and privileges Object sharing, searching and locking Metadata Extensions

Version Control

10

PowerCenter Repository
It is a relational database managed by the Repository Service

Stores metadata about the objects (mappings, transformations etc.) in database tables called as Repository Content
The Repository database can be in Oracle, IBM DB2 UDB, MS SQL Server or Sybase ASE To create a repository service one must have full privileges in the Administrator Console and also in the domain Integration Service uses repository objects for performing the ETL
11

Repository Service
A Repository Service process is a multi-threaded process that fetches, inserts and updates metadata in the repository Manages connections to the Repository from client applications and Integration Service Maintains object consistency by controlling object locking Each Repository Service manages a single repository database. However multiple repositories can be connected and managed using repository domain

It can run on multiple machines or nodes in the domain. Each instance is called a Repository Service process

12

Repository Connections
Each Repository has a repository service assigned for the management of the physical Repository tables

PowerCenter
Client

1 Node A
Service Manager Application Service Repository Service

Node B (Gateway)
Service Manager

3
TCP/IP

Application Service

2 4
Repository Administration Manager Console Native Connectivity or ODBC Driver
13

Repository Database

PowerCenter Administration Console

A web-based interface used to administer the PowerCenter domain. Following tasks can be performed: Manage the domain

Shutdown and restart domain and Nodes

Manage objects within a domain Create and Manage Folders, Grid, Integration Service, Node, Repository Service, Web Service and Licenses Enable/ Disable various services like the Integration Services, Repository Services etc. Upgrade Repositories and Integration Services View log events for the domain and the services View locks Add and Manage Users and their profile Monitor User Activity Manage Application Services Default URL <> http://<hostname>:6001/adminconsole
14

PowerCenter Administration Console

For Creating Folder, Grid, Integration Service, Repository Service

Navigation Window Other Important Task Include Managing Objects like services, nodes, grids, licenses etc. and security within a domain Managing Logs Create &Manage Users Upgrading Repository & Server Shutdown Domain

Main Window

**Upgrading Repository means updating configuration file and also the repository tables
15

Repository Management
Perform all Repository maintenance tasks through Administration Console

Create/Modify the Repository Configuration

Select Repository Configuration and perform maintenance tasks:

Create Contents Delete Contents Backup Contents Copy Contents from Upgrade Contents Disable Repository Service View Locks

Manage Connections
Notify Users Propagate Register/UnRegister Local Repositories Edit Database properties
16

Repository Manager Interface

Navigator Window

Main Window

Shortcut Dependencies

Dependency Window Output Window

17

Repository Manager
Use Repository manager to navigate through multiple folders and repositories. Perform following tasks: Add/Edit Repository Connections Search for Repository Objects or Keywords Implement Repository Security(By changing the password only) Perform folder functions ( Create , Edit , Delete ,Compare) Compare Repository Objects Manage Workflow/Session Log Entries View Dependencies Exchange Metadata with other BI tools

18

Add Repository

STEP 1: Add Repository

STEP 2: Mention the Repository Name and Username

STEP 3: Add Domain & Its Settings

19

Users & Groups

Steps: Create groups Create users Assign users to groups Assign privileges to groups Assign additional privileges to users (optional)

20

Users & Groups

21

Managing Privileges
Check box assignment of privileges

22

Folder Permissions
Assign one user as the folder owner for first tier permissions Select one of the owners groups for second tier permissions All users and groups in the Repository will be assigned the third tier permissions
23

Object Locking
Object Locks preserve Repository integrity Use the Edit menu for Viewing Locks and Unlocking Objects

24

Object Searching
Menu -> Analyze > Search
Keyword search
Used when a keyword is associated with a target definition

Search all
Filter and search objects

25

Object Sharing
Reuse existing objects Enforces consistency Decreases development time Share objects by using copies and shortcuts
COPY Copy object to another folder Changes to original object not captured SHORTCUT Link to an object in another folder or repository Dynamically reflects changes to original object

Duplicates space
Copy from shared or unshared folder

Preserves space
Created from a shared folder

Required security settings for sharing objects:

Repository Privilege: Originating Folder Permission: Destination Folder Permissions: Use Designer Read Read/Write
26

Adding Metadata Extensions

Allows developers and partners to extend the metadata stored in the Repository Accommodates the following metadata types: Vendor-defined - Third-party application vendorcreated metadata lists For example, Applications such as Ariba or PowerConnect for Siebel can add information such as contacts, version, etc. User-defined - PowerCenter users can define and create their own metadata Must have Administrator Repository or Super User Repository privileges
27

Sample Metadata Extensions

Sample User Defined Metadata, e.g. - contact information, business user

Reusable Metadata Extensions can also be created in the Repository Manager

28

Introduction to PowerCenter Design Process

We will walk through Design Process PowerCenter Designer Interface

Mapping Components

30

Design Process
1. Create Source definition(s) 2. Create Target definition(s)

3. Create a Mapping
4. Create a Session Task 5. Create a Workflow from Task components 6. Run the Workflow 7. Monitor the Workflow and verify the results

31

PowerCenter Designer- Interface

Overview Window

Client Tools

Navigator
Workspace

Output

Status Bar
32

Mapping Components
Each PowerCenter mapping consists of one or more of the following mandatory components Sources Transformations Targets The components are arranged sequentially to form a valid data flow from SourcesTransformationsTargets

33

Introduction To PowerCenter Designer Interface

PowerCenter Designer- Interface

Mapping List Folder List

Transformation Toolbar

Iconized Mapping

35

PowerCenter Designer- Source Analyzer

Foreign Key

It Shows the Dependencies of the tables also

36

PowerCenter Designer- Target Designer

37

PowerCenter Designer- Transformation Developer

Transformation Developer is used only for creating reusable transformations

38

PowerCenter Designer- Mapplet Designer

39

PowerCenter Designer- Mapping Designer

40

EXTRACT Source Object Definitions

This section introduces to Different Source Types Creation of ODBC Connections Creation of Source Definitions Source Definition properties Data Preview option

42

Source Analyzer

Navigation Window

Analyzer Window

43

Methods of Analyzing Sources

Import from Database Import from File Import from Cobol File Import from XML file Import from third party software like SAP, Siebel, PeopleSoft etc Create manually

Repository

Source Analyzer

Relational

XML file

Flat file

COBOL file

44

Analyzing Relational Sources

Source Analyzer Relational Source

ODBC

Table View Synonym

DEF

TCP/IP

Repository Service

native
Repository
45

DEF

Importing Relational Sources

Step 1: Select Import from Database

Note: Use Data Direct ODBC Drivers than native drivers for creating ODBC connections

Step 2: Select/Create the ODBC Connection

46

Importing Relational Sources

Step 3: Select the Required Tables Step 4: Table Definition is Imported

47

Analyzing Relational Sources

Editing Source Definition Properties
Key Type

48

Analyzing Flat File Sources

Source Analyzer
Mapped Drive NFS Mount Local Directory

Flat File
DEF

Fixed Width or Delimited

TCP/IP
Repository Service

native
Repository
49

DEF

Flat File Wizard

Three-step wizard Columns can be renamed within wizard Text, Numeric and Datetime datatypes are supported Wizard guesses datatype
50

XML Source Analysis

Source Analyzer
Mapped Drive NFS Mounting Local Directory

.DTD File
DEF

TCP/IP

DATA

Repository Service

native
DEF Repository

In addition to the DTD file, an XML Schema or XML file can be used as a Source Definition

51

Analyzing VSAM Sources

Source Analyzer
Mapped Drive NFS Mounting Local Directory

.CBL File
DEF

TCP/IP

DATA
Repository Service

Supported Numeric Storage Options: COMP, COMP-3, COMP-6 native

Repository
52

DEF

VSAM Source Properties

53

LOAD Target Definitions

Target Object Definitions

By the end of this section you will: Be familiar with Target Definition types Know the supported methods of creating Target Definitions Understand individual Target Definition properties

55

Target Designer

56

Creating Target Definitions

Methods of creating Target Definitions Import from Database

Import from an XML file

Import from third party software like SAP, Siebel etc.

Manual Creation
Automatic Creation

57

Automatic Target Creation

Drag-anddrop a Source Definition into the Target Designer Workspace

58

Import Definition from Database

Can Reverse engineer existing object definitions from a database system catalog or data dictionary

Target Designer

Database
ODBC

TCP/IP

DEF
Repository Service

Table View Synonym

native
Repository DEF
59

Manual Target Creation

1. Create empty definition 2. Add desired columns

3. Finished target definition

ALT-F can also be used to create a new column

60

Target Definition Properties

61

Target Definition Properties

62

Creating Physical Tables

DEF

DEF

DEF

Execute SQL via Designer PHYSICAL Target database tables

LOGICAL Repository target table definitions

63

Creating Physical Tables

Create tables that do not already exist in target database Connect - connect to the target database Generate SQL file - create DDL in a script file Edit SQL file - modify DDL script as needed Execute SQL file - create physical tables in target database

Use Preview Data to verify the results (right mouse click on object)

64

TRANSFORM Transformation Concepts

Transformation Concepts
By the end of this section you will be familiar with: Transformation types

Data Flow Rules

Transformation Views PowerCenter Functions Expression Editor and Expression validation Port Types PowerCenter data types and Datatype Conversion Connection and Mapping Valdation PowerCenter Basic Transformations Source Qualifier, Filter, Joiner, Expression

66

Types of Transformations
Active/Passive Active : Changes the numbers of rows as data passes through it Passive: Passes all the rows through it Connected/Unconnected

Connected : Connected to other transformation through connectors

Unconnected : Not connected to any transformation. Called within a transformation

67

Transformation Types
PowerCenter 8.6 provides 24 objects for data transformation Aggregator: performs aggregate calculations Application Source Qualifier: reads Application object sources as ERP Custom: Calls a procedure in shared library or DLL Expression: performs row-level calculations External Procedure (TX): calls compiled code for each row Filter: drops rows conditionally Mapplet Input: Defines mapplet input rows. Available in Mapplet designer Java: Executes java code Joiner: joins heterogeneous sources Lookup: looks up values and passes them to other objects Normalizer: reads data from VSAM and normalized sources Mapplet Output: Defines mapplet output rows. Available in Mapplet designer
68

Transformation Types
Rank: limits records to the top or bottom of a range Router: splits rows conditionally Sequence Generator: generates unique ID values Sorter: sorts data Source Qualifier: reads data from Flat File and Relational Sources Stored Procedure: calls a database stored procedure Transaction Control: Defines Commit and Rollback transactions Union: Merges data from different databases Update Strategy: tags rows for insert, update, delete, reject XML Generator: Reads data from one or more Input ports and outputs XML through single output port XML Parser: Reads XML from one or more Input ports and outputs data through single output port XML Source Qualifier: reads XML data
69

Data Flow Rules

Each Source Qualifier starts a single data stream (a dataflow) Transformations can send rows to more than one transformation (split one data flow into multiple pipelines) Two or more data flows can meet together -- if (and only if) they originate from a common active transformation Cannot add an active transformation into the mix
ALLOWED DISALLOWED

Passive T T T

Active T

Example holds true with Normalizer in lieu of Source Qualifier. Exceptions are: Mapplet Input and Joiner transformations
70

Transformation Views
A transformation has three views:
Iconized - shows the transformation in relation to the rest of the mapping Normal - shows the flow of data through the transformation Edit - shows transformation ports and properties; allows editing
71

Edit Mode
Allows users with folder write permissions to change or create transformation ports and properties
Define port level handling Define transformation level properties

Enter comments
Make reusable

Switch between transformations

72

Expression Editor
An expression formula is a calculation or conditional statement Used in Expression, Aggregator, Rank, Filter, Router, Update Strategy Performs calculation based on ports, functions, operators, variables, literals, constants and return values from other transformations

73

PowerCenter Data Types

NATIVE DATATYPES TRANSFORMATION DATATYPES

Specific to the source and target database types Display in source and target tables within Mapping Designer

PowerCenter internal datatypes based on ANSI SQL-92 Display in transformations within Mapping Designer

Native

Transformation

Native

Transformation datatypes allow mix and match of source and target database types When connecting ports, native and transformation datatypes must be compatible (or must be explicitly converted)
74

Datatype Conversions
Integer, Small Int Integer, Small Integer Decimal Double , Real String , Text Date/Time Binary X X X X Decimal X X X X Double, Real X X X X String , Text X X X X X X X X Date/ Time Binary

All numeric data can be converted to all other numeric datatypes, e.g. - integer, double, and decimal All numeric data can be converted to string, and vice versa Date can be converted only to date and string, and vice versa Raw (binary) can only be linked to raw Other conversions not listed above are not supported These conversions are implicit; no function is necessary
75

PowerCenter Functions - Types

ASCII CHR CHRCODE CONCAT INITCAP INSTR LENGTH LOWER LPAD LTRIM RPAD RTRIM SUBSTR UPPER REPLACESTR REPLACECHR

Character Functions

Used to manipulate character data

CHRCODE returns the numeric value (ASCII or Unicode) of the first character of the string passed to this function

For backwards compatibility only - use || instead

76

PowerCenter Functions
TO_CHAR (numeric) TO_DATE TO_DECIMAL TO_FLOAT TO_INTEGER TO_NUMBER
Conversion Functions Used to convert datatypes

ADD_TO_DATE DATE_COMPARE DATE_DIFF GET_DATE_PART LAST_DAY ROUND (date) SET_DATE_PART TO_CHAR (date) TRUNC (date)

Date Functions Used to round, truncate, or compare dates; extract one part of a date; or perform arithmetic on a date To pass a string to a date function, first use the TO_DATE function to convert it to an date/time datatype

77

PowerCenter Functions
Numerical Functions

ABS CEIL CUME EXP FLOOR LN LOG MOD MOVINGAVG MOVINGSUM POWER ROUND SIGN SQRT TRUNC

Used to perform mathematical operations on numeric data

Scientific Functions Used to calculate geometric values of numeric data

COS COSH SIN SINH TAN TANH

78

PowerCenter Functions
ERROR ABORT DECODE IIF
Special Functions Used to handle specific conditions within a session; search for certain values; test conditional statements

IIF(Condition,True,False)

ISNULL IS_DATE IS_NUMBER IS_SPACES

Test Functions Used to test if a lookup result is null Used to validate data

SOUNDEX METAPHONE

Encoding Functions Used to encode string values

79

Expression Validation
The Validate or OK button in the Expression Editor will: Parse the current expression Remote port searching (resolves references to ports in other transformations) Parse transformation attributes e.g. - filter condition, lookup condition, SQL Query Parse default values Check spelling, correct number of arguments in functions, other syntactical errors

80

Types of Ports
Four basic types of ports are there Input Output Input/Output

Variable
Apart from these Look-up & Return ports are also there that are specific to the Lookup transformation

81

Variable and Output Ports

Use to simplify complex expressions e.g. - create and store a depreciation formula to be referenced more than once Use in another variable port or an output port expression Local to the transformation (a variable port cannot also be an input or output port)

Available in the Expression, Aggregator and Rank transformations

82

Connection Validation
Examples of invalid connections in a Mapping: Connecting ports with incompatible datatypes Connecting output ports to a Source Connecting a Source to anything but a Source Qualifier or Normalizer transformation Connecting an output port to an output port or an input port to another input port Connecting more than one active transformation to another transformation (invalid dataflow)

83

Mapping Validation
Mappings must: Be valid for a Session to run

Be end-to-end complete and contain valid expressions

Pass all data flow rules Mappings are always validated when saved; can be validated without being saved Output Window will always display reason for invalidity

84

Source Qualifier Transformation

Reads data from the sources Active & Connected Transformation

Applicable only to relational and flat file sources

Maps database/file specific datatypes to PowerCenter Native datatypes.
Eg. Number(24) becomes decimal(24)

Determines how the source database binds data when the Integration Service reads it If mismatch between the source definition and source qualifier datatypes then mapping is invalid All ports by default are Input/Output ports
85

Source Qualifier Transformation

Used as Joiner for homogenous tables using a where clause Filter using a where clause

Sorter Select distinct values

86

Pre-SQL and Post-SQL Rules

Can use any command that is valid for the database type; no nested comments

Can use Mapping Parameters and Variables in SQL executed against the source Use a semi-colon (;) to separate multiple statements Informatica Server ignores semi-colons within single quotes, double quotes or within /* ...*/ To use a semi-colon outside of quotes or comments, escape it with a back slash (\) Workflow Manager does not validate the SQL

87

Application Source Qualifier

Apart from relational sources and flat files we can also use sources from SAP, TIBCO ,Peoplesoft ,Siebel and many more

88

Filter Transformation
Drops rows conditionally
Active Transformation Connected Ports
All input / output

Specify a Filter condition Usage

Filter rows from flat file sources
Single pass source(s) into multiple targets
89

Filter Transformation Tips

Boolean condition is always faster as compared to complex conditions

Use filter transformation early in the mapping

Source qualifier filters rows from relational sources but filter transformation is source independent Always validate a condition

90

Joiner Transformation
By the end of this sub-section you will be familiar with: When to use a Joiner Transformation

Homogeneous Joins
Heterogeneous Joins Joiner properties Joiner Conditions Nested joins

91

Homogeneous Joins
Joins that can be performed with a SQL SELECT statement: Source Qualifier contains a SQL join

Tables on same database server (or are synonyms)

Database server does the join work Multiple homogenous tables can be joined

92

Heterogeneous Joins
Joins that cannot be done with a SQL statement: An Oracle table and a Sybase table Two Informix tables on different database servers Two flat files A flat file and a database table

93

Joiner Transformation
Performs heterogeneous joins on records from two tables on same or different databases or flat file sources
Active Transformation Connected Ports
All input or input / output M denotes port comes from master source

Specify the Join condition Usage

Join two flat files

Join two tables from different databases Join a flat file with a relational table
94

Joiner Conditions

Multiple join conditions are supported

95

Joiner Properties
Join types: Normal (inner) Master outer Detail outer Full outer

Set Joiner Cache

Joiner can accept sorted data

96

Sorted Input for Joiner

Using sorted input improves session performance minimizing the disk input and output The pre-requisites for using the sorted input are
Database sort order must be same as the session sort order Sort order must be configured by the use of sorted sources (flat files/relational tables) or sorter transformation

The flow of sorted data must me maintained by avoiding the use of transformations like Rank, Custom, Normalizer etc. which alter the sort order
Enable the sorted input option is properties tab The order of the ports used in joining condition must match the order of the ports at the sort origin When joining the Joiner output with another pipeline make sure that the data from the first joiner is sorted
97

Mid-Mapping Join - Tips

The Joiner does not accept input in the following situations:
Both input pipelines begin with the same Source Qualifier Both input pipelines begin with the same Normalizer Both input pipelines begin with the same Joiner Either input pipeline contains an Update Strategy

98

Expression Transformation
Passive Transformation

Connected
Ports Mixed Variables allowed Create expression in an output or variable port Usage Perform majority of data manipulation
Click here to invoke the Expression Editor
99

Introduction To Workflows

This section will include Integration Service Concepts The Workflow Manager GUI interface Setting up Server Connections
Relational FTP External Loader Application

Task Developer Creating and configuring Tasks

Creating and Configuring Wokflows

Workflow Schedules

101

Integration Service
Application service that runs data integration sessions and workflows

To access it one must have permissions on the service in the domain

Is managed through Administrator Console A repository must be assigned to it A code page must be assigned to the Integration Service process which should be compatible with the repository service code page

102

Integration Service

103

Workflow Manager Interface

Task Tool Bar Workflow Designer Tools

Navigator Window Workspace

Output Window Status Bar

104

Workflow Manager Tools

Workflow Designer Maps the execution order and dependencies of Sessions, Tasks and Worklets, for the Informatica Server Task Developer Create Session, Shell Command and Email tasks Tasks created in the Task Developer are reusable Worklet Designer Creates objects that represent a set of tasks Worklet objects are reusable
105

Source & Target Connections

Configure Source & Target data access connections Used in Session Tasks Configure: Relational MQ Series FTP Application Loader

106

Relational Connections (Native )

Create a relational (database) connection Instructions to the Integration Service to locate relational tables Used in Session Tasks

107

Relational Connection Properties

Define native relational (database) connection

User Name/Password Database connectivity information Optional Environment SQL (executed with each use of database connection) Optional Environment SQL (executed before initiation of each transaction)
108

FTP Connection
Create an FTP connection Instructions to the Integration Service to ftp flat files Used in Session Tasks

109

External Loader Connection

Create an External Loader connection Instructions to the Integration Service to invoke database bulk loaders

Used in Session Tasks

110

Task Developer
Create basic Reusable building blocks to use in any Workflow Reusable Tasks Session - Set of instructions to execute Mapping logic Command - Specify OS shell / script command(s) to run during the Workflow Email - Send email at any point in the Workflow

Session Command Email

111

Session Tasks
s_CaseStudy1

After this section, you will be familiar with: How to create and configure Session Tasks

Session Task properties

Transformation property overrides Reusable vs. non-reusable Sessions Session partitions

112

Session Task
Integration Service instructs to runs the logic of ONE specific Mapping
s_CaseStudy1

e.g. - source and target data location specifications, memory allocation, optional Mapping overrides, scheduling, processing and load instructions Becomes a component of a Workflow (or Worklet) If configured in the Task Developer, the Session Task is reusable (optional)

113

Session Task
Created to execute the logic of a mapping (one mapping only)
s_CaseStudy1

Session Tasks can be created in the Task Developer (reusable) or Workflow Developer (Workflow-specific) Steps to create a Session Task Select the Session button from the Task Toolbar or Select menu Tasks -> Create

Session Task Bar Icon

114

Session Task - General

s_CaseStudy1

115

Session Task - Properties

s_CaseStudy1

116

Session Task Config Object

s_CaseStudy1

117

Session Task - Sources

s_CaseStudy1

118

Session Task - Targets

s_CaseStudy1

119

Session Task - Transformations

s_CaseStudy1

Allows overrides of some transformation properties Does not change the properties in the Mapping

120

Session Task - Partitions

s_CaseStudy1

121

Command Task
Command

Specify one (or more) Unix shell or DOS (NT, Win2000) commands to run at a specific point in the Workflow Becomes a component of a Workflow (or Worklet) If configured in the Task Developer, the Command Task is reusable (optional)

Commands can also be referenced in a Session through the Session Components tab as Pre- or Post-Session commands
122

Command Task
Command

123

Email Task
Email

Sends email during a workflow Becomes a component of a Workflow (or Worklet) If configured in the Task Developer, the Email Task is reusable (optional) Email can be also sent by using post-session email option and suspension email options of the session. (Non-reusable)

124

Email Task
Email

125

Workflow Structure
A Workflow is set of instructions for the Integration Service to perform data transformation and load Combines the logic of Session Tasks, other types of Tasks and Worklets The simplest Workflow is composed of a Start Task, a Link and one other Task
Link

Start Task

Session Task

126

Additional Workflow Components

Two additional components are Worklets and Links Worklets are objects that contain a series of Tasks

Links are required to connect objects in a Workflow

127

Building Workflow Components

Add Sessions and other Tasks to the Workflow Connect all Workflow components with Links

Save the Workflow

Assign the workflow to the integration Service Start the Workflow

Sessions in a Workflow can be independently executed

128

Developing Workflows
Create a new Workflow in the Workflow Designer
Customize Workflow name

Select an Integration Service

Configure Workflow

129

Workflow Properties
Customize Workflow Properties
Workflow log displays

Select a Workflow Schedule (optional) May be reusable or non-reusable

130

Workflows Properties

Create a User-defined Event which can later be used with the Raise Event Task

Define Workflow Variables that can be used in later Task objects (example: Decision Task)

131

Assigning Workflow to Integration Service

Choose the integration Service and select the folder

Select the workflows Note: All the folders should be closed for assigning workflows to Integration Service
132

Workflow Scheduler Objects

Setup reusable schedules to associate with multiple Workflows Used in Workflows and Session Tasks

133

Workflows Administration

This section details The Workflow Monitor GUI interface Monitoring views

Server monitoring modes

Filtering displayed items Actions initiated from the Workflow Monitor

135

Workflow Monitor Interface

Available Integration Services

136

Monitoring Workflows
Perform operations in the Workflow Monitor Restart -- restart a Task, Workflow or Worklet

Stop -- stop a Task, Workflow, or Worklet

Abort -- abort a Task, Workflow, or Worklet Recover -- recovers a suspended Workflow after a failed Task is corrected from the point of failure View Session and Workflow logs Abort has a 60 second timeout If the Integration Service has not completed processing and committing data during the timeout period, the threads and processes associated with the Session are killed
Stopping a Session Task means the Server stops reading data
137

Monitor Workflows
The Workflow Monitor is the tool for monitoring Workflows and Tasks Review details about a Workflow or Task in two views Gantt Chart view Task view

138

Monitoring Workflows
Task View
Completion Time Workflow Start Time Status

Status Bar

139

Monitor Window Filtering

Task View provides filtering
Get Session Logs (right click on Task) Monitoring filters can be set using drop down menus Minimizes items displayed in Task View

Right-click on Session to retrieve the Session Log (from the Integration Service to the local PC Client)
140

Debugger Features
Debugger is a Wizard driven tool View source / target data

View transformation data

Set break points and evaluate expressions Initialize variables

Manually change variable values

Debugger is Session Driven Data can be loaded or discarded Debug environment can be saved for later use

Debugger Port Setting

Configure the Debugger Port to 6010 as thats the default port configured by the Integration Service for the Debugger

142

Debugger Interface
Debugger windows & indicators
Debugger Mode indicator

Solid yellow arrow Current Transformation indicator

Flashing yellow SQL indicator

Debugger Log tab Target Data window

Transformation Instance Data window

Session Log tab

PowerCenter Designer Other Transformations

This section introduces to Router Sorter Aggregator

Lookup
Update Strategy Sequence Generator Rank Normalizer Stored Procedure External Procedure Custom Transformation Transaction Control

145

Router Transformation
Multiple filters in single transformation
Adds a group

Active Transformation Connected Ports

All input/output

Specify filter conditions for each Group Usage

Link source data in one pass to multiple filter conditions

146

Router Transformation in a Mapping

TARGET_ORD ERS_COST (Ora cle)

SQ_TARGET_O RDERS_COST

RTR_OrderCostt

TARGET_ROU TED_ORDER2 ( Oracle)

TARGET_ROU TED_ORDER1 ( Oracle)

147

Comparison Filter and Router

Filter
Tests rows for only one condition

Router
Tests rows for one or more condition

Drops the rows which dont meet the filter condition

Routes the rows not meeting the filter condition to default group

In case of multiple filter transformation the Integration service processes rows for each transformation but in case of router the incoming rows are processed only once.

148

Sorter Transformation

Sorts the data, selects distinct

Active transformation Is always connected Can sort data from relational tables or flat files both in ascending or descending order Only Input/Output/Key ports are there Sort takes place on the Integration Service machine Multiple sort keys are supported. The Integration Service sorts each port sequentially The Sorter transformation is often more efficient than a sort performed on a database with an ORDER BY clause
149

Sorter Transformation
Discard duplicate rows by selecting Distinct option

Acts as an active transformation with distinct option else as passive

150

Aggregator Transformation
Performs aggregate calculations

Active Transformation Connected Ports

Mixed
Variables allowed Group By allowed

Create expressions in output or variable ports Usage

Standard aggregations

151

PowerCenter Aggregate Functions

Aggregate Functions AVG COUNT FIRST LAST MAX MEDIAN MIN PERCENTILE STDDEV SUM VARIANCE Return summary values for non-null data in selected ports Used only in Aggregator transformations Used in output ports only Calculate a single value (and row) for all records in a group Only one aggregate function can be nested within an aggregate function Conditional statements can be used with these functions

152

Aggregate Expressions

Aggregate functions are supported only in the Aggregator Transformation Conditional Aggregate expressions are supported

Conditional SUM format: SUM(value, condition)

153

Aggregator Properties
Sorted Input Property
Instructs the Aggregator to expect the data to be sorted

Set Aggregator cache sizes (on Integration Service machine)

154

Why Sorted Input?

Aggregator works efficiently with sorted input data
Sorted data can be aggregated more efficiently, decreasing total processing time

The Integration Service will cache data from each group and release the cached data -- upon reaching the first record of the next group Data must be sorted according to the order of the Aggregator Group By ports Performance gain will depend upon varying factors

155

Incremental Aggregation
Trigger in Session Properties -> Performance Tab

ORDER_ITEMS (Oracle)

ORDERS (Oracl e)

SQ_ORDERS_I TEMS

Cache is saved into $PMCacheDir

PMAGG*.dat* PMAGG*.idx*

EXP_GET_INC REMENTAL_DA TA

AGG_INCREME NTAL_DATA

T_INCREMENT AL_AGG (Oracle )

Upon next run, files are overwritten with new cache information Functions like median ,running totals not supported as system memory is used for these functions

Example: When triggered, Integration Service will save new MTD totals. Upon next run (new totals), Service will subtract old totals; difference will be passed forward
Best Practice is to copy these files in case a rerun of data is ever required. Reinitialize when no longer needed, e.g. at the beginning new month processing
156

Lookup Transformation
By the end of this sub-section you will be familiar with: Lookup principles

Lookup properties
Lookup conditions Lookup techniques Caching considerations

157

How a Lookup Transformation Works

For each Mapping row, one or more port values are looked up in a database table

If a match is found, one or more table values are returned to the Mapping. If no match is found, NULL is returned Look Up Transformation
Look-up Values
SQ_TARGET_ITEMS_OR... Source Qualifier Name Datatype Len... 38 72 72 10 38 38 38 38 38 Name ITEM_ID decimal ITEM_NAME string ITEM_DESC string WHOLESALE_CO... decimal DISCONTINUED_... decimal MANUFACTURER...decimal DISTRIBUTOR_ID decimal ORDER_ID decimal TOTAL_ORDER_... decimal LKP_OrderID Lookup Procedure Datatype IN_ORDER_ID decimal DATE_ENTERED date/ time DATE_PROMISED date/ time DATE_SHIPPED date/ time EMPLOYEE_ID decimal CUSTOMER_ID decimal SALES_TAX_RATE decimal STORE_ID decimal 38 19 19 19 38 38 5 38

Return Values
No Yes Yes Yes Yes Yes Yes Yes No No No No No No No No

TARGET_ORDERS_COS... Target Definition Datatype ORDER_ID number(p,s) DATE_ENTERED date DATE_PROMISED date DATE_SHIPPED date EMPLOYEE_ID number(p,s) CUSTOMER_ID number(p,s) SALES_TAX_RATE number(p,s) STORE_ID number(p,s) TOTAL_ORDER_... number(p,s)

Len... Loo... Ret... Associated K...Name ...

3 1 1 1 3 3 5 3 3

158

Lookup Transformation
Looks up values in a database table or flat files and provides data to downstream transformation in a Mapping
Passive Transformation Connected / Unconnected Ports
Mixed L denotes Lookup port R denotes port used as a return value (unconnected Lookup only)

Specify the Lookup Condition Usage

Get related values Verify if records exists or if data has changed
159

Lookup Properties

Override Lookup SQL option Toggle caching

Native Database Connection Object name

160

Additional Lookup Properties

Set cache directory

Make cache persistent

Set Lookup cache sizes

161

Lookup Conditions
Multiple conditions are supported

162

Connected Lookup

SQ_TARGET_ITEMS_OR... Source Qualifier Name Datatype Len... 38 72 72 10 38 38 38 38 38 Name ITEM_ID decimal ITEM_NAME string ITEM_DESC string WHOLESALE_CO... decimal DISCONTINUED_... decimal MANUFACTURER...decimal DISTRIBUTOR_ID decimal ORDER_ID decimal TOTAL_ORDER_... decimal

LKP_OrderID Lookup Procedure Datatype IN_ORDER_ID decimal DATE_ENTERED date/ time DATE_PROMISED date/ time DATE_SHIPPED date/ time EMPLOYEE_ID decimal CUSTOMER_ID decimal SALES_TAX_RATE decimal STORE_ID decimal 38 19 19 19 38 38 5 38 No Yes Yes Yes Yes Yes Yes Yes No No No No No No No No

TARGET_ORDERS_COS... Target Definition Len... Loo... Ret... Associated K...Name ... Datatype ORDER_ID number(p,s) DATE_ENTERED date DATE_PROMISED date DATE_SHIPPED date EMPLOYEE_ID number(p,s) CUSTOMER_ID number(p,s) SALES_TAX_RATE number(p,s) STORE_ID number(p,s) TOTAL_ORDER_... number(p,s)

3 1 1 1 3 3 5 3 3

Connected Lookup Part of the data flow pipeline

163

Unconnected Lookup
Will be physically unconnected from other transformations
There can be NO data flow arrows leading to or from an unconnected Lookup

Lookup function can be set within any transformation that supports expressions
Lookup data is called from the point in the Mapping that needs it Function in the Aggregator calls the unconnected Lookup

164

Conditional Lookup Technique

Two requirements: Must be Unconnected (or function mode) Lookup

Lookup function used within a conditional statement

Row keys (passed to Lookup)

Condition

IIF ( ISNULL(customer_id),0,:lkp.MYLOOKUP(order_no))

Lookup function

Conditional statement is evaluated for each row Lookup function is called only under the pre-defined condition
165

Conditional Lookup Advantage

Data lookup is performed only for those rows which require it. Substantial performance can be gained
EXAMPLE: A Mapping will process 500,000 rows. For two percent of those rows (10,000) the item_id value is NULL. Item_ID can be derived from the SKU_NUMB.

IIF ( ISNULL(item_id), 0,:lkp.MYLOOKUP (sku_numb))

Condition (true for 2 percent of all rows)

Lookup (called only when condition is true)

Net savings = 490,000 lookups

166

Unconnected Lookup - Return Port

The port designated as R is the return port for the unconnected lookup There can be only one return port The look-up (L) / Output (O) port can e assigned as the Return (R) port The Unconnected Lookup can be called in any other transformations expression editor using the expression :LKP.Lookup_Tranformation(argument1, argument2,..)

167

Connected vs. Unconnected Lookups

CONNECTED LOOKUP UNCONNECTED LOOKUP

Part of the mapping data flow Returns multiple values (by linking output ports to another transformation) Executed for every record passing through the transformation More visible, shows where the lookup values are used Default values are used

Separate from the mapping data flow Returns one value (by checking the Return (R) port option for the output port that provides the return value) Only executed when the lookup function is called Less visible, as the lookup is called from an expression within another transformation Default values are ignored

168

To Cache or not to Cache?

Caching can significantly impact performance Cached
Lookup table data is cached locally on the machine Mapping rows are looked up against the cache Only one SQL SELECT is needed

Uncached
Each Mapping row needs one SQL SELECT

Rule Of Thumb: Cache if the number (and size) of records in the Lookup table is small relative to the number of mapping rows requiring lookup or large cache memory is available for Integration Service
169

Additional Lookup Cache Options

Make cache persistent

Cache File Name Prefix

Reuse cache by name for another similar business purpose

Recache from Source

Overrides other settings and Lookup data is refreshed

Dynamic Lookup Cache

Allows a row to know about the handling of a previous row
170

Dynamic Lookup Cache Advantages

When the target table is also the Lookup table, cache is changed dynamically as the target load rows are processed in the mapping New rows to be inserted into the target or for update to the target will affect the dynamic Lookup cache as they are processed Subsequent rows will know the handling of previous rows Dynamic Lookup cache and target load rows remain synchronized throughout the Session run

171

Update Dynamic Lookup Cache

NewLookupRow port values
0 static lookup, cache is not changed

1 insert row to Lookup cache

2 update row in Lookup cache

Does NOT change row type

Use the Update Strategy transformation before or after Lookup, to flag rows for insert or update to the target
Ignore NULL Property
Per port
Ignore NULL values from input row and update the cache using only with non-NULL values from input
172

Example: Dynamic Lookup Configuration

Router Group Filter Condition should be: NewLookupRow = 1

This allows isolation of insert rows from update rows

173

Persistent Caches
By default, Lookup caches are not persistent When Session completes, cache is erased Cache can be made persistent with the Lookup properties When Session completes, the persistent cache is stored on the machine hard disk files The next time Session runs, cached data is loaded fully or partially into RAM and reused

Can improve performance, but stale data may pose a problem

174

Update Strategy Transformation

By the end of this section you will be familiar with:

Update Strategy functionality

Update Strategy expressions Refresh strategies

Smart aggregation

175

Target Refresh Strategies

Single snapshot: Target truncated, new records inserted Sequential snapshot: new records inserted Incremental: Only new records are inserted. Records already present in the target are ignored Incremental with Update: Only new records are inserted. Records already present in the target are updated

176

Update Strategy Transformation

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

Active Transformation Connected Ports

All input / output

Specify the Update Strategy Expression Usage

Updating Slowly Changing Dimensions IIF or DECODE logic determines how to handle the record
177

Sequence Generator Transformation

Generates unique keys for any port on a row

Passive Transformation Connected Ports

Two predefined output ports,
NEXTVAL CURRVAL No input ports allowed

Usage
Generate sequence numbers Shareable across mappings

178

Sequence Generator Properties

Increment Value

To repeat values
Number of Cached Values

179

Rank Transformation
RNKTRANS

Active Connected

Selects the top and bottom rank of the data

Different from MAX,MIN functions as we can choose a set of top or bottom values

String based ranking enabled

180

Normalizer Transformation
NRMTRANS

Active Connected

Used to organize data to reduce redundancy primarily with the COBOL sources
A single long record with repeated data is converted into separate records.

181

Stored Procedure
GET_NAME_US ING_ID

Passive Connected/ Unconnected

Used to run the Stored Procedures already present in the database

A valid relational connection should be there for the Stored Procedure transformation to connect to the database and run the stored procedure

182

External Procedure
Passive Connected/ Unconnected

Used to run the procedures created outside of the Designer Interface in other programming languages like c , c++ , visual basic etc.
Using this transformation we can extend the functionality of the transformations present in the Designer

183

Custom Transformation
Active/Passive Connected

Can be bound to a procedure that is developed using the functions described under custom transformations functions
Using this we can create user required transformations which are not available in the PowerCenter like we can create a transformation that requires multiple input groups, multiple output groups, or both.

184

Transaction Control
TC_EMPLOYEE

Active Connected

Used to control commit and rollback transactions based on a set of rows that pass through the transformation
Can be defined at the mapping as well as the session level

185

Reusability

This section discusses Parameters and Variables Transformations

Mapplets
Tasks

187

Parameters and Variables

System Variables

Creating Parameters and Variables

Features and advantages Establishing values for Parameters and Variables

188

System Variables
SYSDATE

Provides current datetime on the Integration Service machine

Not a static value

$$$SessStartTime

Returns the system date value as a string when a session is initialized. Uses system clock on machine hosting Integration Service
format of the string is database type dependent Used in SQL override Has a constant value

SESSSTARTTIME

Returns the system date value on the Informatica Server

Used with any function that accepts transformation date/time data types Not to be used in a SQL override Has a constant value

189

Mapping Parameters and Variables

Apply to all transformations within one Mapping

Represent declared values

Variables can change in value during run-time Parameters remain constant during run-time

Provide increased development flexibility

Defined in Mapping menu Format is $$VariableName or $$ParameterName

190

Mapping Parameters and Variables

Sample declarations

Userdefined names

Set the appropriate aggregation type

Set optional Initial Value

Declare Variables and Parameters in the Designer Mappings menu

191

Functions to Set Mapping Variables

SetCountVariable -- Counts the number of evaluated rows and increments or decrements a mapping variable for each row SetMaxVariable -- Evaluates the value of a mapping variable to the higher of two values (compared against the value specified)

SetMinVariable -- Evaluates the value of a mapping variable to the lower of two values (compared against the value specified)
SetVariable -- Sets the value of a mapping variable to a specified value

192

Transformation Developer
Transformations used in multiple mappings are called Reusable Transformations

Two ways of building reusable transformations

Using the Transformation developer Making the transformation reusable by checking the reusable option in the mapping designer Changes made to the reusable transformation are inherited by all the instances ( Validate in all the mappings that use the instances )

Most transformations can be made non-reusable /reusable.

***External Procedure transformation can be created as a reusable transformation only
193

Mapplet Developer
When a group of transformation are to be reused in multiple mappings then we develop mapplets

Input and/ Output can be defined for the mapplet

Editing the mapplet changes the instances of the mapplet used

194

Reusable Tasks
Tasks can be created in
Task Developer (Reusable)

Workflow Designer (Non-reusable)

Tasks can be made reusable my checking the Make Reusable checkbox in the general tab of sessions Following tasks can be made reusable:
Session Email

Command

When a group of tasks are to be reused then use a worklet (in worklet designer )
195

Queries???

Thank You!!!