Insurance Data Migrations

This initial assignment was to migrate the Motor Fleet line of business. The migration included Broker Details, Clients, Policies, Risk Information, Vehicles, Claims (including Payments & Recoveries) and Financial Transactions. The data came from a variety of sources, including UNIX platforms and MS Access databases. We migrated up to 20,000 Clients and Policies, 300,000 Claims and 300,000 Vehicles dating back to 1993.

Complexities of this project included, but were not limited to, that fact that the new target system was still in the design and customisation phases and its development was taking place in parallel with the data migration. Major transformation was also required in the Claims migration, the legacy system held each Claim in one large file which had many fields. The new target system was much more sophisticated, holding main claim details, claimants, representatives and reserves in separate tables. So to migrate the data we needed to create AD claimants with names advising the user that they were generated by the migration, we created TP claimants, representative records and reserve records for both AD & TP claimants.

The data cleansing stage of this migration was very complex, there was a requirement to migrate as much Policy History as possible so that going forward the users could see how each Policy had been built up on the new system. The issue was that the Policy History needed cleansing and filtering for each Policy before it was in a good enough state to migrate. So using part automated and part manual resource we undertook a cleansing exercise which ultimately left the user with a very high quality of Policy History. This as helped by our understanding of Insurance and which data would be of relevance to the business going forward.

After the initial Data Analysis of the legacy system had been done, we developed Modules which extracted the data from the legacy system, formatted and cleansed the data and imported the data into the target system. These were a mixture of 3gl programs to extract the data, SSIS routines to bulk load into SQL Server 2005, TSQL and CLR's written in C# to migrate and transform the data through the staging areas.

Once the project was underway we project managed the migration, lead the development and utilised some of the clients existing resource. During the project we held regular requirements and progress meetings with the Stakeholders which drove the project forward, and ensured the rules laid out by the business were maintained. For the Load and Acceptance Testing Stages of the migration we developed a series of Scripts & Checklists for the testers so they could perform various spot checks of the data to ensure the data appeared as expected and the business processes and interfaces worked.

At the time of writing the system and data migration is still being tested with a go-live date due to be announced soon. Throughout this project, we have maintained an excellent relationship with the client. As a result of this and the Reconciliation success rates and accuracy of the above migration we are now developing the migration strategy for the next migration which will be Commercial Lines.