PowerCenter 9.0.1 Mapping Analyst For Excel Guide (English) - Datacadamia

Table of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv Informatica Resources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv Informatica Customer Portal. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv Informatica Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv Informatica Web Site. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv Informatica How-To Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv Informatica Knowledge Base. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Informatica Multimedia Knowledge Base. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Informatica Global Customer Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Chapter 1: Understanding Mapping Analyst for Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Understanding Mapping Analyst for Excel Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Mapping Specifications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Mapping Specification Template. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Mapping Analyst for Excel Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Chapter 2: Standard Mapping Specification Template. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Standard Mapping Specification Template Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Excel Add-in. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Installing the Add-in for Excel 2003. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Installing the Add-in for Excel 2007. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Copying and Renaming the Standard Mapping Specification Template. . . . . . . . . . . . . . . . . . . . . . 5 Viewing Columns on a Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 User-defined Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Configuring User-defined Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Chapter 3: Models Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Models Worksheet Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Configuring the Models Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Creating Multiple Models Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Validating the Models Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Models Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Model/Catalog Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Package Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Schema Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Entity/Table/Record/View Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Attribute/Column/Field Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Datatype Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Table of Contents i

Chapter 4: Packages Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Packages Worksheet Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Configuring the Packages Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Validating the Packages Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Packages Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Chapter 5: Domains and Enumerations Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Domains and Enumerations Worksheets Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Configuring the Domains Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Configuring the Enumerations Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Creating Multiple Domains or Enumerations Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Validating the Domains and Enumerations Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Domains Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Enumerations Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Chapter 6: Mappings Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Mappings Worksheet Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Configuring the Mappings Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Creating Multiple Mappings Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Validating the Mappings Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Mappings Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Mapping Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Source and Target Sections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Mapping Specification Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Chapter 7: Joins, Lookups, and Filters Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Joins, Lookups, and Filters Worksheets Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Joiner Transformation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Configuring the Joins Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Joins Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Lookup Transformation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Lookup Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Configuring the Lookup Condition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Configuring the Lookup Return Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Lookups Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Filter Transformation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Filters Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Creating Multiple Joins, Filters, and Lookups Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Validating the Joins, Lookups, and Filters Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Chapter 8: Rules Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Rules Worksheet Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 ii Table of Contents

Rule Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Rule Example Using Domains and Enumerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Configuring the Rules Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Formatting the Rules Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Creating Multiple Rules Worksheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Validating the Rules Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Rules Worksheet Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Rule Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Source Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Target Section. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Chapter 9: Importing and Exporting Mapping Specifications. . . . . . . . . . . . . . . . . . . . . . 40 Importing and Exporting Mapping Specifications Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Importing Mapping Specifications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Exporting Mappings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Configuring the Level of Log Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Troubleshooting the Import and Export of Mapping Specifications. . . . . . . . . . . . . . . . . . . . . . . . . 44 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Table of Contents iii

Preface The PowerCenter Mapping Analyst for Excel Guide is written for business analysts who want to use Microsoft Excel to develop PowerCenter mappings. It is also written for PowerCenter developers who want to use the Microsoft Excel files to begin developing PowerCenter mappings. This book assumes you have a working knowledge of Microsoft Excel. Informatica Resources Informatica Customer Portal As an Informatica customer, you can access the Informatica Customer Portal site at http://mysupport.informatica.com. 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. 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 infa documentation@informatica.com. 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. Informatica Web Site You can access the Informatica corporate web site at http://www.informatica.com. The site contains information about Informatica, its background, upcoming events, and sales offices. You will also find product and partner information. The services area of the site includes important information about technical support, training and education, and implementation services. 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. iv

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 KB Feedback@informatica.com. Informatica Multimedia Knowledge Base As an Informatica customer, you can access the Informatica Multimedia Knowledge Base at http://mysupport.informatica.com. The Multimedia Knowledge Base is a collection of instructional multimedia files that help you learn about common concepts and guide you through performing specific tasks. If you have questions, comments, or ideas about the Multimedia Knowledge Base, contact the Informatica Knowledge Base team through email at KB Feedback@informatica.com. 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 Europe / Middle East / Africa Asia / Australia Toll Free 1 877 463 2435 Toll Free 00 800 4632 4357 Toll Free Australia: 1 800 151 830 Singapore: 001 800 4632 4357 Standard Rate Brazil: 55 11 3523 7761 Mexico: 52 55 1168 9763 United States: 1 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 Spain and Portugal: 34 93 480 3760 United Kingdom: 44 1628 511 445 Standard Rate India: 91 80 4112 5738 Preface v

vi

CHAPTER 1 Understanding Mapping Analyst for Excel This chapter includes the following topics: Understanding Mapping Analyst for Excel Overview, 1 Mapping Specifications, 1 Mapping Specification Template, 2 Mapping Analyst for Excel Process, 2 Understanding Mapping Analyst for Excel Overview Use Mapping Analyst for Excel to configure PowerCenter mappings in Microsoft Office Excel, and to export PowerCenter mappings to Microsoft Office Excel. The following types of users can collaborate when creating PowerCenter mappings: Business analyst. Creates a mapping specification in Microsoft Excel to define a mapping that can include sources, targets, and transformations. A business analyst is familiar with project requirements and source and target data, but is not a PowerCenter user. PowerCenter developer. Imports the mapping specification with the Repository Manager to create the corresponding PowerCenter objects. A PowerCenter developer can edit objects, implement additional functionality, and run a workflow generated from the mapping. For example, a business analyst wants to merge employee data from multiple sources into a single data warehouse. However, the business analyst is not familiar enough with the PowerCenter Client to create the required mapping. The business analyst uses the Standard mapping specification template included with the PowerCenter Client to create a mapping specification that describes the required sources, transformations, and targets. A PowerCenter developer imports the mapping specification into a PowerCenter repository and develops the mapping in the PowerCenter Client. Mapping Specifications A mapping specification is a Microsoft Excel file that includes metadata to import into the PowerCenter repository. Use a mapping specification to define source or target definitions or to define a mapping by defining the source and target definitions, and the transformations between sources and targets. 1

When you create a mapping specification, you do not have to define all possible objects. The PowerCenter Repository Service imports the object definitions that you define in the mapping specification. For example, if you do not define transformations, the PowerCenter Repository Service imports source and target definitions only. Mapping Specification Template Mapping Analyst for Excel includes the Standard mapping specification template that you can use to develop a mapping specification in Microsoft Excel. The Standard mapping specification template is a Microsoft Excel file installed with the PowerCenter Client that defines the structure for a mapping specification. The Standard mapping specification template contains the following Excel worksheets: Models. Use to configure all source and target definitions. Packages. Use to configure the hierarchy of packages in each model. Domains. Use to configure reference data within a mapping specification. Enumerations. Use to configure a list of reference values for each domain. Mappings. Use to configure a mapping name, source and target port connections, and aggregate and non- aggregate expressions. You can configure multiple mappings on one Mappings worksheet. Joins. Use to join source data from two related heterogeneous sources residing in different locations or file systems. Lookups. Use to configure a lookup to find data outside the mapping pipeline. Filters. Use to configure a filter to remove source data from the mapping pipeline. Rules. Use to configure reusable rules that you can use as expressions on the Mappings worksheet. Note: The Standard mapping specification template also includes a Relationships worksheet that PowerCenter does not use. As a result, the Relationships worksheet is not supported. The Standard mapping specification template, Standard-Blank.xlsx, is in the following directory: PowerCenterClientInstallationDir \client\bin\Sample Specifications Mapping Analyst for Excel Process To work with Mapping Analyst for Excel, use the following process: 1. A business analyst creates a mapping specification based on the Standard mapping specification template. 2. The business analyst enters the source and target metadata on the Models worksheet. 3. The business analyst uses Microsoft Excel to connect the source and target ports and develop expressions, joins, lookups, filters, a

Preface The PowerCenter Mapping Analyst for Excel Guide is written for business analysts who want to use Microsoft