Lea Remigio

One of my favorite parts of a new system implementation is data migration. No lie. Field mapping – the process of equating a field in your old system to a field or fields in your new system – is like a big puzzle.

I love playing detective and putting all of the pieces together. Sometimes you have to saw off some puzzle nubs to make the pieces fit. The trick is finding a way that will minimize the translation pain as you adopt the new system.

Piece 1

Recognize and accept that data migrations can be painful, time consuming, and will require a huge amount of detailed and documented work. Be wary of anyone who tells you otherwise.

Devote a dedicated person or resource from your staff to this task from beginning to end. The person you choose should work well with the rest of your staff; they will be in the position of assisting the technical lead with understanding the true meaning of certain pieces of your old data system and working with your staff to gain that understanding and prioritize the work. It is possible for me, as the technical lead, to play this role, but a deeper understanding of your organization history and culture is always beneficial.

Piece 2

Assist your team and co-workers with the first piece. Talk to them about it. Send emails communicating the timeline and explain their necessary level of involvement and responsibility. Carve out time for them to meet those responsibilities. Send them to mental health counseling or a day at the spa. Give them brownies. Bring in a therapy dog.

that’s Xander the blind therapy dog

Piece 3

Talk to your new software vendor. They owe you a crystal clear work plan and tasks list. You owe them dedication and consistent involvement. You’re both on the same team, so work together to come up with reasonable milestones and responsibilities and do everything in your power to meet your deadlines. Expect the same from your vendor.

You’ll need a sample migration done prior to the final migration, preferably one that goes to a testing site or sandbox and which includes a complete data set. This is standard operating procedure for the software vendors I have worked with.

Piece 4

Cozy up to your data set. Get to know it. Ask it questions about itself. Buy it a cappuccino and spend quality time with it. You will need database access for this (not just end-user views via the user interface) to look at the raw data in the tables, preferably in a way that allows you to create simple queries to look for problem areas.  Microsoft Excel is wonderful, but it may not be the right tool for the job due to the limitations on the number of records (rows) and the ease with which you can accidentally jumble rows or columns. You will likely need to view and work with your old database tables in a platform such as My SQL, MS SQL, Postgres, or MS Access.

Here are some of the questions I regularly ask for any data migration project:

* What is the file size of the database being migrated and how many core records does it contain? (Client records, Grant records, Cases, Households, Services, Assessments, etc.)

* How long has the system been in production? This question may help you understand how ingrained it is within the organization and guide your expectations for change management tasks.

* Has anyone been tracking data outside of the system? (Previously unshared Excel spreadsheets tend to crawl out of the woodwork during system implementations…many times months after go-live.) What critical information may need to be manually entered into the new system that can’t be migrated?

* Was any of the data in the system originally migrated from a different/older information system? This could affect mapping in certain situations, especially if a previous migration was botched.

* Are you migrating data to your new system from multiple data sources? If so, is there overlap? For example, was the same case record entered into both older systems? De-duplicating records is time consuming and error-prone. Consider your options and talk them through with the new software vendor.

* Roughly how many reference data tables are in the old system (lookup/picklist tables)? When mapping fields that use picklist values, you’ll need to document which tables contain the picklist options. In many databases, the reference data tables are named differently than the actual data being migrated. For example, the Gender field in a Client data table may point at a “subGender” table that shows the picklist descriptions for Female, Male, Group, Unknown, etc. When mapping the Gender field, you’ll want to note that the descriptions can be found in the subGender table to ensure the picklist options are populated via the migration.

* Does the system you are retiring depend on picklist fields heavily or do users enter a significant amount of data in free text fields? This is important to have a handle on because when you map a free-text field from one system to a picklist-style field in another, you could end up with thousands of picklist values that must be cleaned up in the new system after final migration.

* Does the old system track users or staff people? If so, how different are the user profiles from the new system and are there user clean-up tasks that should be scheduled for post-migration?

* Will the old information system be destroyed or taken offline? If you have the option to make it available for reference purposes, does this affect what pieces of information you have to migrate to the new system?

Piece 5

When mapping the fields, I take the time to construct a series of import verification reports in the new test site. For example, if I map 25 fields from a client table for migration, I add each of those fields to a column in a report in the new system so that I can easily look for data gaps after the sample migration is run. The nice thing about these reports is that I can use them again after final migration to the production site. It does stretch the mapping timeline out to work this way, but it saves me time in the end because attempting to build verification reports weeks or even months later forces my brain to remember stuff (and that makes me irritable).

Piece 6

Validation – validation – test – test – test!

[or, envision your own cheerleader image]

Hopefully you will have a test migration for validation and exploration of your mapping decisions. Take time to look at every field you mapped. This is where those reports I built make me smile. Expect some mistakes – yours, as the data mapper and probably some from the developers who wrote the big complicated migration scripts. Test the sample migration to ferret out the issues so that the developers can revise the scripts before running the final migration.