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

Full text

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

(2) 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.

(3) 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.

(4) 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.

(5) 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.

(6) 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.

(7) 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.

(8) viii.

(9) Table of Contents Lab Activity 2.1: Create STG_Dealership Mapping ....................................................................................... 1 Lab Activity 2.2: Create STG Payment Mapping ......................................................................................... 13 Lab Activity 2.3: Create STG_Dealership Workflow .................................................................................... 29 Lab Activity 2.4: Create STG Payment Workflow ........................................................................................ 37 Lab Activity 2.5: Log Events Review ............................................................................................................ 45 Lab Activity 3: Troubleshooting .................................................................................................................. 55 Lab Activity 4.1: Load STG Customer Target ............................................................................................... 71 Lab Activity 4.2: Load the STG Employees Target ....................................................................................... 91 Lab Activity 5: Features and Techniques .................................................................................................. 109 Lab Activity 6.1: Using Homogeneous Joins to load the STG Product Target ........................................... 115 Lab Activity 6.2: Troubleshooting Homogeneous Joins ............................................................................ 123 Lab Activity 6.3: Using Heterogeneous Joins and Link Conditions ............................................................ 135 Lab Activity 7: Using the Debug Wizard .................................................................................................... 147 Lab Activity 8.1: Using a Lookup to Load the ODS Employee Target ........................................................ 157 Lab Activity 8.2: Troubleshooting $Source Connection Variables ............................................................ 177 Lab Activity 8.3: Using a Sequence Generator to load ODS Dates Target ................................................ 193 Lab Activity 8.4: Creating a Lookup Cache and Loading the ODS Promotions Target .............................. 201 Lab Activity 8.5: Active Lookups ............................................................................................................... 211 Lab Activity 9: Updating Targets Using PowerCenter ............................................................................... 221 Lab Activity 10.1: Using PowerCenter to Load the Fact Sales Target ....................................................... 247 Lab Activity 10.2: Create a Mapplet .......................................................................................................... 263 Lab Activity 11: Mapping Workshop ......................................................................................................... 267 Lab Activity 12: Workflow Workshop ....................................................................................................... 275 .

(10) .

(11) 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.

(12) 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.

(13) • • • •. •. 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.

(14) 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.

(15) 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. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(16) 3. Right-click the folder labeled Devxx and select Open.. Note: In future instructions this may be referred to as “your folder.”. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 3.

(17) 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. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(18) 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.. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 5.

(19) 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. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(20) 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.. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 7.

(21) 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. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(22) 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.. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 9.

(23) 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. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(24) 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:. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 11.

(25) 12. Lab 2.1- m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(26) 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.. Duration: 30 minutes. 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. PowerCenter 9x Level I Developer. 13.

(27) 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. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(28) 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.. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 15.

(29) 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. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(30) 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:. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 17.

(31) 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. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(32) 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. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 19.

(33) Observe the Target folder of your repository folder in the Navigator window and note that the STG_PAYMENT target has been added.. 20. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(34) 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.. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 21.

(35) 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. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(36) 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.. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 23.

(37) 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. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(38) 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.. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 25.

(39) 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. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(40) 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:. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 27.

(41) 28. Lab 2.2 – m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(42) 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.. Duration: 15 minutes. 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. PowerCenter 9x Level I Developer. 29.

(43) 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. Lab 2.3 - wf_m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(44) 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:. Lab 2.3 - wf_m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 31.

(45) 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. Lab 2.3 - wf_m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(46) 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:. Lab 2.3 - wf_m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 33.

(47) 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. Lab 2.3 - wf_m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(48) 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:. . Lab 2.3 - wf_m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer. 35.

(49) 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 2.3 - wf_m_STG_DEALERSHIP_xx. PowerCenter 9x Level I Developer.

(50) 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.. Duration: 15 minutes. Subject Review. The student will create the workflow for STG_Payments.. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 37.

(51) 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. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(52) 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.. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 39.

(53) 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. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(54) 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.. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 41.

(55) Walkthrough: Start the Workflow. 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. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(56) 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:. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer. 43.

(57) 44. Lab 2.4 – wf_m_STG_PAYMENT_xx. PowerCenter 9x Level I Developer.

(58) 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.. Duration: 15 minutes. 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.. PowerCenter 9x Level I Developer. 45.

(59) 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. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer.

(60) 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.. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer. 47.

(61) 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. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer.

(62) Walkthrough: Access the Workflow Logs. Lab 2.5 – AnalyzeLogs. 1. Right-click the s_m_STG_PAYMENT_xx session a. Select the menu option Get Session Log.. PowerCenter 9x Level I Developer. 49.

(63) 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. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer.

(64) 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. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer. 51.

(65) 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. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer.

(66) 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.. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer. 53.

(67) 54. Lab 2.5 – AnalyzeLogs. PowerCenter 9x Level I Developer.

(68) 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.. Duration: 25 minutes. Subject Review. Lab 3 Troubleshooting. The student will copy a workflow from the DEV_SHARED folder and correct the errors associated with it.. PowerCenter 9x Level I Developer. 55.

(69) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(70) 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. PowerCenter 9x Level I Developer. 57.

(71) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(72) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer. 59.

(73) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(74) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer. 61.

(75) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(76) 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.. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer. 63.

(77) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(78) 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. Lab 3 Troubleshooting. 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.. PowerCenter 9x Level I Developer. 65.

(79) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(80) 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.. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer. 67.

(81) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(82) 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. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer. 69.

(83) 70. Walkthrough:. 1. Note that the session has succeeded!. Success!!. . Congratulations!. Lab 3 Troubleshooting. PowerCenter 9x Level I Developer.

(84) 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. Duration: 60 minutes. 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. PowerCenter 9x Level I Developer. 71.

(85) 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.. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(86) 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:. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. 73.

(87) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(88) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. 75.

(89) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(90) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. 77.

(91) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(92) 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. PowerCenter 9x Level I Developer. 79.

(93) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(94) 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:. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. 81.

(95) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(96) . 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.. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. 83.

(97) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(98) 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).. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. 85.

(99) 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. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(100) Walkthrough: Start the Workflow. 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.. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. 87.

(101) 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: . Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(102) Walkthrough:. 1. Note that the session has succeeded!. Success!!. . Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer. Congratulations!. 89.

(103) 90. Lab 4.1 – m_STG_Customer_Contacts_xx. PowerCenter 9x Level I Developer.

(104) 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.. Duration: 30 minutes. 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. PowerCenter 9x Level I Developer. 91.

(105) 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. Lab 4.2 – m_STG_Employees_xx. PowerCenter 9x Level I Developer.

(106) b. Confirm that your source definition appears as displayed in the following figure:. 3. Click Ctrl-S to save your work to the repository.. Lab 4.2 – m_STG_Employees_xx. PowerCenter 9x Level I Developer. 93.

(107) Walkthrough: Create a Relational Target Definition. 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:. Lab 4.2 – m_STG_Employees_xx. PowerCenter 9x Level I Developer.

(108) 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:. Lab 4.2 – m_STG_Employees_xx. PowerCenter 9x Level I Developer. 95.

(109) 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. Lab 4.2 – m_STG_Employees_xx. PowerCenter 9x Level I Developer.

(110) 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.. Lab 4.2 – m_STG_Employees_xx. PowerCenter 9x Level I Developer. 97.

(111) 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_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. Lab 4.2 – m_STG_Employees_xx. PowerCenter 9x Level I Developer.

No results found