During the course of an implementation 100’s of different fields can use 100s of these types of cross references for combinations of several hundred thousand values in yielding several hundred thousand values out. Many of these translations will undergo many changes made by different people throughout the duration of the project. If the cross references are not properly managed, there could be serious version control issues that are difficult to notice until it’s too late and the consequences can have major implications. To prevent this, there needs to be some sort of version control. Otherwise, the data migration team will be dealing with 100s of separate lists maintained in different formats, stored in different places and it will quickly turn into a mess.
There are several ways to manage all of the cross references needed for a data migration project effectively and others that are not so effective. The least effective way that I’ve seen on a project is for the data migration team to keep track of a list of people that are maintaining all of the cross references and follow up with to get the most up to date version of the cross references right before running a conversion cycle. This method is painful for everybody as it usually results in many emails, trying to track down the cross reference for someone who’s on vacation, and undoubtedly the format will change on at least one that will through the migration program off. This method might seem to offer the most flexibility and it one way it does, but it’s not worth the headache.
A slightly better solution would be to have a cross reference directory\folder, where it’s understood that the most recent cross reference will reside at all times. This method will at least eliminate some of the email chain that will need to go out. However, as changes occur during the course of the project, each of the 100s of cross reference will start to get different file names, formats will change, new tabs will be added without communication, etc. These format\file name changes make it harder to manage the large number of cross references, will increase the communication chain back and forth, and still lead to the possibility that the wrong set of values were used during conversion.
The easiest to maintain solutions will control of these cross references within a single file or table, using a singular format. This singular place could be an Excel workbook, a google spreadsheet, or a table. It doesn’t really matter the exact format. The important part is that it’s all together and easily accessible by the project team. By having a singular place where all of the cross references are maintained and stored, the entire process team can have visibility to them, the values that are used, and the data migration team can have a simple process to incorporate them into the data migration programs. Once the team understands that this singular place, is the place, the process becomes incredibly simple to manage. On extremely large global roll-outs, there might need to be several of these "documents" as there might just be so many people and so many values involved that it might make sense to chunk them up be area or site. The important part is to make them a centralized as possible to make it easy to maintain, track, and manage.
Once the team decides the place of where and how the cross references are going to be maintained, the team will next have to determine on the format of the cross references. Most likely there will be many cases where it’s just a simple one to one translation where there is one value in and one value out. There will also be many cases where there are multiple values in and multiple values out. If maintaining the values in an Excel workbook, the team should be able to just adjust the input and out columns however they would need. If maintaining the translations in a database table, there will either need to be a form that allows the records to be easily maintained and viewed or the team decide on a maximum number of inputs and outputs and a translation needs more than that, the values will just need to be concatenated together in the translation table. It is also possible to maintain multiple input values in a single excel cell and multiple out values in a single excel cell by just using a delimiter, like a pipe “|” in them (commas are bad delimiters and try to avoid them). This technique can also help ease the maintenance. It does take a little training to get the rest of the project team to be comfortable with using a delimiter, but people adjust quickly.
If you have any comments or questions about cross references, or data migration projects in general, please email me at email@example.com or call my phone at 773.549.6945. I’ll be more than happy to help you in any way that I can.