Truths Regarding Data Conversions

Whether you are considering making a change in financial software or you are anticipating an acquisition, one of the primary considerations in a successful conversion is the mapping of loan data. Have you ever imagined the volume and complexity of the fields required to run a loan properly on a system? Behind every financial data processor, you have a large data dictionary identifying key customer and loan information. Customer data would need to include fields like names, addresses, social security numbers, important customer dates, phone numbers, email addresses, etc. With loan information, you have multiple dates, terms, monetary buckets, and tracking indicators to identify the status and condition pertaining to each account. On top of that, new regulatory requirements and collection methods require a steady increase in tracking fields. With an ever-changing environment, the only certainty is that the data dictionary will be ever-changing.

For data conversions, we have learned a few truths:

1. There are universal standard fields that are similar between two data processors
2. The similar fields may have differing field lengths
3. There will be fields that exist in one system, but not in the other
4. There will be data put into fields that may not match the format of the field
5. Understanding key fields are critical to understanding how records tie together

 This article will discuss the opportunities that come with each of the five conditions above.

Universal Standard Fields

When doing data conversions, there are standard fields used across all data processors. For example, we can usually find a first name, last name, address, Social Security Number, and phone numbers relating to a customer. On the loan side, we can usually locate the loan contract date, due date, term, payment amount, and outstanding balance. These are the core fields used in data conversions that allows for easier validation when converting from one system to another (i.e., matching the customer first name in the old and new system). This should be quickly identifiable if the data is not matching between the two conversions and should be the easiest to correct if a field is mapped wrong.

Differing Field Lengths

Just because the fields match-up between the two systems (i.e. first name) does not mean the fields are the same length. For example, the last name field in one system may be 40 characters in length, but in another only 30 characters. The field lengths would have been decided by the data processor sometime prior based on the information of the day. Another example where field lengths can vary is dates. If a date uses the format “mm/dd/yy”, there is a risk of the date field being interpreted wrong based on the century mark. For example, a birth date could be misinterpreted as 2018, instead of 1918. A better format would have been “mm/dd/ccyy” to make sure the century mark was included. This could have been merely an oversight or a space limitation within the data table. Whatever the case when creating new fields, data processors will research and try to anticipate the needs (now and future) of their clients.

Fields Not Existing

No two data systems are alike. This means the data dictionaries between the two systems will not be alike. Fields that were used to report information on one system may be calculated in another. For example, one system could have a value that tracks the amount applied towards the next payment, whereas another system could calculate the values on the fly. This concept can be difficult to grasp if you have the perception that all systems are exactly alike. To overcome the challenges where certain fields may not exist, we use a variety of user-defined fields to fill the gaps where certain data is required to be brought over during a data conversion. In addition, some tables can have user-defined IDs with custom descriptions to help identify unique conditions on the account. When the data is converted, we try and take every reasonable effort to populate the critical values for the loan to function. If the fields to convert are critical and time is available, the data dictionary can be expanded to house the new information.

Data Not Matching Field Format

We have seen where institutions have repurposed fields to fill a tracking or reporting need. As an example, if the collateral tracking fields did not have a model year but had a description field on the screen, the institution may use the description field to put in the year. Although this fills the need of tracking the value on an individual piece of collateral, there is a large risk for data inaccuracy across the institution. Here are some of the challenges:

1. The value is dependent upon users entering the correct format every time to accurately, report on the field. For example, the year could be entered as follows: 2018, 18, ’18, 2O18, and two thousand eighteen.
2. The field could have other data besides what you are repurposing the field for. In our collateral example, the description field could have a description in it instead of the model year.
3. Any data migrations from one system to another might lose some of this information due to data being in different positions or formats. If we were converting the model year from item 1 above into 4-digit year field, the only data able to be converted would be the values that were solely integers (2018,18). Anything with an alpha character (’18, 2O18, two thousand eighteen) would be rejected during the data conversion.

Ultimately, the decision will need to be made if the field is critical enough that the data needs to be consistent. If so, the recommendation would be to program a new data field and have the interface available to force the input of data correctly.

Understanding Key Fields

Key fields are the primary sequencers for records. The primary key holds a unique value that identifies the record from all other records in the database. For example, a loan account number would be a primary key field in a loan master file because the loan account number will only exist once in the file. For a collateral file, you may have two or more key fields to identify the loan and each piece of collateral within that loan. When two records are linked together, a primary key field from one of the secondary records could tie to a key or non-key field in the master record. For example, if we want to pull office information in a record keyed by office number by loan account, the primary key in the office record would tie to the office number field (non-key) in the loan master file. Knowing how the records link together for the prior data processor is critical for the success of converting the data.

Conclusion

Data conversions and loan and customer data dictionaries are complex. When going through a data conversion, a good relationship between the institution and the new data processor needs to exist. The new data processor will rely heavily on the institution as the bridge who will have access to the old data processor and the new data processor in helping validate the accuracy of the data being converted. The institution will rely on the data processor to map the prior information into the proper data fields as well as setting up the appropriate conditions on the account to report and track the loan correctly. As both parties work diligently together, there is a high success rate of getting the data converted correctly and being able to accurately service the converted loans starting day one on the new system.