Complex Hospital Data Migration
When moving the data means understanding the hospital.
Replacing a hospital administrative system is not a transfer of records between databases. Patient encounters, emergency visits, admissions, ambulatory activity, operations, and results carry relationships built up over years of clinical and administrative practice. In this migration those relationships had to be understood, mapped, and transformed before the data could safely become part of the new system.
Context
- Customera major hospital
- SourceAdministrative (ADT) system built in-house on a mainframe network database, decades of data
- TargetModern hospital information system
- ScopePatients, emergency visits, admissions and movements, ambulatory visits, operations, services, diagnosis codes, insurers, and payments
- RoleOfakim performed the migration end-to-end: analysis, mapping, build, delta runs, and cutover
The migration was completed end-to-end using our Migration Control Center (MCC) methodology and tools: source-data analysis, business and technical mapping, gap management, generated transformation, delta migration, and a short cutover.
The Challenge
Hospital data reflects patient journeys, not database structures.
The legacy system represented different parts of one real patient journey as separate technical records, each with its own key. The hospital decided that related emergency-department, hospitalisation, and ambulatory cases held separately in the legacy environment had to become a single case in the new system.
Analysis of the source data then showed what that decision implied. Hospitalisation cases overlapped each other. Emergency visits ended after the admission they led to. Some admissions sat entirely inside an emergency visit. Two events shared a minute where the target allowed one movement per timestamp. None of these combinations could simply be loaded; each needed an explicit business rule.
The question was not only "how do we move this field?" but "what does this information mean in the target, and what should happen when source and target describe the healthcare process differently?"
A concrete example
One Patient Journey. Several Legacy Cases. One Target Case.
A patient arrives at the emergency department, is transferred to a second emergency site, is admitted to a ward, is sent for an ambulatory test, and is discharged. In the legacy system that is four cases.
- ER case
- ER transfer
- Admission
- Ambulatory
- Discharge
In the target it is one case with a chain of movements, and getting there took rules, not field mappings.
- Legacy cases
- Analysis evidence
- Approved rules
- One target case
The approved case-unification rules defined:
- when related emergency, hospitalisation, and ambulatory cases combine into one target case, and when they stay apart
- how dates and times are interpreted when the source holds minutes or only a date and the target needs an ordered sequence of movements
- which target case type and which movements result from each combination found in the data
- how diagnoses, services, and results attached to the legacy cases follow them into the merged case
- what happens to information that exists twice after a merge and only once in the target: a second admission reason, a second discharging physician, an original discharge time
The last point is the one that is easy to miss. Merging records creates information-loss decisions. Where two source cases carried different values for a concept the target holds once, the hospital decided which value becomes the field and the other was written into a structured note on the case, visible to users and reports, with the explicit understanding that it can no longer be filtered or calculated on.
The case-unification specification was revised and re-approved as the analysis found new constellations, so the rules the hospital signed off are the rules the programs applied.
Healthcare migration requires semantic decisions, not just technical mappings.
Our Approach on This Project
Understand first. Then map, transform, and migrate. Each step below links to the same step in our migration lifecycle.
01 · Understand the source from the data
Every candidate source file was profiled: value distributions, relationships, anomalies. The overlaps between cases, the timestamps that could not be ordered, and the codes that carried meaning were found here, before any mapping session.
Analyze02 · Map business meaning with the people who hold it
Mapping sessions brought together the hospital’s administrative experts, the target vendor, and our analysts. Business mapping first, technical mapping after; every open point became an action item owned by the source side, the migration, or the target side.
Map03 · Track gaps to a decision
Scenarios the target could not hold, values it had no answer for, and source data with no destination were recorded as gaps with their evidence and closed by an explicit decision, including what would happen to data that did not migrate.
Resolve Gaps04 · Generate the migration from the mapping
The migration programs were generated automatically from the approved mappings and rules, so the code is that mapping and nothing added by hand. A change in a rule meant a change in the mapping and a regeneration, never a hand edit.
Implement05 · Test the difficult cases on purpose
Test samples were built from the analysis to contain every distinct value and every combination the rules had to handle, so the rare case-merge constellations were tested deliberately rather than hoped for in a random sample.
Test, Rehearse & MigrateMigration Without Freezing the Hospital
Delta migration was part of the design, not a late addition.
The hospital kept operating throughout: new patients arrived, encounters changed, cases closed and reopened. The legacy system kept no change log, so deltas were computed by comparing complete extracts, with delta logic defined for every topic, case unification and patient data included, so that a merge decided in one cycle was still correct when one of its cases changed in the next.
Migration was therefore a controlled sequence of cycles rather than one snapshot. Rehearsals ran on complete, current data. At cutover, the bulk of the history was already loaded; the cutover window carried the last delta and the open cases, patients still in the emergency department and in the wards, extracted last and loaded so that the new system could continue them. The cutover window was under eight hours, on a weekend night. That was the downtime. Care did not stop for the migration: no patient was released because of it, no treatment was postponed or delayed, and the new system was fully operational when the window closed.
Cutover while the hospital keeps runningMore Than Data Quality
Migration analysis exposed gaps in the target solution itself.
Comparing the existing business reality systematically with the target design revealed more than dirty or missing source data. It surfaced:
- source data anomalies
- missing or ambiguous business rules
- differences in representation between source and target
- target requirements the legacy data could not satisfy
- legacy information for which the target had no equivalent
- functional decisions that had to be taken before the migration could proceed
Examples from this project:
- a status field whose actual values included several the target had no answer for, found by reading the distinct values rather than the specification
- a field displayed on a target screen that the load interface did not accept, found when the mapping reached it
- patient type held at a different level in the legacy system than in the target, requiring a target decision before the field could be mapped
- emergency visits during an admission, which the target could only represent as a transfer to a special department and back
Finding these questions during analysis, while the target configuration could still change, is fundamentally different from discovering them in user acceptance testing or during cutover.
Principle: expose gaps earlyWhat This Case Demonstrates
Complex healthcare migration is a business transformation problem expressed through data.
Business meaning comes before transformation
Hospital data cannot be migrated by matching table and field names. Case unification alone required explicit rules about relationships between kinds of patient activity.
PrincipleReal data must challenge the design
The overlaps, the unordered timestamps, and the unsupported values were in the data, not in any document. Profiling the full extract is what surfaced them.
PrincipleGaps become managed decisions
Each gap was recorded with its evidence and followed to a decision with an owner, rather than disappearing into a spreadsheet, into code, or into someone’s memory.
PrincipleCutover starts long before go-live
Delta processing, rehearsals on complete data, and the handling of open cases were designed from the start, which is why the final cutover window could be short.
PrincipleThe Outcome
From legacy complexity to a controlled migration.
The hospital’s administrative migration was completed end-to-end. The migrated scope covered patients, hospitalisation movements, ambulatory movements, operations, services, diagnosis codes, insurers, and payments.
- Patients
- Hospitalisation movements
- Ambulatory movements
- Operations
- Services
- Diagnosis codes
- Insurers
- Payments
Cutover window. Under eight hours, on a weekend night. The last delta and the open cases moved in that window.
What remains is as important as what moved: the source-analysis results, the business and technical mappings, the gap and decision log, and the generated migration programs. Anyone who asks why a value is where it is can be answered from that record.
The migration turned complex legacy healthcare information into explicit, governed, testable decisions about how that information exists in the new system. That is the capability we bring to the next one.
Why This Matters to Your Hospital
Your systems will be different. The migration problem will be familiar.
Every hospital has its own history: different applications, interfaces, local codes, workflow conventions, undocumented rules, and years of accumulated exceptions. We do not assume that the mappings from one hospital can be reused at another. What is reused is the approach: analyze the actual data, understand its business meaning, find the source/target gaps, make the decisions explicit, generate and execute controlled transformations, migrate deltas, and keep the evidence.
- Analyze
- Understand
- Gaps
- Decide
- Generate
- Deltas
- Evidence
Planning a Hospital-System Replacement?
Let's examine the source environment, target objectives, and migration challenge.