Documenting the data transformations to show how the legacy data goes into the new target system is a necessary activity for all data conversion projects. Figuring out the correct rules for getting the legacy data into the target data format is usually where the team spends a significant amount of time. The process is iterative throughout the entire implementation, and on complex projects, the transformation rules will continue to change all the way through go-live.
Since the data mapping process is iterative, it is important to make the first version of the mapping specifications as accurate as possible. Having a quality initial version of the mapping specifications will result in a less painful implementation and allow the team to focus on many of the other tasks involved in the implementation. To help make the initial version of the mapping specification as accurate as possible, it is important that appropriate groups are involved in the creation of the specifications. Often the same people are part of several of the groups and/or a "group" consists of one person.
The first group is the legacy system business experts. This group will know the current business processes. They will know what works and what doesn't, and give some additional insights into the current pain points of running the business today. The second group is the target system business experts; they know or are in charge of figuring out how the business processes should work tomorrow. Next is the legacy system experts, who will know the legacy systems on a more technical level and can assist in identifying fields and tables that are currently used to drive the current business processes. Next up are the target system experts; they will understand how the target system needs to be populated for the business process to run in the target system. The last group would be the data migration team. This last group should be present to help facilitate the data mapping discussions, make sure that the rules discussed make sense, and make sure the information gathered during the initial data assessment is being utilized. All of these groups do not need to be and should not be in all discussions about the mapping specifications, as all pieces and decisions are not relevant to everyone involved. However, all of these groups are important to the process.
Once the appropriate people are in a room to go through the data mapping rules in detail, the team will need to document the rules they discover during the process. The best way to document the specifications is by using some variation of a data mapping document. There are several such variations, but they all have similar characteristics. The PDF below shows what a basic data mapping document should look like.
This is just an example, and it may be appropriate to have additional columns for different projects, but usually having fewer columns allows for more flexibility and easier readability. Often there are multiple legacy systems that will be the source for any particular data set. For these situations, sometimes it makes sense to just add more columns to the right with the rules for the second, third, fourth, etc., source systems. Other times, it makes sense to just keep the one set and include the necessary details in the transformation rules section. The format should be whatever is going to be easiest for the team to maintain and understand without getting too technical.
There are also going to be fields that are currently used in the legacy system but will not be needed anymore in the target system. These fields should be documented, reviewed, and approved to make sure that they are truly not needed in the target system.