How to prepare data for an ERP migration

Plenty of ERP projects that run late or over budget don’t come unstuck on the software. They come unstuck on the data. The new system is configured, tested and ready, and then the business tries to load fifteen years of customers, suppliers, products and open transactions into it. That’s when the problems start.

Preparing data well is mostly about starting earlier than feels necessary, and writing down decisions people would prefer to leave until later.

1. Profile before you plan

Before anyone estimates the effort, look at what you actually have. Profiling means counting and checking: how many customer records there are, how many are active, how many are duplicates, how many have missing fields, how many product codes haven’t moved in five years.

This step changes estimates more than any other. It often turns up more records than anyone expected, many of them inactive or duplicated. Better to learn that in month one than in month nine.

It’s rarely a small problem. When the Australian National Audit Office examined Defence’s replacement security vetting system, it reported that 456 of the 796 columns (57%) in the database being replaced contained data quality issues.

2. Decide what moves

Not everything should migrate. Inactive customers, obsolete products and transactions closed years ago add effort and risk for very little benefit. Agree the rules early, and in writing:

• Which customers and suppliers count as active, and over what period
• Which products move, and what happens to obsolete stock still on hand
• How much transaction history is needed, and where older history will live
• What happens to open orders, open invoices and balances at cutover

3. Map old to new, and get it signed off

Every field in the old system needs a home in the new one, or a decision that it isn’t needed. Codes need mapping too: old cost centres to new ones, old product categories to the new structure, old payment terms to the new list.

The mapping should be a document the business owns and signs. When a number looks wrong after go-live, this is the document that tells you whether the data was moved incorrectly or moved exactly as agreed.

4. Clean at the source, or on the way through

Some problems are best fixed in the old system before extraction, like merging duplicate customers. Others are best fixed during transformation, like reformatting addresses to suit the new system. Decide which is which, and avoid fixing anything by hand that will need fixing again for the next test load.

5. Make the process repeatable

You will load the data more than once. Usually three or four times before go-live, and then again for real at cutover. If each load involves someone fixing spreadsheets by hand, each load takes as long as the first and brings its own new mistakes.

Build the extraction, cleansing, mapping and transformation as a process that gives the same result every time it runs. The first run takes real effort. Every run after that should take hours.

The third test load tells you whether you’re ready. By then the process should run without anyone touching it.

6. Reconcile every load

After each test load, check the new system against the old one. Record counts, balances, open order values, stock quantities. Every difference needs an explanation. A small unexplained gap in testing becomes a large one in production.

Reconcile at the level of detail that matters. Before the Queensland Health payroll system went live in 2010, the project compared fortnightly average gross totals rather than individual pays. The Queensland Audit Office later found that temporary employees whose end dates hadn’t been updated in the data conversion went unpaid.

7. Give the business people time

Data decisions need people who know the customers, products and finances, and those people are busy running the business. Plan their time into the project from the start. A migration that relies on the financial controller “when they get a chance” will be waiting a while.

The Queensland Audit Office’s lessons from the payroll project put it plainly: “Adequate time should be allowed for data preparation and data migration.”

The mistakes we see most

• Leaving data until after the system is configured
• Treating migration as an IT task when most of the decisions belong to the business
• Cleaning by hand for every test load
• Moving everything because deciding what to leave behind feels hard
• Skipping reconciliation because the load looked fine