Adding a Custom Lookup Table for Migration

When performing a data migration into an ERP system using dspMigrate as the primary transformation tool, most times a lookup table will need to be configured and used. A lookup table, also called check tables or cross-reference tables, is used to convert legacy values into values acceptable by the target ERP system. A typical example of this are country names, which, if using an SAP system, must be converted into two character country codes. Since hundreds of different country names are possible, a lookup table would be used to allow the system to use the acceptable target values instead of a developer writing hundreds of rules to account for each country value conversion.

Thankfully, the dspMigrate tool not only includes lookup tables as part of its tool set, but it has also evolved into a highly-intuitive system that automatically incorporates lookup tables to a developed target if those lookup tables have been included in the system type import from the target system. Using countries in SAP as the example again, an SAP system type import will not only download the proper configuration of a country code lookup table, but also associate that table with the corresponding target field that uses it.

But what if the migration called for a customized lookup table that was not part of the core functionality of the target system? This is able to be done, though it requires more manual setup. The aforementioned country code example will be used to draw a correlation between a pre-existing lookup table and a customized one.

1. Two tables must first be created: One table, call it the target lookup table, that contains at least key, which would be the target system values intended for the target system (ex: T005 with key field of LAND1). A populated language field should also be present (ex: SPRAS from SAP). If the Console portion of a migration is configured correctly, the wave is assigned a language, using the target system's language values (for SAP, it would be 'E' for English). The system will then appropriately filter for records that utilize this particular language, which will simplify value mapping. This table must be saved in the same database specified in the migration's wave as well. This database would typically be the copy of the target system data (ex: dgSAP) that is used to import system types and the target system tables.
   
   The second table, call it the source description lookup table, will contain the target values as the key again, along with a field that would contain data matching the original source values, called a description field (ex: T005T with LANDX as the description field). Also, as with the first table, a language field is necessary to filter for the language set up in the migration's wave. This, too, is saved in the target system copy database.
   
   NOTE: A single table may be used as well, as long as it contains the target values as the key, the legacy descriptive values and the language field.
   
   
2. The custom target lookup table must next be manually added to the list of lookup tables in Design > Configuration > Lookup Table Setup. All appropriate fields must be populated. Users must select 'Configuration' for the 'type' field if they need to use the table for value mapping.
   
   
3. Since the custom lookup table is not part of the system type import, it must also be added to the lookup table field for the target field that will utilize the table in value mapping. This is done by clicking 'Fields' in the 'Targets' parent pane and selecting the target field in question.
   
   
4. If this is performed after the initial target setup, then syncing to map is necessary.
   
   
5. If the primary legacy table used in the migration contains the description field data, then the user would proceed in using the xRef mapping action and mapping with the appropriate source table and field. If the description field data is contained in another table, then a secondary source would have to be set up prior to specifying the xRef mapping action and the field's corresponding source table and field.
   
   NOTE: an xRef mapping action must be applied before value mapping can commence.
   
   
6. Value mapping would next be performed as normal, starting with the wave lookup table refresh and proceeding to mapping legacy values to target values.
   
   
7. After building or rebuilding tables and building rules, double check the target table in Transform to verify the successful population of the custom values.

BackOffice Associates offers professional, instructor-led training in an interactive hands-on style. To view the latest schedule of classes currently offered, visit http://www.boaweb.com/company/product-training/