Insurance Data Migrations

Jul 1, 2007

Data Migration is much more than just transporting data from one system to another. To prepare and execute a successful data migration there needs to be a clear methodology which needs to be refined for each migration.

This article is designed as a brief guide on the main processes of a typical migration.  Whilst each step is detailed in sequential order during the actual delivery of the work a more flexible and iterative approach is required.

Step One - Project Management

The business needs to define their requirements and appoint a Project Manager to manage the migration strategy. This includes advising on the migration project planning, budgets and any potential problems along the way. As well as ensuring the business is engaged through out the migration process and project deliverables are aligned to business priorities. In a migration/conversion project, the people component is more critical than the technology component and management commitment to the project is necessary for its success.

Step Two - Legacy System and Target System Software Review

An understanding of all the data sources and the systems involved and any differences/gaps in functionality between the two needs to be gained first.  This is best achieved through close liaison with the users and stakeholders of the business. The hardware of both systems must be reviewed in order to plan a strategy to extract the data and to decide on which tools and technologies are to be used for the transfer of the data.

Step Three - Data Analysis / Profiling on Legacy Databases

Perform thorough analysis reporting on the content, structure, integrity and quality of data down to field level so that the business can decide which data is going to be migrated or archived.  Data profiling and analysis tools can help you identify and understand table interdependencies, redundant data inconsistencies or discrepancies and relationships etc. which exist across data sources.

Step Four - Data Quality Rules and Cleansing

It is important that any Data cleansing / transformation rules and exceptions are defined before the migration begins. Some of these will originate from the business, with their historical knowledge of the data and implicit business rules, whilst others will become apparent through the analysis and profiling processes. The business stakeholders need to be actively involved in the management of data quality activities.  These include manual and automatic updates to the legacy data. This is an on-going activity and you must ensure that you react to any new items of data that need cleaning during the migration phases.  Data Quality perfection is impossible however you need to ensure that all critical data is up to standard for the new system to run properly.

Step Five – Data Mapping and Transformation Rules

Every Migration usually contains a large amount of Data Mapping. This is simply the assignment of one to one relationships between the two systems files - e.g. Cust.No to Customer_Number. Where direct relationships do not exist, business rules are defined to interpret the required information.

You need to ensure that all Fields and Lists that need mapping are identified. Unless a migration is done within the period of a month there is likely to be an amount of time between the initial analysis and live day, so suitable processes (i.e. monthly) must be put into place to deal with new values in the mapped fields.

Step Six – Design/Architecture and Migration Development and Cut Over Planning

In this phase the focus is on the development /coding of the modules required to migrate the data. On larger migrations it is advisable to use a staged approach. So for instance customer details would be migrated, the results of this would be made available to the business with ideally workshops being held showcasing the data in the target system. The same cycle would then be performed for policies, claims, financial transactions etc. Adopting this approach would mean that the business has more of a feel to how the migration is taking shape and any potential issues should be classified and dealt with accordingly.

Step Seven – Reconciliation Reports

Develop bespoke code to extract total counts and summations of critical data for each of the business areas from the legacy, interim and target databases. Typically reported in a spreadsheet form these figures need to be broken down by business area to independently validate the end to end migration process, mitigation of any differences and inspire business confidence in the migration. Any discrepancies must be within an agreed tolerance limit.

Step Eight – Load and User Acceptance Testing

Testing will come in a number of form and requires an iterative approach. The first will be migration load checking. This is where data in the target system is spot checked against data in the legacy system. Testers would usually follow checklists detailing the fields to be checked, the checklists would be electronic and allow the testers to enter pass or fail against each item of data. The second will be unit testing on the target system. Does the target system perform as expected with migrated data in place, as against data that has been keyed in by the user? Again test scripts will be followed at this stage with expected results being compared against actual results.  User Acceptance Testing is the final check to determine the success or otherwise of the migrated data.

Any failures identified through testing will need to be critically reviewed and the business will need to decide whether to:

1. Document it and live with it.
2. Fix in the Legacy system if the quantity is minimum.
3. Change the migration to handle it and repeat steps seven, eight and nine

Step Nine - Live Migration

It is critical to Plan ahead for the Live Migration. The schedule for the live migration run should be fully documented and agreed with the business long before the live day is due. This will allow the various areas of the business to decide how to carry out day to day duties during the time between the data being extracted from the legacy system and the data being loaded onto the target system. Remember, that if you're migrating more than half a million records the chances are you may have to bring your system down for a long weekend or longer to allow for the amount of time it will take to extract, load the data and then run the reconciliation reports to ensure that what has been migrated is correct.  To cause the least amount of disruption to the business there are hardware and software techniques available to help perform the live migration in shorter times.  There are also alternatives strategies to the big bang option which include parallel running and incremental migration. Each option has both advantages and disadvantages in terms of costs and complexities.

Step Ten - Sign Off

Provide a clear audit trail and the piece of mind that the migration is a complete success.

Documentation

This document is aimed at the business user. There is no technical detail. It will contain a section for each area of the business. It contains all details of transformations that were used, any data quality rules and any details of any records that were excluded. It also contains a description of the logic used (business rules) to get the data from the old to the new system. It should be signed off by each area of the business prior to go-live.

Data Migration Specification Document

This document is usually used as a reference guide to show the reader what has been migrated at a field by field level. It would show the source field from the legacy system against each field. Where there is no comparable source field it may just show a default value. It may also be referred to from the sign-off document.

Reconciliation Document

This document will be produced whenever a migration is run. This will contain control totals for each part of the migration. Detailing simple things like number of clients, policies and also detailing more complex things like the value of your accounts by risk on a year by year basis.

Copyright 2007 New City Software Limited. Insurance Data Migrations division. All Rights Reserved.