Informatica Course Overview

Copyright © 2011 IGATE Corporation. All rights reserved. No part of this

publication shall be reproduced in any way, including but not limited to
photocopy, photographic, magnetic, or other record, without the prior
written permission of IGATE Corporation.
IGATE Corporation considers information included in this document to be
Confidential and Proprietary.

Page 0‐1
Informatica Course Overview

Page 0‐2
Informatica Course Overview

Page 0‐3
Informatica Course Overview

Page 0‐4
Informatica Course Overview

Page 0‐5
Informatica Course Overview

Page 0‐6
Informatica Course Overview

Page 0‐7
Informatica Course Overview

Page 0‐8
Informatica Course Overview

Page 0‐9
Informatica Course Overview

Page 0‐10
Informatica Course Overview

Page 0‐11
Informatica Course Overview

Page 0‐12
Informatica Course Overview

Page 0‐13
Informatica Course Overview

Page 0‐14
Informatica Course Overview

Page 0‐15
Informatica PowerCenter

Page 01‐1
Informatica PowerCenter

Page 01‐2
Informatica PowerCenter

A data warehouse is a decision support database that is maintained

separately from an organization’s operational databases. It usually resides
on a dedicated server. This database is designed based on what kind of
information a company is seeking (e.g., sales, marketing, healthcare
membership and providers, etc.). A data warehouse is “the place” for top
executives, managers, analysts, and other end‐users to mine a rich source
of company information. They can ask compelling business questions and
find answers in their data, and make key and timely business decisions
from their desktops using GUI based On‐line Analytical Processing (OLAP)
tools.

Page 01‐3
Informatica PowerCenter

The above Figure 1.1. shows a simplified data warehouse model. The
developer puts business rules for data transformation into the metadata
repository. There is a transformation process that extracts data from the
operational sources like database, legacy systems, and flat files. It
transforms data according to the business rules, and loads the data into the
data warehouse. From the data warehouse, atomic data flows to various
departments for their customized usage. These departmental databases
are called data marts.

The end user will use a query tool to look at the metadata in the repository
and the transformed data in the warehouse. The data in the data
warehouse will be used to cater to dynamic and complex reporting
requirements.

When Informatica’s PowerCenter is fit into the above model, the developer
can use PowerCenter Designer tool to develop mappings and other
metadata for the Informatica repository.

Page 01‐4
Informatica PowerCenter

To populate the data in the data warehouse, the steps are:

• Extract ‐ Extracting appropriate data from existing operational

database(s), flat files etc
• Transform ‐ Cleansing or scrubbing the data, aggregating,
denormalizing and filtering the data
• Load ‐ Loading the data into the database

This data population process is also known as the data transformation

process. Moving data from the operational databases to the data
warehouse needs to be done via extraction tools. Operational data
needs to be mapped to the target data warehouse. As a part of the data
movement, data transformation is performed as specified by the meta
d t rules
data l developed
d l d during
d i g the
th data
d t modeling
d li g stage.
t g PowerCenter
P C t is i a
product from Informatica used to perform ETL operation.

Page 01‐5
Informatica PowerCenter

PowerCenter provides an environment that allows to load data into a

centralized location, such as a datamart, data warehouse, or operational
data store (ODS). Data can be extracted from multiple sources,
transformed according to business logic built in the client application, and
load the transformed data into file and relational targets. Informatica
provides the following integrated components:
• PowerCenter domain. The Power Center domain is the primary unit for
management and administration within PowerCenter. The Service
Manager runs on a PowerCenter domain. The Service Manager
supports the domain and the application services.
• PowerCenter repository. The PowerCenter repository resides in a
relational database. The repository database tables contain the
instructions required to extract, transform, and load data
• Administration Console. The Administration Console is a web
application
pp that yyou use to administer the PowerCenter domain and
PowerCenter security
• PowerCenter Client. The PowerCenter Client is an application used to
define sources and targets, build mappings and mapplets with the
transformation logic, and create workflows to run the mapping logic.
The PowerCenter Client connects to the repository through the
Repository Service to modify repository metadata.
• Repository Service. The Repository Service accepts requests from the
PowerCenter Client to create and modify repository metadata and
accepts requests from the Integration Service for metadata when a
workflow runs
• Integration Service. The Integration Service extracts data from
sources and loads data to targets Page 01‐6
Informatica PowerCenter

•Web Services Hub. Web Services Hub is a gateway that exposes

PowerCenter functionality to external clients through web services
•SAP BW Service. The SAP BW Service extracts data from and loads data
to SAP BW. If you use the PowerExchange for SAP NetWeaver BW
Option, you must create and enable a SAP BW Service in the PowerCenter
domain
•Reporting Service. The Reporting Service runs the Data Analyzer web
application. Data Analyzer provides a framework for creating and running
custom reports and dashboards. You can use Data Analyzer to run the
metadata reports provided with PowerCenter, including the PowerCenter
Repository Reports and Data Profiling Reports. Data Analyzer stores the
data source schemas and report metadata in the Data Analyzer repository
Data Analyzer. Data Analyzer provides a framework to perform
business analytics on corporate data. With Data Analyzer, you
can extract, filter, format, and analyze corporate information
from data stored in a data warehouse, operational data store, or
other data storage models
Metadata Manager Service. The Metadata Manager Service
runs the Metadata Manager web application. You can use
Metadata Manager to browse and analyze metadata from
disparate metadata repositories. Metadata Manager helps you
understand and manage how information and processes are
derived, how they are related, and how they are used. Metadata
Manager stores information about the metadata to be analyzed
in the Metadata Manager repository
PowerCenter Repository Reports. PowerCenter Repository
Reports are a set of prepackaged Data Analyzer reports and
dashboards to help you analyze and manage PowerCenter
metadata
Page 01‐7
Informatica PowerCenter

Sources
PowerCenter accesses the following sources:
Relational: Oracle, Sybase ASE, Informix, IBM DB2, Microsoft SQL Server,
and Teradata.
File: Fixed and delimited flat file, COBOL file, XML file, and web log.
Application: Business sources such as Hyperion Essbase, WebSphere MQ,
IBM DB2 OLAP Server, JMS, Microsoft Message Queue, PeopleSoft, SAP
NetWeaver, SAS, Siebel, TIBCO, and webMethods.
Mainframe: Mainframe databases such as Adabas, Datacom, IBM DB2
OS/390, IBM DB2 OS/400, IDMS, IDMS‐X, IMS, and VSAM.
Other: Microsoft Excel, Microsoft Access, and external web services.

Targets
PowerCenter can load data into the following targets:
Relational: Oracle,, Sybase
y ASE,, Sybase
y IQ,
Q, Informix,, IBM DB2,, Microsoft
SQL Server, and Teradata.
File: Fixed and delimited flat file and XML.
Application: Business sources such as Hyperion Essbase, WebSphere MQ,
IBM DB2 OLAP Server, JMS, Microsoft Message Queue, mySAP, PeopleSoft
EPM, SAP BW, SAS, Siebel, TIBCO, and webMethods.
Mainframe: Mainframe databases such as IBM DB2 for z/OS, IMS, and
VSAM.
Other: Microsoft Access and external web services.

Page 01‐8
Informatica PowerCenter

The PowerCenter Designer has five tools to build mappings and mapplets
so as to specify how to move and transform data between sources and
targets.
The five tools are as follows:
•Source Analyzer
•Target designer
•Transformation Developer
•Mapplet Designer
•Mapping Designer

The Designer helps create source definitions, target definitions, and

transformations to build the mappings.

The D
Th Designer
ig allows
ll to
t workk with
ith multiple
lti l tools
t l att one time
ti and
d to
t workk
in multiple folders and repositories at the same time. It also includes
windows so as to view folders, repository objects, and Tasks.

Page 01‐9
Informatica PowerCenter

The Repository Manager client tool allows to navigate through multiple

folders and repositories and perform basic repository Tasks. The Repository
Manager can be used to:
• Add a repository
• Remove a repository
• Connect to a repository
• Search for repository objects, etc.

Page 01‐10
Informatica PowerCenter

The Workflow Manager is used to define a set of instructions called a

Workflow to execute mappings built in the Designer. Generally, a
Workflow contains a session and any other Task to be performed when a
session is executed.

Page 01‐11
Informatica PowerCenter

The Workflow Monitor is a tool that allows to monitor data loading

process.

Page 01‐12
Informatica PowerCenter

The PowerCenter Administration Console is the administration tool you

use to administer the PowerCenter domain and PowerCenter security.
Use the Administration Console to perform the following tasks:

Domain administrative tasks. Manage logs, domain objects, user

permissions, and domain reports. Domain objects include services, nodes,
grids, folders, and licenses.

Security administrative tasks. Manage users, groups, roles, and privileges.

Page 01‐13
Informatica PowerCenter

A domain is the primary unit for management and administration of

services in PowerCenter. PowerCenter provides the PowerCenter domain
to support the administration of the PowerCenter services.

Page 01‐14
Informatica PowerCenter

Page 01‐15
Informatica PowerCenter

Steps to Create Integration Service:

•In the Administration Console, click Create > Integration Service.
The Create New Integration Service dialog box appears.
•Enter values for the following Integration Service options Service Name,
Location, License, Assign, Run the Service on Grid, Primary Node, Backup
Nodes, Domain for Associated Repository Service, Associated Repository
Service, Repository User Name, Repository Password, Security Domain,
Data Movement Mode
•Click OK.
If you do not specify an associated repository, the following message
appears:
No Repository Service is associated with this Integration Service. Select an
associated Repository Service to view a list of available codepages.
Y cannott enable
You bl th
the Integration
I t g ti Service
S i until til you assign
ig th
the code
d page
g
for each Integration Service process node.
•Click OK.

Page 01‐16
Informatica PowerCenter

PowerCenter Graphical User Interface

Page 01‐17
Informatica PowerCenter

Page 01‐18
Informatica PowerCenter

Page 01‐19
Informatica PowerCenter

Following are the steps to create a repository:

1. In the Navigator of the Administration Console, select the folder where
you want to create the Repository Service.
Note: If you do not select a folder, you can move the Repository Service
into a folder after you create it.
2. Click Create > Repository Service.
The Create New Repository Service dialog box appears.
3. Enter values for the following Repository Service options:
Service Name, Location, License, Node, Database Type, CodePage,
ConnectString, DBUser, DBPassword, TableSpaceName, Creation Mode
4. If you create a Repository Service for a repository with existing content
and the repository existed in a
different PowerCenter domain, verify that users and groups with privileges
f th
for the Repository
R it Service
S i existi t in
i the
th currentt domain.
d i
5. Click OK.

The created repository service will be seen in the folder. Then select the
Repository Service in Navigator window and then the repository service
can be enabled or disabled using Enable or Disable

Page 01‐20
Informatica PowerCenter

The Repository Manager allows to perform basic repository tasks. It has

the following four windows:
• Navigator Window ‐ Displays all the objects that are created in the
Repository Manager, the Designer, and the Workflow Manager. It is
organized first by repository, then by folder.
• Main Window ‐ Provides properties of the object selected in the
Navigator window. The columns in this window will change
depending upon the object selected in the Navigator window.
• Dependency Window ‐ Shows dependencies on sources, targets,
mappings, and shortcuts for objects selected in either the Navigator
or Main window
• Output Window ‐ Provides the output of procedures executed
within Repository Manager

Other Tasks that can be performed within the Repository Manager

include:
• Create folders and edit folder properties
• Establish users, groups, privileges, and edit folder permissions
• Search metadata and perform dependency analyses
• View locks and unlock objects

Page 01‐21
Informatica PowerCenter

Steps to connect to the repository using Repository Manager for the first
time:
1. Launch the Repository Manager tool, choose Start | Programs |
Informatica PowerCenter | Client | Repository Manager.
3. Click on the Add Repository menu option.
4. Enter the following information:
• Repository name
• Username
5. Choose Repository | Connect or right click on the repository name and
choose Connect.
6. The Connect to Repository dialog box appears.
7. Enter the repository username and password.
8. Click More.
9 Th
9. The CConnectt to
t Repository
R it di
dialog
l gbbox expands.
d
10. Enter the host name of the machine the Repository Server is running
on and the port number the Repository Server uses for connections.
11. Click Connect.

Page 01‐22
Informatica PowerCenter

The Repository Server now opens a connection to the database, and a new
icon representing the repository appears in the Repository Manager.
Folders within the repository now appear underneath the icon for that
repository.

Steps to connect to a repository that has been accessed before:

1. Launch the Repository Manager tool, choose Start | Programs |

Informatica PowerCenter | Informatica PowerCenter Client |
Repository Manager.
2. Select the icon for the repository and click the Connect button
on the toolbar.
3. Alternatively, the repository icon can be selected, choose
Repository | Connect.
4. Enter the repository username and password.
5. Click Connect.

Follow the same steps to connect to the repository using PowerCenter

Designer, Workflow Manager, and Workflow Monitor.

Page 01‐23
Informatica PowerCenter

Page 01‐24
Informatica PowerCenter

Page 01‐25
Informatica PowerCenter

The steps for creating a group are as follows:

1. In the Administration Console Screen, Select Configure Security
2. Select Create Group
3. Enter Name and Description
4. Browse and select the Group in case of creating sub grouping

Page 01‐26
Informatica PowerCenter

The steps for creating a user are as follows:

1. In the Administration Console Screen, Select Configure Security
2. Select Create User
3. Enter Name, Password, Full Name, Description, Email, Phone
4. Click OK

Page 01‐27
Informatica PowerCenter

The steps for Assigning the Users to the Group are as follows:
•Select the Group from Navigator Window
•Overview|Edit |Users
•Add the users to the group

Page 01‐28
Informatica PowerCenter

The steps for Assigning the Roles to the Group are as follows:
1. Select Group from Navigator Window
2. Overview|Edit |Roles
3. Select the Appropriate Roles from:
• System‐Defined Roles
• Custom Roles
4. Press OK

The steps for Assigning the Privileges to the Group are as follows:
1. Select Group from Navigator Window
2. Overview|Edit |Privileges
3. Select the appropriate privileges from:
• Tools: Includes the privilege
g to log
g in to the Administration
Console.

• Security Administration: Includes privileges to manage users,

groups, roles, and privileges.

• Domain Administration: Includes privileges to manage the

domain, folders, nodes, grids, licenses, and application
services.
4. Press OK

Page 01‐29
Informatica PowerCenter

Folders provide a way to organize and store all metadata in the repository,
including mappings, schemas, and sessions. Folders are designed to be
flexible, to logically organize the repository. Each folder has a set of
configurable properties that helps to define how users access the folder.
For example, create a folder that allows all repository users to see objects
within the folder, but not to edit them. Or, create a folder that allows users
to share objects within the folder.
Folder Attributes
• FOLDER OWNER ‐ User who serves as focal point to folder
permissions
• PERMISSIONS ‐ Rights to read, write, and/or execute objects in a
folder
• SHARED ‐ Property that allows users to make shortcuts in a
f ld
folder
• SHORTCUT ‐ A dynamic link to an object stored in a shared
folder
• VERSIONS ‐ Folder iterations that indicate development
stages
A folder can be designated as shared by checking ‘Allow Shortcuts’ in the
Folder Properties. When a folder is shared, users can create shortcuts to
objects in the folder, thereby sharing the metadata in the folder. Once a
folder is designated as shared, it cannot be reverted back to make it non‐
shared. If a folder is not shared, users will only be able to make copies of
objects in the folder.
Page 01‐30
Informatica PowerCenter

Folders can be versioned, allowing to keep copies at different stages of

development. Versioning provides the flexibility to:
• Revert back to earlier copy of an object
• Re‐create an object if one is accidentally deleted
• Save a functioning copy while developing and testing

Folders are created and maintained by the Repository Manager. In the

Repository Manager, the users accessing folders, the objects and versions
within
ithi eachh folder
f ld can be
b seen.

In the Designer, use folders to store sources, transformations, mapplets,

targets, and mappings.

In the Workflow Manager, use folders to store Workflows, Tasks, and

sessions. When creating a Workflow, include any session or Task in the
folder.

Note: Create a folder in a repository before connecting to the repository

using the Designer or Workflow Manager.

Page 01‐31
Informatica PowerCenter

Permissions
Allows repository users to perform Tasks within a folder. To perform Tasks
within a folder having the privilege to perform the Task in the repository as
well as the applicable folder permission is a must. The available folder
permissions are:

• Read permission ‐ Allows to view the folder as well as objects in the

folder
• Write permission ‐ Allows to create or edit objects in the folder
• Execute permission ‐ Allows to execute or schedule a Workflow in
the folder

Note: Permissions work in conjunction with privileges. Privileges are

actions
ti th
thatt a user performs
f in
i PowerCenter
P C t applications.
li ti A user with
ith th
the
privilege to perform certain actions can require permissions to perform the
action on a particular object.

Page 01‐32
Informatica PowerCenter

Page 01‐33
Informatica PowerCenter

Steps to create a Folder:

1. In the Repository Manager, connect to the repository.
2. Choose Folder | Create.
3. Choose Folder Properties
4. Enter the following information:
• Name – Folder name, Required.
• Description ‐ Description of the folder, Optional.
• Owner ‐ Owner of the folder. Any user in the repository can be
the folder owner, Required.
• Allow Shortcut ‐ If selected, makes the folder shared, Optional
5. Choose Permissions Tab
• Select the Radio Button from List User, List Group, List All
• Then select Permission check boxes accordingly

Page 01‐34
Informatica PowerCenter

Page 01‐35
Informatica PowerCenter

Page 01‐36
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Page 01‐1
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Page 01‐2
Dimension Modeling for Data Warehouse Introduction to Data Modeling

When the Integration service loads data, it uses the instructions

configured,
fi d tto read,
d ttransform,
f and
d write
it ddata.
t

Page 01‐3
Dimension Modeling for Data Warehouse Introduction to Data Modeling

• Create Repository ‐ The first step is to create the Informatica repository

from the Repository Server Administration Console. This repository will
hold all related metadata and drive Informatica’s extraction and
transformation process. Once the repository and necessary folders have
been created, the actual design process can begin. This is accomplished
in the Designer client application, where developers will spend the
majority of their time.
• Import Source Definitions ‐ The next step will be to put the source
definitions into the repository. The Source Analyzer within the Designer
tool is used for this process
• Create Target Schema ‐ After the source definitions are in the
repository, the target schema should be created. This can be designed in
the Target designer (also with the Designer), reverse engineered from
th database,
the d t b or iimported
t d through
th gh PowerPlugs
P Pl g
• Create Mappings ‐ Mappings can be created to link the sources with the
targets – the translation of ‘business rules’
• Load Data ‐ The final step is to load the data. The Workflow Manager is
used to configure and schedule session Tasks and Workflows to run the
mappings. Then, based upon all the information stored in the repository,
the Integration service will extract, transform, and load the data

Page 01‐4
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The PowerCenter Designer has five tools which helps to build mappings
and mapplets so that one can specify how to move and transform data
between sources and targets. The Designer helps to create source
definitions, target definitions, and transformations to build the mappings.

The Designer consists of the following :

• Navigator window ‐ Use to connect to and work in multiple
repositories and folders. Copy and delete objects options are available
and shortcuts can be created using the Navigator
• Workspace window‐ Use to view or edit sources, targets, mapplets,
transformations, and mappings. Work with a single tool at a time in
the workspace. One can use the workspace in default or workbook
format
• Status
St t bar
b ‐ Displays
Di l the
th status
t t off the
th operation
ti being
b i g performed
f d
• Output window‐ Provides details while performing certain Tasks, such
as saving the work or validating a mapping. Right‐click the Output
window to access window options, such as printing output text,
saving text to file, and changing the font size

Page 01‐5
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The Informatica Designer has five modes:

• The Source Analyzer is used to create or reverse‐engineer source
definitions
• The Target designer is used to create or reverse‐engineer target
schema
• The Transformation Developer is used to create reusable
transformations
• The Mapplet Designer is used to create and edit mapplets. Mapplets
are reusable objects that represent a set of transformations
• The Mapping Designer can be used to create the source to target
mappings that contain the business rules for the server during the
extract, transform, and load process

Page 01‐6
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The first tool, the Source Analyzer, is used to identify the sources that will
be used to build the data mart or warehouse and create repository
definitions for those sources. Source definitions describe the sources that
are going to be providing data to the warehouse. The different ways to
create source definitions in the Repository.

• Import from Database

• Import from File
• Import from COBOL File
• Import from XML…
• Create manually
• Import from SAP
• Import from PeopleSoft
• I
Importt from
f Siebel
Si b l

Page 01‐7
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Relational source definitions can be imported from database tables, views,

and synonyms. When importing a source definition, import the following
source metadata:

• Source name
• Database location
• Column names
• Datatypes
• Key constraints

To import a source definition, first connect to the source database from the
client machine using a properly configured ODBC data source or gateway.
Read permission is required on the database object.

After importing a relational source definition, optionally enter business

names for the table and columns. Also manually define key relationships,
which can be logical relationships created in the repository that do not exist
in the database.

Page 01‐8
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Steps to import a source definition:

1. In the Source Analyzer, choose Sources | Import from Database.
2. Select the ODBC data source used to connect to the source database.
To create or modify an ODBC data source, click the Browse button to
open the ODBC Administrator. Create the appropriate data source
and click OK. Select the new ODBC data source.
3. Enter a database username and password to connect to the
database.
Note: The username must have the appropriate database permissions to
view the object. The owner name for database objects will have to be
specified in order to use them as sources.
4. Click Connect.
If no table names appear or if the table to be imported does not
appear, click
li k All.
All
5. Scroll down through the list of sources to find the source to be
imported. Select the relational object or objects to be imported. Hold
down the Shift key to select a block of sources within one folder, or
hold down the Ctrl key to make non‐consecutive selections within a
folder.
6. Click OK.

Page 01‐9
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The next step in the design process is to create the target schema. This is
accomplished in the second component of the Designer, the Target
designer.

The Target designer provides a GUI interface for creating and customizing
the logical target schema. Target definitions can be customized as needed.

When the customization is complete, one can create the physical targets in
the database from SQL scripts executed from the Target designer.

The methods of creating target schema are:

• Automatic Creation
• Import from Database
• Manual
M l Creation
C ti

Page 01‐10
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Target definitions are stored in a separate section of the repository.

Automatic target creation is useful for creating:

• Staging tables, such as those needed in Dynamic Data Store
• Target tables that will mirror much of the source definition
• Target tables that will be used to migrate data from different
databases, e.g., Sybase to Oracle

Page 01‐11
Dimension Modeling for Data Warehouse Introduction to Data Modeling

When a target definition is imported from a relational table, the Designer

imports the following target details:
• Target name ‐ The name of the target
• Database location ‐ Specify the database location of a relational
source is to be imported. Specify a different location while editing
the target definition in the Target designer and while configuring a
session
• Column names ‐ The names of the columns
• Data types ‐ The Designer imports the native datatype for each
column
• Key constraints ‐ The constraints in the target definition can be
critical, since they may prevent data from moving into the target if
the Integration service violates a constraint during a Workflow. For
example,l if a column
l contains
t i the
th NOT NULL constraint
t i t andd one fail
f il
to map data to this column, the Integration service cannot insert
new records into the target table
• Key Relationships ‐ The Target designer can be customized to
automatically create primary‐foreign key relationships. Choose Tools
| Options and select the Format tab. Check Import Primary and
Foreign Keys. Also logical relationships can be created in the
repository. Key relationships do not have to exist in the database

Page 01‐12
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Target Definitions can also be created manually via a menu option or the
toolbar. An empty definition is created, you can enter the table name,
columns, datatypes, precision, and key information.

Manual target creation is useful for creating tables whose columnar

information cannot be copied from source definitions, such as time
dimension tables or aggregate tables.

Page 01‐13
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Steps to create a Target schema:

I Automatic Target Schema:
1. Drag a relational source definition into the Target designer workspace,
the Designer will create a relational or flat file target definition that
matches the source definition.
II From the Database:
To import a relational target definition:
1. In the Target designer, choose Targets | Import from Database.
2. Select the ODBC data source used to connect to the target database. To
create or modify an ODBC data source first, click the Browse button to
open the ODBC Administrator. After creating or modifying the ODBC
source, continue with the following steps.
3. Enter the username and password needed to open a connection to the
d t b e and
database, d click
li k Connect.
Co e t If the usere is
i not
ot the owner
o e of the table
t ble to
be used as a target, specify the owner name.
4. Drill down through the list of database objects to view the available
tables which can be used as targets.

Page 01‐14
Dimension Modeling for Data Warehouse Introduction to Data Modeling

5. Select the relational table or tables to import the definitions into the
repository. Hold down the Shift key to select a block of tables, or hold
down the Ctrl key to make non‐contiguous selections. Also the Select All
and Select None buttons can be used to select or clear all available
targets.
6. Click OK. The selected target definitions now appear in the Navigator
under the Targets icon.
77. Choose Repository
p y | Save.

III Manual:
1. In the Target designer, choose Targets | Create.
2. Enter a name for the target and select the target type. One can create a
flat file target definition by choosing Flat File for the target type.
3. The target name entered is the name of the new table in the database
when a relational target definition is created. Follow any database‐
specific naming conventions.
4. Click Create.
5. An empty table structure appears in the workspace. (It may be covered
by the dialog box.) The new target definition also appears within the
Navigator window.
6. If another target definition is to be created, enter a new target name and
target type and click Create.
Create Repeat this step for each target to be
created.
7. Click Done when all target definitions are created.
8. Configure the target definition i.e., add the required columns.
9. Choose Repository | Save.

Page 01‐15
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The above diagram shows how a mapping looks like. It has the various
transformations which the source data has to go through. Every mapping
must contain the following components:
• Source definition ‐ Describes the characteristics of a source table or
file
• Transformation ‐ Modifies data before writing it to targets. Use
different transformation objects to perform different functions
• Target Definition ‐ Defines the target table or flat file
• Connectors ‐ Connect sources, targets, and transformations so the
Integration service can move the data as it transforms it

The Mapping Designer displays objects in three different views:

• Iconized ‐ Shows an icon of the object with the object name
• Normal
N l ‐ Shows
Sh th
the columns
l in
i the
th ports
t tab
t b and
d the
th input
i t and
d output
t t
port indicators. One can connect objects that are in the normal view
• Edit ‐ Shows the object properties. Switch between the different tabs
and configure the object in this view

Page 01‐16
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The Designer provides a set of transformations that perform specific

functions. For example, an Aggregator transformation performs
calculations on groups of data. Perform the following Tasks to incorporate
a transformation into a mapping:

• Create the transformation ‐ Create it in the Mapping Designer as part

of a mapping, in the Mapplet Designer as part of a mapplet, or in the
Transformation Developer as a reusable transformation

• Configure the transformation ‐ Each type of transformation has a

unique set of options that can be configured

• Link the transformation to other transformations and target

d fi iti
definitions ‐ Drag
D g one portt tto another
th tto link
li k them
th in
i the
th mapping
i g or
mapplet

Page 01‐17
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Active ‐ An active transformation can change the number of rows that pass
through it, such as a Filter transformation that removes rows that do not
meet the filter condition.

Passive ‐ A passive transformation does not change the number of rows

that pass through it, such as an Expression transformation that performs a
calculation on data and passes all rows through the transformation.

Connected ‐ Connected transformations are connected to the data flow.

Unconnected ‐ 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.

Page 01‐18
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Each transformation has a minimum of four tabs:

• Transformation – allows to rename a transformation, switch between
transformations, enter transformation comments, and make a
transformation reusable
• Ports – allows to specify level attributes such as port name, datatype,
precision, scale, primary/foreign keys, nullability
• Properties – allows to specify the amount of detail in the session log,
and other properties specific to each transformation
• Metadata Extensions – allows to extend the data stored in the
repository by associating information with individual repository
objects

The additional tabs are :

• Condition
C diti – allows
ll to
t enter
t conditions
diti for
f e.g.
g Joiner
J i or Filt
Filter
Transformation
• Sources – allows to specify additional source definitions e.g. Source
Qualifier Transformation
• Normalizer – allows to enter new ports for the Normalizer
Transformation

Page 01‐19
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Active Transformations:
• Source Qualifier ‐ represents all data queries from the source
• Normalizer ‐ normalizes records from VSAM or relational sources
• Aggregator ‐ performs aggregate calculations
• Filter ‐ serves as a conditional filter
• Router ‐ serves as a group conditional filter
• Rank ‐ limits records to top or bottom range
• Sorter ‐ sorts the input rows in ascending or descending order
• Update Strategy ‐ allows for logic to insert, update, delete, or reject
data
• Joiner – allows extraction of data from heterogeneous sources
• Application Source Qualifier ‐ Represents the rows that the
Integration Service reads from an application, such as an ERP source,
when
h it runs a session.
i
• Transaction Control ‐ Defines commit and rollback transactions.
• XML Generator ‐ Reads data from one or more input ports and outputs
XML through a single output port.
• XML Parser ‐ Reads XML from one input port and outputs data to one
or more output ports.
• XML Source Qualifier ‐ Represents the rows that the Integration
Service reads from an XML source when it runs a session.

Page 01‐20
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Passive Transformations:
•Expression ‐ performs simple calculations
•Lookup ‐ looks up values and passes to other objects
•Stored Procedures ‐ calls a stored procedure and captures return
values
•Sequence Generator ‐ generates unique ID values

•External Procedure ‐ Calls a procedure in a shared library

Some transformation can be both active or passive which are as follows:

•Java ‐ Executes user logic coded in Java.
•Complex ‐ Transforms data in unstructured and semi‐structured
formats.
•Custom ‐ Calls a procedure in a shared library or DLL
•SQL ‐ Executes SQL queries against a database.

These transformations will be discussed in the further lessons.

Page 01‐21
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The Source Qualifier displays the transformation datatypes. The

transformation datatypes in the Source Qualifier determine how the source
database binds data when the Integration service reads it. Do not alter the
datatypes in the Source Qualifier. If the datatypes in the source definition
and Source Qualifier do not match, the Designer marks the mapping invalid
when saved. Source Qualifier can be used to perform following tasks:
• Join data originating from the same source database ‐ One can join
two or more tables with primary‐foreign key relationships by linking
the sources to one Source Qualifier
• Filter records when the Integration service reads source data ‐ If a
filter condition is included, the Integration service adds a WHERE
clause to the default query
• Specify an outer join rather than the default inner join ‐ If a user‐
d fi d join
defined j i is
i included,
i l d d the
th Integration
I t g ti servicei replaces
l the
th join
j i
information specified by the metadata in the SQL query
• Specify sorted ports ‐ If a number for sorted ports are specified, the
Integration service adds an ORDER BY clause to the default SQL query
• Select only distinct values from the source ‐ If Select Distinct is
chosen, the Integration service adds a SELECT DISTINCT statement to
the default SQL query
• Create a custom query to issue a special SELECT statement for the
Integration service to read source data

Page 01‐22
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The Expression Transformation is used to calculate values in a single row.

For example, in order to adjust employee salaries, concatenate first and last
names, or convert strings to numbers. Use the Expression transformation
to perform any non‐aggregate calculations.

Page 01‐23
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The first step in the process of moving data between sources and targets is
to create a mapping in the Mapping Designer.

Steps to create a mapping:

1. Open the Mapping Designer.
2. Choose Mappings | Create menu option, or drag a repository object
into the workspace.
3. Enter a name for the new mapping and click OK. The naming
convention for mappings is m_MappingName, such as m_demo

Steps to create a Source Qualifier Transformation:

Drag the required source, which can be a flat file or relational table in the
mapping area, by default the Source Qualifier Transformation will be
created
t d for
f the
th source definition.
d fi iti

Page 01‐24
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Steps to create an Expression Transformation:

1. In the Mapping Designer, choose Transformation | Create. Select the
Expression transformation. Enter a name for it (the convention is
EXP_TransformationName) and click OK.
2. Create the input ports. If the input transformation is already available,
select Link Columns from the Layout menu and then click and drag each
port used in the calculation into the Expression transformation. With
this method,, the Designer
g copies
p the port
p into the new transformation
and creates a connection between the two ports. Or, open the Edit
dialog box and create each port manually.
Note: In order to make this transformation reusable, create each port
manually within the transformation.
3. Repeat the previous step for each input port which has to be added to
the expression.
4. Create the output ports (O) needed, making sure to assign a port
datatype that matches the expression return value.
5. Click the small button that appears in the Expression section of the
dialog box and enter the expression in the Expression Editor. To prevent
typographic errors, where possible, use the listed port names and
functions.
6. Port names used as part of an expression in an Expression
transformation follow stricter rules than port names in other types of
transformations:
• A port name must begin with a single‐ or double‐byte letter or
single‐ or double‐byte underscore (_)
• It can contain any of the following single‐ or double‐byte
characters: a letter, number, underscore (_), $, #, or @
7. Check the expression syntax by clicking Validate. If necessary, make
corrections to the expression and check the syntax again. Then save the
expression and exit the Expression Editor.
8. Connect the output ports to the next transformation or target.
9. Choose Repository | Save.

Page 01‐25
Dimension Modeling for Data Warehouse Introduction to Data Modeling

The Aggregator Transformation allows to perform aggregate calculations,

such as average and sum.

To configure ports in the Aggregator Transformation we can:

• Enter an aggregate expression in any output port, using conditional
clauses or non‐aggregate functions in the port
• Create multiple aggregate output ports
• Configure any input, input/output, output, or variable port as a group
by port, and use non‐aggregate expressions in the port
• Improve performance by connecting only the necessary input/output
ports to subsequent transformations, reducing the size of the data
cache
• Use variable ports for local variables

Page 01‐26
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Components of the Aggregator Transformation

The Aggregator is an active Transformation, changing the number of rows
in the data flow. It must be connected to the data flow. The Aggregator
Transformation has the following components and options:

• Aggregate expression ‐ Entered in an output port. Can include non‐

aggregate expressions and conditional clauses
• Group by port ‐ Indicates how to create groups.
• Sorted input ‐ Is used to improve session performance. To use this
option, the data passed to the Aggregator Transformation sorted by
Group by port in ascending or descending order
• Aggregate Cache ‐ The Integration service stores data in the aggregate
cache until it completes aggregate calculations. It stores group values
i an index
in i d cache h andd row ddata
t in
i the
th data
d t cache.
h

Page 01‐27
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Page 01‐28
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Ex: for calculating sum of salaries department wise, if data comes in

random order, Integration service needs to use cache for holding data until
all the entries for a particular department is calculated (deptno 10,20....).

By using sorted input, Integration service does not use cache and just
performs calculations assuming the data comes in sorter order thus
improvement in performance.

Page 01‐29
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Page 01‐30
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Page 01‐31
Dimension Modeling for Data Warehouse Introduction to Data Modeling

To create an Aggregator Transformation:

1. In the Mapping Designer, choose Transformation | Create menu
option. Select the Aggregator Transformation. The naming convention
for Aggregator Transformations is AGG_TransformationName.
2. Enter a description for the transformation.
3. Enter a name for the Aggregator, click Create. Then click Done. The
Designer creates the Aggregator Transformation.
4. Drag the desired ports to the Aggregator Transformation. The
Designer creates input/output ports for each port included.
5. Double‐click the title bar of the transformation to open the Edit
Transformations dialog box.
6. Select the Ports tab.
7. Click the group by option for each column the Aggregator should use
t create
to t groups.
g
8. If a non‐aggregate expression to modify groups has to be used, click
the Add button and enter a name and data type for the port. Make the
port an output port by clearing Input (I).

Page 01‐32
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Page 01‐33
Dimension Modeling for Data Warehouse Introduction to Data Modeling

Page 01‐34
Informatica PowerCenter Workflow Manager

Page 03‐1
Informatica PowerCenter Workflow Manager

Page 03‐2
Informatica PowerCenter Workflow Manager

Once a mapping is created in the Mapping Designer, the source to target

dataflow specification is ready.

A Workflow is a set of instructions for the Integration Service to perform

the data transformation and loading. When a Workflow starts, the
Informatica Integration Service retrieves mapping, Workflow, and session
metadata from the repository to extract data from the source, transform it,
and load it into the target. It also runs the Tasks in the Workflow. The
Integration Serviceuses Load Manager and Data Transformation Manager
(DTM) processes to run the Workflow.

A Workflow combines the logic of Session Tasks, other types of Tasks and
Worklets. A Session is a type of Task related to the movement of data
th gh the
through th Server.
S Tasks
T k are theth building
b ildi g blocks
bl k off a Workflow.
W kfl

Page 03‐3
Informatica PowerCenter Workflow Manager

The simplest Workflow is composed of:

• Start Task
• A Link
• A Session Task (and any other Task as required for execution)

The first step to develop a Workflow is to create a new Workflow in the

Workflow Designer. A Workflow must contain a Start Task. The Start Task
represents the beginning of a Workflow.

When a Workflow is created, the Workflow Designer creates a Start Task

and adds it to the Workflow. The Start Task cannot be deleted. The next
step is to add Tasks to the Workflow. The Workflow Manager includes
Tasks such as the Session Task, the Command Task, and the Email Task to
d ig th
design the Workflow.
W kfl

Finally, the Workflow Tasks are connected with links to specify the order
of execution in the Workflow.

Page 03‐4
Informatica PowerCenter Workflow Manager

The Workflow Manager tool is used to define the workflow to execute

mappings built in the Designer. After a Workflow is created, it is run in the
Workflow Manager and monitored in the Workflow Monitor.

The Workflow Manager provides several windows:

• The Navigator Window ‐ Allows to view servers and all folders within a
repository and the contents of those folders
• The Main Window – Allows to view all the sessions that have been
created and linked to make a complete Workflow
• The Output Window ‐ Contains messages from the server, such as
success or failure to schedule or start sessions. The Output window
contains the following tabs:
• Save ‐ Displays messages when a Workflow, a Worklet, or a Task
i used.
is d The
Th Save
S tab
t b displays
di l a validation
lid ti summary whenh a
Workflow or a Worklet is saved
• Fetch Log ‐ Displays messages when the Workflow Manager
fetches objects from the repository
• Validate ‐ Displays messages when a Workflow, a Worklet, or a
Task is validated
• Copy ‐ Displays messages when repository objects are copied
• Server ‐ Displays messages from the Integration Service
• Notifications ‐ Displays messages from the Repository Service

Page 03‐5
Informatica PowerCenter Workflow Manager

The Workflow Manager has three development tools:

Task Developer
• Used to construct Session, Shell Command and Email Tasks
• Tasks created in Task Developer are reusable in Worklets or Workflows

Worklet Designer
• Used to construct an object which represents a set of Tasks
• Worklets are reusable in multiple Workflows

Workflow Designer
• “Maps” the execution order of Sessions, Tasks and Worklets for the
Integration Service

Page 03‐6
Informatica PowerCenter Workflow Manager

The Workflow Monitor is a tool used to monitor Workflows and Tasks. The
details about a Workflow or a Task can be viewed in either Gantt Chart view
or Task view. Workflows can be run, stopped, aborted, and resumed from
the Workflow Monitor. The Workflow Monitor displays Workflows that
have run at least once.

Page 03‐7
Informatica PowerCenter Workflow Manager

The Server can be monitored in two modes:

• Online mode – In this mode, the Workflow Monitor continuously
receives the information from the Integration Service and the
Repository Service
• Offline mode – Workflow Monitor displays historic information about
past Workflow runs by fetching information from the Repository

The Workflow Monitor consists of the following windows:

• Navigator window ‐ Displays monitored repositories, servers, and
repository objects
• Output window ‐ Displays messages from the Integration Service
• Time window ‐ Displays progress of Workflow runs

Page 03‐8
Informatica PowerCenter Workflow Manager

The Task view displays details about Workflow runs in a report format. It
displays the following details:

• Task name ‐ The name of the task will be displayed under the
workflow name
• Folder name ‐ The name of the folder containing the Task
• Status ‐ The status of the Task or Workflow
• Workflow name ‐ The name of the Workflow
• Worklet name ‐ The name of the Worklet
• Start time ‐ The time that the Integration Service starts executing
the Task or Workflow
• Completion time ‐ The time that the Integration service finishes
executing the Task or Workflow
• Status
St t message g ‐ Message
M g from
f th
the Integration
I t g ti Service
S i regarding
g di g
the status of the Task or Workflow
• User name ‐ Name of the user who owns the Workflow or Task
• Run type ‐ The method used to start the Workflow. Workflow can be
manually started or scheduled to start
• Task type ‐ The type of the Task

Page 03‐9
Informatica PowerCenter Workflow Manager

The Gantt Chart view displays details about Workflow runs in chronological
format. It displays the following information:

• Task name ‐ Name of the Task in the Workflow

• Duration ‐ The length of time the Integration service spends executing

the most recent Task or Workflow

• Status ‐ The status of the most recent Task or Workflow

• Connection between objects ‐ The Workflow Monitor shows links

between objects in the Time window

Page 03‐10
Informatica PowerCenter Workflow Manager

Before running any Workflow, the first thing that needs to be done is to
configure a database connection. A database connection specifies the
source and target database connection information. When a session that
reads from or writes to a relational database is created or modified, only
configured source and target databases can be selected.

For every source and target database used in the session, a separate
database connection has to be configured.

To create a database connection, one of the following privileges is required:

• Use Workflow Manager
• Super User

Page 03‐11
Informatica PowerCenter Workflow Manager

To create a connection, the following information should be available:

• Database name ‐ Name for the connection
• Database type ‐ Type of the source or target database
• Database username ‐ Name of a user who has the appropriate
database permissions to read from and write to the database
• Password ‐ Database password
• Connect string ‐ Connect string used to communicate with the
repository
• Database code page ‐ Code page associated with the database

Page 03‐12
Informatica PowerCenter Workflow Manager

Steps to create a Relational Database Connection:

1. In the Workflow Manager, connect to a repository.
2. Choose Connections | Relational menu option. A dialog box appears,
listing all the registered source and target database connections.
3. Select the type of database connection to be created.
4. Click Add. The Connection Object Definition dialog box appears.
5. Enter the database connection name, database connection type,
username, password, connect string and code page.
6. Click OK.

Page 03‐13
Informatica PowerCenter Workflow Manager

Tasks are building blocks of a Workflow. The Workflow Manager contains

many types of Tasks to build Workflows and Worklets. Tasks created in the
Task Developer are reusable and the ones created in the Workflow
Designer and Worklet Designer are non‐reusable.

The Workflow Manager validates Task attributes and links. If a Task is

invalid, the Workflow becomes invalid. Workflows containing invalid
sessions may still be valid.

Page 03‐14
Informatica PowerCenter Workflow Manager

Reusable Tasks can be used in multiple Workflows in the same folder. The
reusable Tasks can be seen in the Tasks node in the Navigator window.

Page 03‐15
Informatica PowerCenter Workflow Manager

Tasks created within the Workflow are non‐reusable Tasks. The Non‐
reusable Tasks can be seen in the Sessions node in the Navigator window.

Page 03‐16
Informatica PowerCenter Workflow Manager

A Session Task is the basic object created in the Workflow Manager that
assembles the information contained in a mapping together with database
connection and other information. It is the vehicle for commanding the
Integration Service to extract, transform, and load data and report session
status and performance characteristics. A Workflow groups two or more
sessions to run sequentially or concurrently or as specified in the
Workflow.

A reusable Session Task is created in the Task Developer. We can also

create Session Tasks in the Workflow Designer as we develop the
Workflow. Once the Session is created, the session properties can be
edited at any time.

Page 03‐17
Informatica PowerCenter Workflow Manager

To create a new Session Task, the following information is required:

• Mapping used for the Session Task
• Session name, which must be unique among all sessions in a given
folder
• Source type
• Update strategy for writing to targets
• Target type

Note: Before creating a Session Task, the Workflow Manager has to be

configured to communicate with databases and the Integration service.
Appropriate permissions have to be assigned for any database, FTP, or
external loader connections.

Page 03‐18
Informatica PowerCenter Workflow Manager

The Session Task properties has the following tabs:

• General ‐ Specifies session name, mapping name, and description for
the Session Task
• Properties ‐ Specifies Session log information, test load settings, and
performance configuration
• Config Object ‐ Specifies advanced settings, log options, and error
handling configuration
• Mapping – Specifies source, target information and Override
transformation properties
• Components ‐ Configure pre‐ or post‐session shell commands and
emails
• Metadata Extension ‐ Associate additional non‐business information
with repository objects

Page 03‐19
Informatica PowerCenter Workflow Manager

To create a Workflow:
1. Open the Workflow Designer.
2. Choose Workflows | Create menu option.
3. Enter a name for the new Workflow.
4. Click OK.

To create a Task:
1. In the Workflow Designer, click the Session Task icon on the Tasks
toolbar.
2. Choose Tasks | Create menu option.
3. Select Session task for the Task type. Enter a name for the Session
Task.
4. Click Create. The Mappings dialog box appears.
5 Select
5. S l t the
th mapping
i g to
t be
b usedd in
i the
th Session
S i Task T k and
d click
li k OK.
OK
6. Click Done. The Session Task appears in the workspace.

Page 03‐20
Informatica PowerCenter Workflow Manager

9. Click in the right corner of the Expression field, enter the non‐aggregate
expression using one of the input ports, then click OK. To group by on a
column check the group by checkbox.
10. Click Add and enter a name and data type for the aggregate expression
port. Make the port an output port by clearing Input (I).
11. Click in the right corner of the Expression field to open the Expression
Editor. Enter the aggregate expression, click Validate, then click OK.
p
12. Make sure the expression validates before closing
g the Expression
p Editor.
13. Add default values for specific ports as necessary.
14. Click OK.
15. Choose Repository | Save to save changes to the mapping.

Page 03‐21
Informatica PowerCenter Workflow Manager

You can debug a valid mapping to know about data and error conditions.
To debug a mapping, the Debugger is configured and run from within the
Mapping Designer.

When the Debugger is run, it pauses at breakpoints and allows viewing and
editing of transformation output data.

The Debugger uses a session to run the mapping on the PowerCenter

Server.
When the Debugger is configured, three different debugger session types
can be selected. The Debugger runs a workflow for each session type. The
Debugger session types are :

• Existing
E i ti g non‐reusable
bl session
i ffor th
the mapping.
i g The
Th DDebugger
b gg uses
existing source, target, and session configuration properties. When the
Debugger is run, the PowerCenter Server runs the non‐reusable session
and the existing workflow.

• Existing reusable session for the mapping. The Debugger uses existing
source, target, and session configuration properties. When the
Debugger is run, the PowerCenter Server runs a debug instance of the
reusable session and creates and runs a debug workflow for the
session.

Page 03‐22
Informatica PowerCenter Workflow Manager

• Debug session instance created for the mapping. Using the Debugger
Wizard, the user can configure source, target, and session
configuration properties. When the Debugger is run, the PowerCenter
Server runs a debug instance of the debug workflow and creates and
runs a debug workflow for the session.

Debug Process
Use the following process to debug a mapping:
1. Create breakpoints. Create breakpoints in a mapping if PowerCenter
Server is to evaluate data and error conditions.
2. Configure the Debugger. Use the Debugger Wizard to configure the
Debugger for the mapping. Select the session type the PowerCenter
Server uses when it runs Debugger. When a debug session is created, a
b t off session
subset i properties
ti within
ithi th
the D
Debugger
b gg Wizard
Wi d need d to
t be
b
configured, such as source and target location. You can also choose to
load or discard target data.
3. Run the Debugger. Run the Debugger from within the Mapping
Designer. When the Debugger is run, the Designer connects to the
PowerCenter Server. The PowerCenter Server initializes the Debugger
and runs the debugging session and workflow. The PowerCenter
Server reads the breakpoints and pauses the Debugger when the
breakpoints evaluate to true.

Page 03‐23
Informatica PowerCenter Workflow Manager

4. Monitor the Debugger. When the Debugger is running, target data,

transformation and mapplet output data, the debug log, and the
session log can be monitored. When the Debugger is running, the
Designer displays the following windows:

• Debug log. View messages from the Debugger.

• Target window. View target data.
• Instance window. View transformation data.

5. Modify data and breakpoints. When the Debugger pauses, data can be
modified to see the effect on transformations, mapplets, and targets as
the data moves through the pipeline. Breakpoint information can also
be modified

Page 03‐24
Informatica PowerCenter Workflow Manager

To use the Debugger

1. Select a valid mapping.

2. Select Mappings – Debugger – Edit Breakpoints to create breakpoints if
any.
3. Select Mappings – Debugger – Start Debugger to invoke the Debugger.
4. Select the server, session type, session instance and target table
options to configure the debugger.
5. Monitor the debugger. Observe transformation data in the instance
window and target data in the target window.
6. Modify data if required.

Page 03‐25
Informatica PowerCenter Workflow Manager

Page 03‐26
Informatica PowerCenter Workflow Manager

Page 03‐27
Informatica PowerCenter Workflow Manager

Page 03‐28
Informatica PowerCenter Transformations

Page 04‐1
Informatica PowerCenter Transformations

Page 04‐2
Informatica PowerCenter Transformations

A Lookup Transformation is used to look up data in a relational table, view,

or synonym. A lookup definition can be imported from any relational
database to which both the Informatica Client and Server can connect.
Multiple Lookup Transformations can be used in a mapping.

The Integration service queries the lookup table based on the lookup ports
in the transformation. It compares Lookup Transformation port values to
lookup table column values based on the lookup condition. The result of the
lookup is passed to other transformations and the target.

The Lookup Transformation can be used to:

• Get a related value ‐ For example, if the source table includes
employee ID, but the target table requires employee name to make the
summary data
d t easier
i to
t readd
• Perform a calculation ‐ Many normalized tables include values used in a
calculation, such as gross sales per invoice or sales tax, but not the
calculated value (such as net sales)
• Update slowly changing dimension tables ‐ Use a Lookup
transformation to determine whether records already exist in the
target

Page 04‐3
Informatica PowerCenter Transformations

The Lookup Transformation can be configured in two ways, Connected and

Unconnected, to perform different types of lookups. These lookup
transformations receive input and send output in different ways.

• Connected ‐ A connected Lookup Transformation can be configured to

receive input directly from the mapping pipeline
• Unconnected ‐ An unconnected Lookup Transformation can be
configured to receive input from the result of an expression in another
transformation

A lookup condition has to be specified in the Condition tab. One input port
is needed for each column used in the lookup condition. The same input
port can be used more than once in a condition, and also multiple
diti
conditions can be
b specified.
ifi d

Page 04‐4
Informatica PowerCenter Transformations

To configure a Lookup Transformation the following components are

defined:

• Lookup source ‐ Lookup source can be created from the following

locations:
• Relational or flat file source or target definition in repository
• Source Qualifier definition in Mapping
• Table or file that the Integration service and client can
connect to
• Lookup Ports ‐ The Ports tab contains options similar to other
transformations, such as port name, data type and scale. In addition
to input and output ports, the Lookup Transformation includes a
lookup port type that represents columns of data in the lookup table.
A Unconnected
An U t d Lookup
L k Transformation
T f ti also
l includes
i l d a return
t portt
type that represents the return value
• Lookup Properties ‐ The Properties tab is used to configure
properties such as an SQL override for the lookup, the lookup table
name, and tracing level for the transformation. Most of the options
on this tab allow to configure caching properties
• Lookup Condition ‐ The condition or conditions that the Integration
service has to use is specified in the conditions tab

Page 04‐5
Informatica PowerCenter Transformations

The following steps describe how the PowerCenter Server processes a

connected Lookup transformation:
1. A connected Lookup transformation receives input values directly from
another transformation in the pipeline.
2. For each input row, the PowerCenter Server queries the lookup source
or cache based on
the lookup ports and the condition in the transformation.
3. If the transformation is uncached or uses a static cache, the
PowerCenter Server returns
values from the lookup query.
If the transformation uses a dynamic cache, the PowerCenter Server
inserts the row into
the cache when it does not find the row in the cache. When the
P
PowerCenter
C t Server
S finds
fi d
the row in the cache, it updates the row in the cache or leaves it
unchanged. It flags the
row as insert, update, or no change.
4. The PowerCenter Server passes return values from the query to the
next transformation. If the transformation uses a dynamic cache, you can
pass rows to a Filter or Router transformation to filter new rows to the
target.

Page 04‐6
Informatica PowerCenter Transformations

An unconnected Lookup transformation receives input values from the

result of a :LKP expression in another transformation. You can call the
Lookup transformation more than once in a mapping.
A common use for unconnected Lookup transformations is to update
slowly changing dimension tables.

Page 04‐7
Informatica PowerCenter Transformations

Steps to create a Lookup transformation:

1. In the Mapping Designer, choose Transformation | Create menu
option. Select the Lookup Transformation. Enter a name for the
lookup. The naming convention for Lookup Transformations is
LKP_TransformationName. Click OK.
2. In the Select Lookup Table dialog box choose the lookup table. Click
the Import button if the lookup table is not in the source or target
database.
3. To manually define the Lookup Transformation, click the Skip
button.
4. Define input ports for each Lookup condition.
5. For an Unconnected Lookup Transformation, create a return port
for the value you want to return from the lookup.
6 Define
6. D fi output
t t portst ffor th
the values
l you wantt to
t pass to
t another
th
transformation.
7. Add the lookup conditions.
8. On the Properties tab, set the properties for the lookup.
9. Click OK.
10. For Unconnected Lookup Transformations, write an expression in
another transformation using :LKP to call the Unconnected Lookup
Transformation.

Page 04‐8
Informatica PowerCenter Transformations

The Filter Transformation limits the rows sent to the target or other
transformations. All the rows from a source transformation can be passed
through the Filter Transformation based on a filter condition. All ports in a
Filter Transformation are input/output, and only rows that meet the
condition pass through the Filter Transformation. In some cases, you need
to filter data based on one or more conditions before writing it to targets.
For example, if you have a human resources target containing information
about current employees, you might want to filter out employees who
have resigned.

Page 04‐9
Informatica PowerCenter Transformations

The Filter Condition can be entered in the expression editor. The expression
editor is invoked by clicking on down arrow next to Filter Condition option
under the Properties tab of Edit Transformation window.

For example, if you want to filter out rows for employees whose salary is
less than $30,000, you enter the following condition:

SALARY < 30000

Multiple conditions can be defined using the AND and OR logical operators.
TRUE and FALSE values are implicit return values from any condition and
hence need not be specified in the condition expression. If the filter
condition evaluates to NULL, the row is assumed to be FALSE.

Page 04‐10
Informatica PowerCenter Transformations

Steps to create a Filter transformation:

1. In the Designer, switch to the Mapping Designer and open a mapping.
2. Choose Transformation | Create menu option. Select Filter
Transformation, and enter the name of the new transformation. Click
Create, and then click Done.
3. Select and drag all the desired ports from a source qualifier or other
transformation to add them to the Filter Transformation. After the
ports are selected and dragged, copies of these ports appear in the
Filter Transformation.
4. Double‐click the title bar of the new transformation.
5. Click the Properties tab. A default condition appears. The default
condition is TRUE (constant with a numeric value of 1).
6. Click the Value section of the condition, and then click the Open button.
Th E
The Expression
i Edit
Editor appears.
7. Enter the filter condition to be applied. Use values from one of the
input ports in the transformation as part of this condition. However,
values from output ports in other transformations can also be used.
8. Click Validate to check the syntax of the conditions entered. Fix syntax
errors before continuing.
9. Click OK.
10. Choose Repository | Save menu option to save the mapping.

Page 04‐11
Informatica PowerCenter Transformations

While a Source Qualifier Transformation can join data originating from a

common source database, the Joiner transformation joins two related
heterogeneous sources residing in different locations or file systems.

The combination of sources can be varied. The following sources can be

used:

• Two relational tables existing in separate databases

• Two flat files in potentially different file systems
• Two different ODBC sources
• Two instances of the same XML source
• A relational table and a flat file source
• A relational table and an XML source

Page 04‐12
Informatica PowerCenter Transformations

The Joiner Transformation is used to join two sources with at least one
matching port. The Joiner Transformation uses a condition that matches one
or more pairs of ports between the two sources. For example, you can join a
flat file with in‐house customer IDs and a relational database table that
contains user‐defined customer IDs. If two relational sources contain keys,
then a Source Qualifier Transformation can easily join the sources on those
keys. Joiner Transformations typically combine information from two
different sources that do not have matching keys, such as flat file sources.

One of the sources is specified as the master source, and the other as the
detail source. This is specified in the Ports tab of the transformation by
clicking the M (Master/Detail checkbox) column. When ports of a
transformation are added to a Joiner transformation, the ports from the first
source are automatically set as detail sources. Adding the ports from the
second transformation automatically sets them as master sources. The
master/detail relation determines how the join treats data from those
sources based on the type of join.

The Joiner Transformation supports the following join types, which are set in
the Properties tab:
• Normal (Default)
• Master Outer
• Detail Outer
• Full Outer

The condition of the join is a mandatory condition defining at least one field
from each data source that the transformation uses to perform the join.
These fields must be declared to be of the same data type. For example, the
following condition joins data from two sources based on an item ID:
ITEM_NO = ITEM_NO1

Note: A Sequence Generator or Update Strategy transformation cannot be

used as a source of a Joiner Transformation.

Page 04‐13
Informatica PowerCenter Transformations

Steps to create a Joiner Transformation:

1. In the Mapping Designer, choose Transformation | Create menu
option. Select the Joiner Transformation. Enter a name, click OK. The
naming convention for Joiner transformations is
JNR_TransformationName.
2. Enter a description for the transformation. This description appears in
the Repository Manager, making it easier for all to understand or
remember what the transformation does.
3. Drag all the desired input/output ports from the first source into the
Joiner Transformation. The Designer creates input/output ports for
the source fields in the Joiner, as detail port by default. This property
can be edited later.
4. Select and drag all the desired input/output ports from the second
source into
i t the
th Joiner
J i Transformation.
T f ti The
Th Designer
D ig configures
fig the
th
second set of source fields and master fields by default.
5. Double‐click the title bar of the Joiner Transformation to open the Edit
Transformations dialog box.
6. Select the Ports tab.

Page 04‐14
Informatica PowerCenter Transformations

7. Click any box in the M column to switch the master/detail relationship

for the sources. Change the master/detail relationship if necessary by
selecting the master source in the M column.
Note: Designating the source with fewer unique records as master
increases performance during a join.
8. Add default values for specific ports as necessary. Certain ports are
likely to contain NULL values, since the fields in one of the sources may
g database does
be empty. A default value can be specified if the target
not handle NULL values.
9. Select the Condition tab and set the condition.
10. Click the Add button to add a condition. Multiple conditions can be
added. The master and detail ports must have matching data types. The
Joiner Transformation only supports equivalent (=) joins:
11. Select the Properties tab and enter any additional settings for the
transformations.
12. Click OK.
13. Choose Repository | Save menu option to save changes done to the
mapping.

Page 04‐15
Informatica PowerCenter Transformations

The Rank Transformation is used to select only the top or bottom rank of
data. It can be used to return the largest or smallest numeric value in a
port or group.

The Rank Transformation differs from the transformation functions MAX

and MIN, in that it allows to select a group of top or bottom values, not
just one value. For example, use Rank to select the top 10 salespersons in a
given territory. Or, to generate a financial report, can also be used to
identify the three departments with the lowest expenses in salaries and
overhead. While the SQL language provides many functions designed to
handle groups of data, identifying top or bottom strata within a set of
rows is not possible using standard SQL functions.

All ports
t representing
ti g the
th same row sett can be
b connected
t d to
t the
th
transformation. Only the rows that fall within that rank, based on some
measure set during configuration, pass through the Rank Transformation.
Expression to transform data or perform calculations can also be written.

Page 04‐16
Informatica PowerCenter Transformations

Steps to create a Rank Transformation:

1. In the Mapping Designer, choose Transformation | Create menu option.
Select the Rank Transformation. Enter a name for the Rank. The
naming convention for Rank Transformations is
RNK_TransformationName.
2. Enter a description for the transformation.
3. Click OK, and then click Done. The Designer creates the Rank
Transformation.
4. Link columns from an input transformation to the Rank
Transformation.
5. Click the Ports tab, and then select the Rank (R) option for the port
used to measure ranks.
6. To create groups for ranked rows, select Group By for the port that
d fi
defines th
the group.
g
7. Click the Properties tab and select either the top or bottom rank. For
the Number of Ranks option, enter the number of rows to select for
the rank.
8. Click OK to return to the Designer.
9. Choose Repository | Save menu option.

Page 04‐17
Informatica PowerCenter Transformations

Flat Files can be used as sources and targets in a mapping. To do so, flat
file sources and targets must be defined in the repository. Flat file source
definitions can be imported or created in the Source Analyzer. Flat file
target definitions can be imported or created in the Target designer.

After the file source and target definitions are created, they can be used in
mappings.

Note: Because source definitions must exactly match the source,

Informatica recommends importing file source definitions instead of
creating them manually.

Page 04‐18
Informatica PowerCenter Transformations

Fixed‐width and delimited flat file source and target definitions can be
imported that do not contain binary data. When importing the definition,
the file must be in a directory local to the client machine. In addition, the
Integration service must be able to access all source files during the
Workflow. When a file source or target definition is created, the properties
of the file must be defined.

The Source Analyzer and the Target designer provides a Flat File Wizard,
which prompts for the following file properties:
• File name and location
• File code page
• File type
• Column names and data types
• Number of header rows in the file
• Column size and null characters for fixed‐width files
• Delimiter type, quote character, and escape character for delimited
files

Page 04‐19
Informatica PowerCenter Transformations

Steps to import a Flat file definition:

1. Open the Source Analyzer and choose File | Sources | Import menu
option. ‐or‐
Open the Target designer and choose File | Targets | Import menu
option. The Open Flat File dialog box appears.
2. Click OK. The contents of the file appear in the window at the bottom
of the Flat File Wizard.
3. Edit the following settings as necessary:
• Flat File Type
• A name for this source or target
• Start Import At Row
• Import Field Names From First Line
4. Click Next.
5 Follow
5. F ll th
the instructions
i t ti given
gi in
i the
th wizard
i d to
t manipulate
i l t the
th column
l
breaks in the file preview window. Move existing column breaks by
dragging them. Double‐click a column break to delete it. In case of a
de‐limited flat file, column breaks are automatically created based on
the delimiter.
6. Click Next.
7. Enter column information for each column in the file.
9. Click Finish.
10. Choose Repository | Save menu option to save.

Page 04‐20
Informatica PowerCenter Transformations

Page 04‐21
Informatica PowerCenter Transformations

Page 04‐22
Informatica PowerCenter Mapplets

Page 05‐1
Informatica PowerCenter Mapplets

Page 05‐2
Informatica PowerCenter Mapplets

A mapplet is a reusable object that is created in the Mapplet Designer. It

contains a set of transformations. It allows to reuse the transformation
logic in multiple mappings.

Page 05‐3
Informatica PowerCenter Mapplets

Mapplets help simplify mappings in the following ways:

• Include source definitions ‐ Multiple source definitions and source
qualifiers can be used to provide source data for a mapping
• Accept data from sources in a mapping ‐ The mapplet can receive source
data from the mapping by using an Input transformation to receive
source data
• Include multiple transformations ‐ A mapplet can contain as many
transformations as needed
• Pass data to multiple transformations ‐ A mapplet can feed data to
multiple transformations. Each Output transformation in a mapplet
represents one output group in a mapplet
• Contain unused ports ‐ Like a reusable transformation, a mapplet can
have input and output ports that are not used in a mapping. This helps to
d ig a mapplet
design l t for
f a range
g off uses

Page 05‐4
Informatica PowerCenter Mapplets

In addition to the transformation logic, a mapplet has the following

components.
• Mapplet input ‐ Data can be passed into a mapplet using source
definitions and/or Input transformations. An Input transformation is
used to connect a mapplet to the source pipeline in the mapping

• Mapplet output ‐ Each mapplet must contain one or more output

transformations to pass data from the mapplet into the mapping

• Mapplet ports ‐ Mapplet ports display only in the Mapping Designer.

Mapplet ports consist of input ports from Input transformations and
output ports from Output transformations. Other ports within the
mapplet are referred to as transformation ports. If a mapplet uses
source definitions
d fi iti rather
th than
th Input
I t transformations
t f ti for
f input,
i t it does
d
not contain any input ports in the mapping

Page 05‐5
Informatica PowerCenter Mapplets

An Input Transformation is used to receive input from a source in a

mapping. When the mapplet is used in a mapping, the Input
transformation provides input ports so data can be passed through the
mapplet. Each port in the Input transformation connected to another
transformation in the mapplet becomes a mapplet input port. Input
transformations can receive data from a single active source.

Page 05‐6
Informatica PowerCenter Mapplets

Every mapplet ends with a Mapplet Output Transformation. The data from
the mapplet passes to the mapping through the Mapplet Output
Transformation. A mapplet must contain at least one Output
transformation with at least one connected port in the mapplet. Each
connected port in an Output transformation displays as a mapplet output
port in a mapping. Each Output transformation in a mapplet displays as an
output group in a mapping. An output group can pass data to multiple
pipelines in a mapping.

A mapplet can be expanded in the Mapping Designer by selecting it and

choosing Mappings‐Expand from the menu. This expands the mapplet
within the mapping for view.

Page 05‐7
Informatica PowerCenter Mapplets

Page 05‐8
Informatica PowerCenter Mapplets

Steps to create and configure a mapplet:

1. Create a mapplet. Choose Mapplets | Create from the menu in the
Mapplet Designer. The recommended naming convention for mapplets
is mplt_MappletName.
2. Create Mapplet Transformation logic. Create and Link Transformations
in the same manner as in a mapping.
3. Create mapplet ports.

Page 05‐9
Informatica PowerCenter Mapplets

Normalization is the process of organizing data. In database terms, this

includes creating normalized tables and establishing relationships between
those tables according to rules designed to both protect the data and
make the database more flexible by eliminating redundancy and
inconsistent dependencies.
The Normalizer Transformation normalizes records from COBOL and
relational sources. It allows to organize the data according to the
requirements. A Normalizer transformation can appear anywhere in a data
flow when a relational source is normalized.
A Normalizer Transformation is used instead of the Source Qualifier
Transformation when a COBOL source has to be normalized. When a
COBOL source is dragged into the Mapping Designer workspace, the
Normalizer Transformation automatically appears, creating input and
output
t t portst ffor every column
l in
i the
th source. COBOL sources are often
ft
stored in a denormalized format. The OCCURS statement in a COBOL file
nests multiple records of information in a single record.
The Normalizer Transformation is used to break out repeated data within a
record into separate records. For each new record it creates, the
Normalizer Transformation generates a unique identifier. This key value is
used to join the normalized records. The Normalizer Transformation can
also be used with relational sources to create multiple rows from a single
row of data.

Page 05‐10
Informatica PowerCenter Mapplets

In the above example, the source flat file contains the year‐wise, monthly
sales information in one row. The objective is to split this one row of data
into multiple rows to get sales information by month.

Page 05‐11
Informatica PowerCenter Mapplets

The first step, after bringing in the source definition, would be to create a
Normalizer Transformation. Ports must be manually added under the
Normalizer tab – they cannot be dragged from other transformations.
Defining the output records structure here automatically creates the input
ports that are required to create the desired output. The value in the
Occurs column of the Normalizer tab designates the number of repeated
ports created in the Ports tab.

Page 05‐12
Informatica PowerCenter Mapplets

In the above example, the monthly sales will have the occurs value as 12.

Page 05‐13
Informatica PowerCenter Mapplets

For each new record it creates, the Normalizer transformation generates a

unique identifier called GKID_occurring_field_name. This key value can be
used to join the normalized records.

The Designer also generates one column (port) for each OCCURS clause in
the source file to specify the positional index within an OCCURS clause. The
naming convention for the Normalizer column ID is:
GCID_occurring_field_name.

Page 05‐14
Informatica PowerCenter Mapplets

As shown in the above Figure 5.7, this generated unique key is

GKID_MONTHLY_SALES and the generated column id is
GCID_MONTHLY_SALES for each OCCURS in the source. The Normalizer ID
columns informs about the order of records in an OCCURS clause. For
example, if a record occurs two times, when the Workflow is run, the
Integration service numbers the first record 1 and the second record 2. The
Normalizer column ID is also useful to pivot input columns into rows.

Page 05‐15
Informatica PowerCenter Mapplets

Steps to create a Normalizer Transformation:

1. In the Designer, create a new mapping or open an existing one.
2. Click and drag an imported source definition into the mapping. This
source definition can be a COBOL file, a simple data file or even a
relational source.
3. For a COBOL source, the Designer creates a Normalizer Transformation
by default. For others, manually create the Normalizer Transformation.
4. For the COBOL source, Designer connects the Normalizer
Transformation to the COBOL source definition. Open the new
Normalizer Transformation.
5. For a COBOL source, select the Ports tab and review the ports in the
Normalizer Transformation. For others, the port details will have to be
entered in the Normalizer tab.
6 Select
6. S l t the
th Normalizer
N li tab
t b and
d add
dd new output
t t ports.
t Add a portt
corresponding to each column in the source record that contains
denormalized data. The new ports only allow the number or string
datatypes. Only new ports can be created in the Normalizer tab.
7. Set the appropriate OCCURS value for the specific source data column.

Page 05‐16
Informatica PowerCenter Mapplets

Page 05‐17
Informatica PowerCenter Mapplets

Page 05‐18
Informatica PowerCenter Mapplets

Page 05‐19
Informatica PowerCenter Mapplets

Page 05‐20
Informatica PowerCenter Workflow and Session Log

Page 06-1
Informatica PowerCenter Workflow and Session Log

Page 06-2
Informatica PowerCenter Workflow and Session Log

Page 06-3
Informatica PowerCenter Workflow and Session Log

Page 06-4
Informatica PowerCenter Workflow and Session Log

Use workflow variables when you configure the following types of tasks:
Decision tasks. Decision tasks determine how the Integration Service
runs a workflow. For example, use the Status variable to run a second
session only if the first session completes successfully.
Links. Links connect each workflow task. Use workflow variables in links
to create branches in the workflow. For example, after a Decision task,
you can create one link to follow when the decision condition evaluates
to true, and another link to follow when the decision condition evaluates
to false.
Timer tasks. Timer tasks specify when the Integration Service begins to
run the next task in the workflow. Use a user‐defined date/time variable
to specify the time the Integration Service starts to run the next task.

Use th
U the following
f ll i g keywords
k d to
t write
it expressions
i ffor user‐defined
d fi d and
d
predefined workflow variables:
AND
OR
NOT
TRUE
FALSE
NULL
SYSDATE

Page 06-5
Informatica PowerCenter Workflow and Session Log

Note: For a complete of Task Specific Predefined workflow variables,

please refer to the documentation.

Page 06-6
Informatica PowerCenter Workflow and Session Log

$PMSessionName : This variable can be used in mappings or mapplets.

$PMWorkflowName: This variable can be used in a mapping, a mapplet, or
in workflow tasks such as Decision tasks, and links. You can also use
$PMWorkflowName in input fields that accept mapping or workflow
variables.
Note: For complete details of Built‐in variables, please refer to the
documentation.

Page 06-7
Informatica PowerCenter Workflow and Session Log

While creating a user defined work flow variable verify that the name of a
user defined workflow variable does not have a $ since single $ is reserved
for system variables. Use $$ instead.

Page 06-8
Informatica PowerCenter Workflow and Session Log

Use a user‐defined variable to determine when to run the session that

updates the orders database at headquarters.

To configure user‐defined workflow variables, set up the workflow as

follows:
1. Create a persistent workflow variable, $$WorkflowCount, to represent the
number of times the workflow has run.
2. Add a Start task and both sessions to the workflow.
3. Place a Decision task after the session that updates the local orders
database. Set up the decision condition to check to see if the number of
workflow runs is evenly divisible by 10. Use the modulus (MOD) function to
do this.
4. Create an Assignment task to increment the $$WorkflowCount variable
b one.
by
5. Link the Decision task to the session that updates the database at
headquarters when the decision condition evaluates to true. Link it to the
Assignment task when the decision condition evaluates to false. When you
configure workflow variables using conditions, the session that updates the
local database runs every time the workflow runs. The session that updates
the database at headquarters runs every 10th time the workflow runs.

Page 06-9
Informatica PowerCenter Workflow and Session Log

Page 06-10
Informatica PowerCenter Workflow and Session Log

Every Workflow created in the Workflow Manager, runs on a schedule. The

Integration service runs a scheduled Workflow unless the prior run of the
Workflow fails. When a Workflow fails, the Integration service removes the
Workflow from the schedule. A Workflow must be rescheduled manually in
the Workflow if a scheduled Workflow fails. By default, a Workflow runs on
demand.

Page 06-11
Informatica PowerCenter Workflow and Session Log

The schedule settings defined for a Workflow can be edited in the

scheduler if a reusable scheduler is created. The Integration service
reschedules the Workflow according to the new settings.

Non‐reusable scheduler settings can only be used in the Workflow that

they are defined in.

In case of a reusable scheduler, the same set of schedule settings can be

used for any Workflow in a folder.

The Workflow Manager marks a Workflow as “invalid” if the scheduler

associated with a Workflow is deleted.

Page 06-12
Informatica PowerCenter Workflow and Session Log

A Workflow can be scheduled to run continuously, repeat at a given time or

interval, or start manually.

The various options for the schedule settings are:

Run Options
 Run On Server Initialization ‐ The Integration service runs the
Workflow as soon as the service is initialized. The Integration service
then starts the next run of the Workflow according to the settings in
Schedule Options
 Run On Demand ‐ The Integration service runs the Workflow when
the Workflow is started manually
 Run Continuously ‐ The Integration service runs the Workflow as soon
as the service initializes. The Integration service then starts the next
run off th
the Workflow
W kfl as soon as it finishes
fi i h the
th previous
i run

Schedule Options
 Run Once ‐ The Integration service runs the Workflow once, as
scheduled in the scheduler
 Run Every ‐ The Integration service runs the Workflow at regular
intervals, as configured
 Customized Repeat ‐ The Integration service runs the Workflow on
the dates and times specified in the Repeat dialog box

Page 06-13
Informatica PowerCenter Workflow and Session Log

Steps to create a reusable scheduler:

1. Select Schedulers from Workflows menu.
2. Click New to add a new scheduler.
3. In the General tab, enter a name for the scheduler.
4. Configure the scheduler settings in the Scheduler tab.
5. Click on OK.

Steps to schedule a Workflow:

1. In the Workflow Designer, open the Workflow.
2. Choose Workflows | Edit.
3. In the Scheduler tab, choose Non‐reusable if you want to create a non‐
reusable set of schedule settings for the Workflow. Choose Reusable if
an existing reusable scheduler for the Workflow has to be selected.
N t If there
Note: th iis no reusable
bl scheduler
h d l iin ththe folder,
f ld it h has to
t be
b created
t d
before one chooses Reusable. The Workflow Manager displays a
warning message if there is no existing reusable scheduler.
4. Click the right side of the Scheduler field to select the reusable
scheduler.
5. Click on OK.

Page 06-14
Informatica PowerCenter Workflow and Session Log

To edit a Worklflow the menu option Workflows | Edit is used. When a

Workflow is edited, the repository updates the Workflow information
when the Workflow is saved. If a running Workflow is edited, the
Integration service uses the updated information the next time the
Workflow runs.

Page 06-15
Informatica PowerCenter Workflow and Session Log

When a Workflow is deleted, all non‐reusable Tasks and reusable Task

instances associated with the Workflow are deleted. Reusable Tasks used in
the Workflow remain in the folder when the Workflow is deleted.

If a Workflow that is running is deleted, then the Integration service aborts

the Workflow. If a Workflow that is scheduled to run is deleted, the
Integration service removes the Workflow from the schedule.

A Workflow can be deleted in the Navigator window, or the Workflow

currently displayed in the Workflow Designer workspace can be deleted.

• To delete a Workflow from the Navigator window, open the folder,

select the Workflow and press the Delete key
• To
T ddelete
l t a Workflow
W kfl currently
tl displayed
di l d ini the
th Workflow
W kfl Designer
D ig
workspace, choose Workflows‐Delete

Page 06-16
Informatica PowerCenter Workflow and Session Log

Command line mode: You invoke and exit pmcmd each time you issue a
command. You can write scripts to schedule workflows with the command
line syntax. Each command you write in command line mode must include
connection information to the Integration Service.
Interactive Mode: You establish and maintain an active connection to the
Integration Service. This lets you issue a series of commands.

Page 06-17
Informatica PowerCenter Workflow and Session Log

By default, the PowerCenter installer installs pmcmd in the \server\bin

directory.

When you run pmcmd in command line mode, you enter connection
information such as domain name, Integration
To run pmcmd commands in command line mode:
At the command prompt, switch to the directory where the pmcmd
executable is located.
Enter pmcmd followed by the command name and its required
options and arguments:
• pmcmd command_name [‐option1] argument_1 [‐
option2] argument_2...
Service name, user name and password in each command. For example, to
start
t t the
th workflow
kfl “wf_SalesAvg”
“ f S l A g” in i
folder “SalesEast,” use the following syntax:
D:\Informatica\powercenter8.5.1\server\bin>pmcmd startworkflow ‐sv
INTIM0045 ‐d Informatica8 ‐u INF1 ‐p INF1 ‐f INF1FLD wf_SalesAvg

Page 06-18
Informatica PowerCenter Workflow and Session Log

To run pmcmd commands in interactive mode:

1. At the command prompt, switch to the directory where the pmcmd
executable is located.
By default, the PowerCenter installer installs pmcmd in the \server\bin
directory.
2. At the command prompt, type pmcmd.
This starts pmcmd in interactive mode and displays the pmcmd> prompt. You
do not have to type pmcmd before each command in interactive mode.
3. Enter connection information for the domain and Integration Service. For
example:
connect ‐sv MyIntService ‐d MyDomain ‐u seller3 ‐p jackson
4. Type a command and its options and arguments in the following format:
command_name [‐option1] argument_1 [‐option2] argument_2...
pmcmd runs the command and displays the prompt again.
55. Type
yp exit to end an interactive session.
For example, the following commands invoke the interactive mode,
establish a connection to Integration Service
“MyIntService,” and start workflows “wf_SalesAvg” and “wf_SalesTotal”
in folder “SalesEast”:
pmcmd
pmcmd> connect ‐sv INTIM0045 ‐d Informatica8 ‐u INF1 ‐p INF1
pmcmd> setfolder INF1
pmcmd> startworkflow wf_SalesAvg
pmcmd> startworkflow wf_SalesTotal

Page 06-19
Informatica PowerCenter Workflow and Session Log

Page 06-20
Informatica PowerCenter Workflow and Session Log

The Session Log contains informational, warning, and error messages from
the session processes.

The session log includes a load summary that reports the number of rows
inserted, updated, deleted, and rejected for each target as of the last
commit point. The Integration service reports the load summary for each
session by default.

Page 06-21
Informatica PowerCenter Workflow and Session Log

Session Logs options are configured in the session properties. The following
information can be configured for a session log:
• Location ‐ The directory where the session log should be created. By
default, the Integration service creates the session log in the
directory configured for the $PMSessionLogDir server variable. A
different directory can be entered, but if the directory does not exist
or is not local to the Integration service that runs the session, the
session fails
• Name ‐ The session log can be named or the default name can be
accepted. The default name for the session log is s_mapping [Link]
• Archive ‐ By default, the Integration service does not archive session
logs. It creates one session log for each session and overwrites the
existing log with the latest session log. We can specify the number of
l g fil
log files to
t be
b created
t d by
b alterating
lt ti g the
th properties‐
ti Save
S session
i llogg
for these runs, in Task properties‐Config objects‐log options
• Tracing levels ‐ Setting a tracing level for the session can control the
type of information the Integration service includes in the session log.
By default, the Integration service uses tracing levels configured in
the mapping
The name and location of the session log can be configured on the
Properties tab of the session properties in the Workflow Manager.

Page 06-22
Informatica PowerCenter Workflow and Session Log

The amount of detail in the session log depends on the tracing level set.
Tracing levels can be defined for each transformation or for the entire
session. By default, the Integration service uses tracing levels configured in
the mapping.

Setting a tracing level for the session overrides the tracing levels configured
for each transformation in the mapping. If a normal tracing level or higher is
selected, the Integration service writes row errors into the session log,
including the transformation in which the error occurred and complete row
data.

Page 06-23
Informatica PowerCenter Workflow and Session Log

Generally the tracing level is kept at Normal. For debugging purpose, the
tracing level is set to Verbose Data to get a row by row data loading status.

Page 06-24
Informatica PowerCenter Workflow and Session Log

Session logs are text files that can be opened with any text editor. The
Integration service saves session logs in the directory specified in the
Session Log File Directory field in the session properties.

Session logs can be viewed through the Workflow Monitor. The Workflow
Monitor creates a temporary file that stores the session log. A session log
file can be viewed even if the session fails.

The Integration service generates the session log based on the Integration
service code page. The language of the session log can be specified based
on the locale of the machine hosting the Integration service .

Page 06-25
Informatica PowerCenter Workflow and Session Log

While designing the data warehouse, often there might be a requirement

to decide what type of information to store in targets. As part of the target
table design, we need to determine whether to maintain all the historic
data or just the most recent changes.
For example, there might be a target table, T_CUSTOMERS, that contains
customer data. When a customer address changes, there might be a need
to save the original address in the table, instead of updating that portion of
the customer row. In this case, a new row needs to be created containing
the updated address. The original row may be required to be preserved
with the old customer address. This illustrates how historical information
might be stored in a target table. However, if the T_CUSTOMERS table has
to be a snapshot of current customer data, the existing customer row can
be updated. The original address might be lost in this case. The model
chosen
h constitutes
tit t th
the update
d t strategy,
t t g h how tto h
handle
dl changes
h g to t existing
i ti g
rows.

Update strategy can be used at two different levels:

Within a session ‐ When a session is configured, the Integration service can
be instructed to either treat all rows in the same way (for example, treat all
rows as inserts), or use instructions coded into the session mapping to flag
rows for different database operations.
Within a mapping ‐ Within a mapping, the Update Strategy Transformation
is used to flag rows for insert, delete, update, or reject.

Page 06-26
Informatica PowerCenter Workflow and Session Log

To control how rows are flagged for insert, update, delete, or reject within
a mapping, add an Update Strategy transformation to the mapping. The
update strategy can be also be defined when a session is configured. All
rows can be flagged for insert, delete, or update. Alternatively the data
driven option can be selected, where the Integration service follows
instructions coded into Update Strategy Transformations within the
session mapping.

Page 06-27
Informatica PowerCenter Workflow and Session Log

• DD_INSERT ‐ Flags records for insertion in an update strategy

expression. DD_INSERT is equivalent to the integer literal 0

• DD_UPDATE ‐ Flags records for update in an update strategy

expression. DD_UPDATE is equivalent to the integer literal 1

• DD_DELETE ‐ Flags records for deletion in an update strategy

expression. DD_DELETE is equivalent to the integer literal 2

• DD_REJECT ‐ Flags records for rejection in an update strategy

expression. DD_REJECT is equivalent to the integer literal 3. DD_REJECT
is generally used to filter or validate data. If you flag a record as reject,
the Integration service skips the record and writes it to the session
reject file

Example:
The following expression marks items with an ID number of 1001 for
deletion, and all
other items for insertion:
IIF( ITEM_ID = 1001, DD_DELETE, DD_INSERT )

Page 06-28
Informatica PowerCenter Workflow and Session Log

Steps to create an Update Strategy Transformation:

1. In the Mapping Designer, add an Update Strategy Transformation to a
mapping.
2. Choose Layout | Link Columns.
3. Click and drag all ports from another transformation representing data
to be passed through the Update Strategy Transformation. In the
Update Strategy Transformation, the Designer creates a copy of each
port which is clicked and dragged. The Designer also connects the new
port to the original port. Each port in the Update Strategy
Transformation is a combination input/output port.
4. Normally, all of the columns destined for a particular target would be
selected. After they pass through the Update Strategy Transformation,
this information is flagged for update, insert, delete, or reject.
5 Open
5. O th
the Update
U d t Strategy
St t g Transformation
T f ti and d rename it.
it The
Th naming
i g
convention for Update Strategy Transformations is
UPD_TransformationName.
6. Click the Properties tab.
7. Click the button in the Update Strategy Expression field. The Expression
Editor appears.

Page 06-29
Informatica PowerCenter Workflow and Session Log

8. Enter an update strategy expression to flag rows as inserts, deletes,

updates, or rejects.
9. Validate the expression and click OK.
10. Click OK to save the changes.
11. Connect the ports in the Update Strategy Transformation to another
transformation or a target instance.
12. Choose Repository | Save.

Page 06-30
Informatica PowerCenter Workflow and Session Log

Page 06-31
Informatica PowerCenter Workflow and Session Log

Page 06-32
Informatica PowerCenter Additional Transformations

Page 07‐1
Informatica PowerCenter Additional Transformations

Page 07‐2
Informatica PowerCenter Additional Transformations

A Router Transformation is similar to a Filter Transformation because both

transformations allow to use a condition to test data. A Filter
Transformation tests data for one condition and drops the rows of data that
do not meet the condition. However, a Router Transformation tests data
for one or more conditions and gives the option to route rows of data that
do not meet any of the conditions to a default output group.

The Router Transformation is more efficient. For example, to test data

based on three conditions, only one Router Transformation needs to be
configured instead of three Filter Transformations to perform this Task.
Likewise, when a Router Transformation is used in a mapping, the
Integration service processes the incoming data only once. When multiple
Filter Transformations are used in a mapping, the Integration service
processes the
th incoming
i i g data
d t for
f each h transformation.
t f ti

Page 07‐3
Informatica PowerCenter Additional Transformations

Router Transformation tests data for one or more conditions and gives the
option to route rows of data that do not meet any of the conditions to a
default output group.

A Router Transformation has the following types of groups:

• Input Group
The Designer copies property information from the input ports of the
input group to create a set of output ports for each output group.
• Output Groups
There are two types of output groups:
• User‐defined groups – as per the user requirements, like in the
above diagram CITY and STATE are two user‐defined groups
• Default group ‐ Rows of data that do not meet any of the user
d fi d conditions
defined diti are routed
t d to
t a default
d f lt output
t t group
g

Page 07‐4
Informatica PowerCenter Additional Transformations

The Sorter Transformation can sort data from relational tables or flat files.
Sort takes place on the Integration service machine. Multiple sort keys are
supported. The Sorter Transformation is often more efficient than a sort
performed on a database with an ORDER BY clause.

The Sorter Transformation can also be used to sort data passing through
an Aggregator Transformation configured to use sorted input.

When a Sorter Transformation is created in a mapping, one or more ports

can be specified as a sort key and configure each sort key port to sort in
ascending or descending order.

Page 07‐5
Informatica PowerCenter Additional Transformations

Steps to create a Router Transformation:

1. In the Mapping Designer, open a mapping.
2. Choose Transformation | Create menu option. Select Router
Transformation, and enter the name of the new transformation. The
naming convention for the Router Transformation is
RTR_TransformationName. Click Create, and then click Done.
3. Select and drag all the desired ports from a transformation to add them
to the Router Transformation, or manually create input ports on the
Ports tab.
4. Double‐click the title bar of the Router Transformation to edit
transformation properties.
5. Click the Transformation tab and configure transformation properties as
desired.
6 Click
6. Cli k the
th Groups
G t b and
tab, d then
th click
li k the
th Add button
b tt to t create
t a user‐
defined group. The Designer creates the default group when the first
user‐defined group is created.
8. Click the Group Filter Condition field to open the Expression Editor.
9. Enter a group filter condition.
10. Click Validate to check the syntax of the conditions entered.
11. Click OK.
12. Connect group output ports to transformations or targets.
13. Choose Repository | Save menu option.

Page 07‐6
Informatica PowerCenter Additional Transformations

Steps to create a Sorter Transformation:

1. In the Mapping Designer, choose Transformation | Create. Select the

Sorter Transformation. The naming convention for Sorter
Transformations is SRT_TransformationName. Enter a description for
the transformation. This description appears in the Repository
Manager, making it easier to understand what the transformation does.
2. Enter a name for the Sorter and click Create. The Designer creates the
Sorter Transformation.
3. Click Done.
4. Drag the ports which are to be sorted into the Sorter Transformation.
The Designer creates the input/output ports for each port included.
5. Double‐click the title bar of the transformation to open the Edit
Transformations dialog box.
6. Select the Ports tab.
7. Select the ports to be used as the sort key.
8. For each port selected as part of the sort key, specify whether the
Integration service has to sort data in ascending or descending order.
9. Select the Properties tab. Modify the Sorter Transformation properties
as needed.
10. Click OK.
11. Choose
Ch R
Repository
it |S
Save to
t save changes
h to
t the
th mapping.
i

Page 07‐7
Informatica PowerCenter Additional Transformations

Database developers and programmers use stored procedures for various

Tasks within databases, since stored procedures allow greater flexibility
than SQL statements. Stored procedures also provide error handling and
logging necessary for critical Tasks. Developers create stored procedures
in the database using the client tools provided with the database.

A Stored Procedure Transformation is used to call a store procedure

created in the database to perform a query or calculation that would
otherwise be configured in a mapping.

For example, if there is a well‐tested stored procedure for calculating sales

tax created in the database, a Stored Procedure Transformation calling
the database stored procedure can be configured, instead of recreating
th same calculation
the l l ti in i an Expression
E i Transformation.
T f ti

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 Integration service.

Page 07‐8
Informatica PowerCenter Additional Transformations

Connected Stored Procedure Transformation

The flow of data through a mapping in connected mode also passes

through the Stored Procedure Transformation. All data entering the
transformation through the input ports affects the stored procedure. A
connected Stored Procedure Transformation should be used when
needed data from an input port sent as an input parameter to the stored
procedure, or the results of a stored procedure sent as an output
parameter to another transformation.

Unconnected Stored Procedure Transformation

The Unconnected Stored Procedure Transformation is not connected

directly
di tl to
t the
th flow
fl off the
th mapping.
i g It either
ith runs before
b f or after
ft the
th
session, or is called by an expression in another transformation in the
mapping.

Page 07‐9
Informatica PowerCenter Additional Transformations

Steps to create a Stored Procedure Transformation:

1. Create the stored procedure in the database and test it through the
provided database client tools.
2. Import or create the Stored Procedure Transformation, by providing
ports for any necessary input/output and return values.
3. Determine whether to use the transformation as connected or
unconnected. Determine how the stored procedure relates to the
mapping before configuring the transformation.
4. If connected, map the appropriate input and output ports. Click and
drag the appropriate input flow ports to the transformation, and create
mappings from output ports to other transformations.
5. If unconnected, configure it to run from an expression in another
t
transformation.
f ti The
Th expression
i can contain
t i variables,
i bl and d may or may
not include a return value.
6. Configure the session. The session properties in the Workflow Manager
includes options for error handling when running stored procedures
and several SQL override options.

Page 07‐10
Informatica PowerCenter Additional Transformations

A Sequence Generator Transformation can be used to generate numeric values for the
following:
• Create keys
• Replace missing values
• Cycle through a sequential range of numbers
NEXTVAL
The NEXTVAL port is used to generate a sequence of numbers by connecting it to a
transformation or target.
It is used to connect to a downstream transformation to generate the sequence based on
the Current Value and Increment By properties. It can be connected to multiple
transformations to generate unique values for each row in each transformation.
For example, NEXTVAL can be connected to two target tables in a mapping to generate
unique primary key values. The Integration service creates a column of unique primary key
values for each target table. The figure above illustrates connecting NEXTVAL to two target
tables in a mapping.
Approximately two billion primary or foreign key values can be created with the Sequence
G
Generatort by
b connecting
ti g the
th NEXTVAL portt to
t the
th desired
d i d transformation
t f ti or target
t g t and
d
using the widest range of values (1 to 2147483647) with the smallest interval (1).
CURRVAL
CURRVAL is the NEXTVAL value plus one or NEXTVAL plus the Increment By value. When a
row enters the transformation connected to the CURRVAL port, the Integration service
passes the last‐created NEXTVAL value plus one.

Page 07‐11
Informatica PowerCenter Additional Transformations

Steps to create a Sequence Generator Transformation:

1. In the Mapping Designer, select Transformation | Create. Select the

Sequence Generator transformation. The naming convention for
Sequence Generator Transformations is SEQ_TransformationName.
2. Enter a name for the Sequence Generator, and click Create. Click Done.
3. Double‐click the title bar of the transformation to open the Edit
Transformations dialog box.
4. Enter a description for the transformation. This description appears in
the Repository Manager, making it easier to understand what the
transformation does.
5. Select the Properties tab. Enter settings as necessary.
Note: Unlike other transformations, the Sequence Generator
T
Transformation
f ti properties
ti cannott b be overridden
idd att th
the session
i llevel.l
This protects the integrity of the sequence values generated.
6. Click OK.
7. To generate new sequences during a session, connect the NEXTVAL
port to at least one transformation in the mapping. The NEXTVAL or
CURRVAL ports can be used in an expression in other transformations.
8. Choose Repository | Save.

Page 07‐12
Informatica PowerCenter Additional Transformations

The Union transformation is a multiple input group transformation that

you can use to merge data from multiple pipelines or pipeline branches
into one pipeline branch. Using the Union transformation to merge data
from multiple sources is similar to using the UNION ALL SQL statement to
combine the results from two or more SQL statements.
You can connect heterogeneous sources to a Union transformation. The
Union transformation merges sources with matching ports and outputs
the data from one output group with the same ports as the input groups.
Rules and Guidelines
Consider the following rules and guidelines when you work with a Union
transformation:
• You can create multiple input groups, but only one output group.
• All input groups and the output group must have matching ports. The
i i
precision, datatype,
d t t and
d scale
l mustt b
be id
identical
ti l across allll g
groups.
• The Union transformation does not remove duplicate rows. To
remove duplicate rows, you must add another transformation such as
a Router or Filter transformation.
• You cannot use a Sequence Generator or Update Strategy
transformation upstream from a Union transformation.
• The Union transformation does not generate transactions.

Page 07‐13
Informatica PowerCenter Additional Transformations

Steps to create a Union Transformation:

1. In the Mapping Designer, select Transformation | Create. Select the

Union transformation. The naming convention for Union
Transformations is UN_TransformationName.
2. Enter a name for the Union Transformations, and click Create. Click
Done. The Designer creates the Union Transformation.
3. Double‐click the title bar of the transformation to open the Edit
Transformations dialog box.
4. Enter a description for the transformation. This description appears in
the Repository Manager, making it easier to understand what the
transformation does.
5. Click the Groups tab.
6 Add an input
6. i t group
g for
f each h pipeline
i li or pipeline
i li b branch h you wantt to
t
merge. The Designer assigns a default name for each group. The
groups can be renamed.
7. Click the Group Ports tab.
8. Add a new port for each row of data you want to merge. Enter port
properties such as name and datatype.
9. Click the Properties tab to configure the tracing level.
10. Click OK.
11. Choose Repository | Save to save changes.

Page 07‐14
Informatica PowerCenter Additional Transformations

Mappings contain two classes of transformations, standard and reusable.

Transformations created in a mapping are standard, they cannot be used
in any other mapping. If a transformation is common in multiple mappings
then, it can be made reusable.

Reusable Transformations are transformation objects which are defined

once and can be used multiple times in any number of mappings.
Reusable Transformations have an additional advantage in that every
instance of the transformation that appears in the mapping inherits any
changes made to the original transformation. The Designer stores each
reusable transformation as metadata separate from any mappings that
use the transformation.
Except for the Source Qualifier and the ERP Source Qualifier, all of the
t
transformation
f ti objects
bj t can be
b reusable.
bl
They can be created in two ways:
• By promoting a standard transformation in the Mapping
Designer
• By creating the transformation in the Transformation Developer
Once a reusable transformation has been created, it can only be edited in
the Transformation Developer.

Page 07‐15
Informatica PowerCenter Additional Transformations

When a Reusable Transformation is added to a mapping, a copy (or

instance) of the transformation gets added. The definition of the
transformation still exists outside the mapping.

The instance of a Reusable Transformation is a pointer to that

transformation. When the transformation in the Transformation Developer
is changed, its instances automatically reflect the changes. This feature
saves a great deal of work. Instead of updating the same transformation in
every mapping, the Reusable transformation can be updated once. All
instances of the transformation will automatically inherit the change.

Note: Instances do not inherit changes to property settings, only

modifications to ports, expressions, and the name of the transformation.

As seen in the above example, we can configure an Expression

Transformation to concatenate ‘First name’ and ‘Last name’. This
expression can now be used in any mapping where the concatenation of
names is required.

Page 07‐16
Informatica PowerCenter Additional Transformations

Steps to create a Reusable Transformation:

1. In the Designer, switch to the Transformation Developer.
2. Click the button on the Transformation toolbar corresponding to the
type of transformation to be created.
3. Click and drag within the workbook to create the transformation.
4. Double‐click the transformation title bar to open the dialog displaying its
properties.
5. Click the Rename button and enter a descriptive name for the
transformation, and click OK. The naming convention for Reusable
Transformations is reuTransformation_Name.
6. Click the Ports tab, then add any input and output ports needed for this
transformation.
7. Set the other properties of the transformation, and click OK. These
ti vary according
properties di g tto the
th transformation
t f ti created.
t d For
F example, l if
an Expression Transformation is created, an expression for one or more
of the transformation output ports is to be entered. If a Stored
Procedure Transformation is created, identify the stored procedure to be
called.
8. Choose Repository | Save.

Page 07‐17
Informatica PowerCenter Additional Transformations

Page 07‐18
Informatica PowerCenter Additional Transformations

Page 07‐19
Informatica PowerCenter Workflow Tasks

Page 08‐1
Informatica PowerCenter Workflow Tasks

Page 08‐2
Informatica PowerCenter Workflow Tasks

The Command Task can be used in two ways:

• Standalone Command Task – Command Task can be used anywhere in
the Workflow or Worklet to run shell commands
• Pre‐ and post‐session shell command – Command Task can be called as
the pre‐ or post‐ session shell command for a Session Task

Any valid UNIX command or shell script can be used for UNIX servers, or any
valid DOS command or batch file for Windows servers.
The status of the command (success or failure) is stored in the pre‐defined
variable @command_Task_name.STATUS.

For example, a command Task may be used to copy a file from one directory
to another. For a Windows Server the following DOS command will be used
t copy a file
to fil [Link]
SALES DAT from
f the
th source directory,
di t C:\SALES
C \SALES to
t the
th target
t g t
directory E:\MARKETING
COPY C:\SALES\[Link] E:\MARKETING
For a UNIX server, the following command will be used to perform a similar
operation:
Cp sales/[Link] /marketing
Each command runs in the same environment (UNIX or Windows) as the
Integration service.

Page 08‐3
Informatica PowerCenter Workflow Tasks

Steps to create a Command Task:

1. In the Workflow Designer or the Task Developer, click the Command
Task icon on the Tasks toolbar.
‐or‐
Choose Task | Create. Select Command Task for the Task type.
2. Enter a name for the Command Task. Click Create. Then click Done.
3. Double‐click the Command Task in the workspace to open the Edit
Tasks dialog box.
4. In the Commands tab, click the Add button to add a command.
5. In the Name field, enter a name for the new command.
6. In the Command field, click the Edit button to open the Command
Editor.
7. Enter the command which has to be performed. Enter only one
command d in
i th
the Command
C d Editor.
Edit
8. Click OK to close the Command Editor.
9. Repeat steps 3‐8 to add more Commands in the Task.
10. Click OK.

Page 08‐4
Informatica PowerCenter Workflow Tasks

The Workflow Manager provides an Email Task to send an email during a

Workflow. Re‐usable Email Tasks can be created in the Task Developer
for any type of email. Email Tasks created in the Workflow and Worklet
Designer are non‐reusable.

When a Workflow or Worklet is created the following types of email can

be included:
• Post‐session email. The session can be configured to send an email
when the session completes or fails.
• Suspension email. The Workflow can be configured to send an
email when the Workflow suspends.

For example, if the time taken for a session to complete has to be

ttracked,
k d the
th session
i can beb configured
fig d tot send d an emailil containing
t i i g the
th
time and date the session starts and completes.

An Email Task can also be used anywhere in a Workflow or Worklet. For

example, it can be included in a Workflow after a Command Task that
executes a shell script. The Workflow links can be configured for the
Integration service to send an email if the Command Task fails.

Page 08‐5
Informatica PowerCenter Workflow Tasks

Email variables and format tags can be used in an email message for
post‐session emails.

Using email variables, important session information, such as:

• Number of rows loaded
• Session completion time
• Read and write statistics, etc.
Can be included in the email. The session log or other relevant files can
also be attached to the email. Format tags for a tab (\t) and newline(\n)
can be used in the body of the message to make the message easier to
read.

Page 08‐6
Informatica PowerCenter Workflow Tasks

In the above figure you see a sample email text, which gives the following
details
• Name of the session and mapping
• Session completion time
• Number of records loaded

Page 08‐7
Informatica PowerCenter Workflow Tasks

Steps to create an Email Task in the Task Developer:

1. In the Task Developer, choose Tasks | Create.
2. Select an Email Task and enter a name for the Task. Click Create.
3. Click Done.
4. Double‐click the Email Task in the workspace. The Edit Tasks dialog
box appears.
5. Click Rename to enter a name for the Task.
6. Optionally enter a description for the Task in the Description field.
7. Click the Properties tab.
8. Enter the fully qualified email address of the mail recipient in the
Email User Name field.
9. Enter the subject of the email in the Email Subject field. Or, leave this
field blank.
10 Click
10. Cli k the
th Open
O b tt in
button i the
th Email
E il Text
T t field
fi ld to
t open the
th Email
E il Editor.
Edit
11. Enter the text of the email message in the Email Editor.
12. When the Email Task is used for post‐session email, incorporate
variables and format tags in the message. Leave the Email Text field
blank.
13. Click OK twice to save changes.

Page 08‐8
Informatica PowerCenter Workflow Tasks

When a Timer Task is used in a Workflow, the next Task in the Workflow
(after the timer Task) can be started at an exact time and date. After the
start time of another Task, a wait period can be set in the Workflow, or
Worklet before starting the next Task.

The Timer Task has two types of settings:

• Absolute time ‐ The exact time that the Integration service starts
executing the next Task in the Workflow is specified
• Relative time ‐ The Integration service will wait for a specified period
of time after the Timer Task, the parent Workflow, or the top‐level
Workflow starts

For example, if there are two sessions in a Workflow and the second
i should
session h ld start
t t 1 minute
i t after
ft th
the fi
firstt session
i completes;
l t A Ti
Timer
Task can be used after the first session.

Page 08‐9
Informatica PowerCenter Workflow Tasks

To coordinate the execution of the Workflow, the following types of events

are specified for the Event‐Wait and Event‐Raise Tasks:
• Pre‐defined event ‐ A pre‐defined event is a file‐watch event. For pre‐
defined events, an Event‐Wait Task is used to instruct the Integration
service to wait for the specified indicator file to appear before
continuing with the rest of the Workflow. When the Integration
service locates the indicator file, it starts the next Task in the
Workflow
• User‐defined event ‐ A user‐defined event is a sequence of Tasks in the
Workflow. An Event‐Raise Task is used to specify the location of the
user‐defined event in the Workflow. A user‐defined event is sequence
of Tasks in the branch from the Start Task leading to the Event‐Raise
Task

When all the Tasks in the branch from the Start Task to the Event‐Raise
Task complete, the Event‐Raise Task triggers the event. The Event‐Wait
Task waits for the Event‐Raise Task to trigger the event before continuing
with the rest of the Tasks in its branch.

Page 08‐10
Informatica PowerCenter Workflow Tasks

Event Raise Task

The Event‐Raise Task represents the location of a user‐defined event. A user‐
defined event is the sequence of Tasks in the branch from the Start Task to
the Event‐Raise Task. When the Integration service executes the Event‐Raise
Task, the Event‐Raise Task triggers the user‐defined event.

To use an Event Raise Task, a user‐defined event is declared first. Then, an

Event‐Raise Task is declared in the Workflow to represent the location of
the user‐defined event. In the Event‐Raise Task properties, the name of a
user‐defined event is specified.

Event Wait Task

The Event‐Wait Task waits for an event to occur. The event can be a user‐
defined or a pre‐defined event. Once the event triggers, the Integration
service continues executing the rest of the Workflow.

If the Event Wait Task has to wait for a user‐defined event then, it has to be
triggered by the Event‐Raise Task. The user‐defined event has to be
specified in this Task.

Page 08‐11
Informatica PowerCenter Workflow Tasks

One decision condition can be specified per Decision Task. After the
Integration service evaluates the Decision Task, the pre‐defined condition
variable can be used in other expressions in the Workflow to develop the
Workflow.

You can use the Decision task instead of multiple link conditions in a
workflow. Instead of specifying multiple link conditions, use the pre‐
defined Condition variable in a Decision task to simplify link conditions.

The Decision Task simplifies the Workflow. If a condition is not specified in

the Decision Task, the Integration service evaluates the Decision Task to
True.

Page 08‐12
Informatica PowerCenter Workflow Tasks

A Worklet is an object that represents a set of Tasks. When the Integration

service executes a Worklet, it expands the Worklet. The Integration service
then runs the Worklet as it would run any other Workflow, executing Tasks
and evaluating links in the Worklet.

The Worklet does not contain any scheduling or server information. To

execute a Worklet, it has to be included in a Workflow. The Workflow
Manager does not provide a parameter file or log file for Worklets. The
Integration service writes information about Worklet execution in the
Workflow log.

Page 08‐13
Informatica PowerCenter Workflow Tasks

Steps to create a Worklet:

1. In the Worklet Designer, choose Worklets | Create. The Create Worklets
dialog box appears.
2. Enter a name for the Worklet.
3. Click OK.
4. The Worklet Designer creates a Start Task in the Worklet.
5. Now, drag the required Tasks in the designer space as per the Worklet
logic.

Page 08‐14
Informatica PowerCenter Workflow Tasks

Page 08‐15
Informatica PowerCenter Workflow Tasks

Page 08‐16
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐1
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐2
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐3
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Use the following guidelines when you enter pre‐ and post‐session SQL
commands in the Source Qualifier transformation or target instance:
•You can use any command that is valid for the database type.
However, the Integration Service does not allow nested comments, even
though the database might.
•You can use mapping parameters and variables in the source pre‐ and
post‐session SQL commands.
•Use a semi‐colon (;) to separate multiple statements.
•The Designer does not validate the SQL.

Pre‐ and post‐session SQL commands can be entered on the Properties

tab of the target instance in a mapping.

Page 09‐4
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐5
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

When you create an SQL Query, you can either:

• Generate and edit the default query: If you want to use the existing
transformation options in the extract override, generate and edit the
default query. When the Designer generates the default query, it
incorporates all other configured options, such as a filter or number of
sorted ports. The resulting query overrides all other options you might
subsequently configure in the transformation

• Manually enter the entire query: The resulting query overrides all other
options configured in the transformation

Page 09‐6
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Steps:
1. Open the Source Qualifier transformation, and click the Properties tab.
2. Click the Open button in the SQL Query field. The SQL Editor dialog box
appears.
3. Click Generate SQL.
The Designer displays the default query it generates when querying rows
from all sources included in the Source Qualifier transformation.
4. Enter your own query in the space where the default query appears.
Every column name must be qualified by the name of the table, view, or
synonym in which it appears. For example, if you want to include the
ORDER_ID column from the ORDERS table, enter ORDERS.ORDER_ID. You
can double‐click column names appearing in the Ports window to avoid
typing the name of every column.
5 Select
5. S l t the
th ODBC data
d t source containing
t i i g the
th sources included
i l d d in
i the
th
query.
6. Enter the user name and password to connect to this database.
7. Click Validate.
The Designer runs the query and reports whether its syntax was correct.
9. Choose Repository‐Save.

Page 09‐7
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐8
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

General Rules:
Use the following rules and guidelines when you enter target update
queries:
• When you save a mapping, the Designer verifies that you have
referenced valid port names. It does not verify the accuracy of the SQL.
• A WHERE clause that does not contain any column references updates
all rows in the target table, or no rows in the target table, depending on
the WHERE clause and the data from the mapping.
For example, the following query sets the EMP_NAME to 'MIKE SMITH'
for all rows in the target table if any row of the transformation has
EMP_ID > 100.
UPDATE T_SALES set EMP_NAME = 'MIKE SMITH' WHERE :TU.EMP_ID >
100
• If the
th WHERE clause
l contains
t i no portt references,
f the
th mappingi g
updates the same set of rows for each row of the mapping.
For example, the following query updates all employees with EMP_ID >
100 to have the EMP_NAME from the last row in the mapping.
• UPDATE T_SALES set EMP_NAME = :TU.EMP_NAME WHERE EMP_ID >
100

Page 09‐9
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐10
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐11
Informatica PowerCenter Pre‐Post SQL , SQL Override, Update override

Page 09‐12
Informatica PowerCenter Indirect Files

Page 10‐1
Informatica PowerCenter Indirect Files

Page 10‐2
Informatica PowerCenter Indirect Files

To use multiple source files, you create a file containing the names and
directories of each source file you want the Integration service to use.
This file is referred to as a file list.
When you configure the session properties, enter the file name of the
file list in the Source Filename field and enter the location of the file list
in the Source File Directory field. When the session starts, the
Integration service reads the file list, then locates and reads the first file
source in the list. After the Integration service reads the first file, it
locates and reads the next file in the list.
The Integration service writes the path and name of the file list to the
session log. If the Integration service encounters an error while
accessing a source file, it logs the error in the session log and stops the
session.

Page 10‐3
Informatica PowerCenter Indirect Files

The source file type option in the Session properties enables you to select
whether the source file is direct or Indirect. The Direct or Indirect option
indicates whether the source file contains the source data, or whether it
contains a list of files with the same file properties. Choose Direct if the
source file contains the source data. Choose Indirect if the source file
contains a list of files.

Page 10‐4
Informatica PowerCenter Indirect Files

•In the Workflow Manager, open the session properties.

•Click the Mapping tab and open the Transformations view.
•Click the Properties settings in the Sources node.
•Select the flat file source instance in the Instances field
•In the Source File type field, choose Indirect.
•In the Source Filename field, replace the file name with the name of the
file list.
•Click OK.

Page 10‐5
Informatica PowerCenter Indirect Files

Page 10‐6
Informatica PowerCenter Indirect Files

Page 10‐7
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐1
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐2
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐3
Informatica PowerCenter Mapping Parameter And Mapping Variable

You can use mapping parameters and variables in a source qualifier in a

mapplet or mapping. When you use mapping parameters and variables in
a Source Qualifier transformation, the Designer expands them before
passing the query to the source database for validation. This allows the
source database to validate the query.
You cannot use mapping parameters and variables interchangeably
between a mapplet and a mapping. Mapping parameters and variables
declared for a mapping cannot be used within a mapplet. Similarly, you
cannot use a mapping parameter or variable declared for a mapplet in a
mapping.

Page 11‐4
Informatica PowerCenter Mapping Parameter And Mapping Variable

When the Integration Service needs an initial value, and you did not declare
an initial value for the parameter or variable, the Integration Service uses a
default value based on the data type of the parameter or variable.
The following table lists default Values for Mapping Parameters and
Variables Based on Data type
Data Default Value
String Empty string
Numeric 0
Datetime 1/1/1753 A.D. or 1/1/1
* when Integration Service is
configured for compatibility with 4.0.

Page 11‐5
Informatica PowerCenter Mapping Parameter And Mapping Variable

For example, you want to use the same session to extract transaction
records for each of your customers individually. Instead of creating a
separate mapping for each customer account, you can create a mapping
parameter to represent a single customer account. Then you can use the
parameter in a source filter to extract only data for that customer
account. Before running the session, you enter the value of the
parameter in the parameter file.
To reuse the same mapping to extract records for other customer
accounts, you can enter a new value for the parameter in the parameter
file and run the session.

Page 11‐6
Informatica PowerCenter Mapping Parameter And Mapping Variable

Before you run a session, define the mapping parameter value in a

parameter file for the session. You can use any constant value. During the
session, the Integration Service evaluates all references to the parameter
to the specified value. If the parameter is not defined in the parameter file,
the Integration Service uses the user‐defined initial value for the
parameter.

Page 11‐7
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐8
Informatica PowerCenter Mapping Parameter And Mapping Variable

To use a mapping parameter, perform the following steps:

1. Create a mapping parameter
2. Use the parameter
3. Define the parameter val
Step 1. Create a Mapping Parameter
In the Mapping Designer, choose Mappings‐Parameters and Variables. Or
to create parameters for a mapplet, in the Mapplet Designer, choose
Mapplet‐Parameters and Variables.
Click the Add button
Enter the Name of the Mapping parameter, select the type as Parameter,
select the appropriate datatype and precision for the Mapping parameter.
•Step 2. Use a Mapping Parameter
After you create a parameter, you can use it in the Expression Editor of any
transformation in a mapping or mapplet. You can also use it in Source
Qualifier transformations and reusable transformations.
•Step 3. Define a Parameter Value
Before you run a session, define values for mapping parameters in the
parameter file. When you do not define a parameter value, the Integration
Service uses the initial value for the parameter. If the initial value is not
defined, the Integration Service uses the default value for the parameter
data type.

Page 11‐9
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐10
Informatica PowerCenter Mapping Parameter And Mapping Variable

Mapping variables can be used to perform incremental reads of a

source. For example, suppose the customer accounts in the mapping
parameter example above are numbered from 001 to 065, incremented
by one. Instead of creating a mapping parameter, you can create a
mapping variable with an initial value of 001. In the mapping, use a
variable function to increase the variable value by one. The first time the
Integration Service runs the session, it extracts the records for customer
account 001. At the end of the session, it increments the variable by one
and saves that value to the repository. The next time the Integration
Service runs the session, it extracts the data for the next customer
account, 002. It also increments the variable value so the next session
extracts and looks up data for customer account 003.

Page 11‐11
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐12
Informatica PowerCenter Mapping Parameter And Mapping Variable

You might use a mapping variable to perform an incremental read of the

source. For example, you have a source table containing timestamped
transactions and you want to evaluate the transactions on a daily basis.
Instead of manually entering a session override to filter source data each
time you run the session, you can create a mapping variable,
$$IncludeDateTime. In the source qualifier, create a filter to read only rows
whose transaction date equals $$IncludeDateTime, such as:
TIMESTAMP = $$IncludeDateTime
In the mapping, you can use a variable function to set the variable value to
increment one day each time the session runs. If you set the initial value of
$$IncludeDateTime to 9/1/2000, the first time the Integration Service runs
the session, it reads only rows dated 9/1/2000. During the session, the
Integration Service sets $$IncludeDateTime to 9/2/2000. It saves 9/2/2000
t the
to th repository
it att the
th end
d off the
th session.
i The
Th nextt time
ti it runs th
the
session, it reads only rows from September 2, 2000.

Page 11‐13
Informatica PowerCenter Mapping Parameter And Mapping Variable

Start Value
The start value is the value of the variable at the start of the session. The
Integration Service looks for the start value in the following order:
•Value in parameter file
•Value in pre‐session variable assignment
•Value saved in the repository
•Initial value
•Default value
For example, you create a mapping variable in a mapping or mapplet and enter an
initial value, but you do not define a value for the variable in a parameter file. The
first time the Integration Service runs the session, it evaluates the start value of
the variable to the configured initial value. The next time the session runs, the
Integration Service evaluates the start value of the variable to the value saved in
the repository.
Current Value
The current value is the value of the variable as the session progresses. When a
session starts, the current value of a variable is the same as the start value. As the
session progresses, the Integration Service calculates the current value using a
variable function that you set for the variable. The current value can change as
the Integration Service evaluates the current value of a variable as each row
passes through the mapping. The final current value for a variable is saved to the
repository at the end of a successful session. When a session fails to complete,
the Integration Service does not update the value of the variable in the
repository.

Page 11‐14
Informatica PowerCenter Mapping Parameter And Mapping Variable

You can configure a mapping variable for a Count aggregation type

when it is an Integer or Small Integer. You can configure mapping
variables of any datatype for Max or Min aggregation types.
To keep the variable value consistent throughout the session run, the
Designer limits the variable functions you can use with a variable based
on aggregation type. For example, you can use the SetMaxVariable
function for a variable with a Max aggregation type, but not with a
variable with a Min aggregation type.

Page 11‐15
Informatica PowerCenter Mapping Parameter And Mapping Variable

SetMaxVariable. Sets the variable to the maximum value of a group of

values. To use the SetMaxVariable with a mapping variable, the
aggregation type of the mapping variable must be set to Max.

SetMinVariable. Sets the variable to the minimum value of a group of

values. To use the SetMinVariable with a mapping variable, the
aggregation type of the mapping variable must be set to Min.

SetCountVariable. Increments the variable value by one. In other

words, it adds one to the variable value when a row is marked for
insertion, and subtracts one when the row is marked for [Link]
use the SetCountVariable with a mapping variable, the aggregation
type of the mapping variable must be set to Count.

Page 11‐16
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐17
Informatica PowerCenter Mapping Parameter And Mapping Variable

Page 11‐18
Informatica PowerCenter FTP connection

Page 12‐1
Informatica PowerCenter FTP connection

Page 12‐2
Informatica PowerCenter FTP connection

Page 12‐3
Informatica PowerCenter FTP connection

•Connection name: The connection name used by the Workflow Manager.

•Host name: The name or IP address of the remote machine. Optionally, you
can specify a port number between 1 and 65535 inclusive. If you do not
specify a port number, the Integration Service uses the port number 21 by
default. Use the following syntax for specifying a host name: hostname:port‐
number
or
IP address:port‐number
When you specify a port number, enable that port number for FTP on the
host machine
•Default remote directory: The directory you want the Integration Service
to use by default.
In the session, when you enter a file name without a directory, the
I t g ti S
Integration Service
i appendsd th
the file
fil name tto this
thi directory.
di t Therefore,
Th f this
thi
path must be exact and contain the appropriate trailing delimiters. For
example, if you enter c:/data/ and in the session specify the file FILENAME,
the Integration Service reads the path and file name as c:\data\FILENAME

Page 12‐4
Informatica PowerCenter FTP connection

To create an FTP connection:

•In the Workflow Manager, connect to a repository.
•Choose Connections‐FTP. The FTP Object Browser appears
•Click New.
•Enter the FTP Connection name, username, password, hostname and
Default Remote Directory.
•Click OK.

Page 12‐5
Informatica PowerCenter FTP connection

Page 12‐6
Informatica PowerCenter FTP connection

Page 12‐7
Informatica PowerCenter Dynamic Lookup

Page 13‐1
Informatica PowerCenter Dynamic Lookup

Page 13‐2
Informatica PowerCenter Dynamic Lookup

Page 13‐3
Informatica PowerCenter Dynamic Lookup

Page 13‐4
Informatica PowerCenter Dynamic Lookup

Page 13‐5
Informatica PowerCenter Dynamic Lookup

When the Integration Service reads a row from the source, it updates the
lookup cache by performing one of the following actions:
•Inserts the row into the cache. The row is not in the cache and you
specified to insert rows into the cache. You can configure the
transformation to insert rows into the cache based on input ports or
generated sequence IDs. The Integration Service flags the row as insert
•Updates the row in the cache. The row exists in the cache and you
specified to update rows in the cache. The Integration Service flags the
row as update. The Integration Service updates the row in the cache based
on the input ports.
•Makes no change to the cache. The row exists in the cache and you
specified to insert new rows only. Or, the row is not in the cache and you
specified to update existing rows only. Or, the row is in the cache, but
b d on th
based the lookup
l k condition,
diti nothing
thi g changes.
h g The Th Integration
I t g ti Service
S i
flags the row as unchanged.

Page 13‐6
Informatica PowerCenter Dynamic Lookup

NewLookupRow. The Designer adds this port to a Lookup transformation

configured to use a dynamic cache. Indicates with a numeric value whether
the Integration Service inserts or updates the row in the cache, or makes
no change to the cache. To keep the lookup cache and the target table
synchronized, you pass rows to the target when the NewLookupRow
value is equal to 1 or 2.
Associated Port. Associate lookup ports with either an input/output port
or a sequence ID. The Integration Service uses the data in the associated
ports to insert or update rows in the lookup cache. If you associate a
sequence ID, the Integration Service generates a primary key for inserted
rows in the lookup cache.
Ignore Null Inputs for Updates. The Designer activates this port property
for lookup/output ports when you configure the Lookup transformation to
use a dynamic
d i cache.
h S Select
l t thi
this property
t when
h you d do nott wantt th
the
Integration Service to update the column in the cache when the data in
this column contains a null value.
Ignore in Comparison. The Designer activates this port property for
lookup/output ports not used in the lookup condition when you configure
the Lookup transformation to use a dynamic cache. The Integration
Service compares the values in all lookup ports with the values in their
associated input ports by default. Select this property if you want the
Integration Service to ignore the port when it compares values before
updating a row.

Page 13‐7
Informatica PowerCenter Dynamic Lookup

When the Integration Service reads a row, it changes the lookup cache
depending on the results of the lookup query and the Lookup
transformation properties you define. It assigns the value 0, 1, or 2 to the
NewLookupRow port to indicate if it inserts or updates the row in the
cache, or makes no change.

Page 13‐8
Informatica PowerCenter Dynamic Lookup

Page 13‐9
Informatica PowerCenter Dynamic Lookup

Page 13‐10
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐1
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐2
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Use this mapping when you do not you need to keep any previous
versions / history of dimensions in the table.

Page 14‐3
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐4
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐5
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

In the Type 2 Dimension/Version Data target, the current version of a

dimension has the highest version number and the highest incremented
primary key of the dimension.
Use the Type 2 Dimension/Version Data mapping to update a slowly
changing dimension table when you want to keep a full history of
dimension data in the table. Version numbers and versioned primary keys
track the order of changes to each dimension.

Page 14‐6
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐7
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐8
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐9
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐10
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

For example, in the data below, the versions are 0, 1, and 2. The highest
version number contains the current dimension data.
PM_PRIMARYKEY ITEM STYLES PM_VERSION_NUMBER
65000 Sandal 5 0

65001 Sandal 14 1

65002 Sandal 17 2

Page 14‐11
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

The Type 2 Dimension/Version Data mapping uses a Lookup and an

Expression transformation to compare source data against existing target
data. When you step through the Slowly Changing Dimensions Wizard,
you enter the lookup conditions (source key columns) and source columns
that you want the Integration Service to compare against the existing
target.
For each source row :
•without a matching primary key in the target, the Expression
transformation marks the row new.
•with a matching primary key in the target, the Expression compares user‐
defined source and target columns. If those columns do not match, the
Expression marks the row changed.
The mapping then splits into two separate data flows.
•The first data flow uses the Filter transformation, FIL_InsertNewRecord,
to filter out existing rows. The Filter transformation passes only new rows
to the UPD_ForceInserts Update Strategy transformation.
UPD ForceInserts inserts new rows to the target. A Sequence Generator
UPD_ForceInserts
creates a primary key for each row. The Expression transformation,
EXP_KeyProcessing_InsertNew, increases the increment between keys by
1,000 and creates a version number of 0 for each new row.
•In the second data flow, the FIL_InsertChangedRecord Filter
transformation allows only changed rows to pass to the Update Strategy
transformation, UPD_ChangedInserts. UPD_ChangedInserts inserts
changed rows to the target. The Expression transformation,
EXP_KeyProcessing_InsertChanged, increments both the existing key and
the existing version number by one.

Page 14‐12
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐13
Informatica PowerCenter Creating a Type 2 Dimension/Version Data Mapping

Page 14‐14
 Informatica Powercenter
Lab Book

Copyright © 2011 IGATE Corporation. All rights reserved. No part of this publication shall be
reproduced in any way, including but not limited to photocopy, photographic, magnetic, or other
record, without the prior written permission of IGATE Corporation.
IGATE Corporation considers information included in this document to be Confidential and
Proprietary.
 Informatica Powercenter

Table of Contents
Table of Contents........................................................................................... 2
Lab 1‐1 Create a Folder ..................................................................................... 3
Lab 2‐1 Analyze Source Data ............................................................................... 7
Lab 2‐2 Design a Target Schema .......................................................................... 18
Lab 2‐3 Create a Mapping ................................................................................. 25
Lab 3‐1 Creating Workflow ................................................................................ 40
Lab 3‐2 Start and Monitor Workflows.................................................................... 51
Lab 4‐1 Sales Summary .................................................................................... 59
Lab 5‐1 New Customer .................................................................................... 66
Lab 6‐1 Flat File Join ....................................................................................... 74
Lab 7‐1 Create a Mapplet .................................................................................. 80
Lab 7‐2 Quarterly‐Sales Mapping ......................................................................... 85
Lab 7‐3 Annual Sales Mapping ............................................................................ 95
Lab 8‐1 Updating Current Items .......................................................................... 99
Lab 9‐1 Listing Order Details ............................................................................ 103
Lab 10‐1 Router Transformation ........................................................................ 107
Lab 11‐1 Sequence Generator Transformation ......................................................... 113
Lab 12‐1 Stored Procedure Transformation ............................................................ 118
Lab 13‐1 Configure an Email Task ....................................................................... 124
Lab 14‐1 Configure a Command Task ................................................................... 128
Lab 15‐1 Event‐Wait task................................................................................. 130
Lab 16‐1 Pre‐SQL and Post‐SQL ......................................................................... 131
Lab 17‐1 Multiple Source Files(Indirect File) ........................................................... 132
Lab 18‐1 Mapping Parameter ........................................................................... 133
Lab 19‐1 Mapping Variable .............................................................................. 134
Lab 20‐1 FTP Connection ................................................................................ 135
Lab 21‐1 Dynamic Lookup ............................................................................... 136
Lab 22‐1 Using Mapping Wizard for SCD Type1 ........................................................ 138
Lab 23‐1 Using Mapping Wizard for SCD Type2........................................................ 144
Appendix A – Sources and Targets used in the Mappings ........................................... 148

Page 2 of 158
 Informatica Powercenter

Lab 1‐1 Create a Folder

Goals  Learn to navigate through PowerCenter Repository Manager

 Connect to the repository
 Create a folder, subject to appropriate privileges

Time 15 Minutes

Lab Setup  Informatica Client

 Login details for connecting to the repository

Start the Repository Manager

1. START | PROGRAMS | INFORMATICA POWERCENTER | CLIENT | POWERCENTER
REPOSITORY MANAGER.

Connect to the repository

1. In the Repository Manager’s Navigator Window, use any one of the methods given
below:
i. Double click on the repository (The repository name will be given by your
instructor), or
ii. Select the repository and select the menu option REPOSITORY | CONNECT, or

iii. Select the repository and click the connect icon in the toolbar.

2. In the Connect to Repository dialog box, enter the following details:

iv. Assigned Username.
v. Assigned Password.
3. Add Domain Name,Gateway Host,Gateway Port .
Note: Your faculty will provide the above details.

Page 3 of 158
 Informatica Powercenter

4. Click on the Connect button to connect to the repository.

Note: The Add Domain Name,Gateway Host,Gateway Port are required when you
connect to the repository for the first time. Subsequent connections to the repository
require only the Username and Password.

Create a Folder

1. Click on the repository and select FOLDER | CREATE.

2. Enter the following information:
 Name : TRGx (where x is your student number)
 Descriptions: Comments about the folder
 Click on the permission tab and select TRGx from the user name
Select TRGx as owner
3. Click OK.

Page 4 of 158
 Informatica Powercenter

Page 5 of 158
 Informatica Powercenter

Page 6 of 158
 Informatica Powercenter

Lab 2‐1 Analyze Source Data

Goals  Getting connected to PowerCenter Designer

 Understand the Source Analyzer tool
 Import source definitions into your folder
 Edit source definitions

Time 15 Minutes

Lab Setup Successful connection to the repository using PowerCenter Designer

28
Getting connected to PowerCenter Designer
1. START PROGRAMS | INFORMATICA POWERCENTER CLIENT | DESIGNER.
You can connect to the repository in the following ways:
i. Double click on the repository, or
ii. Select the repository and select the menu option REPOSITORY | CONNECT, or

iii. Select the repository and click on the icon.

5. Enter the Username and Password as follows

Username : TRGx
Password : TRGx
X is the student number assigned to you by your instructor.
6. Once you are connected you can see your folder name (TRGx) appearing in the Navigator
Window.
7. To view all objects in the folder, click on the + sign.

Page 7 of 158
 Informatica Powercenter

8. To open the folder, right click on the folder and select Open.

Note : An open folder is required in order to add, delete or modify objects. All of the
work is performed in the Workspace Window, to the right of the Navigator Window (i.e. ‐
Where the tools such as the Source Analyzer, Warehouse Designer, Mapplet Designer,
etc., are active). The objects created in the Workspace, will appear in the Navigator
Window.

Working with Source Analyzer

1. In the Designer’s Navigator Window, highlight your TRGx folder and select TOOLS |
SOURCE ANALYZER or open the folder. This opens the Source Analyzer window. You
can also click on the Source Analyzer button shown below to open Source Analyzer :

Page 8 of 158
 Informatica Powercenter

2. Notice the folder name and the repository names are displayed in the application title
bar and Open Folders drop‐down list.

Importing Source from Database

1. Select SOURCES | IMPORT FROM DATABASE.

i. The Import tables dialog box appears.

Page 9 of 158
 Informatica Powercenter

ii. Click on the icon, to create the data source.

Page 10 of 158
 Informatica Powercenter

Page 11 of 158
 Informatica Powercenter

iii. Click on Add in the ODBC Data Source Administrator box

Page 12 of 158
 Informatica Powercenter

iv. Select the DataDirect 5.2 Oracle Wire Protocol and enter the Data Source Name,
Description and Server Name.

v. Click on Test Connect to test connectivity.

vi. Enter the following information:
Enter appropriate oracle username and password
Username: LAB01TRGx(X is the student number assigned to you)
Password: oraclex

Page 13 of 158
 Informatica Powercenter

vii. If the connection to the database is successful, click on Apply or OK.

viii. Select the ODBC data source from the pull down list, which corresponds to the
location of the source tables (Oracle database).

9. Click on Connect after providing all the details.

Note: Your Instructor will provide the username and password.

10. In the Select Tables box, expand the owner name until you see a TABLES listing. Select the
EMPLOYEES table.
HINT: To select multiple tables, press the Ctrl key while selecting each table with a
single mouse click.

Page 14 of 158
 Informatica Powercenter

11. Click OK. The source table definition now appear in the Source Analyzer workbook.

Note: Using Designer’s Navigator Window, notice the source definition has also been
added to the Source section, or node, in your folder

Verify the source definitions

1. In the Source Analyzer workbook, for the source definition, expand the Key Types
column.

Page 15 of 158
 Informatica Powercenter

HINT: When the source definitions are in normal mode, hold the mouse over the
separator between the Key Types column and the Name column. When the mouse
turns into a bold double‐arrow, click and drag the mouse to the right to expand the
column.

Edit Source Definitions

1. In the Source Analyzer workbook, double‐click on the EMPLOYEES table. The Edit
Tables dialog box appears.
2. Select the Table tab.
3. In the Description Window, enter:
“This is the EMPLOYEES source table containing data of employees in the company”

4. Select the Columns tab.

5. Select the DEPT_ID column name.
6. In the Description window, type:
“This is the department id to which the employee belongs”

Page 16 of 158
 Informatica Powercenter

7. If you want to enter comments for additional columns, repeat the above steps.
8. Click on OK to save the comments and close the Edit Tables dialog box.
9. Select REPOSITORY | SAVE to save your folder in the repository.

Page 17 of 158
Informatica PowerCenter Lab 2-2

Lab 2‐2 Design a Target Schema

Goals  Understand the TARGET Designer tool

 Create logical target definition
 Add new columns into the target
 Create physical targets

Time 10 Minutes

Lab Setup A successful connection to the repository using PowerCenter Designer

28
Understand TARGET Designer

1. Select TOOLS | TARGET DESIGNER to open the TARGET Designer or click on the TARGET
Designer button as shown below.

2. Make sure you are in the Workbook mode, by selecting VIEW | WORKBOOK. You should
see the workbook tab.

Page 18 of 158
Informatica PowerCenter Lab 2-2

Page 19 of 158
Informatica PowerCenter Lab 2-2

3. Click on the Source Analyzer button. The tab now displays the Source Analyzer icon as
shown below.

4. Select WINDOW | NEW WINDOW. A new TARGET Designer Window is created.

5. In the Workbook Tab section, notice that now there are 3 tabs.

Page 20 of 158
Informatica PowerCenter Lab 2-2

6. If you need to switch back and forth between the tools, you can click on the
appropriate workbook tab, or select WINDOW | WINDOWS and select the window.

Design the target schema (Physical target not existing in database)

1. Select TARGETS | CREATE. The Create Target Table dialog box appears.
2. Enter the name of the target, Tgt_Employees_x.
3. Select the database type for the target table, i.e. Oracle.
4. Click on Create and Done.
5. The new table definition appears in the Target Designer workbook.
6. The target schema definition has also been added to your folder’s Targets section, or
node.
7. Double‐click on the Tgt_Employees_x table. The Edit Tables dialog box appears.

Page 21 of 158
Informatica PowerCenter Lab 2-2

8. Click on the Columns tab.

i. Add the following columns using icon.

ii. Once the table has been defined, close the Edit Tables dialog box
iii. Save the newly designed schema to the repository.

Design the target schema (Physical target existing in database)

1. Select TARGETS | IMPORT FROM DATABASE.

2. Enter login details as shown below or as specified by your Instructor.
3. Click on Connect, expand TRGX and Tables and select the target table.
4. Click on OK.

Page 22 of 158
Informatica PowerCenter Lab 2-2

5. The new table definition appears in the Target Designer workbook.

6. The target schema definition is added to your folder’s Targets section, or in the node in
the Designer’s Navigator Window.

7. Double‐click on the Tgt_Employees_x table. The Edit Tables dialog box appears. Click on
Rename under the Table tab.
8. Click on the Columns tab and note the columns in the table.
9. Save the newly designed schema to the repository.
Create the physical target in the database

Page 23 of 158
Informatica PowerCenter Lab 2-2

1. Click on the Tgt_Employees_x table to select it.

2. Select TARGETS | GENERATE/EXECUTE SQL from the menu. The Database Object
Generation dialog box appears:

3. In the Filename entry box, accept the default script file name, [Link].
4. Select the Selected tables radio button.

Note: Selecting the All tables radio button will write the code for all tables, which are in
the Warehouse Designer workspace window.

5. Under Generation options, make sure the Create Table, Primary Key, Foreign Key and
Drop Table options are checked.
6. Click on the Connect button.
7. Log in to the target database, using the proper ODBC connect string.
8. Click the Generate and Execute button.
9. If you receive a prompt asking if you want to overwrite the contents of the
[Link] file, click on OK.
10. The [Link] containing the DDL script to create the Tgt_Employees_x table is
created and runs against the target database.
11. Scroll through the contents of the Output Window to verify that the table was
successfully created.
12. Click on the Close button.

Page 24 of 158
Informatica PowerCenter Lab 2-3

Lab 2‐3 Create a Mapping

Goals  Understand the Designer’s Mapping Designer tool

 Create a mapping using:
 A source definition
 Source Qualifier and Expression Transformation
 A target definition
 Edit transformations
 Add new ports in transformations
 Add formulas in the Expression transformation
 Validate a mapping
 Understand Designer’s Output window

Time 30 Minutes

Lab Setup  A successful connection to the repository using PowerCenter

Designer
 A source definition created
 A target definition created

Solution

 Create a relational target table that contains the Name as a concatenation of First
Name and Last Name

TRANSFORMATION NAME TYPE DESCRIPTION

EMPLOYEES Relational Source Source Definition

Definition

SQ_EMPLOYEES_X Source Qualifier Data source qualifier for source

table

EXP_EMPLOYEE_NAME_X Expression Link all ports from Source Qualifier

to Expression transformation.
Contains the expression for
concatenation of First Name and
Last Name

TGT_EMPLOYEES_X Relational Target Target definition which contains

Table the concatenated name

Page 25 of 158
Informatica PowerCenter Lab 2-3

Mapping Layout

Source Qualifier Transformation

1. Select TOOLS | MAPPING DESIGNER, or click on the icon. The workbook changes
to the Mapping Designer.

Page 26 of 158
Informatica PowerCenter Lab 2-3

2. Select MAPPINGS | CREATE, or click the Create Mapping icon from the Mapping
toolbar.
3. Enter M_Employee_Name_x for the new mapping name
4. Click on OK.
5. Drag the source from the navigator to the Mapping Designer. Designer creates the
Source Qualifier by default, and connects it to the source as shown below:

6. If the Source Qualifier transformation does not appear by default, Select TOOLS |
OPTIONS and click on the TABLES tab. Ensure automatic creation by checking the
Create Source Qualifiers When Opening Sources checkbox.

Page 27 of 158
Informatica PowerCenter Lab 2-3

7. Drag the source again into the workspace. The Source Qualifier transformation
automatically appears and is connected to the source. Delete the previously dragged
source from the workspace by pressing the delete key.

Note: Use the automatic Source Qualifier creation when you want to create one Source
Qualifier for each source in your mapping and disable the automatic creation when you
want to join data from different sources.

Edit the Source Qualifier Transformation

1. Use either of the methods to invoke the Edit Transformations box.

2. Right click on the Source Qualifier and select Edit…

Page 28 of 158
Informatica PowerCenter Lab 2-3

i. Double click on the Source Qualifier transformation.

ii. Click on the Transformation tab and click on Rename to rename the
transformation.

Page 29 of 158
Informatica PowerCenter Lab 2-3

3. Enter the name and click on OK.

Create the Expression Transformation and link to the Source Qualifier

1. To create the Expression Transformation, use either of following methods:

2. Click on the expression transformation icon in the transformation toolbar. Drag

the pointer, which now appears as crosshairs , into the Workspace window to the
right of the Source Qualifier transformation
3. Select TRANSFORMATION | CREATE.

Page 30 of 158
Informatica PowerCenter Lab 2-3

i. In the Create Transformation Dialog box, select the type of transformation from
the pull‐down list. Enter a name for the transformation.

ii. Click on the Create button.

iii. Click on the Done button.

Note: The expression transformation appears in normal mode.

4. Link the following ports from the SQ_Employees_x to the new expression
transformation using any of the methods:

i. Select LAYOUT | LINK COLUMNS.

ii. Select the Link Columns icon in the toolbar .

HINT : Hold down the Ctrl or Shift key and select the ports in the SQ_Employees_x.
Drag them to an empty line on the expression transformation. When the mouse is
released, not only will the port names (including data types, precision and scale) will
be copied from SQ_Employees_x, but links connecting the ports between the two
transformations will also be created.

Page 31 of 158
Informatica PowerCenter Lab 2-3

Edit Expression Transformation

1. Double click on the header of the Expression transformation to enter Edit mode.
2. Click the Rename button under the Transformation tab.
3. Rename the transformation as Exp_Employee_Name_x.

Note: If the transformation was created via the TRANSFORMATION | CREATE menu
instead of the transformation toolbar, the object will have a name.
4. Click on the Ports tab.
5. Disable the output ports for FIRSTNAME and LASTNAME by removing the checkmark in
the ‘O’ (output) column – this will define the port as input only.

6. Select the FIRSTNAME port and click on the icon to add a new port and rename it
to Name. This will cause the new port to be positioned immediately after FIRSTNAME.
7. Verify whether the data type is string and increase the precision to 51.
8. Disable the input port for NAME by removing the checkmark in the ‘I’ (input) column‐
this will define the port as output only.
9. The Edit Transformations box should look as shown below:

Page 32 of 158
Informatica PowerCenter Lab 2-3

Create Expression Formula

1. Click the down arrow in the expression column of the NAME port. Add Port
Icon

2. Delete the text NAME in the Formula field in the Expression Editor.

3. Select the Ports tab as shown in the figure below.

Page 33 of 158
Informatica PowerCenter Lab 2-3

4. Double‐click on the port LASTNAME and note its presence in the Formula field. Based
on which port is selected, the port details appear under the ‘Instance Name:’ section of
the ports tab.

5. Create the expression as shown below:

Page 34 of 158
Informatica PowerCenter Lab 2-3

6. Click on Validate. This will parse the expression.

Page 35 of 158
Informatica PowerCenter Lab 2-3

7. Click on OK if the expression is successfully parsed.

8. Click OK to close the Edit Transformation dialog box.
9. To save the changes to the repository, select REPOSITORY | SAVE or Ctrl‐S.
10. The finished transformation will look like the following:

Link Expression Transformation and Target

1. Drag the target definition from Navigator Window into the workspace.
2. Link columns of the Expression Transformation to the target definition:
i. Select LAYOUT | AUTOLINK.
ii. In the Autolink dialog box, select the From Transformation and To
Transformation.

Page 36 of 158
Informatica PowerCenter Lab 2-3

iii. Click on Apply Now and OK.

3. Notice the links between expression transformation and target table.

4. The mapping is now complete and should look like the figure shown below:

Page 37 of 158
Informatica PowerCenter Lab 2-3

5. Select MAPPINGS | VALIDATE to validate the mapping.

6. Select REPOSITORY | SAVE or press Ctrl‐S to save the changes to the repository.
Note : Every time a repository save is executed, a series of validation checks are
performed on what has been changed.

Page 38 of 158
Informatica PowerCenter Lab 2-3

7. View the results of the Validation by locating Save tab of the Output window, at the
bottom of Designer.

Note: If the Validation results show ‘INVALID’, locate the last time stamp when the save
repository was executed and scan for the first error. The series of validation checks will
display all of the errors. Rectify the errors and validate the mappings again, until the
mapping is valid.

Page 39 of 158
Informatica PowerCenter Lab 3-1

Lab 3‐1 Creating Workflow

Goals  Create database connections for sources and targets

 Learn how to use Workflow Manager.
 Create a simple Workflow
 Create a session task and start task
 Link tasks

Time 30 Minutes

Lab Setup  Successful connection to the repository using Workflow Manager

 A valid mapping created

Create Relational Connection

1. Select START | PROGRAMS | INFORMATICA POWERCENTER | CLIENT | WORKFLOW
MANAGER.
2. Connect to the repository given by your Instructor.
3. Locate the assigned Studentx folder and open it.
4. Select CONNECTIONS | RELATIONAL.

Page 40 of 158
Informatica PowerCenter Lab 3-1

5. Select the database type from the dropdown and click on New.
6. In the Connection Object Definition box, enter the name db_src_x, username, password
and connection string to connect to the source database.
Note : Use the same login information used for creating the source definition in
Designer.

Page 41 of 158
Informatica PowerCenter Lab 3-1

7. To create a target database connection, repeat steps 1 and 2 and in the Connection
Object Definition box, enter the name db_tgt_x, username, password and connection
string to connect to the target database db_tgt_x.
Note: Use the same login information used for creating the target definition in
Designer.

8. Click CLOSE to close the Relational connection Browser.

Page 42 of 158
Informatica PowerCenter Lab 3-1

Create a Workflow

1. Switch to Workflow Designer.

i. Select TOOLS | WORKFLOW DESIGNER
(OR)
ii. Click on the button as shown below

2. Select WORKFLOWS | CREATE.

3. Enter the name of the Workflow as wf_Employee_Name_x in the Name box under the
General tab, (x represents the assigned student number).

Page 43 of 158
Informatica PowerCenter Lab 3-1

4. Click on the OK button. The Start Task is added to each new workflow by default
5. Select Repository | Save or Ctrl‐S to save.

Edit the Start Task

1. Double Click on the Start task

2. Click the Rename button
3. Type Start_Employee_Name_x in the Rename Task dialog box.

Page 44 of 158
Informatica PowerCenter Lab 3-1

Create a Session Task

1. Click on TASKS | CREATE and In the Create task dialog box enter a name for the task as
S_Employee_Name_x

2. Associate a mapping with the task. The mapping is M_Employee_Name_x, which you
just created.

Page 45 of 158
Informatica PowerCenter Lab 3-1

3. Click on OK in Mappings dialog box and Done in Create Task Dialog box.
4. A Session Task appears in the Workspace. Select REPOSITORY |SAVE.

Edit Session Task

1. Double‐click on the S_Employee_Name_x Session Task in the Workspace and click on

the Mapping tab.
2. Under the Sources folder, click on the down arrow to open the Relational Connection
Browser as shown in the figure below.

Page 46 of 158
Informatica PowerCenter Lab 3-1

3. In the Relational Connection Browser, select the database type from the dropdown and
select the source database connection just created, from the Objects box.

Page 47 of 158
Informatica PowerCenter Lab 3-1

4. Under the Target folder, click on the down arrow to open the Relational Connection
Browser and select the target database connection.
5. Under Properties, Select Normal for the Target load type.

Page 48 of 158
Informatica PowerCenter Lab 3-1

Link Workflow Tasks

1. Locate the Link icon on the right side of the Task Toolbar .
2. Link the Start_Employee_Name and the S_Employee_Name_x tasks.

Page 49 of 158
Informatica PowerCenter Lab 3-1

3. Toggle off the ‘link mode’ by clicking again on the Link icon or single click on one of the
objects.
4. Save changes to the repository.
5. Select WORKFLOW | VALIDATE to validate the workflow.
6. Locate the save tab in the Output Window at the bottom of the Workflow Manager and
view the results of the validation checks.

7. If there are any errors fix them and perform the WORKFLOW | VALIDATE command
from the main menu. The results will be located in the Output Window’s Validate tab.
8. Repeat the process until the Workflow is valid.

Page 50 of 158
Informatica PowerCenter Lab 3-2

Lab 3‐2 Start and Monitor Workflows

Goals  Different methods to start a Workflow

 Monitor a Workflow
 Create a simple Workflow
 Create a session task and start task
 Link tasks

Time 15 Minutes

Lab Setup Successful connection to the repository using Workflow Manager

A valid Workflow created

i.
Start and Monitor The Workflow
1. If the Workflow is valid, it is ready for execution. In the Workflow Designer, use one of
the following methods to start the wf_Employee_Name_x Workflow:
i. Select WORKFLOWS | START WORKFLOW.
ii. Right‐click in the Workspace and select Start Workflow or
iii. Right‐click on the wf_Employee_Name_x Workflow in the Navigator Window and
select Start Workflow.

2. To monitor a Workflow the Workflow Monitor must be opened. This is opened

automatically when a workflow is executed. If this does not happen perform the
following steps to start the Workflow Monitor
i. Select Start | Programs | Informatica PowerCenter Client | Workflow Monitor.
ii. To connect to Repository use one of the following methods:

In the Workflow Monitor Select REPOSITORY | CONNECT; or

Click on the icon in the toolbar; or double‐click on Repository in the Navigator

Window. The Connect To Repository box appears.
Note: Enter repository, server and login details as given by your Instructor.

iii. Select the repository from the Repository pull‐down list.

iv. Enter the Username in the Username box.
v. Enter the Password in the Password box.
vi. Select the Domain from the domain pull‐down list and connect to database.

Page 51 of 158
Informatica PowerCenter Lab 3-2

Page 52 of 158
Informatica PowerCenter Lab 3-2

3. Double click on the folder to view Workflow sessions.

4. You get two views, the Gantt Chart View and the Task View.

5. Select the Gantt View tab. This view displays details about workflow runs in
chronological format. It displays the following information as shown below:

Page 53 of 158
Informatica PowerCenter Lab 3-2

6. Select the Task View. This view displays details about workflow runs in a report format.
The Status column gives the following information:

Page 54 of 158
Informatica PowerCenter Lab 3-2

i. A Succeeded status if the PowerCenter Server was able to successfully complete a

Workflow or Task.
ii. A Failed status may occur if the PowerCenter Server failed the Workflow or Task
due to fatal processing errors.
iii. A Running status if the PowerCenter Server is still processing is still processing
Workflow or Task.

7. View the Session properties by doing one of the following:

i. Right click on the Session selected and select Get Run Properties; or

ii. Click on the Session Properties icon; or simply,

iii. In Properties window select Task Details.

8. The Properties tab of the S_Employee_Name_x dialog box opens. The Session should
display the number of Target Success Rows as shown below:

Page 55 of 158
Informatica PowerCenter Lab 3-2

9. Click on the Source/Target Statistics tab in Properties window. More detail on the
number of rows handled by the Server are shown here:

i. Applied rows are rows the Informatica Server successfully produced and applied
to the target without errors.

Page 56 of 158
Informatica PowerCenter Lab 3-2

ii. Affected rows are generated by the Server and ‘affected to’ (or accepted by) the
target.
iii. Rejected rows are either those read rows that the Server dropped during the
transformation process, or, the rows that were rejected when writing to the
target.

10. View Session Log to determine what occurred during the system run. To view detailed
Session information, do one of the following:
i. Right‐click on the Session in the Name column and select Get Session Log.

ii. Select the Session name and click on the icon.

iii. Select Get Session Log in Properties window

Page 57 of 158
Informatica PowerCenter Lab 3-2

Final Output

1. View the final output using the following SQL statement:

SQL> SELECT * FROM tgt_Employees_x;

Page 58 of 158
Informatica PowerCenter Lab 4-1

Lab 4‐1 Sales Summary

Goals  Create a mapping that gives the summary of all sales by item
description, state, and month
 Use multiple sources
 Create and use Expression and Aggregator transformations in the
mapping
 Use functions in the Expression transformation
 Group columns in the Aggregator transformation

Time 180 Minutes

Lab Setup Informatica PowerCenter Client and login details for connecting to Model
repository

ii.
Background

The Inventory system maintains details of items, stock available, orders placed and customer
related information. There are various requirements related to sales of an item. The company
requires sales summary information.

Solution

 To get a summary of sales by item description, state and month,

 Collect data from various relational tables sources to consolidate the information.
 Create a relational target containing the summary wise details are created.

TRANSFORMATION NAME TYPE DESCRIPTION

ITEMS Relational Source Source definitions

Definition
ORDER_ITEMS
ORDERS
STORES

SQ_SALES_SUMMARY_x Source Qualifier Data source qualifier for all source tables

EXP_SALES_SUMMARY_x Expression Link ITEM_DESC, PRICE, QUANTITY, DATE_ENTERED,

and STATE from the Source Qualifier. Create a
MONTH and YEAR port, and extract the month and

Page 59 of 158
Informatica PowerCenter Lab 4-1

year from the DATE_ENTERED.

AGG_SALES_SUMMARY_x Aggregator Link all ports except the DATE_ENTERED into the
Aggregator. Create ports to hold the TOTAL_SOLD
and TOTAL_PRICE. Create expressions in those ports
to calculate the total quantity sold and the total
price. You will want to select Group By for
ITEM_DESC, STATE, MONTH, AND YEAR.

TGT_SALES_SUMMARY_x Relational Target Target definition

Table

Mapping Layout

Page 60 of 158
Informatica PowerCenter Lab 4-1

Final Output

Page 61 of 158
Informatica PowerCenter Lab 5-1

Problem Solution
1. Import the tables ITEMS, ORDERS, ORDER_ITEMS, and STORES tables .
2. Observing the key relationships indicated by link lines between the tables, shown
below.

3. Create a target schema with name Tgt_SalesSummary_x having following columns:

DESCRIPTION,TOTAL_SOLD,TOTAL_PRICE,MONTH,YEAR,STATE .
4. Create a new mapping with name M_SalesSummary_x.

5. Create Source Qualifier Transformation using with name SQ_SalesSummary_x.

Page 62 of 158
Informatica PowerCenter Lab 5-1

6. Create the Expression transformation with name Exp_SalesSummary_x and

Link the following ports from the SQ_SalesSummary_x to the Expression
Transformation:
ITEM_DESC, PRICE, QUANTITY, DATE_ENTERED, and STATE

HINT: Select the Link Columns icon in the toolbar for Auto‐link.

7. Make DATE_ENTERED an input‐only port .

8. Add new ports MONTH,YEAR as output port only.
9. Configure the MONTH port by entering the expression:
TO_CHAR(DATE_ENTERED, ‘Month’)
10. Configure the YEAR port by entering the expression :
TO_CHAR(DATE_ENTERED, ‘YYYY’)
11. Create an Aggregator transformation with name Agg_SalesSummary_x.
12. Link the following columns from Exp_SalesSummary_x to the Aggregator
transformation:
ITEM_DESC, PRICE, QUANTITY, STATE, MONTH, and YEAR
13. Make PRICE and QUANTITY input‐only ports.
14. Add new ports for the TOTAL_QTY and TOTAL_PRICE.They will be output‐only ports
with expressions.
TOTAL_QTY : SUM(QUANTITY)
TOTAL_PRICE: SUM(QUANTITY * PRICE)
15. Check the GroupBy boxes on the lines for ITEM_DESC, STATE, MONTH, and YEAR These
are the columns by which we want to summarize.

Note : The order of GroupBy ports should be in the sequence as given above.

16. Link the appropriate ports from Agg_SalesSummary_x to Tgt_SalesSummary_x.

17. If the mapping is invalid, make changes and validate the mapping again till valid.
18. The Final mapping will look like the one given below.

Page 63 of 158
Informatica PowerCenter Lab 5-1

19. Create a Workflow by name wf_SalesSummary_x in Workflow Manager.

20. Under the Properties tab, note name of the Workflow Log File Name:
wf_SalesSummary_x.log
21. To create non‐reusable, local Metadata Extensions, click on the Metadata Extensions
tab
i. Enter Creation_Date and Author into the Extension Name.
ii. Do not select Reusable.

iii. Enter appropriate value for Creation_Date [using the syntax = mm/dd/yyyy] and
Author.
22. Create a Session task by name s_SalesSummary_x task and Select the
M_SalesSummary_x mapping from the list of valid mappings.
23. Enter the appropriate description for the session in the General tab.
24. Under the Properties tab, you can enter session log file name, session log file directory,
and other general session settings.

Page 64 of 158
Informatica PowerCenter Lab 5-1

25. Select the appropriate Source Database Connection and Target Database Connection
26. Run and monitor the Workflow.
27. Verify the results.

Page 65 of 158
Informatica PowerCenter Lab 5-1

Lab 5‐1 New Customer

Goals  Create a mapping which reads from a flat file and creates a relational
table consisting of new customers
 Analyze a fixed width flat file
 Configure a Connected Lookup transformation
 Use a Filter transformation to exclude records from the lookup
transformation pipeline

Time 120 Minutes

Lab Setup Successful Connection to the repository using PowerCenter Client

Background

The Marketing department is holding a special promotion for potential customers. The company
has purchased an industry listing from the Nielsen Research Company of their target market,
which includes potential new customers as well as many of their current customers.

Because this promotion is extended to new customers only, the company must first exclude
existing customers from this listing before the promotional mailing is sent.

Solution

 Build a new target table that will contain data only for the new customers
 Use the Informatica tools to import and analyze the source files and create a target
database table
 Use a Lookup Transformation to compare the Customer_ID from the flat file and the
relational table
 Use a Filter transformation to test the result of the lookup and filter out matches. When
no match is found for a given CUSTOMER_ID, the filter allows the potential customer
record into the relational table
 The relational table will contain the list of potential customers, which can now be used for
the promotional mailing

Page 66 of 158
Informatica PowerCenter Lab 5-1

Mapping Layout

TRANSFORMATION TYPE DESCRIPTION

NIELSEN Source Flat file source definition

SQ_NIELSEN_X Source Qualifier Data source qualifier for flat file

LKP_NEW_CUSTOMER_X Lookup Check the CUSTOMERS table in the source

database for occurrences of companies that are
listed in the flat file. The condition will check
NIELSEN.CUST_ID against
CUSTOMERS.CUSTOMER_ID

FIL_NEW_CUST_X Filter Pass through all records from NIELSEN that do not
match up with the CUSTOMER table (CUST_ID has
no corresponding CUSTOMER_ID)

TGT_NEW_CUST_X Target Target definition (Relational Table)

Page 67 of 158
Informatica PowerCenter Lab 5-1

Final Output

Page 68 of 158
Informatica PowerCenter Lab 5-1

Problem Solution

1. Import the [Link] flat file definition into the repository.

HINT: Be sure to set the Files of type: to All files (*.*) from the pull‐down list, before
clicking on OK.

i. Set the following options in the Flat File Wizard:

ii. Select Fixed Width and check the Import field names from first line box.
iii. This option will extract the field names from the first record in the file.
iv. Create a break line or separator between the fields.

Page 69 of 158
Informatica PowerCenter Lab 5-1

v. Structure of [Link] flat file is as shown below:

2. Change field name St to State and Code to Postal_Code and click finish.
NOTE: The physical data file will be present on the Server. At runtime, when the Server
is ready to process the data (which is now defined by this new source definition called
[Link]) it will look for the flat file that contains the data in [Link].
3. Name the new source definition NIELSEN. This is the name that will appear as metadata
in the repository, for the source definition.
4. Create a target definition based on the structure of the source file definition with name
Tgt_New_Cust_x.
Hint: From the Edit table properties in Target designer, change the database type to
oracle.
5. Create a new mapping with the name M_New_Customer_x
6. Create Lookup transformation with name Lkp_New_Customer_x.
7. Identify the Lookup table in the Lookup transformation. Use the CUSTOMERS table
from the source database to serve as the Lookup table and import it from the database.

Page 70 of 158
Informatica PowerCenter Lab 5-1

8. Create an input‐only port in Lkp_New_Customer_x to hold the Customer_Id value,

coming from SQ_NIELSEN_x .
9. Drag/Drop Cust_Id column from the SQ_NIELSEN_x to Lkp_New_Customer_x.
i. Make Cust_Id an input‐only port and Make CUSTOMER_Id a lookup and output
port.
ii. Add the lookup condition: CUSTOMER_ID = Cust_Id.

10. Click the Properties tab and note Connection Information .

Page 71 of 158
Informatica PowerCenter Lab 5-1

11. Create a Filter transformation that will filter through those records that do not match
the lookup condition and name it Flt_New_Cust_x.
12. Drag / drop all the ports from Source Qualifier to the new Filter and CUSTOMER_ID port
from Lkp_New_Customer_x .
13. Enter the filter condition: ISNULL(CUSTOMER_ID). This condition will allow only those
records whose value for CUSTOMER_ID is = null, to pass through the filter.
14. Link all ports except CUSTOMER_ID from the Filter to the Target table.
15. Given below is the final mapping.

16. Create a Workflow by name wf_New_Customer_x and a Session Task by name

s_New_Customer_x.
17. Select the Mapping tab.
18. Select the Source folder and verify the attribute settings are set to the following:
Source Directory path = $PMSourceFileDir\
File Name = [Link] (Use the same case as that present on the server)
Source Type: Direct
Note : For the session you are creating, the Server needs the exact path, file name and
extension for the file as it resides on the Server, to use at run time
19. Under Set File Properties button,Click on Advanced and Check the Line sequential file
format check box.
20. Set appropriate Database Connection for the Lkp_New_Customer transformation.
21. Run and monitor the Workflow.
22. Verify the results.

Page 72 of 158
Informatica PowerCenter Lab 5-1

Page 73 of 158
Informatica PowerCenter Lab 7-1

Lab 6‐1 Flat File Join

Goals  Analyze delimited files

 Join heterogeneous sources
Use Aggregator and Rank transformations to provide top ten revenue
producing items

Time 60 minutes

Lab Setup Successful connection to the repository using PowerCenter Client

Background

The Sales Department wants to use data contained in flat files to build a table summarizing the
revenue on each product by Product Code, Product Name, and Product Category. The weekly
orders from each store are consolidated into one orders file, and the IT organization has
downloaded from the Mainframe a flat file listing of each product sold by the company.

Solution
 Build a new target table that will contain only top ten selling items ranked by sales
revenue
 Use PowerCenter tools to import and analyze the source files and create a target
database table
 Use an Aggregator transformation to group
 Use a Rank transformation object to identify only top ten items to be sent to the target
tables

Page 74 of 158
Informatica PowerCenter Lab 7-1

Mapping Layout

TRANSFORMATION TYPE DESCRIPTION

ORDERS, PRODUCTS Sources Flat file source definitions

SQ_ORDERS, SQ_PRODUCTS Source Qualifier Data source qualifiers for flat file sources

JNR_ORDERS_PRODUCTS_X Joiner Join the heterogeneous sources on the

ITEM_NO field. The products file will be
your master source.

AGG_PRODUCT_REVENUE_X Aggregator Calculate total price and quantity for items

grouped by ITEM_NO, ITEM_NAME, and
PRODUCT_CATEGORY

RNK_TOPTEN_X Rank Rank the top ten revenue‐producing items

TGT_PRODUCTREVENUE_X Target Target definition (Relational)

iii.

Page 75 of 158
Informatica PowerCenter Lab 7-1

Final Output

Problem Solution

1. Import the [Link] and [Link] flat files definition into the repository.
2. Set the following options in the Flat File Wizard:
i. Select the Delimited radio button.

Page 76 of 158
Informatica PowerCenter Lab 7-1

ii. Name the source definition ORDERS,PRODUCTS.

iii. Enter the column names and specify data types and field widths as shown below.

3. Create the target table with name Tgt_ProductRevenue_x .It should have following
columns:
ITEM_NO,ITEM_NAME,PRODUCT_CATEGORY,PRICE,TOTAL_QUANTITY,TOTAL_REVEN
UE.
4. Create mapping with the name M_Product_Revenue_x.
5. DRAG the ORDERS and PRODUCTS source definitions.
6. Drag the Tgt_ProductRevenue_x target definition
7. Create the Joiner transformation Jnr_Orders_Products_x.
8. Link the ITEM_NO, ITEM_NAME, and PRODUCT_CATEGORY ports from SQ_Products
and ITEM_NO, QTY, and PRICE ports: from SQ_ORDERS (Source Qualifier) into
Jnr_Orders_Products_x (Joiner).
9. Identify all the ports from PRODUCTS as Master ports.
HINT: Check the M column checkbox for any one of the ports, which flow originally
from the PRODUCTS source definition.

Page 77 of 158
Informatica PowerCenter Lab 7-1

10. Add a new condition: ITEM_NO = ITEM_NO1.

11. Create an aggregator transformation with name Agg_ProductRevenue_x link it to
JNR_ORDERS_PRODUCTS.
12. Add TOTAL_QTY and TOTAL_REVENUE ports from Tgt_ProductRevenue_x (target
definition) into the Agg_ProductRevenue_x(Aggregator).
13. Group by ITEM_NO, ITEM_NAME, and PRODUCT_CATEGORY.
14. Enter aggregate expressions for the TOTAL_QUANTITY and TOTAL_REVENUE ports:
TOTAL_QUANTITY: SUM(QTY)
TOTAL_REVENUE : SUM(PRICE * QTY)
15. Create Rank Transformation with name Rnk_TopTen_x.
16. Link Agg_ProductRevenue to Rnk_TopTen .
17. Identify the TOTAL_REVENUE port as the one to rank.
18. Deselect the GroupBy options on the ITEM_NAME and PRODUCT_CATEGORY ports.
19. Select Top/Bottom = Top, and Number of Ranks = 10.
Connect the Rank transformation to the target table.

Page 78 of 158
Informatica PowerCenter Lab 7-1

20. Given below is the final mapping.

21. Create a Workflow by name wf_ProductRevenue_x and a session task by the name
s_ProductRevenue_x
22. Run and monitor the Workflow.
23. Verify the results for target table Tgt_ProductRevenue_x.

Page 79 of 158
Informatica PowerCenter Lab 7-1

Lab 7‐1 Create a Mapplet

Goals  Learn to create a mapplet

 Understand how to use variables

Time 60 minutes

Lab Setup PowerCenter Client connectivity

Background

The Sales Department is interested in getting both the quarterly and yearly sales. The Data Mart
server has a high CPU usage, so you would like to pre‐calculate these amounts and put them in
summary tables.

Solution

 Build a mapplet that uses multiple sources and aggregate functions

 Create a variable within the mapplet for use in the aggregate functions
 In the next lab, use this mapplet to give the quarterly sales

TRANSFORMATIONS TYPE DESCRIPTION

ITEMS Source Relational source definitions

ORDER_ITEMS
ORDERS

SQ_SALESBYQTR_X Source Qualifier Data source qualifier to join the source tables and
sort the data

AGG_ SALESBYQTR_X Aggregator Extract the month from the Date_Entered into a
variable port. Call to that variable port when
aggregating the quarterly sales

OUTPUT_SALESBYQTR_X Target Output transformation for the mapplet

Page 80 of 158
Informatica PowerCenter Lab 7-1

Mapplet Layout

Page 81 of 158
Informatica PowerCenter Lab 7-1

Mapplet output

Problem Solution

1. Create a Mapplet with the name MPLT_QtrSales_x. Import the tables ITEMS,
ORDER_ITEMS, and ORDERS.
2. Manually create a Source Qualifier SQ_SalesByQtr_x to pull in data from the above
three source definitions.
3. Create an Aggregator transformation and name it Agg_SalesByQtr_x. Copy and link
ITEM_ID and ITEM_NAME from the Source Qualifier (SQ_SalesByQtr_x) into the
aggregator (Agg_SalesByQtr_x). On the Columns tab, add the following ports:

YEAR string(4) Output

MONTH integer Variable

Q1Sales decimal(19,2) Output

Q2Sales decimal(19,2) Output

Q3Sales decimal(19,2) Output

Page 82 of 158
Informatica PowerCenter Lab 7-1

Q4Sales decimal(19,2) Output

4. Build the expressions for the Variable and Output ports as follows:

PORT EXPRESSION EXPRESSION

BEFORE VALIDATION AFTER VALIDATION

YEAR TO_CHAR(SQ_SalesByQtr_x.DATE_ENTERED, TO_CHAR(DATE_ENTERED,

‘YYYY’) ‘YYYY’)

MONTH GET_DATE_PART(DATE_ENTERED, ‘MM’) <no change>

NOTE: DATE_ENTERED is from Agg_SalesByQtr_x

not from SQ_SalesByQtr_x >

Q1Sales SUM(SQ_SalesByQtr_x.QUANTITY * SUM(QUANTITY * PRICE –

DISCOUNT, MONTH = 1 OR
SQ_SalesByQtr_x.PRICE –
MONTH = 2 OR MONTH = 3)
SQ_SalesByQtr_x.DISCOUNT, MONTH = 1 OR
MONTH = 2 OR MONTH = 3)

Q2Sales SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 4 <no change>

OR MONTH = 5 OR MONTH = 6)

Q3Sales SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 7 <no change>

OR MONTH = 8 OR MONTH = 9)

Q4Sales SUM(QUANTITY * PRICE – DISCOUNT, MONTH = <no change>

10 OR MONTH = 11 OR MONTH = 12)

5. Aggregate records by ITEM_ID, ITEM_NAME and YEAR.

6. Select the Properties tab. Check the Sorted Input box. Exit the Edit Transformation
dialog box.
7. Edit the Source Qualifier. The ports in the Source Qualifier must be in the same order
as the ports in the Aggregator, in order to facilitate the correct summarization by the
groupings you have specified, above.
8. Open the SQL query window. Click the Generate SQL button. Append the following
text to the end of the default SQL statement:
9. ORDER BY, ITEMS.ITEM_ID, ITEMS.ITEM_NAME, ORDERS.DATE_ENTERED.
10. Verify that there are no errors in the SQL. Exit the SQL editor and the Source Qualifier
Transformation.

Page 83 of 158
Informatica PowerCenter Lab 7-1

11. Create a mapplet output transformation by the name Output_SalesByQtr_x. Connect

all the output ports of the Aggregator to the mapplet Output transformation.
12. Verify the results of the mapplet validation in the Output Window.

Page 84 of 158
Informatica PowerCenter Lab 7-2

Lab 7‐2 Quarterly‐Sales Mapping

Goals  Use a mapplet in a mapping

 Configure a Normalizer transformation

Time 60 minutes

Lab Setup Quarterly sales mapplet created in the Lab 7‐1

Background

The Sales Department wants to run queries on sales by quarter. The Data Mart server has a high
CPU usage, so you would like to pre‐calculate these amounts and put them in summary tables.

Solution

 Use the quarterly sales mapplet as source for the mapping

 Use a Normalizer transformation to split the data into master and detail targets
 The master target will contain descriptive information about each item
 The detail target would contain a separate record for each quarterly sales figure
and will have a foreign key to the master table

TRANSFORMATION TYPE DESCRIPTION

MPLT_QTRSALES_X Mapplet Partially transformed data(Source)

NRM_SALESBYQTR_X Normalizer Split the data into a master/detail relationship, in

which the detail table contains a separate record for
each quarterly sales figure.

TGT_ITEMMASTER_X Target Descriptive information about each item

TGT_SALESBYQTR_X Target Quarterly sales figures with a foreign key to

ITEM_MASTER

28

Mapping Layout

Page 85 of 158
Informatica PowerCenter Lab 7-2

Page 86 of 158
Informatica PowerCenter Lab 7-2

Final Output

Page 87 of 158
Informatica PowerCenter Lab 7-2

SQL> select * from tgt_salesbyqtr_x;

ITEM_MASTER_ID QUARTER QUARTERLY_SALES

‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
1 1 489.5
1 2 1719.41
1 3
1 4 729
2 1 719.5
2 2 1084.48
2 3
2 4 2524
3 1
3 2 1534.47
3 3
3 4 249.5
4 1 669.5
4 2 159.69
4 3
4 4 840
5 1 177.5
5 2 372.01
5 3
5 4 543
6 1 759.5

ITEM_MASTER_ID QUARTER QUARTERLY_SALES

‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
6 2 1549.5
6 3
6 4 769.5
7 1 409.5
7 2 494
7 3
7 4
8 1
8 2 224.5
8 3

Page 88 of 158
Informatica PowerCenter Lab 7-2

8 4
9 1 76.5
9 2 82
9 3
9 4
10 1
10 2 281
10 3
10 4 83.5
11 1
11 2 64.9

ITEM_MASTER_ID QUARTER QUARTERLY_SALES

‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
11 3
11 4 129.3
12 1
12 2
12 3
12 4 168.5
13 1
13 2 29.4
13 3
13 4 29.4
14 1
14 2
14 3 51
14 4
15 1
15 2
15 3 849.5
15 4 1699.5
16 1
16 2 835
16 3 539.5

ITEM_MASTER_ID QUARTER QUARTERLY_SALES

‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
16 4
17 1
17 2 779.5
17 3

Page 89 of 158
Informatica PowerCenter Lab 7-2

17 4 1174.5
18 1
18 2 269.5
18 3
18 4 1115
19 1
19 2 71.5
19 3
19 4 30.5
20 1
20 2
20 3
20 4 310.7
21 1 30.5
21 2
21 3
21 4 112.5

ITEM_MASTER_ID QUARTER QUARTERLY_SALES

‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
22 1 54.5
22 2
22 3 125
22 4
23 1 205
23 2
23 3 59.5
23 4
24 1 49.5
24 2
24 3 129.5
24 4
25 1 79.4
25 2
25 3 169.3
25 4
26 1 988.7
26 2
26 3
26 4
27 1 109.4

Page 90 of 158
Informatica PowerCenter Lab 7-2

ITEM_MASTER_ID QUARTER QUARTERLY_SALES

‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
27 2 109.4
27 3
27 4
28 1 174
28 2 347.5
28 3
28 4
29 1 639.5
29 2
29 3
29 4 645
30 1 574.5
30 2
30 3
30 4 184.5
31 1 919.5
31 2
31 3
31 4 684.5
124 rows selected.

Problem Solution

1. Create a new mapping by the name M_SalesByQtr_x.

2. Drag the MPLT_QtrSales_x Mapplet from the Navigator Window into your
workspace.
3. Create the target table definition Tgt_ItemMaster_x table. The structure should be
as shown below:

Page 91 of 158
Informatica PowerCenter Lab 7-2

4. Create the target table definition for the Tgt_SalesByQtr_x table.

5. Make ITEM_MASTER_ID a FOREIGN_KEY.
6. Choose Tgt_Item_Master_x as the primary table.
7. Choose ITEM_MASTER_ID as the primary column.
8. Create a Normalizer Transformation with the name NRM_SalesByQtr_x and create
ports with the data types as shown below:

Page 92 of 158
Informatica PowerCenter Lab 7-2

9. Validate the Mapping and save it to the repository.

10. Given below is the final mapping.

11. Create a workflow by name wf_SalesByQtr_x and a session task by name

s_SalesByQtr_x.

Page 93 of 158
Informatica PowerCenter Lab 7-2

12. Select the Source and Target Database Connection.

13. Run and monitor the Workflow.
14. Verify the results.

Page 94 of 158
Informatica PowerCenter Lab 7-3

Lab 7‐3 Annual Sales Mapping

Goals  Use a mapplet

 Create a Rank Transformation

Time 60 minutes

Lab Setup Quarterly sales mapplet created in the Lab 7‐1

Background

The Sales Department wants to run queries on sales by quarter. The Data Mart server has a high
CPU usage, so you would like to pre‐calculate these amounts and put them in summary tables.

Solution

 Use the quarterly sales mapplet as source for the mapping

 Use a Rank transformation that will rank the data coming out of the mapplet and
compute an annual sales figure for each item. The end result should be a table containing
the top five selling items for each year

TRANSFORMATION TYPE DESCRIPTION

MPLT_QTRSALES_X Mapplet Partially transformed data (Source)

RNK_TOP5SALESBYYEAR_X Rank Add the quarterly sales figure to compute annual

sales figures. Rank the top 5 selling items for each
year

TGT_SALESBYYR_X Target Top 5 selling items for each year

Page 95 of 158
Informatica PowerCenter Lab 7-3

Mapping Layout

Final Output

Problem Solution

1. Create a new mapping by the name M_Top5SalesByYear_x.

2. Drag the MPLT_QtrSales_x Mapplet from the Navigator Window into your workspace
3. Create the target table definition Tgt_ItemSalesByYr_x and create the physical table
in the database. The structure should be as shown below:

Page 96 of 158
Informatica PowerCenter Lab 7-3

4. Create a Rank Transformation and name it Rnk_Top5SalesByYear_x.

5. Connect the ports from MPLT_QtrSales_x (mapplet) to the new Rank transformation.
6. Click on the Ports tab of the Rnk_Top5SalesByYear_x Rank transformation. Make
Q1Sales, Q2Sales, Q3Sales and Q4Sales input‐only ports and give a default value Zero
to each port.
7. Group by YEAR.
8. Add a new output port for rank column and name it as Year_Sales with the
expression:
Q1Sales + Q2Sales + Q3Sales + Q4Sales.
9. Click on the Properties tab.
i. Set the Top/Bottom attribute value to Top.
ii. Set the Number of Ranks attribute to 5.
10. Connect the ports in Rnk_Top5SalesByYear_x (Rank transformation) to the
Tgt_SalesByYear_x (target definition).
11. Validate the Mapping and save it to the repository.

Page 97 of 158
Informatica PowerCenter Lab 7-3

12. Given below is the final mapping:

13. Create a workflow by name wf_Top5SalesByYear_x.

14. Run and monitor the Workflow.
15. Verify the result.

Page 98 of 158
Informatica PowerCenter Lab 8-1

Lab 8‐1 Updating Current Items

Goals  Configure an Unconnected Lookup Transformation

 Configure an Update Strategy transformation
 Understand Tracing levels in session logs

Time 120 Minutes

Lab Setup Successful connection to the repository using PowerCenter Designer and
Workflow Manager

Background

The operational source system that supplies data to your data mart tracks all items that your
company has ever sold, even if they have since been discontinued. Your Sales Department wants
to run queries against a Data Mart table that contains only currently selling items. They don’t want
to use views or SQL, and they want this table updated on a regular basis.

Solution

 Use the operational source table ITEMS to build a new Data Mart table, CURRENT_ITEMS,
which will contain only current selling items
 Create an Unconnected Lookup transformation object to match source items against
current items in the Data Mart.
 Create an Update Strategy transformation to test the result of the lookup and
determine the appropriate row action to take on the first and subsequent runs of the
session.
 New current items will be inserted, discontinued items will be rejected, current items
already in the target will be updated and current items already in the target but
discontinued since the last session run will be deleted.

TRANSFORMATIN TYPE DESCRIPTION

ITEMS Source Operational source table (Relational)

SQ_ITEMS Source Qualifier Data source qualifier – no special overrides

necessary

LKP_ NEW_CUSTOMER_X Lookup Match ITEMS.ITEM_ID against

Tgt_CurrentItems_x.ITEM_ID

UPD_CURRENTITEMS_X Update Strategy Use the Update Strategy expression to populate

Page 99 of 158
Informatica PowerCenter Lab 8-1

the data in the target table

TGT_CURRENTITEMS_X Target Target definition (Relational)

Mapping Layout

Page 100 of 158

Informatica PowerCenter Lab 8-1

Final Output

Page 101 of 158

Informatica PowerCenter Lab 8-1

Problem Solution

1. Import source as ITEMS table.

2. Create a target definition having same ports as in source table and name it as
Tgt_CurrentItems_x.
3. Create a mapping called M_CurrentItems_x.
4. Create an Unconnected Lookup transformation to match ITEMS.ITEM_ID against
Tgt_CurrentItems_x.ITEM_ID and name it as LKP_CURRENT_ITEMS_x.
5. Add a new input port, ITEM_ID_IN, with the same data type as ITEM_ID.
6. Make ITEM_ID the R port.
7. Click the Properties tab.
8. Verify that the database connection is set to the correct target database string
provided by your Instructor.
9. Create an Update Strategy transformation.
10. The pseudocode for the logic is as follows:
if (the record doesn’t exist in the target table) then
if (the discontinued flag is not set) then
INSERT
else
REJECT
else if (record exists)
if (the discontinued flag is not set) then
UPDATE the record
else
DELETE the record
11. Create an expression for the above pseudo code and enter it in the Update Strategy
expression editor. The expression will call the Unconnected Lookup transformation.
12. Complete the mapping.
13. Create a Workflow by name wf_CurrentItems_x and Session Task by name
s_CurrentItems_x.
14. Run and monitor the Workflow.
15. Verify the results.

Page 102 of 158

Informatica PowerCenter Lab 9-1

Lab 9‐1 Listing Order Details

Goals  Create a mapping that uses a Sorter Transformation

Time 30 minutes

Lab Setup A connection to the repository using PowerCenter Designer and

Workflow Manager

Background

Company requires a report, which will show all order details in descending order of order amount.

Solution

 Import Order, Items, Order_Items tables from the database

 Calculate the total Order Amount for each Order
 Create a target, which will show the total order amount in descending order.

TRANSFORMATION NAME TYPE DESCRIPTION

ITEMS Relational Source Source definitions

Definition
ORDER_ITEMS
ORDERS

SQ_ORDERLISTING_X Source Qualifier Data source qualifier for all source tables

AGG_ ORDERLISTING_X Aggregator Link ports ORDER_ID, DATE_ENTERED, CUSTOMER_ID,

QUALNTITY, PRICE, DISCOUNT into the Aggregator.
Create an output port to hold the ORDER_AMOUNT

SRT_ ORDERLISTING_X Sorter Sorts Order Amount in descending order

TGT_ ORDERDETAILS_X Relational Target Target definition

Table

Page 103 of 158

Informatica PowerCenter Lab 9-1

Mapping Layout

Page 104 of 158

Informatica PowerCenter Lab 9-1

Final Output

28
28

Page 105 of 158

Informatica PowerCenter Lab 9-1

Problem Solution

1. Import all the sources from the database (Orders, Items, Order_Items).
2. Create target table with ports:
Order_Id(Primary key, Not Null), Date_Entered(Not Null), Customer_Id(Not Null) and
Order_Amount(Not Null).
3. Create a Source Qualifier transformation and name it SQ_OrderListing_x.
4. Create an Aggregator transformation and group on the Order_id column. Link ports
ORDER_ID, DATE_ENTERED, CUSTOMER_ID, QUANTITY, PRICE, DISCOUNT into the
Aggregator. Make QUANTITY, PRICE, DISCOUNT only input ports.
5. Add a new output port Order_Amount with the expression:
SUM(PRICE * QTY – DISCOUNT).
6. Create the Sorter Transformation and name it Srt_OrderListing_x.
7. Check the Key column of the Order_Amount port and select Descending.
8. Connect the Sorter Transformation to target table.
9. Your mapping should look like the one as given below:

10. Create a workflow by name wf_OrderListing_x and a session task by anme

s_OrderListing_x for.
11. Run and monitor the Workflow.
12. Verify the results.

Page 106 of 158

Informatica PowerCenter Lab 10-1

Lab 10‐1 Router Transformation

Goals  Create a mapping that uses a Router Transformation

Time 60 mins

Lab Setup A connection to the repository using PowerCenter Designer and

Workflow Manager

Background

The company requires Store wise order details.

Solution

 Calculate order amount for each order for each store

 Route the output based on store_id and load the data in different tables created for each
store
 Retrieve store wise order details

TRANSFORMATION NAME TYPE DESCRIPTION

ITEMS Relational Source Source definitions

Definition
ORDER_ITEMS
ORDERS
STORES

SQ_STOREORDERS_X Source Qualifier Data source qualifier for all source tables

AGG_ STOREORDERS_X Aggregator Link ports STORE_ID, ORDER_ID, DATE_ENTERED,

CUSTOMER_ID, QUALNTITY, PRICE, DISCOUNT,
STORE_DESC, ORDER into the Aggregator. Create an
output port to hold the ORDER_AMOUNT

RTR_STOREORDER_X Router Routes order details to different targets based on the

Store_id, which is the group filter conditions.

TGT_KAUAIFRANCHISE_X Relational Target Three target tables that will contain the order amount
Tables details for 3 different stores
TGT_MAUIFRANCHISE_X
TGT_OAHUFRANCHISE_X

Page 107 of 158

Informatica PowerCenter Lab 10-1

Mapping Layout

Page 108 of 158

Informatica PowerCenter Lab 10-1

Final Output

Page 109 of 158

Informatica PowerCenter Lab 10-1

Problem Solution

1. Import source tables from the database (Items, Orders, Order‐Items and Stores).
2. Create three target tables as shown below and name them as follows:
Tgt_KAUAIFRANCHISE_x , Tgt_MAUIFRANCHISE_x and Tgt_OAHUFRANCHISE_x.
3. The ports in all three target tables are as shown below:

4. Drag all columns from Source qualifier into the Aggregator transformation and group
on Store_id and Order_id.
5. Create an output port ORDER_AMOUNT with the expression:
SUM(PRICE * QUANTITY ‐ DISCOUNT).
6. Change PRICE, QUANTITY and DISCOUNT to input ports only.
7. Create a Router transformation with the name Rtr_StoreOrder_x. Link all the output
ports from Aggregator to Router.
8. Select the Groups tab and enter the values under Group Name and Group Filter
Condition as shown in the figure below:

Page 110 of 158

Informatica PowerCenter Lab 10-1

9. The router transformation will generate three groups : Kauai, Maui, Oahu and a
default group.
10. Link columns from each group to the respective targets. For example, the ports
under the Kauai group are linked to the Tgt_KAUAIFRANCHISE_x target. This target
table contains the order details for the store where store id = 2014.

Page 111 of 158

Informatica PowerCenter Lab 10-1

11. The final mapping will look like one given below:

12. Create a workflow by name wf_StoresOrders_x and a session task by name

s_StoresOrders_x.
13. Run and monitor the Workflow.
14. Verify the results.

Page 112 of 158

Informatica PowerCenter Lab 11-1

Lab 11‐1 Sequence Generator Transformation

Goals  Create a mapping that uses a Sequence Generator Transformation

Time 60 minutes

Lab Setup A connection to the repository using PowerCenter Designer and

Workflow Manager

Background

Customer source data arrives at each store in a flat file. Each file contains the customer name and
other customer details. However, there is no unique id to identify each customer. The unique id for
each customer will be generated through the mapping.

Solution

 Use a Sequence Generator transformation to generate a unique id for each customer

 Use this generated Customer id as the primary key in the target table

TRANSFORMATION NAME TYPE DESCRIPTION

CUSTOMERS Flat file Definition Source definition

SQ_CUSTOMERS Source Qualifier Data source qualifier

SEQ_CUSTID_X Sequence Generator Used for generating a sequence id for each customer

TGT_CUSTOMER_X Relational Contains the unique customer id for each customer.

Page 113 of 158

Informatica PowerCenter Lab 11-1

Mapping Layout

28

Page 114 of 158

Informatica PowerCenter Lab 11-1

Final Output

Problem Solution

1. Import source definition for Customers, which is a flat file uploaded on the server.
2. Create a target table and name it as Tgt_Customer_x, which is similar to the source.
Add the CUSTOMER_ID port as a Primary Key.
3. Create a mapping and name it as M_Custid_x.
4. Create the Sequence Generator transformation and name it as Seq_Custid_x.
5. Set the Start Value, End Value, Increment Value and other attributes as shown below.
Check the Reset box.

Page 115 of 158

Informatica PowerCenter Lab 11-1

6. Link NEXTVAL column of Sequence Generator to target table.

7. Link remaining columns from Source qualifier to target table.
8. Your mapping should look like the one shown below:

Page 116 of 158

Informatica PowerCenter Lab 11-1

9. Create a workflow by name wf_Custid_x and a session task by name s_Custid_x.

10. Run and monitor the Workflow.
11. Verify the results.

Page 117 of 158

Informatica PowerCenter Lab 12-1

Lab 12‐1 Stored Procedure Transformation

Goals  Create a mapping that uses a Connected and Unconnected Stored

Procedure Transformation

Time 60 mins

Lab Setup A connection to the repository using PowerCenter Designer and

Workflow Manager and mapping created in Lab 11‐1

Background

Customer source data arrives in a flat file from each store. At times, the customer names may
contain some invalid data. All customer names should be validated to check for spaces, digits,
special characters, etc. so that there is valid customer data in the Data Mart.

Solution

 Use a Connected Stored Procedure transformation to validate the customer name

 The customer name is passed as a parameter to the Stored Procedure
 The Stored Procedure returns a ‘V’ value for valid names and ‘I’ for invalid names

TRANSFORMATION NAME TYPE DESCRIPTION

CUSTOMERS Flat file Definition Source definition

SQ_CUSTOMERS Source Qualifier Data source qualifier

FLT_CHECKCUSTNAME_X Filter Allows rows with valid names to pass to the target

SEQ_CUSTOMERID_X Sequence Generator Used for generating a sequence id for each customer

SP_CHECKCUSTNAME_X Stored Procedure Receives the Customer Name as a parameter, validates

it, i.e. checks for unwanted characters, and returns a
flag ‘V’ if the name is valid and ‘I’ if the name is invalid

TGT_CUSTOMER_X Relational Contains customers whose names are valid

Page 118 of 158

Informatica PowerCenter Lab 12-1

Mapping Layout

Connected Stored Procedure:

Page 119 of 158

Informatica PowerCenter Lab 12-1

Unconnected Stored Procedure:

Page 120 of 158

Informatica PowerCenter Lab 12-1

Final Output

Problem Solution

Connected Procedure

1. Copy the mapping M_Custid_x and rename it as M_CheckCustName_x.

2. Create a connected Stored Procedure transformation and name it as
SP_CheckCustName_x.
3. Select the procedure name from the PROCEDURES folder.
4. The Stored Procedure transformation appears with two ports: Name and Flag.
5. Double click on the stored procedure transformation. Click on the Properties tab,
select the connection information as db_src_x.
6. Note : Your Instructor will provide login details to import the procedure and name of
the procedure. The procedure contains two parameters, Name which is an IN
parameter and FLAG, which is an OUT parameter.
7. Delete the existing links between the Source Qualifier and Tgt_Customer_x.
8. Link Firstname port from Source Qualifier into the Name port of the Stored Procedure
transformation.
9. Create a Filter transformation and link all ports from Source Qualifier into Filter
transformation. Link the FLAG port from Stored Procedure into the Filter.
10. Create the filter condition : FLAG = ‘V’. Link all ports except FLAG into the target.
11. The Sequence Generator transformation will generate the Customer_id in the target.
Only rows with valid customer names will pass to the target.
12. The final mapping should look as given below:

Page 121 of 158

Informatica PowerCenter Lab 12-1

13. Create a Workflow by name wf_CheckCustName_x.

14. Run and monitor the Workflow.
15. Verify the Results.

Page 122 of 158

Informatica PowerCenter Lab 12-1

Unconnected Stored Procedure

1. Using the same mapping, remove the existing Stored Procedure transformation.
2. Create the Stored Procedure transformation again. Do not link it to any other
transformation.
3. In the same mapping, create an Expression transformation before the Filter
transformation. Link relevant Ports.
4. To call the Stored Procedure from the Expression transformation, enter the
expression for the FLAG column, the newly added output port as:
5. :SP.SP_CheckCustName_x(FirstName, Proc_result).
6. FirstName is passed as a parameter to the Stored Procedure and the value returned
by the Stored Procedure will be available in the PROC_RESULT variable.
7. The final mapping is shown as below:

8. Create the Workflow by name wf_CheckCustName_Unconnected_x.

9. Run and monitor the Workflow.
10. Verify the results.

Page 123 of 158

Informatica PowerCenter Lab 13-1

Lab 13‐1 Configure an Email Task

Goals  Configure a Workflow to send an email to designated recipients when

the Integration service runs the workflow

Time 20 minutes

Lab Setup Configured Mail server and a valid Workflow

Background

People, who are authorized to receive the session status, get an email, once the session has
completed. The email gives details of number of rows loaded, rejected, time taken to complete,
etc. An email task can be configured for a session. The Email Task gives information about the start
time, completion time of the session, notification about the workflow status.

Solution

 Create an Email task and place it in a Workflow

1. Create an Email Task and name it as On_Success_Mail in Task Developer.
2. Double‐click on the email task. Click on the General tab, enter the description for the
task as: “sends an email when the session completes”

3. Select the Properties tab and enter the Email User Name and Email Subject details.

Page 124 of 158

Informatica PowerCenter Lab 13-1

4. Create one more Email task, give the name as On_Failure_Mail

and set its properties.
5. Switch to the Workflow Designer and drag the wf_OrderListing_x Workflow created
in Lab 9‐1.
6. Double‐click on the Session Task s_OrderListing_x.
7. Click on the Components tab.
8. Click On Success E‐Mail option select Reusable from the drop down list.

9. Click on the icon and select On_Success_Mail from the drop down list.

Page 125 of 158

Informatica PowerCenter Lab 13-1

10. Click on the icon (shown highlighted in the figure).

11. Enter the email text. Here you can select any post‐session built‐in Email variables,
useful for including important session information.

Page 126 of 158

Informatica PowerCenter Lab 13-1

12. Select the reusable Email task for On Failure E‐Mail. Enter the details required.
13. Run the Workflow.
14. Verify the results.
Note: The concerned people will receive an email regarding the status of the
Workflow, subject to mail server configuration.

Page 127 of 158

Informatica PowerCenter Lab 14-1

Lab 14‐1 Configure a Command Task

Goal  Configure a command task to delete reject files after successful

completion of a Workflow

Time 10 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

Background

Some reject files created during a Workflow run need to be deleted.

Solution

 A Command Task can be configured to specify shell or DOS commands, to delete reject
files, copy a file, or archive target files.
 Use the Command Task to delete reject files.

Workflow Layout

28

1. Create a Command task in Task Developer and name it as Command_Delete_x.

2. In the Commands tab, add a new command and name it as DeleteFiles.
3. Enter the command as shown below:
Note: The command can be any valid UNIX command or shell script for UNIX servers,
or, any valid DOS or batch file for Windows servers.

Page 128 of 158

Informatica PowerCenter Lab 14-1

Note: Check out the actual path for the reject files with the instructor.
4. Open the Workflow wf_Employee_Name_x create in Lab 3‐1.
5. Run the Workflow.
6. Verify the results.
Note: The commands specified in the Command Task are executed on the Informatica
Server. To verify the execution of the commands given in the Command Task you
need to have privileges to login to the Informatica Server and view the BadFiles
directory that has all the reject files.

Page 129 of 158

Informatica PowerCenter Lab 14-1

Lab 15‐1 Event‐Wait task

Goal  Create Event Wait Task and Event Raise task

Time 20 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create Workflow and Task

1. Create a New Workflow.
2. Create an Event Wait task.
3. Go to the Events tab of the Event wait task and select the Radio Button PreDefined.
4. Enter the following in the name of file to watch.
iii. D:\Informatica\PowerCenter9.5.1\server\infa_shared\SrcFiles\[Link]
(Any path on the Powercenter server)
5. Connect the Event wait task to a reusable Command task.
Note: The Command task may contain any command
e.g del D:\Informatica\PowerCenter9.5.1\server\infa_shared\SrcFiles\[Link]
6. Execute the worklow.
Note: The Event Wait Task will wait for the file to be present on the above path(on
the Powercenter server). After the file arrives on that path then only the Reusable
command task begins execution.

Page 130 of 158

Informatica PowerCenter Lab 14-1

Lab 16‐1 Pre‐SQL and Post‐SQL

Goal  Create Pre‐SQL and Pre‐SQL

Time 30 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create Mapping and Workflow

1. Create Mapping with the Source as Dept table and the target as TGT_DEPT table.
2. The Structure of the TGT_DEPT table is same as that of the Dept table.
3. The mapping does not have any transformations.
4. Create an index PKTGTDNO on DEPTNO column of TGT_DEPT table.
5. Create a Workflow with a session task. In the Session, Mapping tab ensure that the Target
Load type is BULK.
6. Execute the workflow.
7. The execution fails since BULK loading does not permit Index defined on it.

II. Pre‐SQL and Post‐SQL in target

1. In the Session, Mapping tab perform the following
2. In the Pre‐SQL Drop index PKTGTDNO.
3. In the Post‐SQL Create an index PKTGTDNO on DEPTNO column of TGT_DEPT.
4. Execute the work flow. The execution succeeds and the Target table gets populated. The
index gets created on the target table.
28 Note:
III. Pre‐SQL and Post‐SQL in source
1. In the Session, Mapping tab do the following
2. In the Post‐SQL Drop index PKSRCDNO.
3. In the Pre‐SQL Create an index PKSRCDNO on DEPTNO column of SRC_DEPT.
4. In the Session, Mapping tab ensure that the Target Load type is NORMAL.
5. Execute the work flow. The execution succeeds and the Target table gets populated. The
index gets created on the target table.

Note: Index may be required to be created on the source table before reading data, to
increase access speed, but it may be dropped in Post‐SQL since it might reduce
performance.

Page 131 of 158

Informatica PowerCenter Lab 14-1

Lab 17‐1 Multiple Source Files(Indirect File)

Goal  Use Multiple source files

Time 20 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create a File List

1. Create a file ([Link]) in a directory local to the PowerCenter Server. The file will
contain the names and directories of each source file to be used in the session.
D:\Informatica\PowerCenter9.5.1\server\infa_shared\SrcFiles\[Link]
D:\Informatica\PowerCenter9.5.1\server\infa_shared\SrcFiles\[Link]
D:\Informatica\PowerCenter9.5.1\server\infa_shared\SrcFiles\[Link]
D:\Informatica\PowerCenter9.5.1\server\infa_shared\SrcFiles\[Link]

Note: The paths mentioned above must be local to the PowerCenter Server machine.
The data present in all the above files is similar to the dept table in oracle.

II. Create a Mapping and Workflow

1. Create a mapping which uses one of the source flat files as the source destination and
TGT_DEPT as the target table.
2. The mapping does not have any transformations other than the source qualifier
transformation. Drag the fields from Source Qualifier transformation to the target.
3. Create a workflow and make the following changes to the session task :
4. Set the Source filename to [Link].
5. Set the Source filetype to Indirect.
6. Execute the workflow.
7. The target table will be populated with rows from all the source files.

Page 132 of 158

Informatica PowerCenter Lab 14-1

Lab 18‐1 Mapping Parameter

Goal  Create a Mapping Parameter

Time 45 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create Parameter File

1. Create a parameter file by the name Param_file.txt
and put it in srcfiles folder
([Link]:\Informatica\PowerCenter9.5.1\server\infa_shared\SrcFiles\)
The contents of the file are
[TRG2.S_MappingParameter]
$$PAY=2000
$$JO=MANAGER

II. Create Mapping

1. Create a New Mapping.
2. In the Mapping create parameters $$PAY and $$JO.
Hint: (Mappings‐>Parameters and Variables).
Note: Type is parameter.
3. Drag the Emp table from the sources to the Mapping (the source qualifier is added
automatically).
4. Create a Filter Transformation and in the Filter Condition enter:
SAL>$$PAY AND JOB=$$JO

III. Create Workflow

1. Create a workflow and a session task.
2. Make appropriate changes in the relational connection for the session task.
3. In the Properties tab of the Session put the parameter file name as:
$PMSourceFileDir\Param_file.txt
4. Execute the workflow.

Page 133 of 158

Informatica PowerCenter Lab 14-1

Lab 19‐1 Mapping Variable

Goal  Create a Mapping Variable

Time 45 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create source definition and target.

1. Import source definition for Customers, which is a flat file uploaded on the
server.
2. Create a target table, which is similar to the source. Add the CUSTOMER_ID port
as a Primary Key. Name the target as Tgt_Customer_x.

II. Create Mapping and Workflow.

1. Create a mapping with Source as Customers(flat file) and Target as
Tgt_Customer_x.
2. Create a mapping variable as $$rownum. Set its aggregation to Count.
3. Create an Expression transformation and drag all fields from Source Qualifier to
the expression transformation.
4. Create an output port in expression transformation and name it as o_rownum.
Let the data type be integer.
5. In expression editor for this output port enter the following:
SETCOUNTVARIABLE($$rownum)
6. Connect o_rownum in expression transformation to CUSTOMER_ID in target.
7. Connect the remaning fields from expression to the Target.
8. Save the mapping and create a session for the mapping.
9. Truncate Tgt_Customer_x table using following script:
Truncate table Tgt_Customer_x;
10. Execute the session and verify the result in Tgt_customers_x.
11. Verify the result in Tgt_customers_x table.

Note: The target table contains customer_id whose value ranges from 1 to 7. If the
same session is run again the values generated wil be from 8 onwards.
Right click on the session and click on view persistent values. It shows the value of
$$rownum which is 7. We can click on Reset values to reset the value.

Page 134 of 158

Informatica PowerCenter Lab 14-1

Lab 20‐1 FTP Connection

Goal  Use an FTP connection

Time 20 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create a Mapping
1. Import a flat file(stored on your local machine) in the source analyzer. Create a
target table in oracle with the same definition as that of source.
2. Drag the ports of the Source Qualifier transformation to the Target table.

II. Create a workflow

1. Goto the Workflow Manager.
2. Create FTP connection.
3. Give the username,password,hostname and default home directory.
e.g. If you are connecting to a linux server give the appropriate username and
password. Give the host name as [Link](IP address of the linux server) and the
default home directory as /home/testuser2(path where the source file resides).
Note: The above details may vary according to the FTP connection.
4. Create a Session task based on the mapping created above.
5. Select Edit Tasks‐>Mappping. Select Sources. Click on FTP. Select the FTP
connection created by you.
6. Click on override in the FTP connection browser. The host name and the default
directory are given. Enter the remote file name(E.g [Link])
7. Execute the workflow.
8. Observe that the target table(Relational) contains the rows from the the
source(FTP – Linux server).
Note: Ensure the the FTP service is started on the FTP server(Linux server in this
case).

Page 135 of 158

Informatica PowerCenter Lab 14-1

Lab 21‐1 Dynamic Lookup

Goal  Create a Dynamic Lookup

Time 45 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create source definition and target

1. Import source definition for Daily Transaction, which is a flat file uploaded on the
server(Daily_transaction.txt).
2. Create a target table using the script below:
CREATE TABLE Tgt_Accounts_x
(
ACC_KEY number NOT NULL,
ACC_NO number(12),
BALANCE number(10)
);

ALTER TABLE TGT_ACCOUNTS_X ADD PRIMARY KEY (ACC_KEY);

II. Create Mapping and Workflow

1. Create a mapping with Source as Daily_transaction(flat file) and Target as
Tgt_Accounts_x.
2. Create a Lookup Transformation. Use target table(Tgt_Accounts_x) as lookup table.
3. Drag Account_Number and Current_Balance columns from source to Lookup
Transformation.
4. Double click on Lookup Transformation.
5. Go to condition tab. Put the condition as follows:
ACC_NO= Account_number.
6. Go to properties [Link] the options Dynamic Lookup Cache and Insert Else
Update.
7. Go to ports tab. Change the datatype of ACC_KEY port to integer. Set Associated port
for ACC_KEY as Sequence‐ID, ACC_NO as Account_Number,BALANCE as
Current_Balance.
8. Create a filter transformation. Drag NewLookupRow,ACC_KEY, ACC_NO and
BALANCE from lookup transformation to filter transformation.
9. Open filter transformation. Go to properties tab and in filter condition put following
expression:

Page 136 of 158

Informatica PowerCenter Lab 14-1

NewLookupRow = 1 OR NewLookupRow = 2
10. Create an update strategy transformation. Drag all the ports from filter
transformation to update strategy transformation.
11. Open update strategy transformation. Go to properties tab and in Update Strategy
Expresion put following expression:
IIF(NewLookupRow=1, DD_INSERT,DD_UPDATE)
12. Connect ACC_KEY, ACC_NO and BALANCE columns to target.
13. Create workflow and session.
14. Ensure that in the session properties Data Driven is selected.
15. In session go to mapping tab. Click on target instance.
16. Check Insert and Update as update. Uncheck delete.
17. Save the workflow and execute it.
18. Verify the result in Tgt_Accounts_x table.

Note:
1. When you check Dynamic lookup cache option in lookup property
NewLookupRow port is automatically created.
2. When you change datatype of ACC_KEY from decimal to interger, Sequence‐Id
automatically comes in Associated port pulldown list.
3. New sequence number is not generated for rows where matching account
number is found. New sequence number is generated only for rows that are
inserted.
4. New sequence number always starts from ‘maximum value for key in table’+1.

Page 137 of 158

Informatica PowerCenter Lab 14-1

Lab 22‐1 Using Mapping Wizard for SCD Type1

Goal  Create a Type 1 mapping using Wizard

Time 45 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create source definition

1. Create tables Customers using following scripts:‐
CREATE TABLE CUSTOMERS
(
CUSTOMERID number(3) NOT NULL,
COMPANYNAME varchar2(34),
FIRSTNAME varchar2(10),
LASTNAME varchar2(8),
ADDRESS varchar2(29),
CITY varchar2(10)
);
ALTER TABLE CUSTOMERS ADD PRIMARY KEY (CUSTOMERID);
2. Use the flat file customers from the Srcfiles folder to populate data in the Customers
relational table.
3. Create a Mapping to perform the above mentioned task.
4. Create a Workflow, task to execute the table.
5. The Customers table contains the following data:

6. Import the Customers table in the Source Analyzer.

II. Create Mapping

1. In the Mapping Designer, choose Mappings‐Wizards‐Slowly Changing Dimension.
2. Enter a mapping name and select Type 1 Dimension, and click Next.

Page 138 of 158

Informatica PowerCenter Lab 14-1

3. Select the source as Customers.

4. In the New Target Table Enter the target name as T_Customers.
Note: This target table will just be added to the Repository. Drag the definition to the
Target Designer and Generate/Execute SQL to create the target table.

Page 139 of 158

Informatica PowerCenter Lab 14-1

5. Select the column Customer ID to use as a lookup condition from the Target Table
Fields list and click Add.
Note: The wizard adds selected columns to the Logical Key Fields list.
6. Select the columns City and Address. Click on Add to add in the list of compare for
changes.
Note: The wizard adds selected columns to the Fields to Compare for Changes list.
When you run the workflow containing the session, the PowerCenter Server
compares the columns in the Fields to Compare for Changes list between source rows
and the corresponding target (lookup) rows. If the PowerCenter Server detects a
change, it marks the row changed
7. Click Finish.
8. To save the mapping, choose Repository‐Save.

III. Create Workflow

1. Create a workflow and session task to execute the mapping.
2. The target table gets populated will all the rows from source.
3. Update the Source table with the following statement:
update CUSTOMERS set companyname = 'PATNI'
WHERE CUSTOMERID=101;
4. Commit the transaction.
5. Execute the session task.
Note: The change made in the source is NOT reflected in the target since the field
companyname was not chosen in the list of Compare for changes.
6. Update the Source table with the following statement:
update CUSTOMERS set city = 'Mumbai' WHERE CUSTOMERID=101;
7. Commit the transaction.
8. Execute the session task.
Note: The change made in the source is reflected in the target since the field city was
chosen in the list of Compare for changes.

IV. Explanation of the Mapping:

The Type 1 Dimension mapping performs the following tasks:
1. Selects all rows.
2. Caches the existing target as a lookup table.
3. Compares logical key columns in the source against corresponding columns in the
target lookup table.
4. Compares source columns against corresponding target columns if key columns
match.

Page 140 of 158

Informatica PowerCenter Lab 14-1

5. Flags new rows and changed rows.

6. Creates two data flows: one for new rows, one for changed rows.
7. Generates a primary key for new rows.
8. Inserts new rows to the target.
9. Updates changed rows in the target, overwriting existing rows
Note: The Type 1 Dimension mapping uses a Lookup and an Expression
transformation to compare source data against existing target data. The LookUp
table is the target table T_customers.

V. LookUp Transformation
1. The IN_CUSTOMERID field is the field from the source table which is taken from the
SQ_Customers.

VI. Expression transformation

Page 141 of 158

Informatica PowerCenter Lab 14-1

1. The ADDRESS and CITY fields are connected from the SQ_Customers, the
PM_PREV_ADDRESS,PM_PREV_CITY,PM_PRIMARYKEY are connected from the
Lookp Up transformation.

2. The value of ChangedFlag is computed as :

IIF(NOT ISNULL(PM_PRIMARYKEY)
AND
(
DECODE(ADDRESS,PM_PREV_ADDRESS,1,0) = 0
OR
DECODE(CITY,PM_PREV_CITY,1,0) = 0
),
TRUE,FALSE)
3. The value of NewFlag is computed as :
IIF(ISNULL(PM_PRIMARYKEY),TRUE,FALSE)

VII. Filter Transformation

1. The Filter Transformation FIL_InsertNewRecord allows only those records to pass
through the filter where the Filter Condition of NewFlag i.e only those records which
are not present in the target.
2. All the fields of this transformation are connected through the Source Qualifier
transformation. The field New flag is connected through Expression transformation.
3. The Filter Transformation FIL_UpdateChangedRecord allows only those records to
pass through the filter where the Filter Condition of Changed i.e only those records
which have been changed.

Page 142 of 158

Informatica PowerCenter Lab 14-1

4. The field ADDRESS and CITY of this transformation are connected through the
Source Qualifier transformation. The field Changed flag is connected through
Expression transformation. The field PM_PRIMARYKEY is conncted through the
LookUp transformation.

VIII. Update Strategy Transformation

1. The UPD_ForceInserts has the Update Strategy Expression DD_INSERT and inserts
row in the target table.
2. The UPD_ChangedUpdate has the Update Strategy Expression DD_UPDATE and
updates rows in the target table.

Page 143 of 158

Informatica PowerCenter Lab 14-1

Lab 23‐1 Using Mapping Wizard for SCD Type2

Goal  Create a Type 2 mapping using Wizard

Time 45 minutes

Lab Setup Successful connection to the repository using PowerCenter Workflow

Manager

I. Create source definition.

1. Create tables Customers using following scripts:

CREATE TABLE CUSTOMERS

(
CUSTOMERID number(3) NOT NULL,
COMPANYNAME varchar2(34),
FIRSTNAME varchar2(10),
LASTNAME varchar2(8),
ADDRESS varchar2(29),
CITY varchar2(10)
);

ALTER TABLE CUSTOMERS ADD PRIMARY KEY (CUSTOMERID);

2. Use the flat file customers from the Srcfiles folder to populate data in the Customers
relational table.
3. Create a Mapping to perform the above mentioned task.
4. Create a Workflow, task to execute the mapping.
5. The Customers table contains the following data.

Page 144 of 158

Informatica PowerCenter Lab 14-1

6. Import the Customers table in the Source Analyzer.

II. Create Mapping
1. In the Mapping Designer, choose Mappings‐Wizards‐Slowly Changing Dimensions.
2. Enter a mapping name and select Type 2 Dimension. Click Next.

3. Select the Logical Key Fields and the Fields to compare for changes.

Page 145 of 158

Informatica PowerCenter Lab 14-1

4. Select the Versioning Method. Click Finish.

Page 146 of 158

Informatica PowerCenter Lab 14-1

5. The Final Mapping looks like this:

Note: In the Expression Transformation EXP_KeyProcessing_InsertChanged, use the following

expression to increment the existing version number by one:
(PM_PRIMARYKEY + 1) % 1,000.

Page 147 of 158

Informatica PowerCenter Appendix B

Appendix A – Sources and Targets used in the Mappings

Relational Tables

STORES

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

STORE_ID Number(28) Decimal(28) Decimal(28) PK/NOT ORDERS

NULL

STORE_DESC Varchar2(72) Varchar(72) Varchar(72) NOT NULL

ADDRESS1 Varchar2(72) Varchar(72) Varchar(72) NOT NULL

ADRESS2 Varchar2(72) Varchar(72) Varchar(72)

CITY Varchar2(32) Varchar(32) Varchar(32) NOT NULL

STATE Varchar2(2) Varchar(2) Varchar(2) NOT NULL

POSTAL_CODE Varchar2(10) Varchar(10) Varchar(10) NOT NULL

MANAGER_ID Number(28) Decimal(28) Decimal(28) NOT NULL

PHONE Varchar2(32) Varchar(32) Varchar(32) NOT NULL

ITEMS

Field Name Oracle MS SQL Server Sybase Key Type / Reference

Constraint

ITEM_ID Number(28) Decimal(28) Decimal(28) PK/NOT ORDER_ITEMS

NULL

ITEM_NAME Varchar2(72) Varchar(72) Varchar(72) NOT NULL

ITEM_DESC Varchar2(40) Varchar(40) Varchar(40)

PRICE Number(10,2) Money(19,4) Decimal(10,2) NOT NULL

WHOLESALE_COST Number(10,2) Money(19,4) Decimal(10,2) NOT NULL

DISCONTINUED_FLAG Number(1) Decimal(1) Decimal(1) NOT NULL

Page 148 of 158

Informatica PowerCenter Appendix B

MANUFACTURER_ID Number(28) Decimal(28) Decimal(28) FK/NOT MANUFACTURERS

NULL

DISTRIBUTOR_ID Number(28) Decimal(28) Decimal(28) FK/NOT DISTRIBUTORS

NULL

ORDER_ITEMS

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ORDER_ID Number(28) Decimal(28) Decimal(28) FK/NOT ORDERS

NULL

ITEM_ID Number(28) Decimal(28) Decimal(28) FK/NOT ITEMS

NULL

QUANTITY Number(28) Decimal(28) Decimal(28) NOT NULL

DISCOUNT Number(10,2) Money(19,4) Decimal(10,2) NOT NULL

Page 149 of 158

Informatica PowerCenter Appendix B

ORDERS

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ORDER_ID Number(28) Decimal(28) Decimal(28) PK/NOT NULL ORDER_ITEMS

DATE_ENTERED Date Datetime Datetime NOT NULL

DATE_PROMISED Date Datetime Datetime NOT NULL

DATE_SHIPPED Date Datetime Datetime NOT NULL

EMPLOYEE_ID Number(28) Decimal(28) Decimal(28) FK/NOT NULL EMPLOYEES

CUSTOMER_ID Number(28) Decimal(28) Decimal(28) FK/NOT NULL CUSTOMERS

SALES_TAX_RATE Number(5,4) Money(19,4) Decimal(5,4) NOT NULL

STORE_ID Number(28) Decimal(28) Decimal(28) FK/NOT NULL STORES

MANUFACTURERS

Field Name Oracle MS SQL Server Sybase Key Type / Reference

Constraint

MANUFACTURER_ID Number(28) Decimal(28) Decimal(28) PK/NOT NULL ITEMS

MANUFACTURER_NAME Varchar2(48) Varchar(48) Varchar(48) NOT NULL

DISTRIBUTORS

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

DISTRIBUTOR_ID Number(28) Decimal(28) Decimal(28) PK/NOT ITEMS

NULL

DISTRIBUTOR_NAME Varchar2(48) Varchar(48) Varchar(48) NOT NULL

CONTACT_PERSON Varchar2(48) Varchar(48) Varchar(48)

CONTACT_PHONE Varchar2(32) Varchar(32) Varchar(32)

CONTACT_EMAIL Varchar2(72) Varchar(72) Varchar(72)

Page 150 of 158

Informatica PowerCenter Appendix B

EMPLOYEES

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

EMPLOYEE_ID Number(28) Decimal(28) Decimal(28) PK/NOT NULL ORDERS

DEPTID NUMBER(2) Decimal(28) Decimal(28) FK/NOT NULL DEPARTMENT

LAST_NAME Varchar2(30) Varchar(30) Varchar(30) NOT NULL

FIRST_NAME Varchar2(30) Varchar(30) Varchar(30) NOT NULL

ADDRESS Varchar2(72) Varchar(72) Varchar(72) NOT NULL

CITY Varchar2(32) Varchar(32) Varchar(32) NOT NULL

STATE Varchar2(2) Varchar(2) Varchar(2) NOT NULL

POSTAL_CODE Varchar2(10) Varchar(10) Varchar(10) NOT NULL

HOME_PHONE Varchar2(30) Varchar(30) Varchar(30) NOT NULL

Page 151 of 158

Informatica PowerCenter Appendix B

Tgt_Customer_x

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

CUSTOMER_ID Number(28) Decimal(28) Decimal(28) PK/NOT NULL ORDERS

COMPANY Varchar2(50) Varchar(50) Varchar(50)

FIRST_NAME Varchar2(30) Varchar(30) Varchar(30) NOT NULL

LAST_NAME Varchar2(30) Varchar(30) Varchar(30) NOT NULL

ADDRESS1 Varchar2(72) Varchar(72) Varchar(72) NOT NULL

ADRESS2 Varchar2(72) Varchar(72) Varchar(72)

CITY Varchar2(30) Varchar(30) Varchar(30) NOT NULL

STATE Varchar2(2) Varchar(2) Varchar(2) NOT NULL

POSTAL_CODE Varchar2(10) Varchar(10) Varchar(10) NOT NULL

PHONE Varchar2(30) Varchar(30) Varchar(30) NOT NULL

EMAIL Varchar2(30) Varchar(30) Varchar(30)

Tgt_CURRENTITEM_x

Field Name Oracle MS SQL Server Sybase Key Type / Reference

Constraint

ITEM_ID Number(28) Decimal(28) Decimal(28) PK/NOT

NULL

ITEM_NAME Varchar2(72) Varchar(72) Varchar(72) NOT NULL

ITEM_DESC Varchar2(40) Varchar(40) Varchar(40)

PRICE Number(10,2) Money(19,4) Decimal(10,2) NOT NULL

WHOLESALE_COST Number(10,2) Money(19,4) Decimal(10,2) NOT NULL

DISCONTINUED_FLAG Number(1) Decimal(1) Decimal(1)

MANUFACTURER_ID Number(28) Decimal(28) Decimal(28)

DISTRIBUTOR_ID Number(28) Decimal(28) Decimal(28)

Page 152 of 158

Informatica PowerCenter Appendix B

Tgt_ItemMaster_x

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ITEM_MASTER_ID Number(15) Decimal(15) Decimal(15) PK/NOT

NULL

ITEM_ID Number(28,0) Decimal(28) Decimal(28) NOT NULL

ITEM_NAME Varchar2(72) Varchar(72) Varchar(72) NOT NULL

YEAR Varchar2(4) Varchar(4) Varchar(4)

Tgt_SalesByQtr_x

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ITEM_MASTER_ID Number(15) Decimal(15) Decimal(15) FK Tgt_ItemMaster_x

QUARTER Number(19,2) Decimal(19,2) Decimal(19,2)

QUARTERLY_SALES Number(19,2) Decimal(19,2) Decimal(19,2)

Page 153 of 158

Informatica PowerCenter Appendix B

Tgt_ItemSalesByYr_x

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ITEM_ID Number(38,0) Decimal(38) Decimal(38)

ITEM_NAME Varchar2(72) Varchar(72) Varchar(72)

YEAR Varchar2(4) Varchar(4) Varchar(4)

YEAR_SALES Number(19,2) Decimal(19,2) Decimal(19,2)

Tgt_OrderListing_x

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ORDER_ID Number(15) Decimal(15) Decimal(15) PK/NOT

NULL

DATE_ENTERED Date Datetime Datetime NOT NULL

CUSTOMER_ID Number(15) Decimal(15) Decimal(15) NOT NULL

ORDER_AMOUNT Number(19,0) Decimal(19,0) Decimal(19,0) NOT NULL

Tgt_KAUAIFRANCHISE_x

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ORDER_ID Number(15) Decimal(15) Decimal(15) PK/NOT

NULL

DATE_ENTERED Date Datetime Datetime NOT NULL

CUSTOMER_ID Number(15) Decimal(15) Decimal(15) NOT NULL

ORDER_AMOUNT Number(19,0) Decimal(19,0) Decimal(19,0) NOT NULL

Tgt_MAUIFRANCHISE_x

Page 154 of 158

Informatica PowerCenter Appendix B

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ORDER_ID Number(15) Decimal(15) Decimal(15) PK/NOT

NULL

DATE_ENTERED Date Datetime Datetime NOT NULL

CUSTOMER_ID Number(15) Decimal(15) Decimal(15) NOT NULL

ORDER_AMOUNT Number(19,0) Decimal(19,0) Decimal(19,0) NOT NULL

Tgt_OAHUFRANCHISE_x

Field Name Oracle MS SQL Sybase Key Type / Reference

Server Constraint

ORDER_ID Number(15) Decimal(15) Decimal(15) PK/NOT

NULL

DATE_ENTERED Date Datetime Datetime NOT NULL

CUSTOMER_ID Number(15) Decimal(15) Decimal(15) NOT NULL

ORDER_AMOUNT Number(19,0) Decimal(19,0) Decimal(19,0) NOT NULL

Page 155 of 158

Informatica PowerCenter Appendix B

Flat Files

Nielson : Structure

NIELSEN

Column Name Datatype Length, Scale

CUST_ID Numeric 18

COMPANY_NAME Text 50

ADDRESS1 Text 72

ADDRESS2 Text 72

CITY Text 30

ST Text 2

CODE Text 10

Orders : Structure

ORDERS

Column Name Datatype Length, Scale

ORDER_NO Numeric 6

LINE_NO Numeric 8

ITEM_NO Numeric 10

ITEM_NAME Text 23

QTY Numeric 8

PRICE Numeric 14, 2

Orders

Order no Line No Item no Item Name Qty Price

100001 20000001 12345 Air Compressor 50 5000
100002 20000001 12345 Air Compressor 70 5000
100003 20000001 12345 Air Compressor 100 5000

Page 156 of 158

Informatica PowerCenter Appendix B

100004 20000002 12346 Remotely Operated Via 55 6000

100005 20000002 12346 Remotely Operated Via 550 6000
100006 20000003 12347 Towable Video Camera 300 7000
100007 20000003 12347 Towable Video Camera 500 7000
100008 20000003 12347 Towable Video Camera 43000000 7000
100009 20000004 12348 Marine super vhs vi 4100 8000
100010 20000004 12348 Marine super vhs vi 1400 8000
100011 20000004 12348 Marine super vhs vi 3400 8000
100012 20000005 12349 Dive computer 9100 8880
100013 20000005 12349 Dive computer 9050 8880
100014 20000005 12349 Dive computer 900 8880
100015 20000006 12340 Under Water Dive veh 2300 7500
100016 20000006 12340 Under Water Dive veh 1300 7500
100017 20000007 12344 Stabilizing vest 3800 6000
100018 20000007 12344 Stabilizing vest 7100 6000
100019 20000008 12343 Under water Metal Det 7600 7500
100020 20000008 12343 Under water Metal Det 6300 7500

Products Structure

PRODUCTS

Column Name Datatype Length, Scale

ITEM_NO Numeric 10

ITEM_NAME Text 23

CAT Text 5

CUST_PRICE Numeric 9, 2

VENDOR_PRICE Numeric 9, 2

PRODUCT_CATEGORY Text 21

Products

Item No Item Name Cat Cust Price Vendor Price Product Category
12340 Under Water Dive cat6 7000 7500 Vehicle
12343 Under water Metal cat1 5000 5500 Misc Equipment
12344 Stabilizing vest cat1 5000 5500 Misc Equipment
12345 Air Compressor cat1 5000 5500 Misc Equipment

Page 157 of 158

Informatica PowerCenter Appendix B

12346 Remotely Operated Vi cat2 2000 6500 Photo Equipment

12347 Towable Video cat3 6000 7500 Photo Equipment
12348 Marine super vhs vi cat4 3000 8500 Photo Equipment
12349 Dive computer cat5 8000 9000 Small Instruments

Customers

CompanyName FirstName LastName Address1 Address2 City

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin
Ana Trujillo Ana Trujillo Avda. de la México D.F.
Antonio Moreno Antonio Moreno Mataderos México D.F.
Around the Horn Thomas Hardy 120 Hanover London
Berglunds snabbköp Christina Berglund Berguvsväge Lulea
Blauer See Hanna Moos Forsterstr. 57 Mannheim
Blondel père et fils Frederique Citeaux 24, place Strasbourg
Bólido Comidas Martin Sommer C/ Araquil, 67 Madrid
Bon app' Laurence Lebihan 12, rue des Marseille
Bottom‐Dollar Elizabeth Lincoln 23 Tsawassen Tsawassen
B's Beverages Victoria Ashworth Fauntleroy London

Region PostalCode Phone Email

DE 12209 030‐0074321 [Link]@[Link]

TO 05021 (5) 555‐4729

TO 05023 (5) 555‐3932

UK WA1 1DP (171) 555‐7788

SE S‐958 22 0921‐12 34 65

US 68306 0621‐08460

FR 67000 [Link]

ES 28023 (91) 555 22 82

FR 13008 [Link]

BC T2F 8M4 (604) 555‐4729

UK EC2 5NT (171) 555‐1212

Page 158 of 158