PowerCenter Mapping Analyst For Excel Guide - Datacadamia

Step 2. Configure the Mapping Specification in Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . 16Create a Mapping Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Configure the Main Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Configure the Source Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Configure the Target Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18Step 3. Validate the Mapping Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19Step 4. Save the Mapping Specification to a Shared Location . . . . . . . . . . . . . . . . . . . . . . . . . 20Step 5. Import the Mapping Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Step 6. Edit the PowerCenter Mapping and Generate a Workflow . . . . . . . . . . . . . . . . . . . . . 21Edit the PowerCenter Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21Generate and Run a Workflow from the Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22Chapter 4: Configuring Multiple Mappings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23Copying and Renaming the Source-Target-Matrix Template . . . . . . . . . . . . . . . . . . . . . . . . . 23Mapping Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24Expression Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Targets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Chapter 5: Configuring Multiple Mappings - Example . . . . . . . . . . . . . . . . . . . . . . . . 27Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27Step 1. Establish the PowerCenter Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28Create the Source Flat File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28Create Target Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28Step 2. Configure the Mapping Specification in Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . 28Create a Mapping Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29Configure the Mappings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29Step 3. Save the Mapping Specification to a Shared Location . . . . . . . . . . . . . . . . . . . . . . . . . 30Step 4. Import the Mapping Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30Step 5. Edit the PowerCenter Mappings and Generate Workflows . . . . . . . . . . . . . . . . . . . . . 31Edit the PowerCenter Mappings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Generate and Run Workflows from the Mappings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32Chapter 6: Importing and Exporting Mapping Specifications . . . . . . . . . . . . . . . . . . 33Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33Importing Mapping Specifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34Exporting Mappings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Chapter 7: Customizing Mapping Specification Templates . . . . . . . . . . . . . . . . . . . . 39Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Metamap Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Rules and Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Configuring a Custom Mapping Specification Template . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41ivTable of Contents

Copying and Renaming the Metamap . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42Associating the Metamap with the Custom Template or Repeating Sheet File . . . . . . . . . . . . . 42Model, Source, Target, and Mapping Worksheet Types in the Metamap . . . . . . . . . . . . . . . . . 43Excel Table Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Required Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44User-Defined Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Modifying the Cell Location of Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46Import and Export Parameters in the Metamap . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48Import Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48Export Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49Saving the Metamap as an XML Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49Chapter 8: Customizing Mapping Specification Templates - Example . . . . . . . . . . . 51Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Step 1. Copy the Mapping Specification Template, Repeating Sheet File, and Metamap . . . . . 52Step 2. Associate the Metamap with the Repeating Sheet File . . . . . . . . . . . . . . . . . . . . . . . . 52Step 3. Edit the Mapping Specification Template and the Repeating Sheet File . . . . . . . . . . . . 53Step 4. Edit the Metamap . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53Edit the TargetClasses Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54Edit the TargetAttributes Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Edit the FeatureMaps Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56Save the Metamap as an XML Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Appendix A: Metamap Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59Overview Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59Models Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59Source and Target Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60DataPackages Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60Schemas Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60Classes Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60Attributes Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61DataTypes Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61Mapping Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62MappingDesignPackages Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62TransformationTasks Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Filters Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Lookups Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63Joins Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63FeatureMaps Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63MITI SystemTypes Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64MITI DataTypes Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64Appendix B: Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65Mapping Analyst for Excel Glossary of Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65Table of Contentsv

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67viTable of Contents

PrefaceThe PowerCenter Mapping Analyst for Excel Guide is written for business analysts who want to use MicrosoftExcel to develop PowerCenter mappings. It is also written for PowerCenter developers who want to use theMicrosoft Excel files to begin developing PowerCenter mappings. This book assumes you have a workingknowledge of Microsoft Excel.Informatica ResourcesInformatica Customer PortalAs an Informatica customer, you can access the Informatica Customer Portal site at http://my.informatica.com.The site contains product information, user group information, newsletters, access to the Informatica customersupport case management system (ATLAS), the Informatica How-To Library, the Informatica Knowledge Base,Informatica Documentation Center, and access to the Informatica user community.Informatica DocumentationThe Informatica Documentation team takes every effort to create accurate, usable documentation. If you havequestions, comments, or ideas about this documentation, contact the Informatica Documentation teamthrough email at infa documentation@informatica.com. We will use your feedback to improve ourdocumentation. 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 the Informatica Documentation Center from http://my.informatica.com.Informatica Web SiteYou can access the Informatica corporate web site at http://www.informatica.com. The site containsinformation about Informatica, its background, upcoming events, and sales offices. You will also find productand partner information. The services area of the site includes important information about technical support,training and education, and implementation services.Informatica How-To LibraryAs an Informatica customer, you can access the Informatica How-To Library at http://my.informatica.com. TheHow-To Library is a collection of resources to help you learn more about Informatica products and features. Itincludes articles and interactive demonstrations that provide solutions to common problems, compare featuresand behaviors, and guide you through performing specific real-world tasks.vii

Informatica Knowledge BaseAs an Informatica customer, you can access the Informatica Knowledge Base at http://my.informatica.com. Usethe 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.Informatica Global Customer SupportThere are many ways to access Informatica Global Customer Support. You can contact a Customer SupportCenter through telephone, email, or the WebSupport Service.Use the following email addresses to contact Informatica Global Customer Support: support@informatica.com for technical inquiries support admin@informatica.com for general customer service requestsWebSupport requires a user name and password. You can request a user name and password at http://my.informatica.com.Use the following telephone numbers to contact Informatica Global Customer Support:viiiPrefaceNorth America / South AmericaEurope / Middle East / AfricaAsia / AustraliaInformatica CorporationHeadquarters100 Cardinal WayRedwood City, California94063United StatesInformatica Software Ltd.6 Waltham ParkWaltham Road, White WalthamMaidenhead, BerkshireSL6 3TNUnited KingdomInformatica BusinessSolutions Pvt. Ltd.Diamond DistrictTower B, 3rd Floor150 Airport RoadBangalore 560 008IndiaToll Free 1 877 463 2435Toll Free00 800 4632 4357Toll FreeAustralia: 1 800 151 830Singapore: 001 800 46324357Standard RateBrazil: 55 11 3523 7761Mexico: 52 55 1168 9763United States: 1 650 385 5800Standard RateBelgium: 32 15 281 702France: 33 1 41 38 92 26Germany: 49 1805 702 702Netherlands: 31 306 022 797Spain and Portugal: 34 93 480 3760United Kingdom: 44 1628 511 445Standard RateIndia: 91 80 4112 5738

CHAPTER 1Understanding Mapping Analyst forExcelThis chapter includes the following topics: Overview, 1 Architecture, 1 Mapping Specification Templates, 3OverviewUse Mapping Analyst for Excel to configure PowerCenter mappings in Microsoft Office Excel. The followingindividuals can collaborate when creating PowerCenter mappings: Business analyst. Creates a mapping specification in Microsoft Office Excel to define a mapping that caninclude sources, targets, and transformations. A business analyst is familiar with project requirements andsource and target data, but is not a PowerCenter user. PowerCenter developer. Imports the mapping specification using the Repository Manager to create thecorresponding PowerCenter objects. The PowerCenter developer can edit the objects, implement additionalfunctionality that might be required, and run a workflow generated from the mapping. The PowerCenterdeveloper can use the Repository Manager to export any valid mapping to a mapping specification for abusiness analyst to review in Microsoft Excel.For example, a business analyst wants to merge employee data from multiple sources into a single datawarehouse but is not familiar enough with the PowerCenter Client to create the required mapping. Thebusiness analyst can use the templates included with the PowerCenter Client to create a mapping specificationthat describes the required sources, transformations, and targets. A PowerCenter developer can then import themapping specification into a PowerCenter repository, continue development of the mapping in thePowerCenter Client, and export the completed mapping for review by the business analyst.ArchitectureMapping Analyst for Excel includes mapping specifications and metamaps.Mapping SpecificationsA mapping specification is a Microsoft Excel file that includes metadata to import into the PowerCenterrepository. Use a mapping specification to define sources or targets or to define a mapping by describing theOverview1

sources and targets and the transformations between sources and targets. Mapping Analyst for Excel includestemplates for mapping specifications. Use the mapping specification templates as a starting point to developmapping specifications in Excel. You can also create a custom mapping specification template.When you create a mapping specification, you do not have to define all objects. For example, if you do notdefine transformations, the Repository Service imports only sources and targets.MetamapsA metamap is a hierarchical representation of the metadata in a mapping specification. A metamap determineshow the Repository Service reads the metadata. Each mapping specification has an associated metamap. Youmust use the same metamap for each mapping specification you create from a template.The metamap contains multiple worksheets that define import and export options and the cell location ofmetadata in a mapping specification.Mapping Analyst for Excel includes metamaps in XML and Excel formats. Use the XML version of themetamap when you import mapping specifications or export mappings. Use the Excel version of the metamapto modify metamap properties when you create a custom mapping specification template.Note: If you create a mapping specification from a template included with Mapping Analyst for Excel, you donot need to modify the metamap.Mapping Analyst for Excel ProcessThe following figure describes the Mapping Analyst for Excel process:3.1.2.MappingSpecificationPowerCenter rgets5.Transformations4.PowerCenterDesignerTo work with Mapping Analyst for Excel, use the following process:21.Create a mapping specification based on a Mapping Analyst for Excel template or on a custom mappingspecification template.2.If you create a custom mapping specification template, modify the associated metamap to read the customtemplate.3.Use the Repository Manager to import the mapping specification. The Repository Service creates thePowerCenter objects.4.Complete the PowerCenter mapping in the PowerCenter Designer.5.Use the Repository Manager to export PowerCenter metadata to a mapping specification for review by abusiness analyst.Chapter 1: Understanding Mapping Analyst for Excel

Mapping Specification TemplatesMapping Analyst for Excel includes the following mapping specification templates and metamaps:Template NameTemplate FileAssociated MetamapMapping template(with Map.xlsMappingTemplate-MetaMap.xmlMapping template(without validation)MappingTemplate Template-MetaMap.xmlSource-TargetMatrix ix-metamap.xlssource-target-matrix-metamap.xmlThe mapping specification templates and metamaps are located in the following directory: PowerCenter Client installation directory \bin\Sample SpecificationsMapping TemplateThe Mapping template contains separate worksheets for overview information, sources, targets, and functions.Use this template to create a mapping specification for a single mapping. Mapping specifications based on theMapping template can contain sources and targets and Joiner, Filter, Expression, Lookup, and Aggregatortransformations.The Mapping template includes a template with validation and a template without validation. Use the templatewith validation to validate metadata values in the mapping specification. Use the template without validation ifyou want to avoid enabling macros on your machine.Source-Target-Matrix TemplateThe Source-Target-Matrix template includes a separate worksheet for each mapping in the mappingspecification. Use this template to create multiple mappings in a single mapping specification. Mappingspecifications based on the Source-Target-Matrix template can contain sources, targets, and Expressiontransformations.The Source-Target-Matrix repeating sheet file, source-target-matrix-mirrepeatingsheet.xls, defines the commonstructure for multiple mappings defined in a mapping specification that is based on the Source-Target-Matrixtemplate. The metamap for the Source-Target-Matrix template reads from the repeating sheet file to define thecell location of metadata in the template.Mapping Specification Templates3

4Chapter 1: Understanding Mapping Analyst for Excel

CHAPTER 2Configuring One MappingThis chapter includes the following topics: Overview, 5 Copying and Renaming the Mapping Template, 6 Main Worksheet, 6 Source and Target Worksheets, 7 Functions, 9 Transformations, 9 Validating the Mapping Specification, 12OverviewA business analyst uses the Mapping template to create a mapping specification for a single mapping. TheMapping template contains separate worksheets for overview information, sources, targets, and functions.Mapping specifications based on the Mapping template can contain sources and targets and Joiner, Filter,Expression, Lookup, and Aggregator transformations. A PowerCenter developer imports the mappingspecification using the Repository Manager to create the corresponding PowerCenter objects. During import,the Repository Service names each mapping “Mapping.” The PowerCenter developer can change the mappingname in the Designer.Before you create a mapping specification based on the Mapping template, choose whether to use a templatewith or without validation. The structure of the template without validation is the same as the template withvalidation.To create a mapping specification based on the Mapping template, a business analyst completes the followingsteps:1.Copy and rename the template.2.Configure the Main worksheet.3.Configure sources and targets.4.Optionally add functions to the Functions worksheet.5.Configure transformations.6.Based on the mapping specification template type, click Validate to validate the transformations.Overview5

Copying and Renaming the Mapping TemplateTo create a mapping specification based on the Informatica Mapping template, copy and rename the template.You can find the Mapping template, MappingTemplate.xls, in the following directory: PowerCenter Client installation directory \bin\Sample SpecificationsMain WorksheetUse the Main worksheet to enter general information for the mapping, such as the name of the source or targetdatabase or file system. The Main worksheet includes the following sections: Model. Entity that contains all mapping metadata. Data Store. Contains the source and target database name and type.In general, when you configure a mapping specification, you do not need to enter all requested metadata.However, certain metadata is required to import the mapping specification into the repository.Enter the following properties in the Model section:PropertyRequired/OptionalModel NameRequiredName of the model. Mapping Analyst for Excel requires a modelnam

Mapping Analyst for Excel includes mapping specifications and metamaps. 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 sources or targets or to define a mapping by describing the