PowerCenter 9.x Developer, Level 1 (OnDemand) Lab Guide_635845966401197748

Informatica PowerCenter 9.x Level One Developer Lab Guide Version: PC95_L1D_201311

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 contain proprietary information of Informatica LLC and are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright law. Reverse engineering of the software is prohibited. No part of the materials and documentation may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording or otherwise) without prior consent of Informatica LLC. The related software is protected by U.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 the restrictions set forth in the applicable software license agreement and as provided in DFARS 227.7202-1(a) and 227.7702-3(a) (1995), DFARS 252.227-7013(c)(1)(ii) (OCT 1988), FAR 12.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 change without 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, Metadata Manager, Informatica Data Quality, Informatica Data Explorer, Informatica B2B Data Transformation, Informatica B2B Data Exchange Informatica On Demand, Informatica Identity Resolution, Informatica Application Information Lifecycle Management, Informatica Complex Event Processing, Ultra Messaging and Informatica Master Data Management are trademarks or registered trademarks of Informatica LLC in the United States and in jurisdictions throughout the world. All other company and product names may be trade names or trademarks of their respective owners. Portions of this educational service, materials and/or documentation are subject to copyright held by 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,032,158; 6,035,307; 6,044,374; 6,092,086; 6,208,990; 6,339,775; 6,640,226; 6,820,077; 6,823,373; 6,850,947; 6,895,471; 7,117,215; 7,162,643; 7,243,110, 7,281,001; 7,421,458; 7,496,588; 7,523,121; 7,584,422, 7,720,842; 7,721,270; and international Patents and other Patents Pending.

6,029,178; 6,789,096; 7,254,590; 7,774,791,

DISCLAIMER: Informatica LLC provides this educational services, materials and documentation “as is” without warranty of any kind, either express or implied, including, but not limited to, the implied warranties of non-infringement, merchantability, or use for a particular 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 technical inaccuracies or typographical errors. The information in this educational service, materials, documentation and related software is subject to change at any time without notice.

ii

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 Integration Develops 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 creating transformations, 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’ work experience and some knowledge of SQL. Course Objectives: 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 source to a target, transforming it as necessary.  Use PowerCenter transformations to cleanse, format, join, aggregate and route data to the appropriate targets  Perform error handling/trapping using PowerCenter mappings  Use PowerCenter 9.x Workflow Manager to build and run a workflow which executes 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 Audience: This course is designed for database developers with little or no experience of PowerCenter. . iii

Document Conventions This guide uses the following formatting conventions: If you see…

It means…

Example

>

Indicates a submenu to navigate to.

boldfaced text

Indicates text you need to type or enter.

Click Repository > Connect. In this example, you should click the Repository menu or button and choose Connect. Click the Rename button and name the new source definition S_EMPLOYEE.

UPPERCASE

Database tables and column names are shown in all UPPERCASE. Indicates a variable you must replace with specific information.

T_ITEM_SUMMARY

Note:

The following paragraph provides additional facts.

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

Tip:

The following paragraph provides suggested uses or a Velocity best practice.

Tip: The m_ prefix for a mapping name is…

italicized text

Connect to the Repository using the assigned login_id.

iv

Other Informatica Resources In addition to the student and lab guides, Informatica provides these other resources:  Informatica Documentation  Informatica Customer Portal  Informatica web site  Informatica Developer Network  Informatica Knowledge Base  Informatica Multimedia Knowledge Base  Informatica How-to Library  Informatica Professional Certification  Informatica Technical Support

Obtaining Informatica Documentation The Informatica Documentation team takes every effort to create accurate, usable documentation. If you have questions, comments, or ideas about this documentation, contact the Informatica Documentation team through email at [email protected]. We will use your feedback to improve our documentation. Let us know if we can contact you regarding your comments. The Documentation team updates documentation as needed. To get the latest documentation for your product, navigate to Product Documentation from http://mysupport.informatica.com.

Visiting the Informatica Customer Portal http://mysupport.informatica.com As an Informatica customer, you can access the Informatica Customer Portal site. The site contains product information, user group information, newsletters, access to the Informatica customer support case management system (ATLAS), the Informatica How-To Library, the Informatica Knowledge Base, the Informatica Multimedia Knowledge Base, Informatica Product Documentation, and access to the Informatica user community.

Visiting the Informatica Web Site You can access Informatica’s corporate web site at: http://www.informatica.com The site contains information about Informatica, its background, upcoming events, and locating your closest sales office. You will also find product information, as well as literature and partner information. The services area of the site includes important information on technical support, training and education, and implementation services.

Visiting the Informatica Technology Network The Informatica Developer Network is a web-based forum growing online community and interactive forum for data integration and data quality professionals around the globe. You can access the Informatica Developer Network at the following URL: http://community.informatica.com/ The site contains information on how to create, market, and support customer-oriented add-on solutions based on interoperability interfaces for Informatica products.

v

Visiting the Informatica Knowledge Base As an Informatica customer, you can access the Informatica Knowledge Base at http://mysupport.informatica.com. Use the Knowledge Base to search for documented solutions to known technical issues about Informatica products. You can also find answers to frequently asked questions, technical white papers, and technical tips. If you have questions, comments, or ideas about the Knowledge Base, contact the Informatica Knowledge Base team through email at [email protected].

Visiting the Informatica Multimedia Knowledge Base As an Informatica customer, you can access the Informatica Knowledge Base at http://mysupport.informatica.com. Use the Knowledge Base to search for documented solutions to known technical issues about Informatica products. You can also find answers to frequently asked questions, technical white papers, and technical tips. If you have questions, comments, or ideas about the Knowledge Base, contact the Informatica Knowledge Base team through email at [email protected].

Visiting the Informatica How-To Library As an Informatica customer, you can access the Informatica How-To Library at http://mysupport.informatica.com. The How-To Library is a collection of resources to help you learn more about Informatica products and features. It includes articles and interactive demonstrations that provide solutions to common problems, compare features and behaviors, and guide you through performing specific real-world tasks.

Obtaining Informatica Professional Certification You can take, and pass, exams provided by Informatica to obtain Informatica Professional Certification. For more information, go to: http://www.informatica.com/products_services/education_services/certification/Pages/index.aspx

Providing Feedback Email any comments on this guide to [email protected].

Obtaining Technical Support There are many ways to access Informatica Technical Support. You can call or email your nearest Technical Support Center listed in the following table, or you can use our WebSupport Service. Use the following email addresses to contact Informatica Technical Support:  

[email protected] for technical inquiries [email protected] for general customer service requests

WebSupport requires a user name and password. You can request a user name and password at: http://mysupport.informatica.com.

vi

Informatica Global Customer Support You 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 Informatica LLC Headquarters 100 Cardinal Way Redwood City, California 94063 United States

Europe / Middle East / Africa

Asia / Australia

Informatica Software Ltd. 6 Waltham Park Waltham Road, White Waltham Maidenhead, Berkshire SL6 3TN United Kingdom

Informatica Business Solutions Pvt. Ltd. 301 & 302 Prestige Poseidon 139 Residency Road Bangalore 560 025 India

Toll Free 877 463 2435

Toll Free 00 800 4632 4357

Standard Rate United States: 650 385 5800

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

Toll Free Australia: 00 11 800 4632 4357 Singapore: 001 800 4632 4357 Standard Rate India: +91 80 5112 5738

vii

viii

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

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.

Getting Started

3) Once complete, to verify the services are running, open Firefox and from the Favorites menu or bar select Informatica Administrator. a) If the services have been started you will be able to log in as Administrator/admin.

Note: If you are not presented with this page you will need to wait a few more minutes. Note: Typically you will not need to start the services as this is an Administration task. 4) To verify all of the services are up and running, when you have logged in to Informatica Administrator (using the user name Administrator and the password admin), select the EDW_DEV folder to the left and the services will be displayed in the window to the right. Verify that the services have a green tick beside them to indicate they are running. The following services should be running: • •

IS_EDW_DEV REP_EDW_DEV

Getting Started

• • • •

•

Note: There may be slight variations in the folders that exist in the Administrator. We are only interested in the EDW_DEV services folder. Modify the Note at the top of page 2 to read as the following: Note: For instructor guided courses: Throughout this and later exercises, xx will refer to the student number assigned to you by your Instructor or the machine you are working on. For example, if you are Student05, then Devxx refers to folder Dev05. Note: For onDemand courses: Throughout this and later exercises, xx refers to your student number. Use 01 as your student number. References to Devxx will therefore be Dev01.

Getting Started

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 the development process. Because you have limited or no prior exposure to this software, 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 relational source 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

Subject Review

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

Lab 2.1- m_STG_DEALERSHIP_xx

PowerCenter 9x Level I Developer

1

Walkthrough: Start the PowerCenter Designer

Note: Throughout this and later exercises, xx will refer to the student number assigned to you by your Instructor or the machine you are working on. For example, if you are Student05, then DEVxx 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 your instructor). ii. For Password, enter Devxx.

iii. Click Connect.

2



3. Right-click the folder labeled Devxx and select Open.

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



3

Walkthrough: Create a Relational Source

1. From the menu, select Tools  Source Analyzer. The workspace to the right of 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 one specific 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.

4



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 Analyzer workspace as shown in Figure 6 below:

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



5

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

6



Walkthrough: Create a Relational Target

1. From the Designer Tools menu, select Target Designer has shown in the figure below:

2. From the menu, select Targets  Import from Database. a. The Import Tables dialog will appear. i. Set the ODBC data source to STGxx. ii. Set the Username to STGxx. iii. Owner Name and Password are STGxx as well. iv. Click the Connect button. v. Expand the Tables node. vi. Select the STG_DEALERSHIP table. The Import Tables dialog should appear the same as displayed in the figure below.

vii. Click OK.



7

The STG_DEALERSHIP source definition will appear in the Target Designer workspace as shown in the figure below:

b. Click Ctrl-S to save the target definition to the repository. i. View the Output window of the Designer. You will see a notification that the source was successfully saved.

8



Walkthrough: Create a Mapping

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

2. Select the menu option Mappings  Create. i. Delete the default mapping name and enter the name m_STG_DEALERSHIP_xx. ii. Click OK. Velocity Best Practices: The m_ as a prefix for a mapping name is specified in the Informatica Velocity Best Practices. Mappings names should be clear and descriptive so that others can immediately understand the purpose of the mappings. Velocity suggests either the name of the targets being accessed or a meaningful 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 Source definition along with the Source Qualifier will by dragged out onto the workspace. This default behavior can be changed by selecting Tools  Options Tables and de-selecting Create Source Qualifiers when opening sources 4. Expand the Targets subfolder, and drag and drop the target STG_DEALERSHIP onto the Mapping Designer.



9

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 the STG_DEALERSHIP target definition. Note: When linking ports in the mapping as described above, ensure that the tip of your mouse cursor is touching a letter in the name or datatype or any property of the port when dragging. 6. Connect all other ports of the Source Qualifier and the target definition as described 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 and select the menu option Arrange All.

10



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:



11

12



Lab Activity 2.2: Create STG Payment Mapping Guidelines

Scenario: 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 too busy to assist at this time. You can build the relational definition using PowerCenter and execute the auto-created DDL to generate the physical table on the 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.


Subject Review

The student will create the flat file source for Payments. Then a relational target definition will be created. After this is saved to the repository, the DDL will get generated and executed on the database. The student will verify that the physical table does exist, and then create the mapping.

Lab 2.2 – m_STG_PAYMENT_xx


13

Walkthrough: Create a Flat File Source

1. If you are not already in the PowerCenter Designer Tool, please start it according 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 a different directory the instructor will specify.

14



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.



15

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.

16



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

viii. Click Finish. ix. The flat file definition should appear on your Source Analyzer workspace as shown below:



17

Walkthrough: Create Target Definition from Source Definition

1. Select the Target Designer icon from the Designer Tools Toolbar above the Source 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.

18



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 definitions saved successfully to the repository



19

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

20



Walkthrough: Create the physical Payment Database 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.



21

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 the Target 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 table does not require them. But it is a good idea to get in the habit of selecting these options. The keys will only create if they are already a part of the definition. You can edit the definition in the Target Designer and add them if you wish.

iv. Select Generate SQL file.

22



Note that the Output window confirms that the file has been created. 2. Select Edit SQL File. The file should appear as displayed below:

3. Close the notepad file. 4. Select Execute SQL File.

Note the Output window confirms the creation of the physical database table.

5. Close the Database Object Generation dialog.



23

Walkthrough:

1. Minimize all open PowerCenter applications.

Verify the physical table exists on the database

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

24



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 figure below:

You have just verified that the physical table has been created and committed on the physical database. g. Close the SQL Developer application.



25

Walkthrough: Create a Mapping

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

2. Select the menu option Mappings  Create. a. Delete the default mapping name and enter the name m_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 target STG_PAYMENT onto the Mapping Designer.

26



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:



27

28



Lab Activity 2.3: Create STG_Dealership Workflow 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 the development process. Because you have limited or no prior exposure to this software, this exercise will serve to orient you to the basic development interfaces. You will create a workflow with relational connections to load Dealership data from a 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.


Subject Review

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

Lab 2.3 - wf_m_STG_DEALERSHIP_xx


29

Walkthrough: Create a Workflow and a Session 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 the toolbar. 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 easily accessible. 5. Create the Session task. a. Click on the Session icon on the toolbar. The icon is shown highlighted below:

i. Click on the Workflow Designer workspace. ii. The Mappings List dialog with a list of mappings to associate with your session task will appear. Choose the m_STG_DEALERSHIP_xx mapping.

30



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:



31

Walkthrough: Configure the 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_DEALERSHIP (in the Session properties navigator window). ii. In the Connections area on the right, select the drop down arrow under SQ_DEALERSHIP – DB Connection. iii. The Relational Connection Browser will appear. Select the Oracle connection SDBU.

b. Click OK.

32



Walkthrough: Configure the Target Connection

1. Select the STG_DEALERSHIP target under the Targets node. a. In the Connections area on the right, select the drop down arrow under DB Connections section. b. The Relational Connection Browser will appear. Select the Oracle connection STGxx. c. Click OK. 2. In the Properties section, change the Target Load Type to Normal as shown below:



33

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

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

34



Walkthrough: Start the Workflow

1. Right-click anywhere in the Workflow Designer workspace and select Start Workflow. a. The Workflow Monitor will open. Select the Task View tab at the bottom of the 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:



35

Walkthrough: Data Results

1. In the Designer, you can view data that was loaded into the target. a. Right-click on the STG_DEALERSHIP 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:

36



Lab Activity 2.4: Create STG Payment Workflow Guidelines

Scenario: 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 the location of the flat file for the source as well as the connection for the relational 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.


Subject Review

The student will create the workflow for STG_Payments.

Lab 2.4 – wf_m_STG_PAYMENT_xx


37

Walkthrough: Create a Workflow and a Session Task

1. If you are not already logged into the Workflow Manager, launch the application by 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 the toolbar 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 your session 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:

38



Walkthrough:

1. Double-click the session task to open it in edit mode. a. Select the Mapping tab.

Configure the Session by setting the Source Connection

i. Select the Source Qualifier icon SQ_PAYMENT (in the Session properties navigator window). ii. Scroll down in the Properties section on the right-side of the Edit Tasks window. Make sure the Source File Directory is set to $PMSourceFileDir \ and the Source FileName is set to payment.txt as shown in the figure below:

Note: $PMSourceFileDir\ is the PowerCenter variable which currently points to c:\infa_shared\SrcFiles This variable can be changed in the PowerCenter Administrator tool.



39

Walkthrough:

1. Select the STG_PAYMENT target under the Targets node. a. In the Connections area on the right, select the drop down arrow under DB Connections section.

Configure the Target Connection

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 shown below:

40



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

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



41


1. Right-click anywhere in the Workflow Designer workspace and select Start Workflow. 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:

42



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:



43

44



Lab Activity 2.5: Log Events Review Guidelines

Scenario: 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 names within both workflow and session logs.



Identify SQL Statements.


Subject Review

Lab 2.5 – AnalyzeLogs

The student will use the Workflow Monitor to access the Workflow and Session logs of the last workflow and familiarize themselves with all the valuable information included there.


45

Walkthrough: Access the Workflow Logs

1. If you are not already logged into the Workflow Monitor, launch the application by clicking on the respective icon in the toolbar. 2. The wf_m_STG_PAYMENT_xx workflow should already be open within the workspace. 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.

46



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 Session s_m_STG_PAYMENT_xx has an empty expression. There will be more on link expressions in a later module.



47

7. Note the name of the node in which the session instance was executed. You may 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 is connected to the repository.

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

iii. Close the Workflow Log Events dialog. 48



Walkthrough: Access the Workflow Logs


1. Right-click the s_m_STG_PAYMENT_xx session a. Select the menu option Get Session Log.


49

2. The Session Log Events dialog opens.

3. Note the name of the Repository, Integration Service, Folder, Workflow and mapping for this session run.

50



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

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

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



51

Walkthrough:

1. Scroll down a bit on the log to the START LOAD SESSION section.

Additional Session Log Review

2. Note the start of the session and the target table listed.

3. Note the read from the source flat file.

52



4. Note the start and end times of the session

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

a. Close the Session Events Log.



53

54



Lab Activity 3: Troubleshooting Guidelines

Scenario: 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.


Subject Review

Lab 3 Troubleshooting

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


55

Walkthrough: Start the PowerCenter Repository Manager

1. From within the PowerCenter Designer, please click the PowerCenter Repository Manager icon the toolbar to start it.

2. PowerCenter will log you into the Repository Manager with the Devxx login used to log into the Designer. a. PowerCenter will automatically open your folder.

56



Walkthrough: Copy the Workflow

1. Double-Click the Dev_Shared folder to open it. a. Expand the Workflows subfolder of the Dev_Shared folder. i. Select the wf_m_Errors1_xx workflow as shown below.

2. Copy the Workflow a. Select menu option Edit  Copy. b. Select your Devxx Folder. c. Select the menu option Edit  Paste. d. You will see the following confirmation message:

i. Click Yes. Lab 3 Troubleshooting


57

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

a. Expand the Sources, Targets, Mappings and Workflows subfolders of your Devxx 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

58



Walkthrough:

1. Navigate back to the PowerCenter Designer.

Rename the Mapping

2. Refresh your folder. a. Right-Click on your folder and select Disconnect. b. Right-Click on your folder and select Open. i. Note that the Error Mapping, Source and Target now exist in your folder. 3. Open the mapping in the Mapping Designer workspace. a. Select the menu option Mappings  Edit. b. Rename the mapping so that the xx is your Student id. c. Click OK. 4. Click Ctrl-S to save your work to the repository. a. Use the Output window to verify the mapping saved successfully to the repository.

Walkthrough: Start the Workflow Manager

1. If you are not already logged into the Workflow Manager, launch the application by clicking on the respective icon in the toolbar. a. Log into the Repository with your Devxx login. i. Open your folder. 2. Navigate to the Workflow Designer using the Workflow Tools Toolbar. a. If a workflow exists on the workspace, use menu option Workflows  Close to clear the workspace. 3. Refresh your folder. a. Right-Click on your folder and select Disconnect. b. Right-Click on your folder and select Open. i. Note that the wf_m_errors1_xx workflow is now listed in your folder



59

Walkthrough: Rename the Workflow

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 the repository.

60



Walkthrough:

1. Right-click on the workspace and Start the Workflow

Trouble Shoot a Session Failure

2. Navigate to the Workflow Monitor a. Note that the workflow failed



61

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.

62



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 execute permission on the EDW connection object. d. Close the Workflow Log.



63

Walkthrough: Correct the Session Failure

After speaking with your team mates and consulting the database, you discover that the 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 the relational connection for the target to your ODS schema, ODSxx. i. Select the drop down arrow to the right of the ODS_Transactions Relational target. ii. The Relational Connection Browser will appear. iii. Select the ODSxx Connection. Click OK.

64



Your screen should appear similar to the figure shown below:

iv. Click OK. 6. Save the Repository. 7. Start the Workflow and navigate to the Workflow Monitor.

Walkthrough: Troubleshoot Second Failure


Note the Workflow has failed for a second time. 1. Right-Click on the second run of the s_m_Errors1_xx session a. Select Get Run Properties.


65

The Run Properties screen will appear on the bottom right.

2. The error message indicates that the source file (Transactions.dat) cannot be found 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 should indeed 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 Sources folder of navigator on the Mapping Tab. iii. In the Properties Window at the bottom right, note that the value for the Source FileName is Transactions.dat

66



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

a. Click OK. b. Save the Repository. c. Start the Workflow.



67

Note the Workflow has failed again.

Walkthrough: Troubleshoot the Third Failure

1. Right-click on the session s_m_Errors1_xx in the Workflow Monitor a. Select Get Run Properties. b. On the right-bottom pane of the screen you should see the following error:

2. Navigate back to the Workflow Manager. a. Edit the session s_m_Errors1_xx. b. Click the Mapping Tab. i. Then select ODS_Transactions in the Targets folder of the navigator window of the Mapping Tab. ii. In the Properties Window at the bottom right, note that the value for the Target Load Type is Bulk.

68



c. Change the Target Load Type to Normal as shown in the figure below.

d. Click OK. 3. Save the Repository. 4. Start the Workflow



69

70

Walkthrough:

1. Note that the session has succeeded!

Success!!

Congratulations!



Lab Activity 4.1: Load STG Customer Target Guidelines

Scenario: The staging area for Mersche Motors data warehouse has a customer contacts table. Mersche Motors receives new data from their regional sales office daily in the form of three text files. The text files are identical. For processing simplicity, Mersche Motors will be making use of the PowerCenter ability to read a list of files from a single source. Objectives: 

Create a filter transformation to eliminate unwanted rows form a flat file source.



Create an Expression transformation to reformat incoming rows before they are written to a target.



Use the DECODE function as a small lookup to replace values for incoming data before writing to target.



Create a session task that will accept and process a file list as a source.



Create a workflow


Subject Review

PowerCenter will source from a file list. This file list contains the names of three delimited flat files from the regional sales offices. All rows with a customer number 99999 will need to be filtered out. There are a number of columns that will need to have the data reformatted, this will include substrings, concatenation and decodes.

Lab 4.1 – m_STG_Customer_Contacts_xx


71

Walkthrough: Create a Flat File Source Definition

1. If you are not already in the PowerCenter Designer Tool, please start it according to the instructions in Lab 2.1 a. Log into the Repository with your Devxx login. b. Open your folder. c. Open the Source Analyzer workspace and select Clear All. i. Import the customer_layout.dat flat file definition. This file is located in the C:\Infa_Shared\SrcFiles directory. If the file is located in a different directory, your instructor will specify. d. Ensure that the following parameters are selected: i. Import field names from the first line. ii. Comma delimited flat file. iii. Text Qualifier is Double Quotes iv. Format of the Date field is Datetime. e. Confirm that your source definition appears as displayed in the following figure:

Note: Only one flat file definition is required when using a file list as a source in PowerCenter. All the files that make up the file list must have the same identical layout in order for the file list to be successfully processed by PowerCenter. f.

72

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



Walkthrough: Create a Relational Target Definition

1. Create a shortcut to the STG_CUSTOMERS definition from the DEV_SHARED folder in your folder. Name the shortcut SC_STG_CUSTOMERS. a. Confirm that your target definition appears the same as displayed in the figure below:



73

Walkthrough: Create a mapping

1. Open the Mapping Designer. a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close. b. Create a new mapping named m_STG_CUSTOMER_CONTACTS_xx. i. Add the customer_layout file source to the mapping ii. Add SC_STG_CUSTOMER target to the mapping. c. Your mapping will appear similar to the figure below:

Walkthrough: Create a Filter

It has been determined that there are certain erroneous records that exist at the source that should be filtered out of the data stream so that they don’t get loaded to the target. The data condition to be tested for? Records with a customer number of 99999 or records with null customer numbers. The Filter transformation is the perfect object for this operation. If your source were relational, you could use the Source Filter on the Source Qualifier transformation to achieve the same results.

74



1. Select the Filter transformation tool button located on the Transformation tool bar 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 the Filter:  CUSTOMER_NO  FIRSTNAME  LASTNAME  ADDRESS  CITY  STATE  ZIP  COUNTRY  PHONE_NUMBER  GENDER  INCOME  EMAIL  AGE



75

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 to active 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

76



f.

The Properties will appear as displayed in the figure below:

g. Click OK.

Walkthrough:

Create an Expression transformation directly after the Filter transformation.

Create an Expression

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

2. Select the following ports from the Filter transformation and pass them to the Expression transformation:  FIRSTNAME  LASTNAME  PHONE_NUMBER  GENDER  AGE



77

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

3. Edit the Expression transformation a. Rename it exp_FORMAT_NAME_GENDER_PHONE. b. Uncheck the ‘O’ box for every port EXCEPT Age so that those ports are ‘Input Only’. The AGE port should remain ‘Input/Output’. c. Prefix each of these input ports with IN_. Do not prefix the AGE port with ‘IN_’. Remember, this port should remain Input/Output. d. Create Output Ports i. Create a new output port after the AGE port by positioning the cursor on the AGE port and clicking the add icon. ii. Enter the information as follows: Port Name

Datatype

Precision

Expression

OUT_CUST_NAME

String

41

IN_FIRSTNAME || ‘ ‘ || IN_LASTNAME

Note: This new port will concatenate the FIRSTNAME and LASTNAME ports into a single string. Do not use the CONCAT in expressions. Use || to achieve concatenation. The CONCAT function is available for backward compatibility. Velocity Best Practice: Prefixing input only ports with IN_ and output ports with OUT_ is a Velocity Best Practice. This makes it easier to tell what the ports are without having to open the transformation.

78



e. Create a new output port after the OUT_CUST_NAME port. It should be called 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 the SUBSTR function. The TO_CHAR function is performed first. The SUBSTR function is then performed against the return value from TO_CHAR. f.

Create a new output port after the OUT_CUST_PHONE port. It should be named OUT_GENDER.

Port Name

Datatype

Precision

Expression

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 be named OUT_AGE_GROUP.

Port Name

Datatype

Precision

Expression

OUT_AGE_GROUP

String

10

DECODE(TRUE, AGE < 20, 'LESS THAN 20', AGE >= 20 AND AGE <= 29, '20 TO 29', AGE >= 30 AND AGE <= 39, '30 TO 39', AGE >= 40 AND AGE <= 49, '40 TO 49', AGE >= 50 AND AGE <= 60, '50 TO 60', AGE >= 60, 'GREATER THAN 60')

The DECODE function used in this previous expression can be used to replace nested IIF functions or small static lookup tables. The DECODE expression in the previous step will return the value MALE if the incoming port GENDER is equal to M, FEMALE if GENDER equals F, or UNK if GENDER equals anything besides F or M. This DECODE function assigns the appropriate age group level to each customer based on their age. DECODE used in this manner tests multiple columns and conditions, evaluated in a top to bottom order for TRUE or FALSE. Lab 4.1 – m_STG_Customer_Contacts_xx


79

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.

80

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



6. Your mapping should appear similar to the figure below:

7. Save your work. a. Verify that the mapping is valid. 8. Right-click in the workspace and select Arrange all Iconic. a. Your mapping should appear similar to the following figure:



81

Walkthrough:

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

Create and run the workflow

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, choose m_STG_CUSTOMER_CONTACTS_xx) should automatically appear. i. After choosing the mapping, the workflow will be automatically created for you using the name of the mapping (wf_m_STG_CUSTOMER_CONTACTS_xx). The start task will appear already 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 the navigator 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 files which 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 Filename will be a text file that contains a list of the text file(s) to be read in as individual sources. When you create a file list you open a blank text file with an application such as Notepad and type on a separate line each text file that is to be read as part of the file list. You may precede each file name with directory path information. If you don’t provide the directory path, PowerCenter assumes the files will be located in the same directory as the file list file. i. In Properties | Attribute | Source Filetype, click the dropdown arrow and change 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 read this file as a list and not as a direct source. g. Your screen should appear similar to the figure below:

82



The file list used in this exercise lists three text files which are found in the default location of the file list file, $PMSourceFileDir\. The figure below displays the contents of customer _list.dat.



83

h. Select SC_STG_CUSTOMERS located under the Target folder in the navigator window. i. Set the relational target connection property to STGxx. i.

Save your work. i. Check the output window to ensure the workflow saved to the repository successfully.

3. Configure the email a. Under the Components Tab i. Change the ‘On-Success Email’ Type to Non-reusable. ii. Select the Value edit button

84



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 =’). These qualifiers are included in the variable (%w).



85

d. Your email text should appear as shown below:

e. Click OK i. Optional - Configure an On-Failure email. f.

Click OK.

4. Save the workflow.

86




1. 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 by reviewing the session log and make any necessary changes to your mapping or workflow.



87

Walkthrough:

1. Check your email a. Login to Windows Live Mail. Programs

Check your email

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 the Data Results

88

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



Walkthrough:


Success!!



Congratulations!

89

90



Lab Activity 4.2: Load the STG Employees Target Guidelines

Scenario: The staging area for Mersche Motors data warehouse has an Employee information table. The employee information is saved into three text files daily. The text files are identical. For processing simplicity, Mersche Motors will be making use of PowerCenter ability to read a list of files from a single source. Objectives: 

Create and use a Reusable Transformation.



Practice using File Lists.



Practice using Session Email functionality.


Subject Review

PowerCenter will source from a file list. The file list contains the names of three delimited flat files from the regional sales offices. All rows with a customer number of 99999 will need to be filtered out. There are a number of columns that will need to have data reformatted according to the company’s business rules. The functions used to reformat the data include substring, concatenation and decodes.

Lab 4.2 – m_STG_Employees_xx


91

Walkthrough: Start the PowerCenter Designer

1. If you are not already in the PowerCenter Designer Tool, please start it according to the instructions in Lab 2.1 a. Log into the Repository with your Devxx login. b. Open your folder. c. Open the Source Analyzer workspace and select Clear All.

2. Import the employees_layout.txt flat file definition. This file is located in the C:\Infa_Shared\SrcFiles directory. If the file is located in a different directory, your instructor will specify. a. Ensure that the following parameters are selected: i. Import field names from the first line. ii. Comma delimited flat file. iii. Text Qualifier is Double Quotes iv. Format of the Hire_Date field is Datetime. v. Format of the Date_Entered field is Datetime.

92



b. Confirm that your source definition appears as displayed in the following figure:

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



93


94

1. Create a shortcut to the STG_EMPLOYEES definition from the DEV_SHARED folder in your folder. Name the shortcut SC_STG_EMPLOYEES. a. Confirm that your target definition appears the same as displayed in the figure below:



Walkthrough: Create a Reusable Transformation

Velocity Best Practice: A Velocity Design best practice is to use as many reusable transformations as possible. This decreases development time as well as keeps mappings consistent. 1. Open the mapping m_STG_CUSTOMER_CONTACTS_xx. 2. Edit exp_FORMAT_NAME_GENDER_PHONE a. Check the Make Reusable box on the Transformation tab.

b. Click Yes when you see the popup box:



95

Tip: Converting a transformation to reusable is nonreversible. The transformation will now be saved in the Transformations subfolder within the Navigator window and will be available as a standalone object to drag into any mapping 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.

96



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

c. Drag the exp_FORMAT_NAME_GENDER_PHONE into the Transformation Developer workspace. i. Edit the transformation name and add RE_ 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.



97


1. Open the Mapping Designer. a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close. b. Create a new mapping named m_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:

98



Walkthrough: Add a Reusable Expression Transformation

1. In the Navigator window, under Transformations subfolder, click and drag RE_exp_FORMAT_NAME_GENDER_PHONE onto the Mapping Designer workspace. a. Link the following ports from SQ_employees_layout to the SC_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.



99

a. Link the following ports from SQ_employees_layout to RE_exp_FORMAT_NAME_GENDER_PHONE: SQ_employees_layout

RE_exp_FORMAT_NAME_GENDER _PHONE

FIRSTNAME

in_FIRSTNAME

LASTNAME

in_LASTNAME

PHONE_NUMBER

in_PHONE_NUMBER

GENDER

in_GENDER

AGE

AGE

b. Link the following ports from RE_exp_FORMAT_NAME_GENDER_PHONE to SC_STG_EMPLOYEES.

RE_exp_FORMAT_NAME_GENDER_PHONE

SC_STG_EMPLOYEES

OUT_NAME

EMPLOYEE_NAME

OUT_PHONE

EMPLOYEE_PHONE_NU MBER

OUT_GENDER

EMPLOYEE_GENDER

OUT_AGE_GROUP

AGE_GROUP

c. Save your work.

100



d. Your mapping should appear similar to the figure below:



101

Walkthrough:

1. Launch the Workflow Manager and sign into your assigned folder. a. Create a new workflow called wf_m_STG_EMPLOYEES_ xx.


b. Create the s_m_STG_EMPLOYEES_xx session. i. Under the Mapping tab: 1. Select the SQ_employee_layout located under the Sources folder in the navigator window. 2. Confirm the Source file directory is set to $PMSourceFileDir\. 3. In Properties | Attribute | Source Filename type in employees_list.txt. 4. In Properties | Attribute | Source Filetype, click the dropdown arrow and change the default from Direct to Indirect. c. Your screen should appear similar to the figure below:

102



d. Select SC_STG_EMPLOYEES located under the Target folder in the navigator window. e. Set the relational target connection property to STGxx. 2. Save your work. a. Check the output when to ensure the workflow saved to the repository successfully. 3. Under the Components Tab a. Change the ‘On-Success Email’ Type to Non-reusable. b. Select the Value edit button



103

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.

104



Walkthrough: Start the workflow

1. Start the Workflow a. Review the session properties. b. Your information should appear as displayed in the figure below.

If your session failed or had errors troubleshoot and correct them by reviewing the session log and make any necessary changes to your mapping or workflow.



105

Walkthrough:

1. Check your email a. If it is not already open, login to Windows Live Mail

Check email

i. User name = [email protected]. ii. Password = Studentxx b. Your inbox should show an on-success email as seen below:

106







107

108



Lab Activity 5: Features and Techniques Guidelines

Scenario: 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.


Lab 5 Features and Techniques


109

Walkthrough: Arrange All and Arrange All Iconic

WARNING: In this lab, do not save your work. While it is normally best practice to save your work frequently while working in PowerCenter, in this case you will be making changes to a Mapping that is already the way you want it. So don’t save your 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 you make 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 the Source 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 Source Qualifier. b. Click the SQ again to return to the normal (arrow) cursor. Autolinking provides a quick way to connect the output ports in one transformation to the input ports in another transformation. Autolink by Name searches for ports with identical names and connects them Autolink by Position connects the first output port to the first input port, the second output port to the second input port, etc. c. Delete the links again and Autolink the two by Position.

110



Walkthrough: Select Link Path

Suppose another developer has created a large, complex Mapping that is not working quite right: some data is winding up in the wrong fields. And you have been asked 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 of the CUST_ZIP_CODE field all the way back to the ZIP field in the Source definition. Selecting the link path enables you to easily trace the lineage of any field forward and backward through a Mapping.

Walkthrough: Propagating Port Properties

You have to change the datatype of a field in the Source. Do you really have to manually 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 its precision 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 a direction 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?



111

Walkthrough: Moving Ports

Sometimes just rearranging the ports on a transformation will make the Mapping easier to read. 1. Edit the Filter transformation and select the Ports tab. a. Click the AGE port and use the “up arrow” ( ) button to move it to the top of the list of ports. b. Single-click and hold the number next to the ZIP field. Note the square that appears in the cursor. c. Drag ZIP right below AGE. d. Click Cancel to discard the changes.

Walkthrough: Transformation Creation

1. Another Method of Creating Transformations Plus, it bypasses the default names PowerCenter gives a transformation. a. From the menu, select TransformationCreate b. Select Aggregator from the dropdown box. c. Name the Aggregator agg_Demo_Create. d. Click Create. i. The new transformation appears in the workspace. e. By the same method, create a Filter named fil_Demo_Create. While we’re at it, how do you remove an unwanted transformation? f.

Click Done.

g. The Filter you just created is already selected. Hold down the Shift key and click the Aggregator you created to select it, too. h. Hit the Delete key on your keyboard.

112

i.

Note that the Designer dialogue tells you which transformations will be deleted.

j.

Click Yes.



Walkthrough: Reverting to Saved

Sometimes you make a mistake that you can’t easily undo and need to go back to where you were before. If you haven’t saved, you can do it. 1. In the Repository Navigator, right-click your folder and select Disconnect. a. When asked whether to save the changes to your folder, click No. b. Reopen your folder. c. If necessary, reopen the Mapping. d. Note that it is back to the way it was before Step 1. e. Arrange All for the next step.

Walkthrough:

1. You may not be able to see the whole Mapping in your workspace. But you can. a. Maximize PowerCenter Developer.

Scaling

b. How many transformations can you see? c. In the Standard toolbar at the top of the window, click the Zoom dropbox ( ) and select 60. d. Can you see more transformations? e. Click the Scale to fit icon ( f.

Walkthrough: Switching Transformations While Editing

) beside the Zoom dropbox.

You can now see all your transformations at once.

1. When editing several transformations, you don’t have to close the Edit Transformations dialogue and reopen it repeatedly… a. Double-click the Source Qualifier transformation to edit it. b. Select the Ports tab. c. In the Select transformation: dropbox, select the Filter transformation. d. What happens?



113

Walkthrough: Copy Objects Within and Between Mappings

1. You may find that you want to duplicate a set of transformations within a Mapping or a Mapplet, preserving the dataflow between them. This technique may prove useful if you know that you will need to use the logic contained in the transformations in other Mappings or Mapplets. a. Arrange All Iconic. b. Use your left mouse button to draw a rectangle that encloses the Filter and Expression 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 the previously saved version).

114



Lab Activity 6.1: Using Homogeneous Joins to load the STG Product Target Guidelines

Scenario: There are two Oracle tables that together contain vital information about the products sold by Mersche Motors. You will need to combine the data from both tables into a single staging table that can be used as a source of data for the data warehouse. Objectives: 

Import relational source definitions.



View relationships between relational sources.



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


Subject Review

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 join statement automatically generated by the Source Qualifier. The join set will be loaded into the staging table.

Lab 6.1 – m_STG_Product_xx


115

Walkthrough: Create a Relational Source Definition

1. If you are not already in the PowerCenter Designer Tool, please start it according 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 and password 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 figure below:

Tip: The arrow connecting the keys PRODUCT_ID AND PRODUCT_CODE denotes a relationship stored in the Informatica repository. By default, referential integrity (primary to foreign key) relationships defined on a database are imported when each of the tables in the relationship are imported. The arrow head is on the Primary Key (Parent) end of the relationship.

116



Walkthrough: Create a Shortcut to the Target Definition


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.

1. Open the Mapping Designer. a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close. b. Create a new mapping named m_STG_PRODUCT_xx. c. Add the source definitions PRODUCT and PRODUCT_COST to the mapping. d. Delete the SQ_PRODUCT and SQ_PRODUCT_COST source qualifier transformations automatically created by PowerCenter. i. Select the Source Qualifier Transformation from the PowerCenter Transformation toolbar as shown highlighted below:

e. Click on the Mapping Designer workspace. f.


The Select Sources for Source Qualifier Transformation dialog box will appear as shown below:


117

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 Qualifier SQ_PRODUCT_PRODUCT_COST to the target SC_STG_PRODUCT ports with 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 the target. a. Your mapping will appear similar to the figure below:

118



Walkthrough: SQL Select Statement

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 Query property. 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 SELECT Statement; this is because the column is not linked in the mapping and is not 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 the session will create what is called the ‘default query’. This is based on the ports and their 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 new port out of the Source Qualifier (or delete a link), you would have to go in and regenerate the SQL.



119

Note: The relationship between PRODUCT_ID and PRODUCT_CODE was used to generate the inner join statement. If you desire to join two source tables on two columns that are not keys within the database, you may establish a relationship between 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 and run the workflow

1. Launch the Workflow Manager and sign into your assigned folder. a. If there is a workflow visible in the workspace, close it by selecting Workflows  Close. 2. Create a new workflow called wf_m_STG_PRODUCT_ xx.

a. Edit the s_m_STG_PRODUCT_xx session. i. Under the Mapping tab: 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 repository successfully.

120



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 by reviewing the session log and make any necessary changes to your mapping or workflow.



121


122




Lab Activity 6.2: Troubleshooting Homogeneous Joins Guidelines

Scenario: 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 a development state, that has some type of join problem. He has asked you to copy it from the shared area and correct the issue. Objectives: 

Study the Source Qualifier Transformation and correct the join problem.


Subject Review

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

Lab 6.2 - wf_m_Error2_xx


123

Walkthrough: Start the PowerCenter Repository Manager


a. PowerCenter will log you into the Repository Manager with the Devxx login used to log into the Designer. b. PowerCenter will automatically open your folder.

124



Walkthrough:

1. Select the workflow to copy

Copy the Workflow

a. Double-Click the Dev_Shared folder to open it. b. Expand the Workflows subfolder of the Dev_Shared folder. c. Select the wf_m_Error2_xx workflow as shown below:



125

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 Repository Manager.

126



2. Expand the Sources, Targets, Mappings and Workflows subfolders of your Devxx folder. a. DEALERSHIP and MRKT_EMPLOYEES should have been added as Sources. b. PERSONNEL should have been added as a Target. c. m_Error2_xx mapping should have been added. d. wf_m_Error2_xx workflow should have been added. 3. Close the Repository Manager



127

Walkthrough: Rename the Mapping

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 the repository.

Walkthrough: Refresh the Workflow folder

1. If you are not already logged into the Workflow Manager, launch the application by 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 Workflows  Close 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

128




1. Drag the workflow wf_m_Error2_xx onto the Workflow Manager Workflow Designer workspace. a. Select the menu option Workflow  Edit. i. Rename the Workflow so that the xx is your Student id. ii. Rename the Workflow log to reflect 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. Rename the session log to reflect the session name. c. Click OK. 2. Click Ctrl-S to save your work to the repository. a. Use the Output window to verify the mapping saved successfully to the repository.

Walkthrough:

1. Start the workflow


a. Right-click on the session and select Get Run Properties. b. The workflow will fail with this error: ‘zero iteration count’ as illustrated in the figure from the session log below:

Note: It’s possible to look at the First Error Attribute Value and get some direction on where to look for the problem. DEALERSHIP. DEALERSHIP _ID = MRKT_EMPLOYEES.DEALERSHIP_ID seems to be causing the issue. Let’s open the mapping and take a look at the SQL relationship.



129

2. Maximize the PowerCenter Designer. a. If it is not already open, open the m_Errors2_xx mapping. b. Double-click the source qualifier SQ_EMPLOYEE_DEALERSHIP. i. Click on the Properties tab. Note that only the relationship has been entered into the SQL Query value. That is incorrect. As we’ve seen in our previous lab, you can use the SQL Query value to override the entire SQL statement. However, you cannot use only the join condition in that value. If you would like to override only the join condition, but allow PowerCenter to dynamically build the ‘Select’ clause, then the relationship declaration should be entered into the User Defined Join value

130



c. Correct the mapping as shown below:

d. Click OK. e. Save your work to the repository.



131

Walkthrough:

1. Maximize the Workflow Manager. a. The session may require that the mapping be refreshed.

Refresh the Mapping

b. Right-Click on the session as shown below and select Refresh Mapping.

c. You should now see a small yellow triangle located on the session object. This small triangle indicates that the session has been ‘impacted’ by a change at the mapping level. Note: If you do not see an impacted symbol after refreshing the mapping, the repository automatically refreshed the session for you.

2. Save the Repository a. Note that the Impacted Symbol disappears. i. To be sure the mapping changes were refreshed into the session; b. Edit the session i. Click the Mapping Tab 132



ii. Select the Sources subfolder in the navigator on the left. iii. Review the Properties on the bottom right. The join condition should now exist within the User Defined Join value. Alternatively, instead of updating the mapping, you could have overridden the join value here on the session as seen below:



133


134

1. Start the wf_m_Errors2_xx workflow. a. A successful execution will result in Task Details and Source/Target Statistics like the figure below:



Lab Activity 6.3: Using Heterogeneous Joins and Link Conditions Guidelines

Scenario: Mersche Motors receives sales transactions data from their regional sales offices in the form of a text file. The sales transactions data needs to be loaded into the staging table. The load of the STG_Transactions table uses data from the relational table STG_Product. Therefore, our workflow must check to ensure the successful load of the STG_Product table before proceeding with the load of STG_Transactions. Objectives: 

Study the Joiner Transformation and use it to join two data streams from two different source types.



Determine how to select the Master side of the join.



Specify a join condition.



Learn how to use link conditions in a workflow.


Subject Review

PowerCenter will source from a flat file and relational table. A Joiner transformation is used to create one dataflow that is then written to a relational target. The flat file is missing one field the staging target table needs; the cost of each product. This value can be read from the STG_PRODUCT table. Each row from the source file contains a value named Product. This value has an identical corresponding value in the STG_PRODUCT table PRODUCT_ID column. Use the Joiner transformation to join the flat file to the relational table (heterogeneous join) using this relationship and then write the results to the STG_TRANSACTIONS table.

Lab 6.3 – m_STG_Transactions_xx


135

Walkthrough: Create a Flat File Source Definition

1. Log into the PowerCenter Designer using your Devxx username and password. a. Open your folder. b. Open the Source Analyzer workspace and select Clear All. 2. From the menu, select Sources  Import from File. a. Import the sales_transactions.txt comma delimited flat file. b. Ensure that the TRANSACTION_DATE is a datetime field. 3. Save the repository

Walkthrough: Create a Relational Source Definition

Walkthrough: Create a Shortcut to the Target Definition

1. With the Source Analyzer still open; a. Import the STG_PRODUCT table found in the STGxx schema. Use STGxx as owner name and password.

1. Open the Target Designer. a. Right-click in the workspace and select Clear All. b. Create a shortcut to STG_TRANSACTIONS. Rename it SC_STG_TRANSACTIONS. c. Save your work.

136



Walkthrough:

1. Open the Mapping Designer. a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close.

Create a mapping

2. Create a new mapping named m_STG_TRANSACTIONS_xx. a. Add the source definitions sales_transactions and STG_PRODUCT to the mapping. b. Add SC_STG_TRANSACTIONS target to the mapping. c. Your mapping should appear similar to the figure below:

Walkthrough: Create a Joiner Transformation

1. Select the Joiner transformation from the Transformation toolbar with a single left click. The figure below shows the Joiner transformation highlighted on the toolbar:

a. Create a new Joiner transformation. i. Select all the ports from the SQ_sales_transactions source qualifier and copy/link them to the Joiner transformation.



137

ii. Select only PRODUCT_ID AND PRODUCT_COST ports from SQ_STG_PRODUCT source qualifier and copy them to the Joiner transformation. You mapping should be similar to the figure below:

b. Edit the Joiner transformation i. Rename it jnr_SALES_TRANSACTION_STG_PRODUCT. 1. Select the Ports tab 2. Set the Master (M) property to STG_PRODUCT ports. Note: Which ports should be Master? Use the source that is the smaller, in row and bytes, if the data is not sorted. If the source data is sorted, use the source with the fewest number of join column duplicates.

138



Master Property

c. Uncheck the output check box for PRODUCT_ID. d. Rename the PRODUCT_ID port to IN_PRODUCT_ID. e. Select the Condition tab. i. Click the Add a new condition button. The Add a new condition button is highlighted in the figure below:

f.


The Master drop down box will default to IN_PRODUCT_ID.


139

g. Select the Detail drop down box and set it to PRODUCT. Your condition should be the same as displayed in the figure below:

Note: The Joiner transformation can support multiple port conditions to create a join. If you need multiple port conditions, simply click the Add a new condition button to add the other ports that make up the port condition. h. Click OK. i.

140

Save the repository.



Walkthrough: Link the Target Table

1. Link the following ports from the Joiner transformation to the corresponding columns in the target object. jnr_SALES_TRANSACTION_STG_PRODUCT

SC_STG_TRANSACTIONS

CUST_NO

CUST_ID

PRODUCT

PRODUCT_ID

DEALERSHIP

DEALERSHIP_ID

PAYMENT_DESC

PAYMENT_DESC

PROMO_ID

PROMO_ID

DATE_ID

DATE_ID

TRANSACTION_DATE

TRANSACTION_DATE

TRANSACTION_ID

TRANSACTION_ID

EMPLOYEE_ID

EMPLOYEE_ID

TIME_KEY

TIME_KEY

SELLING_PRICE

SELLING_PRICE

PRODUCT_COST

UNIT_COST

DELIVERY_CHARGES

DELIVERY_CHARGES

QUANTITY

SALES_QTY

DISCOUNT

DISCOUNT

HOLDBACK

HOLDBACK

REBATE

REBATE



141

Your mapping should appear similar to the figure displayed below:

a. Save your work. b. Verify your mapping is valid in the Output window. If the mapping is not valid, correct the invalidations that are displayed in the message.


1. Launch the Workflow Manager and sign into your assigned folder. a. If there is a workflow visible in the workspace, close it be selecting Workflows  Close. 2. Open wf_m_STG_PRODUCT_xx. a. Edit the workflow. Rename it wf_m_STG_PRODUCT_TRANSACTIONS_xx. b. Rename the Workflow log to reflect the new workflow name. c. Click OK. 3. Edit s_m_STG_PRODUCT_xx. a. In the Mapping Tab b. Select the truncate target table option for SC_STG_PRODUCT.

142



4. Click on the session task on the Workflow Task toolbar. Click on the Workflow Designer workspace.

a. Select m_STG_TRANSACTIONS_xx mapping to associate with the session. i. Double-click to edit the s_m_STG_TRANSACTIONS_xx session. ii. Under the Mapping tab: 1. Confirm that the Source File directory is set to $PMSourceFileDir\. 2. Confirm that that Source File name is set to sales_transactions.txt. 3. Set the relational source connection for STG_PRODUCT to STGxx 4. Set the relational target connection property to STGxx. b. Click OK.

5. Draw a link between the s_m_STG_PRODUCT_xx session and the s_m_STG_TRANSACTIONS_xx session. a. Your workflow should appear similar to the figure below:



143

b. Double-click the link between s_m_STG_PRODUCT_xx and s_m_STG_TRANSACTIONS_xx sessions. i. The s_m_STG_PRODUCT_xx Expression Editor will appear. ii. Build the following expression into the editor: $s_m_STG_PRODUCT_xx.Status = SUCCEEDED Note: $s_m_STG_PRODUCT_xx.Status can be entered by double clicking on the pre-defined variable. c. The Expression Editor should appear similar to the figure displayed below:

d. Click OK. e. Your workflow should appear similar to the figure displayed below:

6. Save your work.

144



a. Check the output when to ensure the workflow saved to the repository successfully. 7. Start the workflow. a. Review the Workflow Run properties and the Session Statistics. b. Your information should appear as displayed in the figure below.

c. If your session failed or had errors troubleshoot and correct them by reviewing the session log and make any necessary changes to your mapping or workflow. d. Open the Workflow log and observe the notation regarding the processing of the Workflow link between s_m_STG_PRODUCT_xx and s_m_STG_TRANSACTIONS_xx.



145


146




Lab Activity 7: Using the Debug Wizard Guidelines

Scenario: The Mapping m_STG_DATES_DEBUG contains at least one error that results in bad data being rejected and not loaded into the target table. You must find and correct this error so that the data warehouse project can proceed successfully. Objectives: 

Use the Debug toolbar



Use the Debug Wizard


Lab 7 – The Debugger


147

Walkthrough: Copy and Inspect the Debug Mapping

1. In the Designer application, make sure you are connected and open to your assigned Devxx folder. a. Expand the folder Dev_Shared. i. Locate and select the Mapping m_STG_DATES_DEBUG. ii. From the menu, select EditCopy. b. Return to your Devxx folder. i. Select your Mapping subfolder. ii. From the menu, select EditPaste. iii. In the “Copy Confirmation” dialogue, click Yes. c. Save the Repository. Note: Note that the Mapping validates properly. The validation process ensures that the Mapping is technically valid, but it cannot test for errors in business logic. d. Open the Mapping in the Mapping Designer workspace. i. Inspect the Mapping to get an overall idea of what kind of processing is being done. ii. Read the expressions in the Expression transformation. e. You have been told only that there is an “error” in the data being written to the target, without any further clarification as to the nature of the error. Note: Many Mapping errors can be found by carefully inspecting the Mapping, without using the Debug Wizard. If the error cannot be quickly located in this manner, the Debug Wizard can help you by showing the actual data passing through the transformation ports. However, to use the Debug Wizard effectively, you need to understand the logic of the Mapping.

148



Walkthrough: Start the Debugger

1. Open the Debug Toolbar and Start the Debug Wizard a. On the Toolbar, right-click to bring up a list of available toolbars. Select the “Debugger” toolbar.

b. The Debugger Toolbar will appear:

Note: If the Debugger Toolbar is not visible, it is possible that another toolbar has shifted it off the screen. Rearrange the other toolbars until you can see it. c. EITHER click the Start Debugger button ( Debug Wizard.

) OR press F9 to start the

d. The first page of the Debug Wizard is informational. Please read it and press Next. Note: The Debug Wizard requires a valid Mapping and Session to run – it cannot help you determine why a Mapping is invalid. The Output window of the Designer will show you the reason(s) why a Mapping is invalid.



149

e. The Wizard should now look like this:

f.

From the dropdown box, select the Integration Service IS_EDW_DEV.

g. In the Session box, select the Create a debug session radio button. h. Click Next. The next page of the Wizard allows you to set connection properties, similar to creating Sessions in the Workflow Manager application. i.

Set the Target Connection Value to STGxx You will discard the debugger data in a later step, so this value will be ignored.

j.

Select the Properties tab at the bottom. i. Add the “.txt” extension to the Source filename property value. ii. Set the Target load type property to Normal

150



k. Your Wizard should now look like this:

l.

Click Next three times.

These panels enable you to set which transformations in the Mapping you wish to monitor in this debugging session, and set Session configuration information, such as a parameter file or which connections the variables $Source and $Target correspond to. m. Ensure that Discard target data is checked.

n. Click Finish. Lab 7 – The Debugger


151

PowerCenter creates and initializes a Debug Session. The Debug Wizard automatically opens windows to view target and transformation data. No data will be read until you are ready to view it. o. Resize the Debugger Target Data Display and Debugger Data Display windows as needed. i. A good guideline is to have them look something like this:

152



Walkthrough:

1. Your screen should now look like this:

Locate the Error

a. In the Target Instance dropdown box, select STG_DATES_VIEW. b. In the Instance dropdown box, select SQ_dates. Note: The term “instance” here refers to an object in the Mapping. Thus, each transformation is an “instance.” c. Click the “Next Instance” button (

) on the Debugger toolbar.

i. Data displays in the Instance window.

d. Toggle the dropdown to the Expression transformation.



153

Note that there is no data available as yet – the Instance window, with the Next Instance button, shows data as it moves from transformation to transformation through the Mapping.

e. Click the Step to Instance button (

)

Note that one more row has been read as shown below.

Toggle to the Expression transformation and notice the first row has been “pushed” to the Expression transformation and the Target table.

154



f.

Click the Step to Instance button several more times (at least 13), watching how the data flows from the Instance window to the Target Instance window. Compare the results between the Target instance and Instance windows.

What is the nature of the error in the data being written to the table? g. Double-click the Expression transformation to open it. Note: the transformation properties are grayed-out. While you can view and copy expressions, you cannot edit the Mapping or its components while the Debugger session is running. h. What is causing the error?



155

Walkthrough: Correct the Error

Note: Nonetheless, you CAN try new variations on expression while the Debugger is running. 1. Click the Ports tab. a. Enter the Expression Editor for one of the output ports – preferably the one that seems likely to be causing the problem. b. Select the text of the expression (even though it is grayed-out) and copy it to the Windows clipboard by typing Ctrl+C. c. Click Cancel twice. d. Right-click the Expression transformation and select Evaluate Expression. i. Paste the expression text you chose into the Expression Editor and press Evaluate. 1. The Debugger will immediately evaluate the expression with the current data in the ports. 2. You can make as many changes to the Expression here as you need. 3. Once you have a modified expression that you want to keep, copy it to the Windows clipboard. e. Close the expression evaluator

f.

Stop the Debugger by pressing the Stop Debugger button ( Debugger toolbar

) on the

g. Click Yes to “Shutdown the debugger.” h. Edit the Expression transformation and put your modified Expression in place by pasting it into the Expression Editor. i.

Save your work. i. Restart the Debugger and test to ensure that your fix worked.

156



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

Scenario: Information about Mersche Motors employees is loaded to a relational target table on a regular basis by an external process. Your tech lead has decided to recreate that external process using PowerCenter. Sensitive employee data, such as salary has only now become available for use in the warehouse. It is important that we go back and update the entire table with this salary information using this new mapping. This information has been made accessible in text file format. This file and the EMPLOYEE relational Oracle table can be joined together using the EMPLOYEE_ID column from each source. A workflow should be created which will check for the presence of the flat file before executing the load. If the flat file does not show up in time, an email alert should be sent. Objectives: 

Utilize lookup to a flat file.



Determine how to use Treat Source Rows As session property.



Use Event Wait, Timer, Control and Email Tasks in a Workflow


Subject Review

PowerCenter will use a flat file as a lookup for employee salary data and use that information to update the target ODS_EMPLOYEE table. The session property, Treat Source Rows As, will be used to update the entire table. Use an Event Wait, Timer and Email task to ensure flat file is on the server in time and if not, send email.

Lab 8.1 – m_ODS_Employees_xx


157

Walkthrough: Create a Shortcut to a Relational Source Definition

1. Log into the PowerCenter Designer using your Devxx username and password. a. Open your folder. b. Open the Source Analyzer workspace and select Clear All. 2. Create a shortcut to the relational source, EMPLOYEES. This table will be found under the SDBU node of the Sources subfolder in DEV_SHARED. a. Rename it SC_EMPLOYEES. b. Your source should appear similar to the figure below:

3. Save your work.

158



Walkthrough: Create a Shortcut to a Relational Target Definition


1. Open the Target Designer. a. Right-click in the workspace and select Clear All. b. Create a shortcut to ODS_EMPLOYEES. Rename it SC_ODS_EMPLOYEES. 2. Save your work.

1. Open the Mapping Designer. a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close. 2. Create a new mapping named m_ODS_EMPLOYEES_xx. a. Add the source definition SC_EMPLOYEES to the mapping. b. Add SC_ODS_EMPLOYEES target to the mapping. 3. Save your work.

Walkthrough: Create a Lookup transformation

1. Select the Lookup transformation from the Transformation toolbar with a single left click. The figure below shows the Lookup transformation highlighted on the toolbar:

a. Move your mouse pointer onto the Mapping Designer workspace and single click your left mouse button. This will create a new Lookup Transformation.



159

b. The Select Lookup Table for Lookup Transformation dialog box will appear. i. Select Import  Flat File for the location of the Lookup table.

ii. Locate the c:\infa_shared\SrcFiles directory and select the file salaries.txt. If the file is located in a different location, the instructor will specify.

160



2. Configure the Flat File Wizard a. The Flat File wizard will appear. Confirm that the Delimited Option button is selected. b. Select the Import field names from the first line check box. Your wizard should appear similar to the figure below:

c. Click Next. d. Confirm that only the Comma check box under Delimiters is selected. e. Select the No Quotes option button under Text Qualifier. f.

Click Next.

g. Confirm that the field names are displayed under Column Information. These were imported from the first line. h. Click Finish.



161

i.

Confirm that your Lookup transformation appears as displayed in the figure below:

3. Link the New Lookup, LKPTRANS to the SQ_SC_EMPLOYEES a. Drag the EMPLOYEE_ID from the SQ_SC_EMPLOYEES source qualifier to the new Lookup Transformation. b. Edit the Lookup Transformation. i. Rename it lkp_SALARIES. Velocity Best Practice: Velocity naming conventions specify to name Lookup transformations lkp_LOOKUP_TABLE_NAME. c. Click on the Port tab. i. Rename EMPLOYEE_ID1 TO IN_EMPLOYEE_ID. ii. Clear the output port for IN_EMPLOYEE_ID. iii. Click Apply. 1. You should receive a popup message indicating the Lookup condition is empty. Click the Yes button to continue. 2. Select the Condition tab. 3. Select the Add a new Condition button. d. Your condition should look similar to the figure below:

e. Click OK.

162



4. Save your work. 5. Link Remaining Ports a. Autolink by name all ports from the SQ_SC_EMPLOYEES source qualifier to the SC_ODS_EMPLOYEES target. b. Link the SALARY port from lkp_SALARIES to the SC_ODS_EMPLOYEES target. c. Your mapping should appear similar to the figure below:


1. Launch the Workflow Manager and sign into your assigned folder. a. If there is a workflow visible in the workspace, close it be selecting Workflows  Close. b. Create a new workflow called wf_m_ODS_EMPLOYEES_ xx i. Edit the s_m_ODS_EMPLOYEES_xx session. ii. Under the Mapping tab: 1. Set the relational source connection for SQ_SC_EMPLOYEES to SDBU 2. Set the relational target connection property to ODSxx. 3. Click the lkp_SALARIES lookup under the Transformations subfolder. 4. Ensure the Lookup Source File directory is set to $PMLookupFileDir. 5. Set the Lookup Source Filename to salaries.txt.



163

c. Your Lookup properties should appear as shown below:

164



d. Click Properties tab of the Session. e. Change the Treat Source Rows as property to Update. f.

Your screen should appear as displayed below:

Note: PowerCenter’s default behavior is to mark each row for Insert. However, that behavior can be altered. When you change the Treat Source Rows as property to Update, Integration Service marks all rows to update the target. You can further define the update operation by adding an update strategy transformation to the mapping. With an update strategy, you can test each row against a condition, and then mark each row accordingly; update, insert or delete. g. Click OK.



165

2. Save your work. a. Check the output when to ensure the workflow saved to the repository successfully.

Walkthrough:

1. Start the Workflow

Start the workflow

2. Review the session properties. a. Your information should appear as displayed in the figure below.

b. If your session failed or had errors troubleshoot and correct them by reviewing the session log and make any necessary changes to your mapping or workflow.

166



Walkthrough:


Review the Data Results

Walkthrough:

1. Add Workflow Tasks to check for arrival of Flat File.

Add tasks to the Workflow

It is always a good idea to make sure your sessions are executing as they should before you add other logic to a workflow. It makes troubleshooting much easier. Now that we have determined that our session task is executing as expected, we will add additional workflow tasks to this workflow to ensure that it executes as expected. a. Still in the Workflow Manager, select the Event Wait task from the Workflow Tasks toolbar. The Event Wait task is shown highlighted below:

i. Drop the Event Wait task on the Workflow Designer workspace. ii. Delete the link between the Start Task and the Session Task by clicking on the link to make it red, then select your Delete key. iii. Draw a link between the Start Task and the Event Wait task.



167

b. Edit the Event Wait task. i. On the General tab, select Fail Parent if this task fails, as shown below:

ii. Select the Events tab iii. Ensure the Predefined: This is a file-watch event radio button is selected. iv. Enter c:\infa_shared\SrcFiles\Watch_xx.txt as the name of the watch file as shown below. Be sure and enter the path exactly as stated here in the guide:

v. Rename the Event Wait task evnt_SALARIES. vi. Click OK. c. Link evnt_SALARIES to the session task s_m_ODS_EMPLOYEES_xx.

168



i. Your workflow should appear similar to the figure below:

d. Select the Timer task from the Workflow Tasks toolbar. The Timer task is shown highlighted below:

e. Drop the Timer Task on the workspace. f.

Edit the Timer Task. i. On the General tab, rename the Timer task to tmr_ODS_EMPLOYEES_xx. ii. Select Fail parent if this task fails iii. Select the Timer tab. iv. Select the Relative Time radio button.

Add a time in which you would like the timer to start. Two minutes from the time you start the workflow would be best. g. Select the from the start time of this task radio button. i. Your screen should look similar to the figure below:

ii. Click OK.



169

h. Link the Start Task to tmr_ODS_EMPLOYEES. i.

Right-click and select Arrange  Horizontal.

j.

Your workflow should appear as shown in the figure below:

2. Select the Email task from the Workflow Tasks toolbar. The Email task is shown highlighted below:

a. Drop the Email Task on the workspace. b. Edit the Email Task. i. On the General tab, rename the Email task to eml_No_Salaries. ii. Select Fail parent if this task fails. iii. Select the Properties tab. iv. Email User Name = [email protected] v. Email Subject = No Salaries Flat File c. Email Text = The Salaries Flat File did not show up in time to execute the ODS_Employees load. Please remember to use the Student email address ‘[email protected]’ where xx is your Student ID’ Do not use the Administrator ID

i. Click OK. 170



3. Link the Timer Task tmr_ODS_EMPLOYEES to eml_No_Salaries Email Task. 4. Double-click the link between tmr_ODS_EMPLOYEES and eml_No_Salaries Email Task and enter $evnt_SALARIES.Status != SUCCEEDED as the link condition.

a. Click OK. 5. Right-Click on the workspace and select Arrange  Horizontal. a. Your workflow should look similar to the figure below:



171

b. Select the Control task from the Workflow Tasks toolbar. The Control task is shown highlighted below:

c. Drop the Control Task on the workspace. d. Edit the Control Task. i. On the General tab, rename the Control task to cntl_Stop_Workflow. ii. Select Fail parent if this task fails. iii. Under ‘Treat the Input Links as:’ section, select the OR radio button.

172



e. Select the Properties tab. i. Set the Value drop down to Stop Parent.

ii. Click OK. f.

Link the session s_m_ODS_EMPLOYEES_xx to the Control Task

g. Link the eml_No_Salaries email task to the Control Task h. Your workflow should appear similar to the figure below:

i. Save your work.



173

Walkthrough: Restart the Workflow

1. Navigate to the Workflow Monitor a. Select Filters  Tasks from the menu

b. Ensure that the Email task is selected.

c. Right-click on the Workflow Designer workspace and select Start Workflow. d. Observe the workflow. Note that the event wait and timer tasks are running

e. Create the watch_xx.txt file and place it on the S:\infa_shared\SrcFiles directory. This file may need to be created on a different mapped drive. Please ask your instructor where the watch file should be placed.

174



f.

The Workflow Run should appear similar to the figure below:

g. Feel free to experiment with the workflow. h. What happens if the watch_xx.txt file never shows up? i.

Do you get an email?

j.

What happens if the control task General Property of ‘Treat Input Links as:’ is changed to And?



175

176



Lab Activity 8.2: Troubleshooting $Source Connection Variables Guidelines

Scenario: There is a workflow in the Dev_Shared area that is failing due to some type of Lookup Connection error. You have been asked to copy the workflow to your working folder and troubleshoot the issue. Good Luck! Objectives: 

Gain Lookup Connection Variable Troubleshooting skills


Subject Review

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

Lab 8-2 Troubleshooting Lookup Connections


177

Walkthrough: Open the Repository Manager


a. PowerCenter will log you into the Repository Manager with the Devxx login used to log into the Designer. b. PowerCenter will automatically open your folder.

178



Walkthrough:

1. Double-Click the Dev_Shared folder to open it. a. Expand the Workflows subfolder of the Dev_Shared folder

Copy the Workflow

b. Select the wf_m_Error3_xx workflow as shown below.

c. Copy the Workflow i. Select menu option Edit  Copy. ii. Select your Devxx Folder. iii. Select the menu option Edit  Paste.



179

d. You will see the following confirmation message:

i. Click Yes. e. Note the Copy Messages in the Output Window of the Repository Manager.

f.

Expand the Sources, Targets, Mappings and Workflows subfolders of your Devxx folder. i. ODS_SALES should have been added as a Source. ii. The mapping contains 3 target instances. All 3 use the structure from a single target definition which references 1 physical DB table. iii. m_Errors3_xx mapping should have been added. iv. wf_m_Error3_xx workflow should have been added.

g. Close the Repository Manager

180



Walkthrough: Rename the Mapping

1. Refresh the 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 Targets now exist in your folder. iv. Open the mapping in the Mapping Designer workspace. v. Select the menu option Mappings  Edit. vi. Rename the mapping so that the xx is your Student id. vii. Click OK. b. Click Ctrl-S to save your work to the repository. i. Use the Output window to verify the mapping saved successfully to the repository.

Walkthrough: Refresh the Workflow Folder

1. Start the Workflow Manager a. If you are not already logged into the Workflow Manager, launch the application by clicking on the respective icon in the toolbar. b. Log into the Repository with your Devxx login. c. Open your folder. d. Navigate to the Workflow Designer using the Workflow Tools Toolbar. e. If a workflow exists on the workspace, use menu option Workflows  Close to clear the workspace. i. Right-Click on your folder and select Disconnect. ii. Right-Click on your folder and select Open. f.

Note that the wf_m_Error3_xx are now listed in your folder



181


1. Drag the workflow wf_m_Error3_xx onto the Workflow Manager Workflow Designer workspace. a. Select the menu option Workflow  Edit. i. Rename the Workflow so that the xx is your Student id. ii. Rename the Workflow Log so that the xx is your Student id. b. Right-click on the session and select Edit. i. Rename the session so that the xx reflects your student id. ii. Rename the Session Log so that the xx is your Student id. c. Click OK. d. Click Ctrl-S to save your work to the repository. i. Use the Output window to verify the mapping saved successfully to the repository.

Walkthrough:

1. Right-click on the workspace and Start the Workflow.


a. Navigate to the Workflow Monitor 182



b. Note that the session failed

c. Right-click on s_m_Error3_xx and select ‘Get Run Properties’. d. Review the Run Properties

As stated earlier, if your mapping has more than one target and the value for connection variable $Target was not been set, the session will fail. e. Navigate back to the Workflow Manager



183

f.

Edit the s_m_errors3_xx session i. Select the Mapping tab ii. Select Connections on the left side of the interface as shown below:

Note that the $Target Connection Value has not been set g. Select the small down arrow to the right of the $Target Connection Value

184



h. The Connection Browser will appear:

i. Select the ODSxx connection ii. Click OK i.

Your session connections should appear similar to the figure below:



185

i. Click OK ii. Save the Repository j.

Walkthrough: Troubleshoot Connection Failure #2

Start the Workflow again

1. Note the session failed again a. Right-Click on the s_m_errors3_xx session and select ‘Get Run Properties’

Although the error message is different than the last one, it clearly indicates there is still a problem with $Source or $Target connection variables. Since we have corrected the $Target problem, we must now take a look at $Source Variable Connection value. b. Navigate back to the Workflow Manager

186



c. Edit the s_m_errors3_xx session i. Select the Mapping tab ii. Select Connections on the left side of the interface as shown below:

Note that the $Source connection value is not set. However, also notice that ods_sales source does not have a connection!



187

d. Select the Source on the left side navigator

Note that the source is a file, and therefore, uses a file reader. Since the source does not use a relational connection, $Source cannot be used as the lookup connection variable. e. Navigate back to the Connections of the Navigator

After reviewing the multiple databases that are used to load this warehouse, you’ve determined that the lookup table is located in the EDW database. f.

188

Select the down arrow to the right of the DIM_DATES_WEEK_MONTH DB Connection Value.



g. The Connection Browser should appear:

i. Select the Use Object Radio button ii. Select the EDWxx connection h. Click OK twice i.

Save the Repository

j.

Start the Workflow



189

Walkthrough: Troubleshoot Connection Failure #3

1. Note the session failed again a. Right-Click on the s_m_errors3_xx session and select ‘Get Run Properties’

You’ve seen this error message before, correct? When there is an index on a target table, the Target Load Type must be set to Normal. It currently must be set to Bulk. Remember that you have two targets on this mapping, so be sure to check both of them. b. Navigate back to the Workflow Manager

190



c. Edit the s_m_errors3_xx session i. Select the Mapping tab ii. Select the ODS_SALES_TOTAL_MONTHLY target in the Navigator:

Note that the Target Load Type is indeed Bulk iii. Change the Target Load Type to Normal iv. Select Truncate Target Table option d. Select ODS_ SALES_TOTAL_WEEKLY i. Change the Target Load Type to Normal for it as well ii. Select Truncate Target Table option e. Click OK f.

Save the Repository

g. Start the Workflow Lab 8-2 Troubleshooting Lookup Connections


191

Walkthrough:


Congratulations!

Success!!

192



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

Scenario: The Mersche Motors data warehouse has a date dimension table that needs to be loaded. The date dimension needs to be loaded before any of the other dimension tables due to foreign key constraints. The Dates Stage table is loaded with one record for each date covered in the Operational Data Store (ODS). Each date has been described with the date attributes used in the ODS, such as the month name, quarter name, whether the date is a weekday or a weekend and so forth. In this lab, you will use the Dates Staging table to build a Dates ODS table for the warehouse. The ODS_Dates table will be used as a persistent lookup cache for other dimension loads. A primary key for the ODS_Dates table will be assigned using the Sequence Generator. Objectives: 

Create Sources and Targets based on shortcuts.



Create a Sequence Generator transformation.



Create unique integer primary key values using the NEXTVAL port.


Subject Review

PowerCenter will extract the dates from a shared relational table and load them into a shared relational target. All columns in the source table have matching columns in the target table. A primary key for the target table will be assigned using the Sequence Generator.

Lab 8.3 – m_ODS_Dates_xx


193


1. Log into the PowerCenter Designer using your Devxx username and password. a. Open your folder. b. Open the Source Analyzer workspace and select Clear All. c. Create a shortcut to the relational source, STG_DATES. This table will be found under the STG node of the Sources subfolder in DEV_SHARED. i. Rename it SC_STG_DATES. d. Your source should appear similar to the figure below:

e. Save your work.

194




Walkthrough: Create the mapping

1. Open the Target Designer. a. Right-click in the workspace and select Clear All. b. Create a shortcut to ODS_DATES. Rename it SC_ODS_DATES. c. Save your work.

1. Open the Mapping Designer. a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close. b. Create a new mapping named m_ODS_DATES_xx. c. Add the source definition SC_STG_DATES to the mapping. d. Add SC_ODS_DATES target to the mapping. e. Save your work

Walkthrough: Create a Sequence Generator

2. Select the Sequence Generator transformation from the Transformation toolbar with a single left click. The figure below shows the Sequence Generator transformation highlighted on the toolbar:

a. Move your mouse pointer onto the Mapping Designer workspace and single click your left mouse button. This will create a new Sequence Generator Transformation.



195

b. Confirm that your new Sequence Generator transformation appears similar to the figure below:

c. Edit the Sequence Generator Transformation. i. Rename it seq_ODS_DATES_Date_Key. ii. Select the Properties tab and observe the properties available in the Sequence Generator. iii. Check the Reset Transformation attribute value. iv. Click the OK button to return to the Normal view of the Sequence Generator. d. Save your work.

196



Walkthrough: Link the Target Table

1. Link all the ports from the Source Qualifier transformation to the corresponding columns in the target object utilizing the Autolink by Name feature.

a. Link the NEXTVAL port from Seq_ODS_DATES_Date_Key to the Date_Key port of the SC_ODS_DATES target. b. Save your work. c. Verify the mapping is valid in the Output window. If the mapping is not valid, correct the invalidations that are displayed in the message.


1. Launch the Workflow Manager and sign into your assigned folder. a. If there is a workflow visible in the workspace, close it by selecting Workflows  Close. b. Create a new workflow called wf_m_ODS_DATES_ xx. i. Edit the s_m_ODS_DATES_xx session. ii. Under the Mapping tab: 1. Set the relational source connection for SQ_SC_STG_DATES to STGxx. 2. Set the relational target connection for SC_ODS_DATES target to ODSxx. 3. Set the Target Load Type to Normal.



197

4. Check the property to Truncate the target table option in the target properties. c. Save your work. i. Check the output to ensure the workflow saved to the repository successfully. d. Start the workflow. e. Review the session properties. f.

Your information should appear as displayed in the figure below.

g. If your session failed or had errors troubleshoot and correct them by reviewing the session log and make any necessary changes to your mapping or workflow.

198







199

200



Lab Activity 8.4: Creating a Lookup Cache and Loading the ODS Promotions Target Guidelines

Scenario: Mersche Motors runs a number of promotions that begin and end on certain dates. The promotions are stored in the ODS Promotions table. This table also stores the start and expiring dates as date keys that reference the ODS_DATES table. However, that data just became available due to the creation of the previous mapping. We will use the newly created date keys to update the ODS Promotions table. Objectives: 

Understand how to configure and use a persistent Lookup cache.


Subject Review

The ODS_PROMOTIONS table requires start and expiration date keys. These exist in the ODS_DATES table that was populated in the previous lab. To obtain these date keys, which were created by the sequence generator, it will be necessary to perform a Lookup to the ODS_DATES table in the ODS database. The ODS_DATES table changes infrequently so it will be loaded into cache in a persistent state. The lookup cache can be used often by other Mappings that load ODS tables.

Lab 8.4 – m_ODS_Promotions_xx


201


1. Log into the PowerCenter Designer using your Devxx username and password. a. Open your folder. b. Open the Source Analyzer workspace and select Clear All. c. Create a shortcut to the relational source, PROMOTIONS. This table will be found under the SDBU node of the Sources subfolder in DEV_SHARED. i. Rename it SC_PROMOTIONS. d. Your source should appear similar to the figure below:

e. Save your work.


202

1. Open the Target Designer. a. Right-click in the workspace and select Clear All. b. Create a shortcut to ODS_PROMOTIONS. Rename it SC_ODS_PROMOTIONS. c. Save your work.



Walkthrough:

1. Open the Mapping Designer. a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close.

Create the mapping

b. Create a new mapping named m_ODS_PROMOTIONS_xx. c. Add the source definition SC_PROMOTIONS to the mapping. d. Add SC_ODS_PROMOTIONS target to the mapping. e. Use Autolink by Name to link SQ_SC_PROMOTIONS and SC_ODS_PROMOTIONS. f.

Save your work.

g. Your mapping should appear similar to the figure below.



203

Walkthrough:

1. Examine the figure below:

Create Lookups for the Start and Expire Date Keys

f

a. In Figure 2, compare START_DATE and EXPIRE_DATE in SQ_SC_PROMOTIONS to START_DK and EXPIRY_DK in the SC_ODS_PROMOTIONS target table. Notice that these two ports are not connected and the datatypes are different. The target requires key values (number), not dates. In what table do these Date Key values exist? b. Examine the figure below:

The ODS Dates table was populated by the previous lab, the DATE_KEY was generated by the seq_ODS_DATES_Date_key Sequence Generator transformation and DATE_VALUE has a datetype of datetime..

c. To acquire the value for the START_DK in the ODS_SC_PROMOTIONS target table, you need to perform a Lookup on the ODS_DATES table. i. You will base the Lookup Condition on the _________________ port from SQ_SC_PROMOTIONS Source Qualifier and the _________________ column in the ODS_DATES Lookup table. ii. Similarly, to acquire the value for the EXPIRE_DK in the SC_ODS_PROMOTIONS target, you will need a second Lookup on the ODS_DATES as well.

204



iii. You will base the Lookup Condition on the __________________ port from SQ_SC_PROMOTIONS Source Qualifier and the ________________ column in the ODS_DATES Lookup table. d. Add a Lookup Transformation to the mapping based on the SC_ODS_DATES (shortcut to the ODS_DATES) target table.

i. Rename the Lookup Transformation to lkp_START_DATE_KEY. e. Click OK twice. f.

Click YES to verify the “Look up Condition is empty”. You will define this shortly.

g. Now drag and drop the START_DATE port from SQ_SC_PROMOTIONS Source Qualifier to an empty port in the lkp_START_DATE_KEY transformation. i. Make START_DATE input only. ii. Rename START_DATE to IN_ START_DATE.



205

h. Define the Lookup Condition to look like the figure below:

i.

On the Properties tab, verify the following values: i. Lookup Table Name = ODS_DATES (default). ii. Lookup Caching Enabled = Checked (default). iii. Lookup Cache Persistent = Checked (needs to be set). iv. Cache File Name Prefix = LKP_ODS_DATES_xx (where xx is your student number).

j.

Link the DATE_KEY port from the lkp_START_DATE_KEY transformation to the START_DK port in the SC_ODS_PROMOTIONS target.

k. Save your work. Note: Notice that this transformation has many ports. We could have unchecked Output column on all ports except for the ones that we need but since this lookup transformation will be persistent it would have limited its functionality for all other mappings that might leverage that data. The lkp_START_DATE_KEY transformation will not retrieve values for the EXPIRY_DK because the lookup conditions will be different. l.

Create a second Lookup Transformation called lkp_EXPIRE_DATE_KEY by copying lkp_START_DATE_KEY.

m. Make the changes necessary to the Lookup to ensure that the EXPIRE_DATE finds the proper DATE_KEY. i. Rename IN_START_DATE to IN_EXPIRE_DATE ii. Verify the Lookup Condition is correct. n. Link the EXPIRE_DATE port from the SQ_SC_PROMOTIONS Source Qualifier to the IN_EXPIRE_DATE port in the lkp_EXPIRE_DATE_KEY transformation. o. Link DATE_KEY port from the lkp_EXPIRE_DATE_KEY transformation to the EXPIRY_DK port in the SC_ODS_PROMOTIONS target.

206



p. Save your work.


1. Launch the Workflow Manager and sign into your assigned folder. a. If there is a workflow visible in the workspace, close it be selecting Workflows  Close. b. Create a new workflow called wf_m_ODS_PROMOTIONS_ xx. i. Edit the s_m_ODS_PROMOTIONS_xx session. ii. Under the Mapping tab: 1. Set the Target Load Type to Normal. 2. Click on Connections in the Navigator. 3. Set the relational source connection for SQ_SC_PROMOTIONS to SDBU. 4. Set the relational target connection for SC_ODS_PROMOTIONS target to ODSxx. c. Set the $Target connection value to ODSXX.



207

d. The Mapping tab should appear similar to the figure below:

e. Save your work. f.

Check the output when to ensure the workflow saved to the repository successfully.

g. Start the workflow. h. Review the session properties.

208



i.


j.




209



By setting the Lookup Cache Persistent property on the Lookup Transformation five files were created on the machine hosting the Integration Service, see figure below. Note that these files are on the server machine, not your local (client) computer and that the names will correspond to the name that you entered in the Cache File Name Prefix Lookup property. To view these files, you will need to map to the file system on the Integration Service host machine. In this system, the files will be placed on S:\infa_shared\Cache. Verify that the files have a timestamp similar to when you ran the above workflow.

210



Lab Activity 8.5: Active Lookups Guidelines

Scenario: Mersche Motors management needs a report to determine the total amount each customer owes the company regardless of where the sale took place. A system date is also required so that management will know how much the customer owes on a certain date. Management would also like to get a total of all the orders each customer has with the company as well. Objectives: 

Exam and use Informatica v9 multiple row return lookups.


Subject Review

A multiple row return lookup will be used in conjunction with an aggregator to achieve this calculation.

Lab 8.5 – m_ODS_Customer_Billing_xx


211

Walkthrough: Create a Source Definition Shortcut

1. Launch the Designer and login to your assigned folder. a. Open the Source Analyzer workspace and select Clear All. i. Create a shortcut to the ODS_CUSTOMERS relational table found in the DEV_SHARED folder. b. Rename it SC_ODS_CUSTOMERS.


Walkthrough: Create the mapping

1. Create a shortcut to the ODS_CUSTOMER_BILLING definition from the DEV_SHARED folder in your folder. Name the shortcut SC_ODS_CUSTOMER_BILLING.

1. Open the Mapping Designer. a. Create a new mapping named m_ODS_CUSTOMER_BILLING_xx b. Add the SC_ODS_CUSTOMERS source definition. c. Add the SC_ODS_CUSTOMER_BILLING target definition. d. Save your work.

212



Walkthrough: Create a Lookup Transformation

1. Create a Lookup Transformation named MultiRowLkp_CUSTOMER_ORDERS. a. On the Select Lookup Table for Lookup Transformation dialog box, select the Return All Values on Multiple Match checkbox.

b. Import the lookup table from SDBU.CUSTOMER_ORDERS. c. Select all of the output ports from SQ_SC_ODS_CUSTOMERS. Copy and link them to the lookup. d. Set the Lookup Condition to CUST_ID = CUSTOMER_NO



213

e. Select the Properties tab i. Note that the Lookup Policy on Multiple Match is disabled and has a value of ‘Use All Values’.

f.

214

Click on the Ports tab



g. Ensure that only CUST_ID, ORDER_NO and UNIT_COST are all configured as lookup ports. h. Delete ports ORDER_DATE, SHIP_DT, ITEM_NO, QUANTITY, ITEM_DESC as they are not needed at the target. i.

Your Ports tab should appear similar to the figure below:

j.

Click OK.

k. Save your work.



215

Walkthrough: Create an Aggregator Transformation

1. Create an Aggregator Transformation called agg_ODS_CUSTOMERS. a. Ports to copy and link from Lookup to the aggregator transformation: i. CUST_ID ii. ORDER_NO iii. UNIT_COST iv. DEALERSHIP_ID v. CUSTOMER_NO vi. NAME vii. POSTAL_CODE viii. CONTACT_DATE ix. GENDER_CATEGORY x. SENIOR_FLAG xi. HIGH_INCOME_FLAG

b. Group by CUST_ID. c. Add 3 new output ports i. Output Port #1 1. Name = SUM_UNIT_COST 2. Datatype = Decimal 3. Precision = 35, 2 4. Expression = SUM(UNIT_COST) 5. ii. Output Port #2 1. Name = SYSTEMDATE 2. Datatype = DateTime 3. Expression = Sysdate

216



iii. Output Port #3 1. Name = COUNT_CUSTOMER_ORDERS 2. Datatype = Decimal 3. Precision = 10 4. Expression = COUNT(ORDER_NO)

d. Make the CUST_ID, ORDER_NO and UNIT_COST input only ports.

e. The Ports tab should appear similar to the figure below:



217

i. Link the agg_ODS_CUSTOMER ports to the target as shown in the figure below:

f.

Walkthrough: Create and Execute the Workflow

Save your work.

1. Launch the Workflow Manager and sign into your assigned folder. a. Create a new workflow named wf_m_ODS_CUSTOMER_BILLING_xx. b. Edit the session s_m_ODS_CUSTOMER_BILLING_xx i. Set the relational connection value for SQ_SC_ODS_CUSTOMER to ODSxx. ii. Set the connection value for SC_ODS_CUSTOMER_BILLING target to ODSxx. iii. Set the Load Target Type to Normal for the target. iv. Set the connection value for Multirowlkp_CUSTOMERS_ORDERS to SDBU. c. Save your work and start the workflow

218



d. Review the session properties, your information should appear similar to the figure below:

One record has rejected. Why?



219

Walkthrough: Review Data Results

220




Lab Activity 9: Updating Targets Using PowerCenter Guidelines

Scenario: The Mersche Motors data warehouse employee table is updated on a daily basis. Source rows from the transactional area need to be tested to determine if the row already exists in the dimension table. Rows need to be tagged for update or insert accordingly. Any rows containing bad data will need to be written to an error file. Objectives: 

Employ Update Strategy Transformation to tag rows for INSERT or UPDATE.



Utilize the Router Transformation to conditionally route rows to different target instances.



Use Default values option for NULL data replacement.



Overriding Target writer option.


Subject Review

Rows from the TRANS_EMPLOYEES table need to be loaded into the DIM_EMPLOYEES table. Before loading the rows, EMPLOYEE_ID needs to be tested for NULL values. Invalid rows need to be written to an error file. Valid rows need to be tested to determine if they exist already in the DIM_EMPLOYEES target table and tagged for either INSERT or UPDATE accordingly. Finally, any rows sent to the DIM_EMPLOYEE table need to get valid date keys from DIM_DATES.

Lab 9 – m_DIM_Employees_xx


221

Walkthrough: Copy the Mapping

1. Copy mapping from a shared area a. Log into the PowerCenter Designer using your Devxx username and password. b. Open your folder. c. Navigate to the Mapping Designer using the Designer Tools Toolbar. d. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close. e. Copy m_DIM_EMPLOYEES_xx partial mapping from the DEV_SHARED folder to your student folder. f.

Click ‘Yes’ on the Dependencies Dialog box.

g. Rename the new mapping m_DIM_EMPLOYEES_xx using your student number. h. Save your work.

222



Walkthrough:

1. Edit the Expression Transformation a. Open the mapping m_DIM_EMPLOYEES_xx.

Edit the Expression Transformation

b. Your mapping should appear similar to the figure below.

c. Edit the exp_NULL_EMPLOYEE_ID expression transformation and add a Default value of 99999 to the EMPLOYEE_ID port. i. Click the

button to validate the default entry and click OK.

d. Edit the lkp_DIM_EMPLOYEES_EMPLOYEE_ID Lookup Transformation. Note the Lookup Condition: EMPLOYEE_ID = in_EMPLOYEE_ID This condition is looking for a value where the Employee ID from the DIM_EMPLOYEE Target table is equal to the Employee ID coming from the Trans_Employees Source. i. Rename the EMPLOYEE_ID to DIM_EMPLOYEE_ID 1. Note: DIM_EMPLOYEE_ID represents the ID that would come from the lookup on the Target, DIM_EMPLOYEE. 2. If that value comes back as a null, then the Employee_ID coming from the source has no matching record for that Employee at the Target. Meaning it is a new record and by definition, an Insert Record.



223

3. If that value comes back as not null then, there is a record at the Target matching the Employee_ID coming in from the Source, so the record is an update. ii. Rename the in_EMPLOYEE_ID to Source_EMPLOYEE_ID 1. Note: Source_Employee_ID represents the ID that would come from the Source, Trans_Employees. 2. The value could come into the lookup as ‘99999’, which would mean it was null at the source, as determined by our Default Value on the port in the Expression Transformation. A null coming from the source has been determined to be an erroneous record. 3. Source_Employee_ID could contain a not null value, which would mean it is either an insert or update for the DIM_EmployeeTarget. Logic written in the Groups Tab of the Router Transformation will determine which it is. 4. Renaming the Target Employee_ID makes the mapping more readable, however, it will cause PowerCenter to submit an erroneous SQL statement to the database similar to the following

iii. Therefore, we should override the SQL in the lookup (lkp_ DIM_EMPLOYEES_EMPLOYEE_ID) and change the SQL so it refers to the correct field name.

224



e. Click the Properties tab of the Lookup Transformation. f.

Click the SQL Override dropdown arrow.

g. Click the Generate SQL button. h. Change the SQL Statement i. Edit the SQL written by PowerCenter from: SELECT DIM_EMPLOYEES.DIM_EMPLOYEE_ID as DIM_EMPLOYEE_ID FROM DIM_EMPLOYEES To SELECT DIM_EMPLOYEES. EMPLOYEE_ID as DIM_EMPLOYEE_ID FROM DIM_EMPLOYEES



225

i.

The SQL should appear similar to the figure below:

j.

Click OK.

k. Save your work.

Walkthrough: Create a Router Transformation

The Router Transformation will be used to determine which rows will be inserted, updated or sent to the error file. This will be done by checking the value of the EMPLOYEE_ID port. 2. Add a Router Transformation to the mapping:

a. Drag both ports from lkp_DIM_EMPLOYEES_EMPLOYEE_ID into the Router. b. Drag all ports except EMPLOYEE_ID from exp_NULL_EMPLOYEE_ID to the Router.

226



3. Your Router should appear similar to the figure below:

a. Edit the Router Transformation i. Rename the Router rtr_DIM_EMPLOYEES. ii. In the Groups tab add 3 new groups using the Add new group icon. 1. Name the first group INSERTS: 2. Add the Group filter Condition: ISNULL(DIM_EMPLOYEE_ID) AND Source_EMPLOYEE_ID != 99999 3. Name the second group UPDATES: Add the Group filter Condition: NOT ISNULL(DIM_EMPLOYEE_ID) AND Source_EMPLOYEE_ID != 99999 4. Name the third group ERRORS: 5. Add the Group filter Condition: Source_EMPLOYEE_ID = 99999



227

Your Router should appear similar to the figure below:

228



Walkthrough:

1. Add an Update Strategy Transformation named upd_INSERTS to the mapping. a. In the Router, scroll down to the INSERTS group and drag all ports, except DIM_EMPLOYEE_ID1 and HIRE_DATE1, to upd_INSERTS update strategy.

Create an Update Strategy for inserted records

b. Edit the upd_INSERTS Update Strategy i. Rename the Source_EMPLOYEE_ID1 port to EMPLOYEE_ID1. ii. In the Properties tab iii. Select the Update Strategy Expression Value box. Delete the 0 and enter DD_INSERT. See the figure below:

c. Click OK.



229

d. Your mapping should look similar to the figure below:

Walkthrough: Create a Lookup to DIM_DATES

230

1. Create a Lookup Transformation named lkp_DIM_DATES_INSERTS that references the DIM_DATES target table. The definition of the target table was copied to your folder during the copy mapping operation:



a. Pass DATE_ENTERED1 from upd_INSERTS to lkp_DIM_DATES_INSERTS. b. Edit the lkp_DIM_DATES_INSERTS Lookup transformation i. Make all the ports Lookup only except 1. DATE_KEY – should be Output and Lookup. ii. Rename the DATE_ENTERED1 port to IN_DATE_ENTERED. c. In_Date_Entered – Should be Input only. d. Lkp_DIM_DATES_INSERTS ports tab should appear similar to the figure below:



231

e. Create the condition DATE_VALUE = IN_DATE_ENTERED. Ensure that you use DATE_VALUE, not DATE_KEY. f.

In the Properties tab set the following values: i. Lookup Cache persistent = Checked (needs to be set) Cache File Name Prefix = lkp_DIM_DATES_xx

Walkthrough: Link Insert Update Strategy to Insert Target

1. Link upd_INSERTS and lkp_DIM_DATES_INSERTS to the Target a. Link the DATE_KEY port from lkp_DIM_DATES_INSERTS to the INSERT_DK column in the DIM_EMPLOYEES_INSERTS target. b. Right-click anywhere in the workspace and select Autolink… i. Select upd_INSERTS from the From Transformation drop down box ii. Select DIM_EMPLOYEES_INSERTS from the To Transformation box. iii. Select the More>> button and enter a ‘1’ for the From Transformation Suffix. c. You screen should appear similar to the figure below:

d. Click OK.

232



e. Your mapping should look similar to the figure below:

f.

Walkthrough:

Save your work.

1. Create an Update Strategy for UPDATES

Create Update Strategy for the updated record

a. Create an Update Strategy transformation named upd_UPDATES. b. In the Router, scroll down to the UPDATES Router group and drag all ports, except Source_EMPLOYEE_ID3 and HIRE_DATE3 to the upd_UPDATES update strategy transformation. c. Edit the upd_UPDATES transformation i. Rename the DIM_EMPLOYEE_ID3 port to EMPLOYEE_ID3 ii. In the Properties tab, select the Update Strategy Expression value box. Delete the 0 and enter DD_UPDATE.



233

Walkthrough:

1. Create Second Lookup to DIM_DATES a. Right-click on the existing lkp_DIM_DATES_INSERTS lookup transformation and select Copy.

Create Lookup for Insert Date Information

b. Move the cursor to the workspace, right-click and select Paste. c. Link DATE_ENTERED3 from upd_UPDATES to IN_DATE_ENTERED in the new Lookup transformation. d. Edit the new Lookup transformation i. Rename the lookup to lkp_DIM_DATES_UPDATES. e. Ensure the Lookup condition is: DATE_VALUE = IN_DATE_ENTERED f.

The Update portion of your mapping should appear similar to the figure below:

234



Walkthrough:

1. Link upd_UPDATES and lkp_DIM_DATES_UPDATES to the Target

Link Updates Lookup to Update Target

a. From lkp_DIM_DATES_UPDATES, link DATE_KEY to UPDATE_DK in DIM_EMPLOYEES_UPDATES target. b. Right-click anywhere in the workspace and select Autolink… i. Select upd_UPDATES from the From Transformation drop down box ii. Select DIM_EMPLOYEES_UPDATES target from the To Transformation box. iii. Select the More>> button and enter a ‘3’ for the From Transformation Suffix. iv. Your screen should look similar to the figure below:

c. Click OK. d. Ensure that DIM_Employee_ID3 is linked to EMPLOYEE_ID of the Target Transformation, DIM_EMPLOYEES_UPDATES.



235

e. Your mapping should look similar to the figure below:

f.

236

Save your work.



Walkthrough:

1. Link ERRORS Router Group to the Target

Link Router Errors Group to Error Target

a. Using Autolink… i. Select the ERRORS group of rtr_DIM_EMPLOYEES from the From Transformation drop down box ii. Select DIM_EMPLOYEES_ERR from the To Transformation box iii. Select the More>> button and enter a ‘4’ for From Transformation Suffix. iv. The Autolink dialog should appear similar to the figure below:

b. Click OK.



237

c. Ensure that Source_Employee_ID4 is linked to EMPLOYEE_ID of the Target Transformation.

d. Save your work and ensure the mapping is valid. e. Arrange all Iconic. Your mapping should appear similar to the figure below:

238



Walkthrough:

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


i. If there is a workflow visible in the workspace, close it be selecting Workflows  Close. b. Create a new workflow called wf_m_DIM_EMPLOYEES_xx. i. Edit the s_m_ DIM_EMPLOYEES_xx session. ii. Under the Mapping tab: 1. Set the relational source connection for SQ_TRANS_EMPLOYEES to SDBU. 2. Set the relational target connection for DIM_EMPLOYEES_INSERTS target to EDWxx. 3. Set the Target Load Type to Normal. 4. Set the relational target connection for DIM_EMPLOYEES_UPDATES target to EDWxx. 5. Set the Target Load Type to Normal. 6. Select the DIM_EMPLOYEES_ERR target. Change the Writer value to File Writer as shown below.

Note: To create a flat file as a target instead of the original table, simply change the Writers type from Relational to File. A fixed width flat file based on the format of the target definition will be created automatically. The properties of this file can also be altered by the user.



239

7. Scroll down in Properties to the Output filename. Rename the output file so that it includes your student number.

iii. Select Connections. Set $Target Connection Value to EDWxx.

240



The mapping tab of s_m_DIM_Employees_xx should appear similar to the figure below:



241

Walkthrough:

1. Add a Source Filter Override a. Click on the SQ_TRANS_EMPLOYEES source

Override the Source Filter

b. In the Source Filter enter: DATE_ENTERED = ‘01/02/2003’ Note: It is sometimes easier to add a quick Source filter in the Session than to go back and modify the mapping, save it, refresh the session, save it, then run the workflow. SQL overrides will override any entries in the mapping until the override is deleted. Make sure if using ‘shortcuts’ the prefix to the table is deleted before saving the filter.

242



Walkthrough:

a. Save your work.

Start the Workflow

b. Check the output when to ensure the workflow saved to the repository successfully. c. Start the workflow. d. Review the session properties. e. Your information should appear as displayed in the figure below.

f.




243



Scroll all the way to the right and confirm that the INSERT_DK column was updated and not the UPDATE_DK column. Also, you may want to review the three rows that were written to the error file. Navigate to S:\infa_shared\Tgtfiles or see the instructor for the location of the files. If the server is running on UNIX, you may need special permission from your administrator to see the files.

244



Walkthrough:

1. Prepare, Execute and Monitor the Second Run

Execute the Workflow for a Second Time

a. Edit the s_m_DIM_EMPLOYEES_xx session task. i. In the Mapping tab, 1. Click SQ_TRANS_EMPLOYEES in the Navigator window. 2. Scroll down the Properties section and edit the Source Filter to reflect day two loading: 01/03/2003. ii. Save and Execute the workflow. b. Review the session properties and transformation statistics. c. They should appear the same as displayed in the figure below:



245

d. Review the DIM_EMPLOYEES target data from the Designer. Scroll to the far right of the data screen and notice that there are now entries for UPDATE_DK and new entries at the bottom of the list for INSERT_DK.

246



Lab Activity 10.1: Using PowerCenter to Load the Fact Sales Target Guidelines

Scenario: Mersche Motors dealerships sometimes give aggressive discounts that are outside the authorized range. These type of discounts are a small percentage compared to the number of rows being processed but the information needs to be processed accordingly. Also, even though the source dimension table contains 7 days of data this production execution will load the entire dimension table into the SALES_FACT table in a single workflow. Objectives: 

Employ the use of an Unconnected Lookup to conditionally check rows as they pass through the mapping.



Utilize the Aggregator Transformation to group and sum values.



Use Mapping Parameters to improve the flexibility of the mapping.


Subject Review

The information needed resides in two separate dimension tables. To compound this, the relationship between the two tables does not exist on the database. Referential integrity will have to be created within PowerCenter. Special formulas are needed to process the discounts out of range. To make the mapping more efficient, mapping parameters and variables will be used.

Lab 10.1 – m_Fact_Sales_xx


247

Walkthrough: Copy the Mapping

1. Log into the PowerCenter Designer using your Devxx username and password. a. Open your folder. b. Navigate to the Mapping Designer using the Designer Tools Toolbar. c. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close. d. Copy m_FACT_SALES_xx partial mapping from the DEV_SHARED folder to your student folder. e. Rename the new mapping m_ FACT_SALES_xx using your student number. f.

Right-click and select Arrange All Iconic

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

h. Save your work. i.

Open the Source Analyzer workspace and select Clear All.

j.

Drag the DIM_TRANSACTIONS and DIM_PAYMENT relational sources from the EDW node onto the workspace. The PAYMENT_DESC column from DIM_TRANSACTIONS and the PAYMENT_TYPE_DESC column from the DIM_PAYMENT table are logically related so we can build a join on them. They both contain payment type description information. Note: Creating the PK-FK relationship within the Source Analyzer does not create the relationship on the actual physical database. The relationship is created on the Source Definitions within the PowerCenter Repository only.

248



k. Your source definitions should appear the same as displayed below:



249

Walkthrough: Edit the Lookup

1. Edit the lkp_DIM_DATES Lookup Transformation a. Edit the lkp_DIM_DATES Lookup b. In the Properties tab c. Change the Cache File Name to lkp_DIM_DATES_xx

d. Click OK.

250



Walkthrough:

1. Navigate back to the Mapping Designer.

Create a Mapping Parameter

a. Add a mapping parameter by clicking Mappings  Parameters and Variables. b. On the Declare Parameters and Variables dialog, click the Add a new variable to this table icon as shown in the figure below:

c. Create a new parameter: i. Parameter Name = $$MAX_DISCOUNT ii. Type = Parameter iii. Datatype = Decimal iv. Precision = 15, 2 v. For the initial value, enter 17.25



251

d. The mapping parameter should appear similar to the figure below:

e. Click OK. f.

252

Save your work.



Walkthrough: Create an Unconnected Lookup

1. Create a Lookup transformation using the DIM_PROMOTIONS relational target table. You will use your EDW connection as ODBC Data Source as shown below:

a. Edit the lookup tranformation i. Name the lookup lkp_DIM_PROMOTIONS. ii. Under the Ports tab 1. Click on PROMO_ID, then click the Copy icon Paste icon

, and then the

.

2. Name the new port IN_PROMO_ID and make it an input only port. 3. Make DISCOUNT the return port. 4. Uncheck the Output ports for all other ports except PROMO_ID and DISCOUNT.



253

b. The lookup should look the same as the figure below:

c. Create the lookup condition comparing PROMO_ID and IN_PROMO_ID. d. Click OK and save your work.

254



Walkthrough:

1. Add Unconnected Lookup Test to Expression a. Edit the exp_DISCOUNT_TEST Expression transformation.

Add Unconnected Lookup Test

b. If the IN_DISCOUNT port has a value greater that the value passed in via a mapping parameter, then we need to get an acceptable value from the DIM_PROMOTIONS table. The variable port v_DISCOUNT will be used to hold the return value. Edit the v_DISCOUNT variable port and add the expression: IIF(IN_DISCOUNT > $$MAX_DISCOUNT, :LKP.LKP_DIM_PROMOTIONS(PROMO_ID), IN_DISCOUNT) The discount is held as a whole number. We need to change this to a percentage and apply it against the selling price to derive the dollar value of the discount. Edit the output port OUT_DISCOUNT and add the expression: V_DISCOUNT / 100 * SELLING_PRICE

Walkthrough:

1. Create an Aggregator transformation named agg_FACT_SALES.

Create an Aggregator Transformation

a. Drag the PRODUCT_KEY PORT from lkp_DIM_PRODUCT to agg_FACT_SALES b. Drag the DATE_KEY port from lkp_DIM_DATES to agg_FACT_SALES. c. Drag the following ports from the Expression transformation to the Aggregator: i. PAYMENT_ID ii. CUST_ID iii. DEALERSHIP_ID iv. PROMO_ID v. SELLING_PRICE vi. UNIT_COST vii. SALES_QTY viii. HOLDBACK ix. REBATE x. OUT_DISCOUNT



255

d. Your mapping should look similar to the figure below:

e. Open the Aggregator and re-order the key ports in the following order: i. CUST_ID ii. PRODUCT_KEY iii. DEALERSHIP_ID iv. PAYMENT_ID v. PROMO_ID vi. DATE_KEY f.

256

Group By these ports.



g. Uncheck the output ports for SELLING_PRICE, UNIT_COST and SALES_QTY. i. Rename: ii. SELLING_PRICE to IN_SELLING_PRICE iii. UNIT_COST to IN_UNIT_COST iv. SALES_QTY to IN_SALES_QTY v. OUT_DISCOUNT to DISCOUNT

h. Add the following new ports: i. Create a new output port after the DISCOUNT port.


Port Name

OUT_UNITS_SOLD

Datatype

Decimal

Precision

3

Expression

SUM(IN_SALES_QTY)


257

ii. Create a new output port after the OUT_UNITS_SOLD Port Name

OUT_REVENUE

Datatype

Decimal

Precision

15,2

Expression

SUM((IN_SELLING_PRICE * IN_SALES_QTY) - DISCOUNT HOLDBACK - REBATE)

iii. Create a new output port after the OUT_REVENUE port.

i.

258

Port Name

OUT_COST

Datatype

Decimal

Precision

15,2

Expression

SUM(IN_UNIT_COST)

The Aggregator ports should be the same as displayed in the figure below:



j.

Use Autolink by name to link the ports from the agg_FACT_SALES transformation to the FACT_SALES target table. You will need to use the prefix of OUT_ to link all of the ports.

k. The results should appear the same as the figure below:

l.

Save your work.

m. Iconize the mapping.



259


1. Launch the Workflow Manager and sign into your assigned folder. a. If there is a workflow visible in the workspace, close it be selecting Workflows  Close. b. Create a new workflow called wf_m_FACT_SALES_ xx. i. Edit the s_m_FACT_SALES_xx session. ii. Under the Mapping tab: 1. Set the relational source connection for SQ_DIM_TRANSACTIONS_PAYMENT to EDWxx. 2. Set the relational target connection for FACT_SALES target to EDWxx. 3. Set the Target Load Type to Normal. 4. Set $Target Connection Value to EDWxx. 5. Set your Connections to the entries below:

c. Save your work. d. Start the workflow. e. Review the session properties

260



f.


g. If your session failed or had errors troubleshoot and correct them by reviewing the session log and make any necessary changes to your mapping or workflow.





261

262



Lab Activity 10.2: Create a Mapplet Guidelines

Scenario: The Team Lead has noticed that there are other situations where we can reuse some of the transformations developed in the FACT_SALES mapping. Objectives: 

Use a mapplet in a mapping


Subject Review

To take advantage of previously created objects, we will create a mapplet from existing objects used in a previous mapping. This mapplet can then be used in other mappings.



263

Walkthrough:

1. In the Mapping Designer, re-open the m_FACT_SALES_xx mapping. a. Highlight the following five transformations by holding down the Ctrl key and pressing the left mouse button:

Create the Mapplet

i. lkp_DIM_PROMOTIONS ii. lkp_DIM_PRODUCT iii. lkp_DIM_DATES iv. exp_DISCOUNT_TEST v. agg_FACT_SALES b. Select Edit  Copy or the Ctrl+C. c. Open the Mapplet Designer. Create a mapplet named mplt_AGG_SALES. i. Select Edit  Paste or the Ctrl+V. ii. Right-Click in the workspace and Arrange All. iii. Select the Scale to Fit icon. d. Your mapplet definition should look the same as the figure below:

e. Add a Mapplet Input transformation

f.

264

Add a Mapplet Output Transformation



g. From the exp_DISCOUNT_TEST transformation, drag all Input ports to the Mapplet Input transformation h. From the Aggregator agg_FACT_SALES, drag all Output ports to the Output transformation. i.

Select the Scale to Fit icon.

j.

The mapplet should look similar to the figure below:

k. Save your work. l.

Notice that the mapplet is invalid. Scroll through the messages in the output window. They point to the expression exp_DISCOUNT_TEST as having an invalid symbol reference. The reference to the parameter $$MAX_DISCOUNT is invalid as it does not exist within the mapplet parameter definition. Note: Mapping parameters and variables that are created in a mapping are not available for use in the mapplet that is called from the mapping.

Walkthrough:

1. Select Mapplets  Parameters and Variables.

Create a new Mapplet Parameter

i. Parameter Name = $$MAX_DISCOUNT ii. Type = Parameter iii. Datatype = Decimal iv. Precision = 15,2 v. Initial Value = 17.25 b. Save your work.



265

Walkthrough: Add the Mapplet to the Mapping

1. Make a copy of the m_FACT_SALES_xx mapping and open it in the Mapping Designer. a. Delete the 5 transformations that you previously copied to the mapplet. b. In the Navigator, select Mapplets -> mplt_AGG_SALES. Drag and drop it onto the Mapplet workspace. c. Use Autolink by Name to link the ports from the SQ_DIM_TRANSACTIONS_PAYMENT to the mplt_AGG_SALES input. d. Manually link the DISCOUNT port to the IN_DISCOUNT port. e. Use Autolink by name to link the Output portion of the mapplet to the target. You will need to specify ‘OUT_ ‘for the prefix and ‘1’ for the suffix. f.

Save your work

g. You mapping should look the same as the figure below:

266



Lab Activity 11: Mapping Workshop Guidelines

Scenario: Mersche Motors management wants to be able to analyze how certain promotions are performing. They want to be able to gather the promotions by day for each dealership for each product being sold. The data warehouse now has the perfect architecture to deliver that information. Objectives: 

Design and create a mapping to load an aggregate table.


Subject Review

The instructions will provide enough detail for you to design and build the mapping necessary to load the promotions aggregate table. It is suggested that you use the Velocity best practices that have been discussed during the course. The workshop will provide mapping specification documents that can be to design the mapping. If you are unclear on any instructions, please ask the instructor.


Lab 11 – m_FACT_Promotions_Agg_Daily_xx

267

Velocity Deliverable: Mapping Specifications Mapping Name:

Source System(s): Target System(s): Initial Rows:

Rows/Load:

Short Description: Load Frequency: Preprocessing: Post Processing: Error Strategy: Reload Strategy: Unique Source Fields (PK): Dependant Objects

268



SOURCES Tables Table Name

System/Schema/Owner

Selection/Filter

File Location

Fixed/Delimited

Files File Name

Additional File Info

TARGETS Tables

Schema Owner

Table Name

Update

Delete

Insert

Unique Key

Files File Name


File Location

Fixed/Delimited



269

LOOKUPS Lookup Name Table

Location

Match Condition(s) Persistent / Dynamic Filter/SQL Override

HIGH LEVEL PROCESS OVERVIEW

Source

Target

PROCESS DESCRIPTION DETAIL SOURCE TO TARGET FIELD MATRIX Target System/ Table

270

Target Column

Data type

Source System/ Table

Source Column

Data type

Expression


Default Value if Null

Data Issues/ Quality


Walkthrough: Workshop Details

1. Sources and Targets a. Source: TRANSACTIONS b. This relational table contains sales transactions for 7 days. It exists in the SDBU schema and contains 5,475 records. For the purpose of this mapping, we will read all 7 days of data. See the figure below for the source table layout.



271

c. Target: FACT_PROMOTIONS_AGG_DAILY i. This relational is located in the EDWxx schema. After executing the mapping, it should contain 1,073 records. See the figure below for the target table layout:

Walkthrough: Mapping Details

1. In order to successfully create the mapping, you will need to know some additional details. a. The management has decided they don’t want to keep track of the Manager Discount or the Employee Discount (PROMO_ID 105 and 200) so these will need to be excluded from the load. b. The PRODUCT_KEY can be obtained from the DIM_PRODUCT table by matching on the PRODUCT_ID. c. The DATE_KEY can be obtained for the DIM_DATES table by matching the TRANSACTIONS_DATE to the DATE_VALUE. d. UNITS_SOLD is derived by summing the QUANTITY. e. REVENUE is derived by taking the QUANTITY times the SELLING_PRICE and then subtracting the DISCOUNT, HOLDBACK and REBATE.

272



i. Most of the discounts are valid but occasionally they may be higher than the acceptable value of 17.25. When this occurs, you will need to obtain an acceptable value based on the PROMO_ID. The acceptable value can be obtained from the DIM_PROMOTIONS table by matching the PROMO_ID. ii. The DISCOUNT is a percentage stored as a number. To calculate the actual discount in dollars, you will need to divide the DISCOUNT by 100 and multiply it by the SELLING_PRICE.

f.

Walkthrough: Execution Details

1.

REVENUE_PER_UNIT is derived by dividing the REVENUE by the sum of QUANTITY.

2.

COST is derived by summing the COST port from the TRANSACTIONS source.

3.

COST_PER_UNIT is derived by summing the COST and dividing it by the sum of the QUANTITY.

Save your work often.

1. Review the session properties, your information should appear similar to the figure below:



273

Walkthrough: Review Data Results

274




Lab Activity 12: Workflow Workshop Guidelines

Scenario: Mersche Motors tech lead has indicated that all staging tables need to be loaded in a single workflow. Objectives: 

Design and create a workflow to load all of the staging tables.


Subject Review

The instructions will provide enough detail for you to design and build the workflow necessary to load all staging table in a single run. It is suggested that you use the Velocity best practices that have been discussed during the course. If you are unclear on any instructions, please ask the instructor.


Lab 12 – wf_Load_All_Staging_Tables_xx

275

Velocity Deliverable: Mapping Specifications Mapping Name:

Source System(s): Target System(s): Initial Rows:

Rows/Load:

Short Description: Load Frequency: Preprocessing: Post Processing: Error Strategy: Reload Strategy: Unique Source Fields (PK): Dependant Objects

276



SOURCES Tables Table Name

System/Schema/Owner

Selection/Filter

File Location

Fixed/Delimited

Files File Name


TARGETS Tables

Schema Owner

Table Name

Update

Delete

Insert

Unique Key

Files File Name


File Location

Fixed/Delimited



277

LOOKUPS Lookup Name Table

Location

Match Condition(s) Persistent / Dynamic Filter/SQL Override

HIGH LEVEL PROCESS OVERVIEW

Source

Target

PROCESS DESCRIPTION DETAIL SOURCE TO TARGET FIELD MATRIX Target System/ Table

278

Target Column

Data type

Source System/ Table

Source Column

Data type

Expression


Default Value if Null

Data Issues/ Quality


Walkthrough: Workshop Details

1. Mapping Required a. This section contains a list of the mappings that will be used in the workflow 1. m_STG_PAYMENT_xx 2. m_STG_PRODUCT_xx 3. m_STG_DEALERSHIP _xx 4. m_STG_CUSTOMER_CONTACTS_xx 5. m_STG_TRANSACTIONS_xx 6. m_STG_EMPLOYEES_xx

Walkthrough: Workflow Details

1. This section contains the workflow processing details: b. Name the workflow wf_LOAD_ALL_STAGING_TABLES. c. If you do not have the sessions named below in your folder, your instructor will show you how to copy them from the Solutions Folder. d. No session can begin until an indicator file shows up. The indicator file will be named fileindxx.txt and will be created by you using a text editor. You will need to place this file in the directory indicated by the instructor after you start the workflow. If you are in a UNIX environment, you may need special permissions. e. In order to utilize the CPUs in a more efficient manner, you will want to run some of the sessions concurrently and some of them sequentially: i. The session containing mappings m_STG_PAYMENT_xx, m_STG_PRODUCT_xx and m_STG_DEALERSHIP _xx can be run sequentially. ii. The session containing mapping m_STG_CUSTOMER_CONTACTS_xx can be run concurrently to the sessions in the previous bullet point. f.

If any of the previous session fails, then an email should be sent to the administrator and the workflow aborted.

g. The session containing mapping m_STG_EMPLOYEES_xx needs to be run after the 4 previously mentioned sessions complete successfully. h. The session containing mapping m_STG_TRANSACTIONS_xx can be run concurrently to the m_STG_EMPLOYEES_xx session.



279

i. If either of the previous sessions fail, an email should be sent to the administrator. ii. All sessions need to truncate the target tables. iii. The management only wants the workflow to run a maximum of 50 minutes. Should the workflow take longer, an email should be sent to the administrator. Should the workflow finish in the allotted time, the timer task will need to be stopped. i.

280

There is more than one solution to the workshop. You will know that your solution has worked when all of the sessions have completed successfully.



PowerCenter 9.x Developer, Level 1 (OnDemand) Lab Guide_635845966401197748

Recommend Documents