Navigating the Interface Data Mapping Exercise

So you’ve made it into the design phase of your interface build. This is where the real work begins. Like Mike Rowe in an episode of “Dirty Jobs”, prepare to get dirty.

Mapping and Validation is where the details of the interface get fleshed out. The work you do during this phase pays huge dividends for the remaining phases of the project and the success of the interface long term.

The mapping and validation document is a working document as you move through this phase and a reference document for future phases and post production support. It records what was done and why.

In this post I will focus on the data mapping exercise and cover data validation and exception handling in another post.

Mapping Exercise

Start by creating a list of all of the data fields for the destination system. You can do this by looking at the screens and making notes or if the destination system has an API or open interface for the transaction, get the list of fields from there. I like to use a spreadsheet and create a tab for the source system.

Identify the following for each field

• Data type – Is the field a character string, integer, decimal, date, datetime?
• Field lengths –
  • For character strings make note of the maximum field length
  • For integers make note of largest and smallest allowed value
  • For decimals make note of the largest and smallest allowed values as well as the number of positions allowed after the decimal
  • For date and datetime fields make note of earliest and latest date values and if the field supports a time value as well as a date value
• Is the field required? – It is important to know if the field is required during the mapping step
• Is there any validation for this field value? An essential part of the mapping exercise is to understand what validation each field is going to be subjected to in order to maintain the referential integrity and data quality requirements in the destination system.

Next, create a list of all of the fields in the source system that you want to be sent to the destination system. Just like the destination system, you can look at the screens that support that transaction or if there is a standard data export or extract generated by the source system, you can use those fields.

For each field identify the data type, field length, required, and validation for the field.

Mapping

Now that you have a complete list of the source and destination fields you are ready to begin mapping. During the mapping process you are going to take a source field and match it with a destination field. Some people prefer to start with the destination field and find the best fit field from the list of source fields. Either way you want to approach it is fine, we will be going back and reviewing the list from each perspective after to mapping to find any fields without a match.

Next, you are going to evaluate the field attributes to see if the mapping is going to be straight forward or if there are additional logic that needs to be evaluated in order to complete the mapping.

Data Types

If the data types between the source and destination systems are the same that is a good start. However, if they do not match, you may need to do some conversions.

You should explicitly define the data type conversion and its possible side effects. For example, if you converting from a float to an integer you will lose the decimal precision from the flow. Make a note of the precision loss so it is a known side effect and not an issue later on.

It is also important to define what happens when the data type conversion fails. If a character string is being converted to an integer and it contain character values, how should the interface handle the failure? Some options are:

• Leave the destination field blank
• Default a value for the destination
• Raise an error and not interface the record

Field Lengths and Precision

After the data types have been considered, you must also consider the lengths and precision of the fields.

Like the data type conversions you should explicitly define the mapping to ensure you are identifying any potential data loss. For example, if the source field is 512 characters long and the destination field is only 256 characters long, how do you handle the extra characters?

Mapping, Translating, Defaulting

When considering the individual data fields coming through the interface, there are three possible ways to handle the data. The data can be mapped, translated, or defaulted.

Mapping

Data mapping is simply using the value from the source field to be the value for the destination field. The field is unchanged as it moves through the interface.

Translating

Translating data takes the value from the source field and applies some logic to it to derive the value for the destination field.

The translation can be as simple as converting the source values of “Yes” and “No” to “true” and “false”. Or they can be very complicated needing cross reference tables or advanced business logic to determine the destination value.

Defaulting

Defaulting the destination value can be used when there is no source field or when the translation logic does not arrive at a value. Instead of raising an error, a constant default value can be used instead.

Unmapped Fields

When the initial mapping is complete you may have some fields from the source system that do not have a home in the destination system and you may have some fields in the destination system that do not have a matching field in the source system. Here are some ways to address these mismatches.

Source system field without a home in the destination system

When you have a source system field that does not have a home in the destination system it is usually an information only field and does not play a key role in the integrity of the destination transactions (or it would have a home).

• Is the source field necessary for the person using the data in the destination system? There are many cases where the context of the transaction changes and a field that is important to the users of the source system is not important to the users of the destination system.
• If the field is necessary, can the source system value be placed in an extension field? Many systems offer additional generic fields to be used for just this sort of thing.
• If there is no generic place to put the source value, can the source value be placed into a notes or comments field?
  As a last resort, the orphaned source fields can be combined and placed into a notes or comments field in the destination system. This keeps the data from being lost, however it has the side effect of the field losing its identity in the destination system.

Destination system field without a matching field in the source system

If you have a field in the destination system that does not have a source you only have a few options.

• Is the destination field essential for the users of the destination system?
  If not, one option is to leave the field blank in the destination system.
• If the field is required, is there a default value that you can use to satisfy the requirement?
• Is there a way to get the information from some other source?
  Sometimes it is necessary to pull data from another system to be used to complete the data needed for a transaction.

Conclusion

Once you have completed this step, you should have a thorough understanding of the details of your interface from a data perspective. You should know what data is flowing and how it is transformed and validated as it moves through the interface. You will also have a document that describes the minutia of the interface and can be used in subsequent phases and long-term by the production support team.