Navigating Non-Spatial Data Migration

Cambian Business Services of Surrey, Canada isn’t what you’d think of as a typical FME user. Not at all, in fact. They create complex integrated healthcare information systems, and there’s generally not a map in sight. It was while they were working on a large migration project, moving patient records from a variety of legacy systems to one common model, when a serendipitous lunch brought FME into the picture.

Bruce Forde, President and CEO at Cambian, was describing the challenge to Safe’s VP Development Dale Lutz. “We had to migrate patient data from 60 different clinics, and at least 10 different legacy applications. Not only does each application use a different data model, the deployment of the same model can vary from instance to instance,” Bruce tells us. “Some were running on various versions of Windows®, some were on Linux®. We needed a way to move it all, quickly, into the destination PostgreSQL database model, and then redeploy it back at the client sites”. Adding to the challenge, once a snapshot of a clinic database was taken for migration, the clinic continued to operate, adding data that would need to be dealt with post-migration.

This sounded like a familiar challenge to Dale – albeit without a spatial component. By the end of lunch, they’d decided to give it a go with FME. Safe’s Pro Services department built a proof of concept workspace to start from, and then it was over to Chun Zhang, Cambian’s Director of Technology, to take it into production. Working with CSV exports from the legacy applications, she used FME to define both schema mapping and data cleansing rules, making extensive use of string and attribute transformers to manipulate the data into the destination schema.

As Chun tells us, “FME provided a flexible way of capturing schema mapping rules, and the workspaces’ reusability saved us a great deal of time. The logging features were very helpful during development – a really useful tool for debugging. We were able to migrate a clinic’s data and get it back onsite, in the new system, in a matter of days. If we’d had to reformat and scrub the data by hand, it would have easily taken ten times the effort.”

What did we learn? Whether your data is traveling round-trip between spatial systems or making a one-way non-spatial voyage, FME can map the route!

Learn more about FME database support at safe.local/Databases.