Case Study

Data Migration

The Challenge

A private hospital, formerly independent but now part of the Spire Group, that provided one-off procedures (hip replacement etc.) to the NHS was changing their patient management software platform. The data in the old system was required in the new system to support the patient pathway from referral by the NHS, through consultation, treatment, and finally discharge back to the care of the NHS. Historic data was required to form a complete patient record should the patient re-visit for another procedure, or the information be required by the originating physician in the National system.

The Approach

Both the old and new systems were running in the hospital, supported by local servers and networks. All the supporting processes and connections were in development under different programme initiatives and this part of the programme was simply the transfer of data. The hospital was not processing patients, so the data was not being updated at the time and was therefore static. This was an important advantage.
The old system had a data extract facility, and the new system had a data import facility. There were large amounts of data, but the total number of records did not exceed the boundaries of Microsoft products so generally available data manipulation applications, such as Excel and Access based on personal computers, could be used without having to dedicate resource to the running of a SQL database.

An extract of data was first run, and the data opened in MS Access without manipulation. At this stage, the extract was validated by comparing records totals, and by randomly examining and comparing data in Access and the native patient display. Direct data viewing found the biggest (by extension the most complex) patient records and these were compared too in order to verify the completeness of the extract.

A copy of this complete and verified extract was then archived as the original, and a convention established to ensure retention of data before and after each manipulation stage. The IT department made available the relatively large amounts of storage this would need, however it still required careful management to ensure each stage could be stored successfully. Some compromises had to be made in numbers of copies, and a log was established of the exact manipulations carried out so that data from any stage could be replicated if necessary.

The extracted data was compared with the documentation for the new system import routine and each data field from the old system mapped to one in the new system. We then ran a test import into the new system. This produced a successful import, and a log of the fields and records not imported, with error codes. THC designed data manipulation routines to eradicate 80% of the records with errors. Every effort was made to ensure the integrity of the data – new errors could not be tolerated. THC also designed routines for testing the data in the new system to ensure it had not only been imported successfully, but that there were no problems created. We placed special emphasis on test results, making sure that if the routines could not import the data in its original form, that it was shifted to free-form fields in order to retain accuracy of content and integrity. The automated processes could not be relied upon to import the final 20% of the data without creating issues. We could see where the issues were being created and therefore in what way the data had to be manipulated. A cost-benefit analysis showed that a manual process of comparison, old -v- new records, was the best method of attack, and so we built a system that could be run by unpractised staff to highlight further problems for rectification. The Migration Manager called on THC staff to assist with grouping and fixing subsequent data imports after the data had been rectified.

The whole process was controlled by the THC Migration Manager. As the timeframe was tight, expecting to stretch over days not months, rather than establishing protocols that could be implemented by any of the people involved in the process reference at all stages would be made to the same individual. This represented a risk should that person by incapacitated, however this was felt to be generally acceptable. Our Migration Manager was at pains to ensure the process was logged with decisions, modes of operation, issues and risks so another person could step in if necessary. Progress was shared regularly with the THC Team running other processes on site.

At all times emphasis was placed on the validity of the data. There were no circumstances under which incorrect patient data could be tolerated. Although there was no regulatory authority that would be inspecting the data, it was assumed they would be interested should a data error lead to patient harm. Obviously, this was a situation that could not be allowed under any circumstances. Some establishing principles had already been made, and Clinisys had delivered WinPath Enterprise to a common platform, to which everybody had access. However, there was no overall day-to-day management dedicated to final installation of the working system.


Successful migration of all the data in the old system into the new system. There were no outstanding error conditions or data manipulation problems. All the testing of imported data had been successfully completed with no outstanding issues. The timescales had been met, and although the requirement had not been identified by the client until extremely late in the overall programme, a highly successful process had been designed and implemented in very tight timescales and to strict criteria.

The Lessons Learned

The client did not want to contribute to a lessons learned process, however THC felt it was important to carry out our own internal review.
It was decided that;
i) At the commencement of every programme/project the requirement for data migration must be discussed – sometimes the client has not identified the requirement.
ii) Greater emphasis must be placed on the requirement – it is often the case that the data migration process is underestimated.
iii) Specialist data migration must be available to THC and should be an integral part of our offering – the depth of skill is often under-regarded and overlooked.