One of the key requisites for data migration is setting up a plan for translating data from the old system to the new system or warehouse. The requirements include creating a number of lookup lists both for key entities e.g. Customers, Accounts, Addresses and also reference data e.g. old system may have yes / no new system may have 0 / 1 for the equivant data. In order to accurately translate the requirements from the old system to the new system it is vital to manage these lookups correctly.

It is preferable to create these lookup lists as tables rather than coding them in the transformations since this increases the flexibility.

Lookup lists may be done as seperate tables or in one large table, my personal preference is for all straight translation tables to have a single table with a key attribute indicating the source data from the system.

For entity translations, the design of the structure will be influenced by whether there is an element of de-duplication of the data or whether it’s a straight through translation from one entity to it’s equivalent in the new system

We will be creating a number of articles discussing structures for translation tables which we hope you will find interesting.