“Its like going on a trip to the Antarctic. You could go and buy all the right clothes, but youd be miles and miles ahead if you took someone who had actually been there once,” said Chris Clegg, the former program manager for Prudential plc, the U.K. life company.

At Prudential, Mr. Clegg was in charge of the migration of two 30-year-old legacy mainframe systems to a smaller, more flexible and robust client services system. “Essentially, Prudential wanted to upgrade its old systems, which were slow, hard to maintain and expensive, and move to newer technology that provides scalability, Web browsers and report writing capability,” he said.

(When Mr. Clegg was interviewed, he was Prudentials program manager. His contract with Prudential has since ended and he now freelances for other companies.)

The main issue of data migration of legacy systems is getting the data across from the old system to the new and preserving the integrity, he said. “Thats complicated by the fact you dont know why some of the data is on the system or the rules that were used to create it,” he said, noting that the rationale for including the data may be lost over time or is in someones head who has long since left the company.

Prudential made the initial decision to complete the migration in-house, he said. “Its a typical decision within large companies. Theyve got the people, and its less expensive than bringing in outside consultants.”

Prudential went down that route for a while, and then Mr. Clegg decided it was costing too much money, taking too much time, and the software tool being used was never going to cope. “So I decided to get some professionals in,” he said, noting he used Cedar Knowledge Solutions, the predecessor of Kognitio Ltd., a consultant and system integrator based in High Wycomb, England.

The original system Prudential was working on in-house was going to take seven days, 24 hours a day, to migrate, he noted. But that wasnt deemed acceptable, he said. “We had a target to migrate over a weekenda 48-hour period, which was the maximum time the business could be without system capability. This was important so that the system would not be down for clients who phoned in.”

Mr. Clegg said he was attracted to the outside firm he chose for the job because it had experience with the data migration process and a rigorous system for determining whether everything had been migrated across properly.

Data cleanup was an integral part of the project, he says. “We had to determine which data we were going to take with us and decided not to bother to take some of the data that was 20 years old and didnt fit with new requirements.”

Because the new system had rigorous data integrity requirements about what does or doesnt get in, “you cant migrate until youve sorted out these issues,” he says. “The only alternative is to exclude the records that dont have proper data.”

As a test run, data was “dumped” onto the live system and an exception report was thrown out, he said, explaining that the report highlighted records that would fail in the migration.

“We had people, for example, who didnt have their sex listed in their file,” he said. “On the new system, thats not possible. You have to fill in the field.”

All the records that were missing data points or had anomalies were sent back to Prudentials data-cleanup group to be entered manually. This data-cleanup process was done over and over again. “So when we came to the day of actual migration, we did one last run of the scans, and in most cases, there were zero anomalies because theyd all been fixed.” Mr. Clegg said. As a result of this rigorous process, “we migrated with 100 percent success,” he said, noting that there were only four records that couldnt be migrated and were corrected later.

To complete the implementation, the new system first went live for new business only. “Then we did the migration of one of the systems, and then the migration of the second one,” Mr. Clegg said. “The old systems were then turned off one at a time, so at the end we just had the new system running with all the data on it.”

Prudential performed three dress rehearsals for each system implementation. “Each dress rehearsal was actually a go live with a back out. When the dress rehearsal was done, we could have stayed live, but we chose to back out.” This enabled Prudential to make sure everything was running well, and “it gave us a chance to check out backup procedures.”

For companies that need to migrate data, Mr. Clegg says its important to be prepared for an expensive and time-consuming exercise. “Its not something you can just take on and tuck it in to another project or do as part of your day-to-day line activities,” he said. “Its a specialist task; it needs a separate stream of people. Its not something that you give a programmer to do.”

At Advent Underwriting, a London-based Lloyds managing agency, Derek Southgate, IT development manager, oversaw the nine-month data migration project involving the merger of two underwriting systems that became necessary after a merger of two agencies that formed Advent.

Both of the underwriting systems, which had been supplied by different software houses, were aging and had limited ongoing support from the software companies, Mr. Southgate said. “We did look at converting the data from one of the systems onto the other, [but] neither of those systems seemed to be an appropriate platform to go forward on.”

These old AS/400 systems did risk recording, claim recording, reinsurance management and all the associated accounting exercises in the context of the Lloyds environment, he explained.

As part of the data migration process, Mr. Southgate and his team selected ROOM Solutions Subscribe system; ROOMs associated aggregation management product, EXACT; and ROOMs business intelligence service that provides a data warehouse. The London-based ROOM Solutions offers products and consultancy services.

“We elected to do the data extract from the old systems by first using the data warehouse as a staging exercise,” he said, explaining Advent migrated the data first into the business intelligence data warehouse and then subsequently did a more detailed conversion to the Subscribe product.

“We used the first conversion to prove we could extract the correct sets of information from the original AS/400 systems,” he said. The old systems were the result of 10 years of maintenance, patches and data fixes. Obviously, these things didnt always get done consistently, so we ended up with orphan policies, premiums, claims and outstandings,” he said. An accounting transaction should have an associated policy or claim, he explained, and all the missing links have to be resolved.

“We worked with ROOM to come up with the best set of compromises over the different sets of data deficiencies we found. We progressively worked on the quality of data until we could do a reconciliation with the basic accounting amounts.”

The next stage involved proving this data against business-oriented reports, which was the start of the user-acceptance testing phase, he said, explaining that comparisons were made between the analyses available from the old systems to the equivalent being developed with ROOM. “It became a progressive exercise in refining our understanding of the underlying data and what we were going to get out of the ROOM system.”

Mr. Southgate also said that its essential to get the business people to sign off on the new system so that the auditors, underwriters, claims and accounts people actually accept it as an adequate replacement for the old system.

Due to time constraints, Advent didnt have the luxury of a large amount of parallel running, where effectively both systems could be operated at the same time. “We got to the deadline and we did all the conversion over a long weekend, and from that time onward, underwriters were live on the new systems,” Mr. Southgate said.

“We had to go back and do a bit of data patching because we realized we hadnt got all the information out of the old systems or there were inconsistencies,” he said. “However, by that stage, we understood enough about the new systems and the old systems to be able to do that in a fairly controlled fashion.”

He said the migration of the legacy systems into the new system has helped the underwriters to become far more self-sufficient. “They now have an analysis tool so that they can go in and do the vast majority of statistical analyses themselves, which has reduced the requirement within the IT area for a lot of ad hoc reports.” IT now has to help underwriters only when theres a problem or theres a more complicated analysis required, he said.

What were the pitfalls? Mr. Southgate said Advent took too many steps in the conversion process. It would have been better if “we could have targeted the final conversion style on the first stage of the conversion rather than doing three steps. That would have meantless rewriting of some of the scripts as we could have progressively populated the conversion database,” he said.

Another problem discovered after the conversions was “we hadnt realized there were certain business analyses done using some of the data, which had to be added later,” he said. “Because the whole lot was done in such a restricted period of time, we didnt have a full set of all the business requirements defined up front.”

Mr. Southgate said it would have been useful to have “more time to find out about all the usesoften hidden in user spreadsheetsof the old systems.”

At Markel International in London, the issue of multiple legacy systems is being addressed via the use of a data warehouse, according to Tony Ledger, IT director.

Mr. Ledger said Markel is using the services of Sagent UK Ltd. to assist in a data warehouse project, which will make the data in the legacy systems available to underwriters and managers within the business units, “so they can analyze the business and enhance the quality of decision-making going forward.” Sagent UK is a data integration and data analytics company based in Reading, England.

Currently, Markel has 15 line-of-business processing systems as a result of mergers and acquisitions, making it extremely difficult to gather data to analyze the business. “Prior to the creation of the data warehouse, management reporting was a time-consuming process,” Mr. Ledger said.

Most of the systems were written in-house over many years, with some going back as far as 15 years. The systems process premium payments, claims and all of the technical accounting that goes with the underwriting, he said.

The data migration project will bring all the data together, consolidate it, put it into one “data warehouse bucket” and organize it within the data warehouse database.” The warehouse will make it easy to assess data for analysis, unlike the current legacy systems, he says.

The data warehouse migration will also ensure Markel is consistent in its use of definitions of data. “So when we say net premium, everybody understands what net premium means, and the relevant figures have been pulled through from the 15 systems into the data warehouse.” As an example, this will permit an underwriter to see net premium by broker for 2002 for business written in the United States, he explained.

Hopefully, via management information analysis, the data warehouse will assist the company to grow business profitably and weed out those parts that dont make money, he said.

Explaining the data migration process, Mr. Ledger said Markel is using Sagent, an extract, transform and load (ETL) tool, to pull out the data from the 15 core systems and put it into a data staging area–where the 15 become one.

Sagent extracts the data out of the source systems, then does some transformations to the data to ensure that when it then gets loaded into the data staging area, “were being consistent in what were loading–were using the same terminology and definitions,” he said.

From the data staging area, Sagent also is used to move the data into the data warehouse database, where its then organized in such a way that makes it accessible for analysis, he said.

The big advantage with a tool like Sagent is that it permits future changes to be made to the extraction of the data “because its extremely fast and easy to replicate,” he contends.

“Within the next 18 to 30 months, we will be looking to replace 14 of those processing systems with one and the 15th with a new system,” he explains, in a traditional system conversion exercise. “So well go from 15 to two. There obviously will be quite considerable data migration as part of that.”

A longer version of this article originally appeared in the July edition of NUs sister publication, TechDecisions.

Reproduced from National Underwriter Property & Casualty/Risk & Benefits Management Edition, July 28, 2003. Copyright 2003 by The National Underwriter Company in the serial publication. All rights reserved.Copyright in this article as an independent work may be held by the author.