powercenter 9.x developer, level 1 (ondemand) lab guide_635845966401197748

8/15/2019 PowerCenter 9.x Developer, Level 1 (OnDemand) Lab Guide_635845966401197748

1/293

Informatica PowerCenter 9.xLevel One Developer

Lab Guide

Version: PC95_L1D_201311


2/293

ii

Informatica PowerCenter 9.x Level One Developer

Version: PC95_L1D_201311

November 2013

Copyright (c) 1998–2013 Informatica LLC. All rights reserved.

This educational service, materials, documentation and related software containproprietary information of Informatica LLC and are provided under a license agreementcontaining restrictions on use and disclosure and are also protected by copyright law. Reverseengineering of the software is prohibited. No part of the materials and documentation may bereproduced or transmitted in any form, by any means (electronic, photocopying, recording orotherwise) without prior consent of Informatica LLC. The related software is protected byU.S. and/or international Patents and other Patents Pending.

Use, duplication, or disclosure of the related software by the U.S. Government is subject to therestrictions set forth in the applicable software license agreement and as provided in DFARS227.7202-1(a) and 227.7702-3(a) (1995), DFARS 252.227-7013(c)(1)(ii) (OCT 1988), FAR12.212(a) (1995), FAR 52.227-19, or FAR 52.227-14 (ALT III), as applicable.

The information in this educational service, materials and documentation is subject to changewithout notice. If you find any problems in this educational service, materials or documentation,please report them to us in writing.

Informatica, Informatica Platform, Informatica Data Services, PowerCenter, PowerCenterRT,PowerCenter Connect, PowerCenter Data Analyzer, PowerExchange, PowerMart, MetadataManager, Informatica Data Quality, Informatica Data Explorer, Informatica B2B DataTransformation, Informatica B2B Data Exchange Informatica On Demand, Informatica IdentityResolution, Informatica Application Information Lifecycle Management, Informatica ComplexEvent Processing, Ultra Messaging and Informatica Master Data Management are trademarksor registered trademarks of Informatica LLC in the United States and in jurisdictionsthroughout the world. All other company and product names may be trade names ortrademarks of their respective owners.

Portions of this educational service, materials and/or documentation are subject to copyright heldby third parties, including without limitation: Copyright © Adobe Systems Incorporated. All rights

reserved. Copyright © Microsoft. All rights reserved. Copyright © Oracle. All rights reserved.Copyright @ the CentOS Project.

This Software is protected by U.S. Patent Numbers 5,794,246; 6,014,670; 6,016,501; 6,029,178;6,032,158; 6,035,307; 6,044,374; 6,092,086; 6,208,990; 6,339,775; 6,640,226; 6,789,096;6,820,077; 6,823,373; 6,850,947; 6,895,471; 7,117,215; 7,162,643; 7,243,110, 7,254,590;7,281,001; 7,421,458; 7,496,588; 7,523,121; 7,584,422, 7,720,842; 7,721,270; and 7,774,791,international Patents and other Patents Pending.

DISCLAIMER: Informatica LLC provides this educational services, materials anddocumentation “as is” without warranty of any kind, either express or implied, including, butnot limited to, the implied warranties of non-infringement, merchantability, or use for aparticular purpose. Informatica LLC does not warrant that this educational service,materials, documentation or related software is error free. The information provided in this

educational service, materials, documentation and related software may include technicalinaccuracies or typographical errors. The information in this educational service, materials,documentation and related software is subject to change at any time without notice.


3/293

iii

Preface

Welcome to the “PowerCenter 9x Level One Developer” course.

This four-day, instructor led course introduces students to Informatica PowerCenter 9.x

through lecture and hands-on exercises. The course is designed for Data IntegrationDevelops new to Informatica PowerCenter.

Informatica PowerCenter is a collection of thick client workbench tools that Developers

can use to create, execute, monitor and schedule Data Integration processes.

The course will introduce attendees to working with the PowerCenter Designer,

Workflow Manager, and Workflow Monitor tools, performing tasks such as creatingtransformations, mappings, reusable objects, sessions and workflows to extract, transform

and load data. They will develop cleansing, formatting, sorting and aggregating

procedures. They can learn how to use routers, update strategies, parameters /variables

and overrides. This course will cover many different types of lookups, such as cached, persistent, dynamic and multiple row returns. Workflow tasks will be created to define a

set of instructions for executing the Data Integration routines.

Prerequisites:

Prerequisites include basic familiarity with Windows GUI and at least two years’ workexperience and some knowledge of SQL.

Course Objectives:

Audience:

This course is designed for database developers with little or no experience ofPowerCenter.

.

After successfully completing this course, students should be able to:

Use Informatica Support to resolve questions and problems with PC9.x.

Use PowerCenter 9.x Designer to build mappings that extract data from a sourceto a target, transforming it as necessary.

Use PowerCenter transformations to cleanse, format, join, aggregate and routedata to the appropriate targets

Perform error handling/trapping using PowerCenter mappings

Use PowerCenter 9.x Workflow Manager to build and run a workflow whichexecutes a sessions associated with a mapping

Design and build simple mappings and workflows based on essential business

needs. Perform basic troubleshooting using PowerCenter logs and debugger


4/293


5/293


6/293


7/293

vii

Informatica Global Customer SupportYou can contact a Customer Support Center by telephone or through the Online Support. Online

Support requires a user name and password. You can request a user name and password at

http://mysupport.informatica.com.

Use the following telephone numbers to contact Informatica Global Customer Support:

North America / South

America

Europe / Middle East / Africa Asia / Australia

Informatica LLC

Headquarters

100 Cardinal Way

Redwood City, California

94063

United States

Toll Free

877 463 2435

Standard Rate

United States: 650 385 5800

Informatica Software Ltd.

6 Waltham Park

Waltham Road, White Waltham

Maidenhead, Berkshire

SL6 3TN

United Kingdom

Toll Free

00 800 4632 4357

Standard Rate

Belgium: +32 15 281 702

France: +33 1 41 38 92 26

Germany: +49 1805 702 702

Netherlands: +31 306 022 797

United Kingdom: +44 1628 511 445

Informatica Business

Solutions Pvt. Ltd.

301 & 302 Prestige Poseidon

139 Residency Road

Bangalore 560 025

India

Toll Free

Australia: 00 11 800 46324357

Singapore: 001 800 4632 4357

Standard Rate

India: +91 80 5112 5738


8/293

viii


9/293

Table

of

Contents

Lab

Activity

2.1:

Create

STG_Dealership

Mapping

.......................................................................................

1

Lab Activity 2.2: Create STG Payment Mapping ......................................................................................... 13

Lab

Activity

2.3:

Create

STG_Dealership

Workflow

....................................................................................

29

Lab Activity 2.4: Create STG Payment Workflow ........................................................................................ 37

Lab Activity 2.5: Log Events Review ............................................................................................................ 45

Lab Activity 3: Troubleshooting .................................................................................................................. 55

Lab Activity 4.1: Load STG Customer Target ............................................................................................... 71

Lab Activity 4.2: Load the STG Employees Target ....................................................................................... 91

Lab Activity 5: Features and Techniques .................................................................................................. 109

Lab

Activity

6.1:

Using

Homogeneous

Joins

to

load

the

STG

Product

Target

...........................................

115

Lab

Activity

6.2:

Troubleshooting

Homogeneous

Joins

............................................................................

123

Lab

Activity

6.3:

Using

Heterogeneous

Joins

and

Link

Conditions

............................................................

135

Lab

Activity

7:

Using

the

Debug

Wizard

....................................................................................................

147

Lab Activity 8.1: Using a Lookup to Load the ODS Employee Target ........................................................ 157

Lab

Activity

8.2:

Troubleshooting

$Source

Connection

Variables

............................................................

177

Lab Activity 8.3: Using a Sequence Generator to load ODS Dates Target ................................................ 193

Lab

Activity

8.4:

Creating

a

Lookup

Cache

and

Loading

the

ODS

Promotions

Target

..............................

201

Lab

Activity

8.5:

Active

Lookups

...............................................................................................................

211

Lab Activity 9: Updating Targets Using PowerCenter ............................................................................... 221

Lab

Activity

10.1:

Using

PowerCenter

to

Load

the

Fact

Sales

Target

.......................................................

247

Lab Activity 10.2: Create a Mapplet .......................................................................................................... 263

Lab Activity 11: Mapping Workshop ......................................................................................................... 267

Lab Activity 12: Workflow Workshop ....................................................................................................... 275


10/293


11/293

Getting Started

Getting started

This training environment serves multiple course requirements. Because of this, the background

services required for each course are set for manual start.

A user friendly menu has been created which will Start or Stop all the services required for each

course.

Step 1. Login to the image.

1) Log into your image using the Administrator/admin user and password.

Step 2. Start the Informatica Services

1) To access the menu, reference the Stoplight icon located near the lower left of the Windows

desktop.

2) Click the Stoplight icon to reveal the course menu and from the list available choose to start the

Level_1_Developer > 1. Class Services start. The services may take approximately 10-15 minutes to

start initially. You will only need to do this once during the course. Once they have been started

they can be left running.

Note: Variations in the Stoplight menu may exist however you are required to start the

Level_1_Developer services.

Note: The status is displayed in green so you will be able to see when the services have started.


12/293


13/293


14/293

Lab 2.1- m_STG_DEALERSHIP_xx PowerCenter 9x Level I Developer 1

Lab Activity 2.1: Create STG_Dealership Mapping

Guidelines Scenario:

You have been asked to learn how to use Informatica PowerCenter in order to

more efficiently accomplish your organization’s ETL objectives and automate thedevelopment process. Because you have limited or no prior exposure to thissoftware, this exercise will serve to orient you to the basic development interfaces.You will create a pass-through mapping to load Dealership data from a relationalsource into the STG_DEALERSHIP target table.

Objectives:

Learn how to navigate the repository folder structure.

Understand the purpose of the tools accessed from the Designer.

Create and save source and target definitions and shortcuts.

Learn how to access and edit the database connections objects.

Create simple pass-through mapping.

Duration:

15 minutes

SubjectReview

PowerCenter includes two development applications, the Designer, which you willuse to create mappings, and the Workflow Manager, which you will use later tocreate and start workflows. This exercise is designed to serve as your first hands-on experience with PowerCenter, and supplement the instructor demonstrations.You will import source and target definitions from the database as well as createshortcuts from the Dev_Shared folder. You will create a pass-through mapping.


15/293

2 Lab 2.1- m_STG_DEALERSHIP_xx PowerCenter 9x Level I Developer

Walkthrough:

Start thePowerCenterDesigner

Note: Throughout this and later exercises, xx will refer to the student numberassigned to you by your Instructor or the machine you are working on. For example,if you are Student05, then DEV xx refers to the folder DEV05.

1. On your desktop, double-click the PowerCenter Designer icon ( ) to start it.

2. In the Repository Navigator, double-click REP_EDW_DEV.

a. In the “Connect to Repository” dialogue:

i. For Username, enter Devxx (xx is the number assigned by yourinstructor).

ii. For Password, enter Devxx.

iii. Click Connect.


16/293


3. Right-click the folder labeled Dev xx and select Open.

Note: In future instructions this may be referred to as “your folder.”


17/293


Walkthrough:

Create aRelationalSource

1. From the menu, select Tools Source Analyzer . The workspace to the rightof the Navigator window changes to an empty space.

Note: The small toolbar directly to the right of the Navigator window, at the top.There are the five Designer tools. Each tool allows you to create and modify onespecific type of object, such as sources. The figure below shows the Designer tools

with the first tool (the Source Analyzer) selected.

2. From the menu, select Sources Import from Database.

a. The Import Tables dialog will appear.

i. Set the ODBC data source to SDBU .

ii. Set the Username to SDBU .

iii. Owner Name and Password are SDBU as well.

iv. Click the Connect button.

v. Expand the Tables node.

vi. Select the DEALERSHIP table.


18/293


The Import Tables dialog should appear the same as displayed in the figure below.

Note: You can select multiple objects for simultaneous import by using the Ctrl key.

b. Click OK.

The DEALERSHIP source definition will appear in the Source Analyzerworkspace as shown in Figure 6 below:

c. Click Ctrl-S to save the source definition to the repository.


19/293


View the bottom left pane of the Designer. This is the Output Window. You will seea notification that the source was successfully saved.


20/293


21/293


22/293


Walkthrough:

Create aMapping

1. Open the Mapping Designer by clicking the respective icon in the toolbar. Theicon is shown highlighted below:

2. Select the menu option Mappings Create.

i. Delete the default mapping name and enter the namem_STG_DEALERSHIP_xx .

ii. Click OK.

Velocity Best Practices: The m_ as a prefix for a mapping name is specified in theInformatica Velocity Best Practices. Mappings names should be clear anddescriptive so that others can immediately understand the purpose of themappings. Velocity suggests either the name of the targets being accessed or ameaningful description of the function of the mapping.

3. Perform the following steps in the Navigator window:

a. Expand the Sources subfolder.

b. Expand the SDBU subfolder.

c. Drag and drop the source DEALERSHIP into the mapping.

Note: Two objects will appear on the Mapping Designer. By default, the Sourcedefinition along with the Source Qualifier will by dragged out onto the workspace.This default behavior can be changed by selecting Tools Options Tables andde-selecting Create Source Qualifiers when opening sources

4. Expand the Targets subfolder, and drag and drop the targetSTG_DEALERSHIP onto the Mapping Designer.


23/293


Your mapping should appear as displayed on the figure below:

5. Select the SQ_DEALERSHIP Source Qualifier transformation:a. Drag and drop the port DEALERSHIP_ID from the Source Qualifier

(SQ_DEALERSHIP) to the DEALERSHIP_ID port in theSTG_DEALERSHIP target definition.

Note: When linking ports in the mapping as described above, ensure that the tip ofyour mouse cursor is touching a letter in the name or datatype or any property ofthe port when dragging.

6. Connect all other ports of the Source Qualifier and the target definition asdescribed above in the following fashion:

SQ_DEALERSHIP STG_DEALERSHIP

DEALERSHIP_MANAGER_ID DEALERSHIP_MANAGER_ID

DEALERSHIP_DESC DEALERSHIP_DESC

DEALERSHIP_LOCATION DEALERSHIP_LOCATION

DEALERSHIP_STATE DEALERSHIP_STATE

DEALERSHIP_REGION DEALERSHIP_REGION

DEALERSHIP_COUNTRY DEALERSHIP_COUNTRY

7. When you are done linking the ports, right-click within the mapping area andselect the menu option Arrange All.


24/293


8. You mapping should appear as follows:

9. Select Ctrl-S to save your work to the repository.

a. Confirm that your Output window displays the message below:


25/293



26/293

Lab 2.2 – m_STG_PAYMENT_xx PowerCenter 9x Level I Developer 13

Lab Activity 2.2: Create STG Payment Mapping


You have been given a Payments flat file that needs to be loaded into a relational

Staging Oracle table that has the same definition as the flat file. The DBA is toobusy to assist at this time. You can build the relational definition usingPowerCenter and execute the auto-created DDL to generate the physical table onthe database.

Objectives:

Learn how to create a mapping that loads from a flat file to a relational table.

Learn how to create a source definition from a flat file.

Learn how to create a relational stage target from a source definition.

Create a physical database table using DDL generated from PowerCenter.

Create simple pass-through mapping.

Duration:

30 minutes

SubjectReview

The student will create the flat file source for Payments. Then a relational targetdefinition will be created. After this is saved to the repository, the DDL will getgenerated and executed on the database. The student will verify that the physicaltable does exist, and then create the mapping.


27/293

14 Lab 2.2 – m_STG_PAYMENT_xx PowerCenter 9x Level I Developer

Walkthrough:

Create a FlatFile Source

1. If you are not already in the PowerCenter Designer Tool, please start itaccording to the instructions in Lab 2.1

2. Return to the Source Analyzer by selecting Tools Source Analyzer .

3. Right-Click in the Source Analyzer workspace and select Clear All.

4. From the menu, select Sources Import from File.

5. The Open Flat File dialog will appear.

a. Change the drop down box Files of Type to All Files (*.*).

b. Locate the c:\Infa_Shared\SrcFiles\payment.txt . If the file is located in adifferent directory the instructor will specify.


28/293


c. Select payment.txt

d. Click Open.

i. The Flat File Import Wizard appears.

ii. Confirm that the Delimited option button is selected.

iii. Select the Import Field Names from the first line checkbox.


29/293


The Wizard should appear as displayed below:

iv. Click Next.

v. Confirm that only the Comma check box under Delimiters is selected.

vi. Select the No Quotes button under Text Qualifier.

vii. Click Next.


30/293


Confirm that the field names are displayed under Column Information. These wereimported from the first line of the flat file.

viii. Click Finish.

ix. The flat file definition should appear on your Source Analyzer workspaceas shown below:


31/293


Walkthrough:

Create TargetDefinitionfrom SourceDefinition

1. Select the Target Designer icon from the Designer Tools Toolbar above theSource Analyzer workspace as shown below:

2. Right-click anywhere in the Target Designer workspace and select Clear All.

a. In the Navigator window, open the Sources folder.

b. Open the FlatFiles subfolder

c. Select the payment source definition as shown below:

3. Drag the source definition payment onto the Target Designer.

4. Double-click the Target definition to put it in Edit mode.

a. Select the Rename button.


32/293


b. Rename the target definition to STG_PAYMENT.

c. Select the Database Type drop down and select Oracle as shown below.

d. Click OK.

5. Select Ctrl-S to save your new target definition to the repository.

a. Verify through the Output window that your Source and Target definitionssaved successfully to the repository


33/293


Observe the Target folder of your repository folder in the Navigator window andnote that the STG_PAYMENT target has been added.


34/293


Walkthrough:

Create thephysicalPaymentDatabase

table.

1. From the menu, select Targets Generate/Execute SQL.

a. At the ODBC data source drop down, select STG.

b. Username is STGxx. Password is the same. As shown below:

i. Select the Connect button.

The physical target table STG_PAYMENT will be created within the STGxx schema

of your Oracle database.


35/293


c. The Database Object Generation Dialog will appear.

i. Delete the Default Filename and enter: STG_PAYMENT_SQL_xx.sql where ‘xx’ represents your student number.

ii. Select the Selected Tables radio button from the Generate From section.

This will ensure that you will only create DDL for the table selected in theTarget Designer workspace.

iii. In the Generate Options section, select Create Table, Primary Key,Foreign Key checkboxes.

The Primary key and Foreign key are not necessary in this instance since this tabledoes not require them. But it is a good idea to get in the habit of selecting theseoptions. The keys will only create if they are already a part of the definition. Youcan edit the definition in the Target Designer and add them if you wish.

iv. Select Generate SQL file.


36/293


37/293


Walkthrough:

Verify thephysicaltable existson the

database

1. Minimize all open PowerCenter applications.

2. Locate the SQL Developer shortcut on the desktop.

3. Double-click the SQL Developer icon.

Note: SQL Developer is an Oracle database editor.

4. The application should appear as shown below:

a. Double-click INFAORCL under the connections node.

b. When prompted, login as STGxx/ STGxx


38/293


c. Navigate to your STGxx connection.

d. Expand Tables.

e. Double-click on the STG_PAYMENT table.

f. Note that the appropriate columns exist in the table as shown in the figurebelow:

You have just verified that the physical table has been created and committed onthe physical database.

g. Close the SQL Developer application.


39/293


Walkthrough:

Create aMapping

1. Open the Mapping Designer by clicking the respective icon in the toolbar. Theicon is shown highlighted below:

2. Select the menu option Mappings Create.

a. Delete the default mapping name and enter the namem_STG_PAYMENT_xx .

b. Click OK.

3. Perform the following steps in the Navigator window:

a. Expand the Sources subfolder.

b. Expand the FlatFile subfolder.

c. Drag and drop the source PAYMENT into the mapping.

d. Expand the Targets subfolder, and drag and drop the targetSTG_PAYMENT onto the Mapping Designer.


40/293


4. Select the SQ_PAYMENT Source Qualifier transformation:

a. Link the ports as shown below:

5. Type Ctrl-S to save your work to the repository.

a. Confirm that your Output window displays the message below:


41/293



42/293

Lab 2.3 - wf_m_STG_DEALERSHIP_xx PowerCenter 9x Level I Developer 29

Lab Activity 2.3: Create STG_Dealership Workflow


You have been asked to learn how to use Informatica PowerCenter in order to more

efficiently accomplish your organization’s ETL objectives and automate thedevelopment process. Because you have limited or no prior exposure to thissoftware, this exercise will serve to orient you to the basic development interfaces.You will create a workflow with relational connections to load Dealership data froma relational source into the STG_DEALERSHIP target table

Objectives:

Understand the purpose of the tools accessed from the Workflow Manager.

Create Session tasks to run the mappings and configure connectivity.

Create Workflows to run the Session tasks.

Execute the Workflows and monitor the results.

Duration:

15 minutes

SubjectReview

PowerCenter includes two development applications, the Designer, which you havealready used create mappings, and the Workflow Manager, which you will use tocreate and start workflows. This exercise is designed to serve as your first hands-on experience with PowerCenter, and supplement the instructor demonstrations.


43/293

30 Lab 2.3 - wf_m_STG_DEALERSHIP_xx PowerCenter 9x Level I Developer

Walkthrough:

Create aWorkflowand aSession Task

1. Launch the Workflow Manager by clicking on the respective icon in the toolbar.The icon is shown highlighted below:

2. Open the Workflow Designer workspace by clicking the respective icon in thetoolbar. The icon is shown highlighted below:

3. Select the menu option Workflows Create.

a. Delete the default workflow name and enter wf_m_STG_DEALERSHIP_xx.

b. Click OK.

The Start Task will appear on your workspace.

4. Adjust position of the Tasks Toolbar

Your Tasks Toolbar could be hanging off the top right side of the Workflow

Manager as shown below:

a. Grab the handle of the Task Toolbar and pull it to the left so that it is easilyaccessible.

5. Create the Session task.

a. Click on the Session icon on the toolbar. The icon is shown highlightedbelow:

i. Click on the Workflow Designer workspace.

ii. The Mappings List dialog with a list of mappings to associate with yoursession task will appear. Choose the m_STG_DEALERSHIP_xx mapping.


44/293


iii. Click OK.

6. Select the Link tool from the toolbar. The Link icon is highlighted below:

a. Select the Start Task and drag the link to the Session task.

When completed the workflow should appear as follows:


45/293


Walkthrough:

Configurethe SourceConnection

1. Double-click the session task to open it in edit mode.

a. Select the Mapping tab.

i. Select the Source Qualifier icon SQ_DEALERSHIP (in the Sessionproperties navigator window).

ii. In the Connections area on the right, select the drop down arrow underSQ_DEALERSHIP – DB Connection.

iii. The Relational Connection Browser will appear. Select the Oracleconnection SDBU.

b. Click OK.


46/293


47/293


3. Select Connections on the left side of the Edit Tasks screen. The screenshould appear as shown below:

i. Click OK.

b. Click Ctrl-S to save the workflow to the repository


48/293


Walkthrough:

Start theWorkflow

1. Right-click anywhere in the Workflow Designer workspace and select StartWorkflow.

a. The Workflow Monitor will open. Select the Task View tab at the bottom ofthe interface

2. Right-click on s_m_STG_DEALERSHIP_xx and select Get Run Properties.

The completed session run properties should display as shown below:


49/293


50/293

Lab 2.4 – wf_m_STG_PAYMENT_xx PowerCenter 9x Level I Developer 37

Lab Activity 2.4: Create STG Payment Workflow


You have created a mapping which loads a Payments flat file into a relational

Staging Oracle table. You now need to create a workflow which will contain thelocation of the flat file for the source as well as the connection for the relationaltarget table.

Objectives:

Understand the purpose of the tools accessed from the Workflow Manager.

Create Session tasks to run the mappings and configure connectivity.

Create Workflows to run the Session tasks.

Execute the Workflows and monitor the results.

Duration:

15 minutes

SubjectReview

The student will create the workflow for STG_Payments.


51/293

38 Lab 2.4 – wf_m_STG_PAYMENT_xx PowerCenter 9x Level I Developer

Walkthrough:

Create aWorkflowand aSession Task

1. If you are not already logged into the Workflow Manager, launch the applicationby clicking on the respective icon in the toolbar as shown in Lab 2.3.

2. Open the Workflow Designer workspace by clicking the respective icon in thetoolbar as shown in Lab 2.3.

a. If a workflow already exists within the workspace, select menu option

Workflows Close.

3. Select the menu option Workflows Create.

a. Delete the default workflow name and enter wf_m_STG_PAYMENT_xx .

b. Click OK.

The Start Task will appear on your workspace.

4. Create the Session task.

a. Click on the Session icon on the toolbar.

b. Click on the Workflow Designer workspace.

i. The Mappings List dialog with a list of mappings to associate with yoursession task will appear. Choose the m_STG_PAYMENT_xx mapping.

ii. Click OK.

5. Link the Start task to the session s_m_STG_PAYMENT_xx.

6. When completed the workflow should appear as follows:


52/293


Walkthrough:

Configurethe Sessionby settingthe Source

Connection

1. Double-click the session task to open it in edit mode.

a. Select the Mapping tab.

i. Select the Source Qualifier icon SQ_PAYMENT (in the Sessionproperties navigator window).

ii. Scroll down in the Properties section on the right-side of the Edit Taskswindow.

Make sure the Source File Directory is set to $PMSourceFileDir \ and theSource FileName is set to payment.txt as shown in the figure below:

Note:$PMSourceFileDir\ isthe PowerCentervariable whichcurrently points to

c:\infa_shared\SrcFilesThis variable can bechanged in thePowerCenter

Administrator tool.


53/293


Walkthrough:

Configurethe TargetConnection

1. Select the STG_PAYMENT target under the Targets node.

a. In the Connections area on the right, select the drop down arrow under DBConnections section.

i. The Relational Connection Browser will appear. Select the Oracle

connection STGxx.

ii. Click OK.

b. In the Properties section, change the Target Load Type to Normal as shownbelow:


54/293


2. Select Connections on the left side of the Edit Tasks screen. The screenshould appear as shown below:

a. Click OK.

b. Click Ctrl-S to save the workflow to the repository.


55/293


Walkthrough:

Start theWorkflow

1. Right-click anywhere in the Workflow Designer workspace and select StartWorkflow.

a. The Workflow Monitor will open.

b. Right-click on s_m_STG_PAYMENT _xx and select Get Run Properties.

The completed session run properties should display as shown below:


56/293


Walkthrough:

Data Results

1. In the Designer, you can view data that was loaded into the target.

a. Right-click on the STG_PAYMENT target definition.

i. Select Preview Data.

ii. Set the ODBC Data Source to STG.

iii. Enter the user name STGxx.

iv. Enter the password STGxx and click the Connect button.

v. Your data should appear as displayed below:


57/293



58/293

Lab 2.5 – AnalyzeLogs PowerCenter 9x Level I Developer 45

Lab Activity 2.5: Log Events Review


Your workflows have completed successfully. However, you still feel you should

familiarize yourself with the logging functions of PowerCenter.

Objectives:

Learn how to access the Workflow and Session logs in the Workflow Monitor.

Identify memory allocations.

Learn location of workflow names, folder names, and Integration Service nameswithin both workflow and session logs.

Identify SQL Statements.

Duration:

15 minutes

SubjectReview

The student will use the Workflow Monitor to access the Workflow and Session logsof the last workflow and familiarize themselves with all the valuable informationincluded there.


59/293

46 Lab 2.5 – AnalyzeLogs PowerCenter 9x Level I Developer

Walkthrough:

Access theWorkflowLogs

1. If you are not already logged into the Workflow Monitor, launch the applicationby clicking on the respective icon in the toolbar.

2. The wf_m_STG_PAYMENT_xx workflow should already be open within theworkspace.

3. Right-click the wf_m_STG_PAYMENT_xx and select the menu option Get

Workflow Log.

a. Note: Repository folders, other than your own, may be visible.


60/293


4. The Workflow Log Events dialog opens

5. Identify the run_id for the workflow.

6. Note that the Link between the Start Task and the Sessions_m_STG_PAYMENT_xx has an empty expression. There will be more onlink expressions in a later module.


61/293


7. Note the name of the node in which the session instance was executed. Youmay have to adjust the columns by grabbing the handle of the column header.

i. Note the name of the domain and the name of the user who isconnected to the repository.

ii. Note the notification of successful execution of both the session andthe workflow.

iii. Close the Workflow Log Events dialog.


62/293


Walkthrough:

Access theWorkflowLogs

1. Right-click the s_m_STG_PAYMENT_xx session

a. Select the menu option Get Session Log.


63/293


64/293


i. Note that this session was executed by a 32-bit Integration Service, thename of the node and version of the software.

ii. Note that the commit is target-based and that the commit interval is10,000. This can be changed on the properties of the session.

iii. Note the SQL Insert Statement for the target and that there is currentlynot a primary key on the table, therefore updates are not supported


65/293


66/293


4. Note the start and end times of the session

5. Note the Load Summary and the number of inserts, applied, rejected andaffected records.

a. Close the Session Events Log.


67/293



68/293

Lab 3 Troubleshooting PowerCenter 9x Level I Developer 55

Lab Activity 3: Troubleshooting


Now that you have some experience, your Tech Lead has given you a flawed

workflow that has errors in need of correction.

Objectives:

Learn how to copy mappings and workflows from a shared area.

Identify errors within a mapping and workflow.

Learn how to use the PowerCenter interfaces and logs to identify errors.

Duration:

25 minutes

SubjectReview

The student will copy a workflow from the DEV_SHARED folder and correct theerrors associated with it.


69/293


70/293


71/293

58 Lab 3 Troubleshooting PowerCenter 9x Level I Developer

3. Note the Copy Messages in the Output Window of the Repository Manager.

a. Expand the Sources, Targets, Mappings and Workflows subfolders of yourDevxx folder.

i. Transactions should have been added as a Source.

ii. ODS_Transactions should have been added as a Target.

iii. m_Errors1_xx mapping should have been added.

iv. wf_m_Errors1_xx workflow should have been added.

b. Close the Repository Manager


72/293


73/293


Walkthrough:

Rename theWorkflow

1. Open the wf_m_errors1_xx workflow in the Workflow Designer workspace.

a. Select the menu option Workflow Edit.

i. Rename the Workflow so that the xx is your Student ID.

ii. Select the Properties tab and in the Workflow Log File Name attribute,rename the log to match the workflow name.

b. Right-click on the session and select Edit.

i. Rename the session so that the xx reflects your Student ID.

ii. Select the Properties tab and in the Session Log File Name attribute,rename the session log to match the session name.

iii. Click OK.

c. Click Ctrl-S to save your work to the repository.

i. Use the Output window to verify the mapping saved successfully to therepository.


74/293


75/293


b. Right-click on s_m_Errors1_xx and select ‘Get Session Log’.

c. Note the message you receive:

3. Right-click on the wf_m_Errors1_xx workflow

a. Select Get Workflow Log.


76/293


b. Review the Workflow Log

c. Most of the entries are informational; however, take special note of the three

error messages toward the end of the log.

i. These errors indicate that your student logon does not have executepermission on the EDW connection object.

d. Close the Workflow Log.


77/293


Walkthrough:

Correct theSessionFailure

After speaking with your team mates and consulting the database, you discover thatthe target ODS_TRANSACTIONS exists in your ODS database schema.

4. Navigate back to the Workflow Manager.

5. Edit the Session and click on Mapping Tab.

a. Select Connections on the left side of the interface as shown below:

b. The relational connection for the target is EDW. You must change therelational connection for the target to your ODS schema, ODSxx.

i. Select the drop down arrow to the right of the ODS_TransactionsRelational target.

ii. The Relational Connection Browser will appear.

iii. Select the ODSxx Connection. Click OK.


78/293


79/293


The Run Properties screen will appear on the bottom right.

2. The error message indicates that the source file (Transactions.dat) cannot befound on the server machine at C:\infa_shared\SrcFiles.

a. After checking the server, you see a file called sales_transactions.txt.

b. After a conversation with the team, you determine that the source file shouldindeed be changed to sales_transactions.txt.

c. Navigate back to the Workflow Manager.

i. Edit the session s_m_Errors1_xx.

ii. Click the Mapping Tab. Then select SQ_Transactions in the Sourcesfolder of navigator on the Mapping Tab.

iii. In the Properties Window at the bottom right, note that the value for theSource FileName is Transactions.dat


80/293


3. Change the Source Filename to sales_transactions.txt as shown in the figurebelow.

a. Click OK.

b. Save the Repository.

c. Start the Workflow.


81/293


82/293


83/293


Walkthrough:

Success!!

1. Note that the session has succeeded! Congratulations!


84/293


85/293


86/293


87/293


88/293

Lab 4.1 – m_STG_Customer_Contacts_xx PowerCenter 9x Level I Developer 75

1. Select the Filter transformation tool button located on the Transformation toolbar and place it in the workspace between the Source Qualifier and the Target.The icon is shown highlighted below:

a. Your mapping will appear similar to the following figure:

2. Link the following ports from the Source Qualifier (SQ_customer_layout) to theFilter:

CUSTOMER_NO

FIRSTNAME

LASTNAME ADDRESS

CITY

STATE

ZIP

COUNTRY

PHONE_NUMBER

GENDER

INCOME EMAIL

AGE


89/293

76 Lab 4.1 – m_STG_Customer_Contacts_xx PowerCenter 9x Level I Developer

3. Edit the Filer transformation

a. Rename it to fil_CUSTOMER_NO_99999

b. Select the Properties tab.

i. Your display will appear similar to the figure below:

c. Click the dropdown arrow for the Filter Condition Transformation Attribute toactive the Expression Editor.

d. Remove the TRUE condition from the Expression Editor.

e. Enter the following expression:

i. CUSTOMER_NO != 99999 OR ISNULL(CUSTOMER_NO)

ii. Click OK to return to the Properties of the Filter transformation


90/293


f. The Properties will appear as displayed in the figure below:

g. Click OK.

Walkthrough:

Create anExpression

Create an Expression transformation directly after the Filter transformation.

1. Select the Expression transformation tool button located on the Transformationtool bar and place it in the workspace directly after the Filter. The icon is shownhighlighted below:

2. Select the following ports from the Filter transformation and pass them to theExpression transformation:

FIRSTNAME

LASTNAME

PHONE_NUMBER

GENDER

AGE


91/293


92/293


e. Create a new output port after the OUT_CUST_NAME port. It should becalled OUT_CUST_PHONE.

Port Name Datatype Precision Expression

OUT_CUST _PHONE

String 14 '(' ||SUBSTR(TO_CHAR(in_PHONE_NUMBER), 1, 3) || ') ' ||SUBSTR(TO_CHAR(in_PHONE_NUMBER), 4, 3) || '-' ||SUBSTR(TO_CHAR(in_PHONE_NUMBER), 7, 4)

The expression above uses nesting to call the TO_CHAR function from within theSUBSTR function. The TO_CHAR function is performed first. The SUBSTRfunction is then performed against the return value from TO_CHAR.

f. Create a new output port after the OUT_CUST_PHONE port. It should benamed OUT_GENDER.


OUT_GENDER String 6 DECODE(in_GENDER, 'M', 'MALE', 'F','FEMALE', 'UNK')

g. Create a new output port after the OUT_CUST_PHONE port. It should benamed OUT_AGE_GROUP.


OUT_AGE_GROUP String 10 E O E(TRUE,AGE=20ANDAGE=30ANDAGE=40AND AGE=50 ANDAGE=60,'GREATERTHAN60')

The DECODE function used in this previous expression can be used to replacenested IIF functions or small static lookup tables. The DECODE expression in theprevious step will return the value MALE if the incoming port GENDER is equal toM, FEMALE if GENDER equals F, or UNK if GENDER equals anything besides For M.

This DECODE function assigns the appropriate age group level to each customerbased on their age. DECODE used in this manner tests multiple columns andconditions, evaluated in a top to bottom order for TRUE or FALSE.


93/293


h. Click OK.

4. Link the Expression Transformation to the target.

exp_FORMAT_NAME_GENDER_PHONE SC_STG_CUSTOMER

AGE CUST_AGE

OUT_CUST_NAME CUST_NAME

OUT_CUST_PHONE CUST_PHONE_NMBR

OUT_GENDER CUST_GENDER

OUT_AGE_GROUP CUST_AGE_GROUP

5. Link the Filter Transformation to the Target.

fil_CUSTOMER_NO_99999 SC_STG_CUSTOMER

CUSTOMER_NO CUST_ID

ADDRESS CUST_ADDRESS

CITY CUST_CITY

STATE CUST_STATE

ZIP CUST_ZIP

COUNTRY CUST_COUNTRY

INCOME CUST_INCOME

EMAIL CUST_E_MAIL


94/293


95/293


Walkthrough:

Create andrun theworkflow

1. Launch the Workflow Manager and sign into your assigned folder.

2. Here is a shortcut to creating a workflow

a. If there is a workflow open in the workspace, close it.

b. Click on the session task on the tool bar.

c. Click on the empty workspace.

d. A list of available mappings (in this case, choosem_STG_CUSTOMER_CONTACTS_xx) should automatically appear.

i. After choosing the mapping, the workflow will be automatically created foryou using the name of the mapping(wf_m_STG_CUSTOMER_CONTACTS_xx). The start task will appearalready linked to the properly named session.

Good stuff!

e. Edit the s_m_STG_CUSTOMER_CONTACTS_xx session.

i. Under the Mapping tab:

ii. Select the SQ_customer_layout located under the Sources folder in thenavigator window.

iii. Confirm the Source file directory is set to $PMSourceFileDir\.

f. In Properties | Attribute | Source Filename type in customer_list.dat.

Note: The source instance you are reading is known as a File List. It is a list of fileswhich will be appended together and treated as one source file by PowerCenter.The name of the text file that is listed in Properties | Attributes | Source Filenamewill be a text file that contains a list of the text file(s) to be read in as individualsources. When you create a file list you open a blank text file with an applicationsuch as Notepad and type on a separate line each text file that is to be read as partof the file list. You may precede each file name with directory path information. Ifyou don’t provide the directory path, PowerCenter assumes the files will be locatedin the same directory as the file list file.

i. In Properties | Attribute | Source Filetype, click the dropdown arrow andchange the default from Direct to Indirect.

Note: When you use the file list feature in PowerCenter you have to set Properties | Attributes | Source Filetype to Indirect so that PowerCenter will understand to readthis file as a list and not as a direct source.

g. Your screen should appear similar to the figure below:


96/293


97/293


98/293


b. Email User Name = [email protected]

c. Email Subject = Stage Customers Load Succeeded

i. Select the Email Text Value edit button

ii. In the Email Text, add the following;

iii. Stage Customers Load has completed successfully!

iv. Add the following Built-In Variables:

Workflow Name = %w

Session name = %s

Session Start Time = %b

Session Completed Time = %c

Total Records Loaded = %l

Total Records Rejected = %r

Note: you do not have to type in the qualifiers (i.e. ‘Workflow Name =’). Thesequalifiers are included in the variable (%w).


99/293


100/293


101/293


Walkthrough:

Check youremail

1. Check your email

a. Login to Windows Live Mail. Programs Windows Live Mail

b. User name = [email protected].

c. Password = Studentxx

d. Your inbox should show an on-success email as seen below:

Walkthrough:

Review theData Results

1. Preview the target data from the Designer. Your data should appear asdisplayed in the figure below:


102/293


Walkthrough:

Success!!

1. Note that the session has succeeded! Congratulations!


103/293


104/293


105/293


106/293


107/293

94 Lab 4.2 – m_STG_Employees_xx PowerCenter 9x Level I Developer

Walkthrough:

Create aRelationalTargetDefinition

1. Create a shortcut to the STG_EMPLOYEES definition from the DEV_SHAREDfolder in your folder. Name the shortcutSC_STG_EMPLOYEES.

a. Confirm that your target definition appears the same as displayed in thefigure below:


108/293


109/293


Tip: Converting a transformation to reusable is nonreversible.

The transformation will now be saved in the Transformations subfolder within theNavigator window and will be available as a standalone object to drag into anymapping as a shortcut.

c. Review the Transformation dialog box. What differences do you see?

d. Select the Ports tab. Can you change anything here? Why are you unable

to make changes?

3. Make changes to the reusable transformation.

a. Open the Transformation Developer by clicking the respective icon(highlighted) on the toolbar.


110/293

Lab 4.2 – m_STG_Employees_xx PowerCenter 9x Level I Developer 97

b. From the Navigator window, locate the Transformations subfolder in yourrespective student folder.

c. Drag the exp_FORMAT_NAME_GENDER_PHONE into the Transformation

Developer workspace.

i. Edit the transformation name and addRE_ to the beginning of the name.

ii. Click Apply

d. Select the Ports tab.

i. Remove the ‘CUST’ from the port names.

e. Click OK.

4. Save your work.


111/293


Walkthrough:

Create amapping

1. Open the Mapping Designer.

a. If a mapping is visible in the workspace, close it by selected menu optionMappings Close.

b. Create a new mapping namedm_STG_EMPLOYEES_xx.

c. Add the employees_layout file source to the mapping

d. Add SC_STG_EMPLOYEES target to the mapping.

e. Your mapping will appear similar to the figure below:


112/293


Walkthrough:

Add a ReusableExpressionTransformation

1. In the Navigator window, under Transformations subfolder, click and dragRE_exp_FORMAT_NAME_GENDER_PHONE onto the Mapping Designerworkspace.

a. Link the following ports from SQ_employees_layout to theSC_STG_EMPLOYEES target as shown below:

SQ_employees_layout SC_STG_EMPLOYEES

EMPLOYEE_ID EMPLOYEE_ID

ADDRESS EMPLOYEE_ADDRESS

CITY EMPLOYEE_CITY

STATE EMPLOYEE_STATE

ZIP_CODE EMPLOYEE_ZIP_CODE

COUNTRY EMPLOYEE_COUNTRY

FAX_NUMBER EMPLOYEE_FAX_NUMBER

EMAIL EMPLOYEE_EMAIL

NATIVE_LANGUAGE NATIVE_LANG_DESC

SECOND_LANGUAGE SEC_LANG_DESC

THIRD_LANGUAGE TER_LANG_DESC

POSITION_TYPE POSITION_TYPE

DEALERSHIP_ID DEALERSHIP_ID

REGIONAL_MANAGER REGIONAL_MANAGER

DEALERSHIP_MANAGER DEALERSHIP_MANAGER

HIRE_DATE HIRE_DATE

DATE_ENTERED DATE_ENTERED

2. Save your work.


113/293


114/293


115/293


Walkthrough:

Create and runthe workflow


a. Create a new workflow called wf_m_STG_EMPLOYEES_ xx.

b. Create the s_m_STG_EMPLOYEES_xx session.


1. Select the SQ_employee_layout located under the Sources folder inthe navigator window.

2. Confirm the Source file directory is set to$PMSourceFileDir\.

3. In Properties | Attribute | Source Filename type inemployees_list.txt.

4. In Properties | Attribute | Source Filetype, click the dropdown arrowand change the default from Direct toIndirect.

c. Your screen should appear similar to the figure below:


116/293


d. Select SC_STG_EMPLOYEES located under the Target folder in thenavigator window.

e. Set the relational target connection property toSTGxx.

2. Save your work.

a. Check the output when to ensure the workflow saved to the repositorysuccessfully.

3. Under the Components Tab

a. Change the ‘On-Success Email’ Type toNon-reusable.

b. Select the Value edit button


117/293


c. Email User Name = [email protected]

d. Email Subject = Stage Employees Load Succeeded

e. Select the Email Text Value edit button

i. Add the following Variables:

Workflow Name = %w

Session name = %s

Session Start Time = %b

Session Completed Time = %c

Total Records Loaded = %l

Total Records Rejected = %r

ii. Your email text should appear as shown below:

f. Click OK

i. Optional - Configure an On-Failure email.

g. Click OK.

4. Save the workflow.


118/293


119/293


Walkthrough:

Check email

1. Check your email

a. If it is not already open, login to Windows Live Mail

i. User name = [email protected].

ii. Password = Studentxx

b. Your inbox should show an on-success email as seen below:


120/293


Walkthrough:

Review the DataResults



121/293



122/293

Lab 5 Features and Techniques PowerCenter 9x Level I Developer 109

Lab Activity 5: Features and Techniques


In this lab you will learn and practice some features and techniques that will

increase your efficiency as a PowerCenter Developer.

Objectives:

Learn features and techniques in the PowerCenter interface.

Duration:

30 minutes


123/293

110 Lab 5 Features and Techniques PowerCenter 9x Level I Developer

Walkthrough:

Arrange All andArrange AllIconic

WARNING: In this lab, do not save your work. While it is normally best practice tosave your work frequently while working in PowerCenter, in this case you will bemaking changes to a Mapping that is already the way you want it. So don’t saveyour work!

1. In the Mapping Designer, if it is not already open, open

m_STG_CUSTOMER_CONTACTS_xx.

2. Right-click and select Arrange All

In a complex Mapping, it can be hard to see how the parts relate. How can youmake this better?

Arrange All is a tool for arranging the transformations in a Mapping neatly.

a. Right-click again and select Arrange All Iconic.

Arrange All Iconic enables you to quickly see the relationships between the objects

in a Mapping.

Walkthrough:

Autolink

1. Arrange All on the Mapping.

a. Drag the cursor across the links between the Source definition and theSource Qualifier to select them.

b. Hit the Delete key on your keyboard.

2. Right-click and select Autolink by Name.

a. Position the cursor over the Source, then click and drag to the SourceQualifier.

b. Click the SQ again to return to the normal (arrow) cursor.

Autolinking provides a quick way to connect the output ports in onetransformation to the input ports in another transformation.

Autolink by Name searches for ports with identical names and connectsthem

Autolink by Position connects the first output port to the first input port, thesecond output port to the second input port, etc.

c. Delete the links again and Autolink the two by Position.


124/293

Lab 5 Features and Techniques PowerCenter 9x Level I Developer 111

Walkthrough:

Select Link Path

Suppose another developer has created a large, complex Mapping that is notworking quite right: some data is winding up in the wrong fields. And you have beenasked to debug it. How can you figure out where the data is coming from? Answer:By tracing the link paths.

1. On the Target definition, right-click the CUST_ZIP_CODE field and select

Select Link Path Backward.

a. The link to the CUST_ZIP_CODE field is now red.

b. Expand the Filter transformation so you can see the related field there.

c. Note that the links leading both into and out of it are red.

d. You can, by expanding the appropriate transformations, trace the lineage ofthe CUST_ZIP_CODE field all the way back to the ZIP field in the Sourcedefinition.

Selecting the link path enables you to easily trace the lineage of any field forwardand backward through a Mapping.

Walkthrough:

Propagating PortProperties

You have to change the datatype of a field in the Source. Do you really have tomanually adjust every port along its link path? No.

2. Edit the Source Qualifier and select the Ports tab.

a. Change the name of the CUSTOMER_NO port to CUST_NO and itsprecision from 5 to 10.

b. Click OK.

c. Right-click CUST_NO in the Source Qualifier and select Propagate Attributes.

d. In the “Propagate Port Attributes” dialogue:

i. Under "Attributes to Propagate" select Name and Precision, with adirection of Both.

ii. Click Preview.

iii. Note the green and red arrows. What will be changed?

iv. Click Propagate, then Close.

v. Was a change made in the Filter? What was it?

vi. Was a change made in the Target definition? Why or why not?


125/293


126/293


127/293

114 Lab 5 Features and Techniques PowerCenter 9x Level I Developer

Walkthrough:

Copy ObjectsWithin andBetweenMappings

1. You may find that you want to duplicate a set of transformations within aMapping or a Mapplet, preserving the dataflow between them. This techniquemay prove useful if you know that you will need to use the logic contained in thetransformations in other Mappings or Mapplets.

a. Arrange All Iconic.

b. Use your left mouse button to draw a rectangle that encloses the Filter andExpression transformations. This will select these objects.

c. Press Ctrl+C on your keyboard, immediately followed by Ctrl+V.

d. Note that both transformations have been copied onto the mapping,including the dataflow between them. They have been renamed with a “1”on the end of their names.

e. Open another Mapping.

f. Press Ctrl+V again.

g. The transformations are added to the open Mapping.

h. Disconnect from your folder but do not save the changes (revert to thepreviously saved version).


128/293

Lab 6.1 – m_STG_Product_xx PowerCenter 9x Level I Developer 115

Lab Activity 6.1: Using Homogeneous Joins to load the STGProduct Target


There are two Oracle tables that together contain vital information about theproducts sold by Mersche Motors. You will need to combine the data from bothtables into a single staging table that can be used as a source of data for the datawarehouse.

Objectives:

Import relational source definitions.

View relationships between relational sources.

Use a Source Qualifier to define a homogeneous join and view the statement.

Duration:

30 minutes

SubjectReview

PowerCenter will define a homogeneous join between the two Oracle source tables.That source database server will perform an inner join on the tables based on a joinstatement automatically generated by the Source Qualifier. The join set will be

loaded into the staging table.


129/293

116 Lab 6.1 – m_STG_Product_xx PowerCenter 9x Level I Developer

Walkthrough:

Create aRelationalSourceDefinition

1. If you are not already in the PowerCenter Designer Tool, please start itaccording to the instructions in Lab 2.1 using your Devxx login.

a. Open the Source Analyzer workspace and select Clear All.

i. Choose the menu option Sources Import from Database.

ii. Connect using the ODBC Data Source SDBU, the username andpassword are the same.

iii. Import the relational tables PRODUCT and PRODUCT_COST.

iv. Save your work.

b. Your Source Analyzer workspace should appear as displayed in the figurebelow:

Tip: The arrow connecting the keys PRODUCT_ID AND PRODUCT_CODEdenotes a relationship stored in the Informatica repository. By default, referentialintegrity (primary to foreign key) relationships defined on a database are importedwhen each of the tables in the relationship are imported. The arrow head is on thePrimary Key (Parent) end of the relationship.


130/293


Walkthrough:

Create aShortcut tothe TargetDefinition

1. Open the Target Designer.

a. Right-click in the workspace and select Clear All.

b. Create a shortcut to STG_PRODUCT. Rename it SC_STG_PRODUCT.

2. Save your work.

Walkthrough:

Create amapping

1. Open the Mapping Designer.

a. If a mapping is visible in the workspace, close it by selected menu optionMappings Close.

b. Create a new mapping named m_STG_PRODUCT_xx.

c. Add the source definitions PRODUCT and PRODUCT_COST to themapping.

d. Delete the SQ_PRODUCT and SQ_PRODUCT_COST source qualifiertransformations automatically created by PowerCenter.

i. Select the Source Qualifier Transformation from the PowerCenterTransformation toolbar as shown highlighted below:

e. Click on the Mapping Designer workspace.

f. The Select Sources for Source Qualifier Transformation dialog box willappear as shown below:


131/293


g. Confirm that both sources are selected and click OK.

2. Double-click the Source Qualifier Transformation to enter edit mode.

a. Rename the object SQ_PRODUCT_PRODUCT_COST.

Add SC_STG_PRODUCT target to the mapping.

b. Link each of the output ports in the Source QualifierSQ_PRODUCT_PRODUCT_COST to the target SC_STG_PRODUCT portswith the same name.

c. Right-click anywhere in the workspace and use AutoLink by Name.

d. Link COST port to the PRODUCT_COST port.

3. Save your mapping and confirm that it is valid. Note that the PRODUCT_CODE

port in the Source Qualifier is unlinked as intended as it is not needed at thetarget.

a. Your mapping will appear similar to the figure below:


132/293


Walkthrough:

SQL SelectStatement

Examine the SQL Select Statement in the Source Qualifier

1. Edit the Source Qualifier

a. Click on the Properties tab.

b. Open the SQL Query Editor by clicking the arrow in the SQL Queryproperty.

c. Click the Generate SQL button.

Note: the join statement can now be previewed, and that it is an inner join. Also note that the PRODUCT_CODE column is not in the SELECTStatement; this is because the column is not linked in the mapping and isnot needed.

d. Your SQL Editor should appear as displayed in the figure below:

e. Click OK twice.

f. Save your work.

Note: It is generally not a good practice to save the generated SQL unless there is

a need to override it. If you cancel out of the SQL editor, then at runtime thesession will create what is called the ‘default query’. This is based on the ports andtheir links in the mapping. If you click OK and leave the SQL in the editor window,you’ve overridden (hardcoded) the default query. Anytime you want to link a newport out of the Source Qualifier (or delete a link), you would have to go in andregenerate the SQL.


133/293


Note: The relationship between PRODUCT_ID and PRODUCT_CODE was used togenerate the inner join statement. If you desire to join two source tables on twocolumns that are not keys within the database, you may establish a relationshipbetween them by dragging the foreign key to the primary key column in the Source Analyzer. You may also modify the join statement to make it an outer join.

Walkthrough:

Create andrun theworkflow


a. If there is a workflow visible in the workspace, close it by selectingWorkflows Close.

2. Create a new workflow called wf_m_STG_PRODUCT_ xx.

a. Edit the s_m_STG_PRODUCT_xx session.


1. Set the relational source connection property to SDBU.

2. Set the relational target connection property to STGxx.

3. Click OK.

3. Save your work.

a. Check the output when to ensure the workflow saved to the repositorysuccessfully.


134/293


4. Start the workflow.

a. Review the session properties.

b. Your information should appear as displayed in the figure below:

c. If your session failed or had errors troubleshoot and correct them byreviewing the session log and make any necessary changes to yourmapping or workflow.


135/293


Walkthrough:

Review theData Results



136/293

Lab 6.2 - wf_m_Error2_xx PowerCenter 9x Level I Developer 123

Lab Activity 6.2: Troubleshooting Homogeneous Joins


Your Tech Lead has noticed that you are becoming proficient in creating mappings

and workflows using homogeneous joins. He knows of a workflow, currently in adevelopment state, that has some type of join problem. He has asked you to copyit from the shared area and correct the issue.

Objectives:

Study the Source Qualifier Transformation and correct the join problem.

Duration:

15 minutes

SubjectReview

Wf_m_Error2_xx exists in the Dev_Shared folder and contains two relationalsources with a homogeneous join in the Source Qualifier. This workflow needs tobe copied to your folder and renamed. The workflow is failing to load records to thePersonnel table. Analysis of the join will be necessary to correct the problem.


137/293


138/293


139/293

126 Lab 6.2 - wf_m_Error2_xx PowerCenter 9x Level I Developer

d. Copy the Workflow

i. Select menu option Edit Copy.

ii. Select your Devxx Folder.

iii. Select the menu option Edit Paste.

e. You will see the following confirmation message:

f. Click Yes.

i. If you receive a conflict message, select Reuse.

ii. Note the Copy Messages in the Output Window of the RepositoryManager.


140/293


141/293

128 Lab 6.2 - wf_m_Error2_xx PowerCenter 9x Level I Developer

Walkthrough:

Rename theMapping

1. Refresh your folder

a. Navigate to the PowerCenter Designer

i. Right-Click on your folder and select Disconnect.

ii. Right-Click on your folder and select Open.

iii. Note that the Error Mapping, Source and Target now exist in your folder.

b. Open the m_Errors2_xx mapping in the Mapping Designer workspace.

2. Select the menu option Mappings Edit.

a. Rename the mapping so that the xx is your Student id.

b. Click OK.

3. Click Ctrl-S to save your work to the repository.

a. Use the Output window to verify the mapping saved successfully to therepository.

Walkthrough:

Refresh the

Workflowfolder

1. If you are not already logged into the Workflow Manager, launch the applicationby clicking on the respective icon in the toolbar.

a. Log into the Repository with your Devxx login.

b. Open your folder.

c. Navigate to the Workflow Designer using the Workflow Tools Toolbar.

d. If a workflow exists on the workspace, use menu option WorkflowsClose to clear the workspace.

2. Refresh your folder

a. Right-Click on your folder and select Disconnect.

b. Right-Click on your folder and select Open.

c. Note that the wf_Error2_xx are now listed in your folder


142/293

Lab 6.2 - wf_m_Error2_xx PowerCenter 9x Level I Developer 129

Walkthrough:

Rename theWorkflow

1. Drag the workflow wf_m_Error2_xx onto the Workflow Manager WorkflowDesigner workspace.

a. Select the menu option Workflow Edit.

i. Rename the Workflow so that the xx is your Stude

powercenter 9.x developer, level 1 (ondemand) lab guide_635845966401197748

Documents