WIXLIB

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 athttp://my.informatica.com.Use the following telephone numbers to contact Informatica Global Customer Support:viPrefaceNorth 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 1Reference Table ManagerOverviewThis chapter includes the following topics: Overview, 1 Using Reference Table Manager, 3 Understanding Reference Data, 4 Logging in to Reference Table Manager, 7 Reference Table Manager Interface, 7 Managing Connections, 8 Creating Reference Tables, 10OverviewReference Table Manager is a PowerCenter web application that you use to manage reference data such as valid,default, and cross-reference values. Use Reference Table Manager to create, edit, import, and export referencedata. Create reference tables to establish relationships between values in the source and target systems duringdata migration.For example, you are migrating from a legacy system to an upgraded ERP system. Before you load the data, youmay need to modify some values from the source to a value that is acceptable in the target system. Createreference tables to define and manage these values. Create Lookup transformations in PowerCenter mappingsto look up data in the reference tables.The Reference Table Manager Service in the PowerCenter domain runs the Reference Table Managerapplication. You can create and configure the Reference Table Manager Service in the PowerCenterAdministration Console.1

The following figure shows Reference Table Manager components:External Reference FilesReference Table ManagerServiceReference Table ManagerRepositoryReference TableManager ApplicationReference Table StagingArea 1Reference Table StagingArea 2External Reference FilesMicrosoft Excel or flat files that contain reference data. Use Reference Table Manager to import data from theseexternal reference files into reference tables.You can also export reference tables to external reference files.Reference Table Manager ApplicationA web application used to manage reference data that is stored in reference tables. Use Reference Table Managerto create, edit, import, and export reference data. You can also manage user connections, view user information,and view audit trail log events.Reference Table Manager ServiceAn application service that runs the Reference Table Manager application in the PowerCenter domain. You cancreate and configure the Reference Table Manager Service in the PowerCenter Administration Console.Reference Table Manager RepositoryA relational database that stores reference table metadata, user information, user connections, and the audit trailtable. Reference table metadata includes the following information: Connection name Table name Display name Description Comments Date on which the table was last modified User who last accessed the reference tableReference Table Staging AreaA relational database that stores the reference tables. All reference tables that you create or import usingReference Table Manager are stored within the staging area. You can create and manage multiple staging areas torestrict access to the reference tables.The relational database for each reference table staging area must meet the following minimum systemrequirements: 2The database must be IBM DB2, Microsoft SQL Server, or Oracle.Chapter 1: Reference Table Manager Overview

The database user account must have the following permissions on the reference tables in the database:Database PermissionDescriptionSelectRequired to view the reference table in Reference TableManager.InsertRequired to create new rows in the reference table.UpdateRequired to update rows in the reference table.Alter TableRequired to rename columns, add columns, or delete columnsin the reference table.The Reference Table Manager Service uses JDBC drivers to connect to the reference tables in the database. TheJDBC drivers are installed with Reference Table Manager.The reference table staging area stores the following information: Reference tables Default value summary tableReference TablesReference tables that you manage in Reference Table Manager. All reference tables that you create manually,externally, or through import are stored in the reference table staging area.Default Value Summary TableA system table that the Reference Table Manager Service creates. The Reference Table Manager Service savesvalues from the default row of each reference table to the RTM REFERENCE TABLE DEFAULTS table. Youcan specify the default row in each reference table you manage in Reference Table Manager. Use the default rowto specify default values for each column in the reference table.The RTM REFERENCE TABLE DEFAULTS table is updated every time you add or edit a default row in areference table. It stores information in the following columns: System name Table name Column name Column value Modified on Modified byThe Reference Table Manager Service creates and maintains a default value summary table in each referencetable staging area that you create. You cannot view or edit the RTM REFERENCE TABLE DEFAULTS tablein Reference Table Manager. Use this table within PowerCenter mappings to look up default values.Using Reference Table ManagerUse Reference Table Manager to create and manage reference tables. To create a reference table in ReferenceTable Manager, complete the following steps:1.Set up a user account. Contact the database administrator to set up a user account and assign privileges inthe PowerCenter Administration Console.2.Create a connection to the reference table staging area. Contact the database administrator to set up adatabase for the reference table staging area and create connections.Using Reference Table Manager3

3.4.Create reference tables. Use the following methods to create reference tables in Reference Table Manager: Create reference tables manually. Create reference tables when you import data from external reference files. Create reference tables when you register relational tables that have been moved to the reference tablestaging area.Manage reference tables. Add, edit, or delete the rows and columns to manage reference tables. You canalso export data in the reference tables to Microsoft Excel or flat files.Understanding Reference DataReference data represents data that can be used to evaluate the accuracy, usability, and readiness of source datafor data migration or other projects. This section provides an overview of the following types of reference data: Cross-reference values Valid values Default valuesCross-Reference ValuesCross-reference values represent a set of data used to establish relationships between data in the source systemand the target system. Create reference tables for cross-reference values when you need to associate valuesbetween the source and target systems. For example, the source tables use the value ‘California’ to represent thestate code. However, the target tables use the value ‘CA.’ You can create a reference table to associate ‘California’in the source system with the ‘CA’ in the target system.ExampleYou want to migrate from a PeopleSoft source system to an SAP target system. The customer table in thePeopleSoft system has the following values for the STATE INFO column:Source TableSource ColumnSource ValuePS CUSTOMERSTATE INFOArizonaPS CUSTOMERSTATE INFOCaliforniaPS CUSTOMERSTATE INFOTexasPS CUSTOMERSTATE INFOUtahThe target system stores customer information in a different format. The target table SAP CUSTOMER storesstate information in the DOMAIN column:Target TableTarget ColumnTarget ValueSAP CUSTOMERDOMAINAZSAP CUSTOMERDOMAINCASAP CUSTOMERDOMAINTXSAP CUSTOMERDOMAINUTYou need to load customer data from the source system to the target. To avoid errors during migration, associateeach value in the source table with the corresponding value in the target table.4Chapter 1: Reference Table Manager Overview

Use Reference Table Manager to create a reference table that associates the values between the source and targettables. You can create the reference table manually or by importing external data. You can also create tableswithin the database and register them with Reference Table Manager.The following table displays the reference table for cross-reference values:Source TableSourceColumnSourceValueTarget TableTargetColumnTargetValuePS CUSTOMERSTATE INFOArizonaSAP CUSTOMERDOMAINAZPS CUSTOMERSTATE INFOCaliforniaSAP CUSTOMERDOMAINCAPS CUSTOMERSTATE INFOTexasSAP CUSTOMERDOMAINTXPS CUSTOMERSTATE INFOUtahSAP CUSTOMERDOMAINUTNext, you can create a PowerCenter mapping to look up data in the reference table before transforming the datain source tables and loading into the target tables.Valid ValuesValid values represent a set of data used to qualify data in the source system before migrating to a target system.Create reference tables for valid values when you need to define the valid values for the source system that areacceptable to the target system. You can use the reference table for valid values to filter or correct invalid recordsin the source system before loading to a target system.ExampleThe ORDERS table in the source system stores order information. Orders are grouped based on the locationfrom which they were placed. The source system has the field REGION to specify the location:SourceSource TableSource ColumnSource GIONFLSAPORDERSREGIONIDSAPORDERSREGIONMNHowever the target system is restricted to the following zones:TargetTarget TableTarget ColumnTarget NENorthPSFTORD14ZONESouthYou can create the following reference table to define valid values:SourceSource TableSource ColumnValid anding Reference Data5

SourceSource TableSource ColumnValid e PowerCenter mappings to filter records in the source table before loading to a target table. You can usea Router transformation to filter and modify the records before loading them into targets.Default ValuesDefault values represent a set of data used to assign default values to columns in the source system beforemigrating to target system. Include default values in a reference table when you need to assign default values tosource records before loading to targets. You may need to assign default values to records with undefined or nullvalues in the source system if the target system does not accept null or undefined values.ExampleYou are migrating from a legacy source system to an SAP target system. The ORDER and ITEM tables in thesource system have records with undefined values.The ORDER table has records with the following values:ORDER IDVOLUMECOUNTRYORD FRThe ITEM table contains the following values:ITEM IDITEM NAMEITEM 001PaperITEM 002ITEM 003DESCRIPTIONVOLUME1200InkjetTonerLaserYou need to load the data from the source tables to target tables in the SAP system. However, the target systemdoes not allow null or undefined values. To avoid errors during migration, assign default values to each of therecords with undefined values in the source before loading the data to a target.You create a reference table of valid values for each source table. In each reference table, you also create onerecord that contains the default values for all fields in the corresponding source table.You create the following row of default values in the reference table for the ORDER source table:VOLUMECOUNTRYORDER COMPLETED0USNOYou create the following row of default values in the reference table for the ITEM source table:6ITEM NAMEDESCRIPTIONVOLUMEN/AN/A500Chapter 1: Reference Table Manager Overview

You create PowerCenter mappings to look up and assign default values to fields in source rows with null orundefined values before loading to the target.Logging in to Reference Table ManagerYou can log in to the Reference Table Manager application from the PowerCenter Administration Console orby accessing the Reference Table Manager URL from a browser.If you access Reference Table Manager from the PowerCenter Administration Console, you do not need toenter the login information again to access the Reference Table Manager application.To log in to Reference Table Manager with the Reference Table Manager URL, you must have a user name andpassword.To log in to Reference Table Manager:1.Open Microsoft Internet Explorer or Mozilla Firefox.2.Enter the following URL in the address field for the Reference Table Manager login page:http(s):// host : port /rtmThe host and port represent the host name and port number of the node that runs the Reference TableManager application.If the node is configured for HTTPS with a keystore that uses a self-signed certificate, a warning messageappears. To enter the site, accept the certificate.The PowerCenter Reference Table Manager login page appears.3.Enter the user name and password.4.Click Login.Reference Table Manager InterfaceThe Reference Table Manager interface has the following tabs: Connections. Create, edit, and delete user connections to the reference table staging area. For moreinformation, see “Managing Connections” on page 8. Reference Tables. Create, edit, and export reference tables. You can also import external reference files. Formore information about creating reference tables, see “Creating Reference Tables” on page 10. For moreinformation about managing reference tables, see “Managing Reference Tables” on page 13. Audit Trail. View audit trail log events. For more information, see “Viewing Audit Trail Log Events” onpage 18. Users. View information about users and privileges. For more information, see “Managing Users” onpage 17.Logging in to Reference Table Manager7

Managing ConnectionsBefore you create or import reference data, use Reference Table Manager to configure the database connectionto access the reference table staging area. The reference table staging area stores reference tables that you managewith Reference Table Manager. You can create and manage multiple staging areas to restrict access to thereference tables.Before you create a connection, set up a database for the reference table staging area. Use appropriate databaseclient tools to set up the database. Verify that the database meets the minimum system requirements. For moreinformation about the minimum system requirements, see “Reference Table Staging Area” on page 2. For moreinformation about setting up a database, see the PowerCenter Configuration Guide.The following table describes the connection properties that you configure:PropertyDescriptionConnection NameName of the connection. The connection name can be between 1 and 100characters long. It is not case sensitive and cannot include the following specialcharacters:\ / * :? " DescriptionDescription of the connection. The description can be between 1 and 255characters long.TypeDatabase type for the reference table staging area: IBM DB2, Microsoft SQLServer, or Oracle.Host NameHost name for the reference table staging area.PortPort number for the reference table staging area.SID / Service NameSID or Service name for Oracle database. You can enter a value for SID orservice name in the Database Name field. Enabled if you select the Oracledatabase.Database NameThe service name for IBM DB2, the SID or service name for Oracle, or thedatabase name for Microsoft SQL Server database.User NameDatabase user name to log in to the reference table staging area. The useraccount must have the appropriate permissions to read and write to the tables inthe database. If the user account does not have appropriate permissions, theaction menus in the Reference Table Manager can be disabled or can generateerrors when you manage the columns and rows in the reference tables.PasswordPassword for user account to log in to the reference table staging area. Must bein 7-bit ASCII. The password is encrypted when it is saved to the ReferenceTable Manager repository.Additional JDBCParametersAdditional JDBC options. Enter the options in the following format:optionname1 value; optionname2 valueFor example,AlternateServers ( alternateHost : port );LoadBalancing trueTest TableName of the test table used to verify the connection to the database. Use thisproperty to test the connection with a table in the database.By default, a connection is enabled when you create it. You can disable a connection to perform maintenancetasks or to migrate data from test to production.RELATED TOPICS: “Registering Reference Tables” on page 168Chapter 1: Reference Table Manager Overview

Creating a ConnectionTo create a connection:1.In Reference Table Manager, click the Connections tab.2.Click New.The Create Connection dialog box appears.3.Enter values for the connection properties.4.Click Test Connection to test the connection.A message indicating success or failure of the connection appears.5.Click OK.If the con

Informatica, PowerCenter, PowerCenterRT, PowerCenter Connect, Powe rCenter Data Analyzer, PowerExchange, PowerMart, Metadata Manager, Informatica Data Quality, Informatica Data Explorer, Informatica B2B Data Exchange, Informatica On Demand, Informatica Treasury Services Solutions Accelerator, Informatica XE and Informatica Reference Table .