Don’t Let Data Migration Become Your ERP Roadblock

Orange road sign that says "Data Migration Ahead"
Data Migration can terrify even the most seasoned implementation consultants. Examining some of most notorious ERP failures of the last decade reveals data migration as a common culprit — Vodafone, PG&E, and Target’s expansion into Canada are notable examples.

Data Migration can terrify even the most seasoned implementation consultants. Examining some of most notorious ERP failures of the last decade reveals data migration as a common culprit — Vodafone, PG&E, and Target’s expansion into Canada are notable examples.

If some of the biggest companies in the world can fall victim to data migration disasters, it is important to identify the pitfalls and potholes that lie in road to a successful ERP implementation. Here are some data migration tips that can help guide you.

You Have to Have a Plan

Once you have made your ERP selection, you should already be planning the actions involved in migrating your data to your new ERP system. When data migration is properly confronted, planned for, and executed, it spells the difference between a successful and a failed ERP implementation.

The two most important questions to answer are:

  1. Where is the data?
  2. How do you get it into the new system?

These questions often go unanswered and overlooked simply because they are so simple. But without a plan that answers them in very specific detail, you’re better off going off-roading without maps or GPS.

Look at all of the data that needs to be migrated and determine the following:

  • Locate all of the data sources
  • Identify what is moving where
  • Who is responsible for the migration?
  • Decide how to transfer the data
  • When should the migration occur?
  • How will it be formatted?

Keep it high-level at this stage of the planning — while we aren’t worried about anything as granular as mapping fields at this point, that will come next.

Look at the Quality of the Data

The next data migration tip: data quality is the crux of project success. The old cliché “garbage in equals garbage out” applies here: in moving to a new system, there is no benefit in migrating faulty data.

Make sure to identify:

  • Missing data/missing fields
  • Inconsistent mapping
  • Separate overlapping systems with duplicate or conflicting data
  • Generalized, badly defined values
  • Confusing naming schema
  • Out of date, invalid, inaccurate data
  • Sloppy formatting/invalid syntax that could break the new system

There are ERP tools for data cleansing that should be considered as part of the overall data migration plan, and it helps to have some familiarity with automated data migration solutions.

Here at ERP Advisors Group, we specialize in providing expert data migration guidance. Often we find that the configuration of the application has been set, but no one has thought about the actual data and getting it cleaned. We help our clients assess the quality of the data, whether it is good or bad, and how much effort is required to clean it.

Is the Answer to Data Quality to Handle It Manually?

No — don’t ever attempt a fully manual transform. There are a lot of good software solutions for data migration, and we can help you find the right ones for your needs.

At the same time, software won’t do all the work, so make sure to maintain a balance of automation and human oversight. There is a lot of work that can go into removing duplicates, for example: SQL routines can take care of the vast majority of the work, but it still requires some manual guidance to ensure excellent data quality.

What is the Best Approach to Loading the Data?

We have found that problems arise whenever someone attempts to load the data all at once. We recommend working by stages, such as making sure a first small test load works before attempting a larger load.

It is also best to conduct live tests with real data wherever possible, because this can help reveal the types of problems that should be addressed before the go-live, such as mapping errors or bad syntax. Testing real data provides a range of variables that can be difficult to replicate with “Jane Doe” or “1234 Pleasant Lane.”

Allocate Enough Resources

It may come as a surprise to organizations going through a data migration for the first time that the implementation partner is not responsible for cleaning the data. This is important to understand: the client must provide the data in usable form, or they are setting themselves up for yet another ERP failure.

Data migration often requires more work than anyone could imagine, and it’s best to err on the side of allocating extra time and personnel for the data migration.

Get somebody who has done some systems implementation to help guide the data migration team, be available for the project, and carve out some time. This could be an hour a day to get the plan together, to help select the ERP tools to use for the data migration, or to make an accurate assessment of what is the quality of the data.

Be willing to adjust resources and personnel on the fly to meet actual needs, because it will cost far less than dealing with the disaster of a failed migration.


Gartner Group forecasts that there will be approximately $500 billion spent on buying enterprise software in 2020, an increase of over 10% from the previous year. Despite this investment in ERP selection, your software vendors and implementation partners aren’t responsible for your data.

At the point where the software vendor or implementation partner says, “Okay, give me your data,” be prepared:

  • Make sure you have a data migration plan in place that covers all the bases
  • Don’t start the data migration with bad data
  • Find the right ERP tools to help automate the process
  • Don’t try to do the migration all at once
  • Correctly estimate the required resources —it’s going to take a dedicated team to get the job done

As a final word for people thinking about embarking on a data migration: confront what you must do about your data immediately after the ERP selection. You may find that you need help — and we can guide you in the right direction.

Download Our Free Data Migration Guide

Juliette Welch: Thank you for joining us for today's call: Don't let data migration become your ERP roadblock.

Shawn Windle is our speaker for today. Shawn is the Founder and Managing Principal of ERP Advisors Group based in Denver, Colorado. On today's call, Shawn will discuss the five data migration “gotchas” and their handlings and how you might just tame the data migration beast yourself.

So, Shawn, what is the first “gotcha” we should watch out for?

Shawn Windle: I think the biggest one is you have to have a plan. So, if you think about data migration there's a lot of steps that are involved — a lot of detailed things, there's some high-level things that you have to take a look at, like for instance, even where is the data and then how do you get it to the new system? So, without a plan that really lays that out in a lot of detail, you're kind of flying blind.

Like for instance, we have a client right now that — we're working with the technical team and the technical team so that we know we've got this system, we've got to get the data out of this — we call it the source system — and then we have to put it into the target system and we said that's exactly right. That's great, but we also have these other six systems that we have to pull data out of and put it into the target system. And so they were kind of like wait what? There's other systems, and of course they knew that, but we were able to come in and say, let's look at all of the data that you're looking to bring over and figure out what the data is, who's going to move it, how they're going to pull it out, when it needs to be pulled out, and in what format does it need to go?

So definitely the first “gotcha” is you've got to have a really solid plan at a high-level that covers some of those key points and then goes into some of the details.

Juliette: It seems like the quality of the data plays a role in this. Can you tell us more about that?

Shawn: Oh my gosh, I'm sure that the people on the call and that'll be watching this video — garbage in equals garbage out.

It's such a cliche, but it's so true, so if you have a source system of your customers, — you're talking about like your customers, the people that you're doing business for — and you have a customer that's maybe in there multiple times, because our dear salespeople, sometimes they'll just do what they need to do to get an order, thankfully. We're all about that.

But at the same time, if we may have the same customer in five or six times where it says ABC Co, ABC Company, ABC Company Inc. —so, we have three different records here that are all of the same exact customer and now we're going to take that data out of our old system — which we didn't like in the first place, that's why we're moving to a new system, why we're migrating to a new system — we'll take the old data and the quality of it and move it over, and it's not very good. What's the benefit of the new system?

So, data cleansing, data hygiene — we're going to talk more about that topic more next month about what data hygiene means. It sounds like something you do at the dentist. It's the dentist for data. That's maybe what we should be marketing ourselves as — we're the dentist for data — because it's kind of funny. Because as part of our role with our clients, we're like look, we're going to do anything it takes to make you happy with your software. That's the valuable final product that we want. And a lot of times the configuration of the application is set, but then the actual data hygiene — again, getting the data clean — nobody thinks about it.

So, we kind of help clients to figure out like, okay, what is the quality of the data? Is it good or bad? And then how much efforts required to clean it? And there's all kinds of great tools in the marketplace that can help with that, a lot of manual stuff that you have to do sometimes, too, but it's super important.

Juliette: So is the answer to data quality to handle it manually then?

Shawn: Oh, that's a great question. Very good question. If you absolutely, positively have to. Like I said, there are some tools in the marketplace that can help with really getting data — you almost have to bring it up. You have to present the data, be able to see the data, and then decide what to do in terms of cleaning it.

And then once you identify you have three or four or five customers that are the same, how do we — it's called dedupe it — so how do we deduplicate those records? So we go from four, five, six, ten, to one, and then put that old record in the new record format.

Automation is absolutely the way to go where you can. We work with a lot of clients, a lot of small, medium sized businesses. And sometimes they don't have the tools to really look at how do we do automation? But even Excel can help with the function VLOOKUP — people that are listening to this call probably just winced when I said VLOOKUP. But it is a really strong tool in Excel that helps you to have data over here and data over there and you want to look up the data that's over here over there.

So, there's some tools even in Excel you can use to do that. And then even doing SQL databases — we do a lot for clients where we'll pull in their existing data and run it through routines in SQL that will do deduplication, that'll look for fields that aren't right given the type of field that it is, maybe we have a numeric field in an alphanumeric field or whatever.

A lot of that can be automated through SQL and then like I said, there's actually some data migration tools in the market — there's some for NetSuite and some other apps that are worth taking a look at that can definitely help, but you really need a specialist to guide you through that process to just make sure that it gets done right.

Juliette: With that said, what would you say is the best approach to actually loading the data?

Shawn: So, if you think about a new system — let's equate it to like a car. So, we got a car and it's got the doors. Well, the car itself, while there's a lot of value for some people — my father-in-law is like this, he has gorgeous cars, and you look at these cars that he's had restored and they're beautiful. I mean almost like tears come to your eyes when you see these cars. And you get the aesthetics of it and the working through it is amazing to just get the car to that state and then he gets to drive them, which is the really fun part.

So now let's take that analogy over to enterprise software. So, we spend a lot of money to buy the software. Clients spend a lot of money to configure it and get it all set up, and they're like wow, look at that beautiful application. Not really, they don't really think that about software. But, the benefit of the software is the data. It's getting the data — your business data — into the application, and that's where it really takes off.

So, to get the data into the product — into the software application — using import tools, almost every application that's worth its value has some kind of either an integration tool as part of its platform — or like even for QuickBooks we're just working with a client that uses a tool that's built specifically to take in different data feeds and then push it into the application, so you don't really have to do things where you're pulling data, getting a file, going into PL SQL, or some of the SQL languages and dropping it directly into the database — those days are gone. There's usually tools that sit on the app to import it and use it — use them for sure because it makes life a lot easier.

Juliette: Are there any other “gotchas” that could come as a surprise?

Shawn: The migration, like the whole thing. I mean, I did the research on this, that there's over $400 billion a year spent on enterprise software around the world — per Gartner Group — $400 billion. And they're buying software. And you get these salespeople — bless their souls — saying oh, this is going to work, it's going to help you, and it's going to make things better and it will. It definitely will. And the customer, their eyes twinkly, say yes, this is great, it's going to make everything wonderful. Buy the software — I’ll buy the software! And the implementation partners come in and they help to get this set up and then the implementation partner says okay, give me your data. And the client says, well aren't you going to get it for me because I'm paying you to do this implementation? And the client is sort of wrong. Because if they look at the contract — and I hope everybody gets this, I really do, because it's not like the implementation partners or the software vendors are trying to be deceptive. They just say look, I can't be responsible for your data, I just can't do it.

It's sort of like a schoolteacher saying I'm going to be responsible for your child. Of course, they're responsible for the safety and for the education of the child, but if something else is going on with the kid, they're like, hey, this isn't my kid. So, the data is kind of your kid and a lot of people do not understand that when they get into these ERP implementations, even customer relationship management, human capital management, all of it where the vendor is going to say, “give me your data.” So, I think that's a big surprise and it's not like it can't be managed, that's the other thing. There are solutions in the market and you just have to go through it.

It's sort of like you go and buy a big new house and you're going to get your stuff over there — everybody knows you have to get a moving company and pack up your old house and get the mover over and your wife ends up yelling at you or your husband ends up dropping stuff — whatever happens on your moves that’s what usually happens on mine.

Juliette: Hopefully not.

Shawn: It's hard, and it's stressful. And that is a big part of these enterprise software implementations. It's very stressful because of the data, and our clients just don't realize that often. So, definitely putting it out there for everybody. Make sure you spend some time on cleaning the data. Make sure you understand you're going to be responsible for it.

Juliette: Alright, that sounds great. What can you say about allocating enough resources?

Shawn: I'll keep using the house analogy.

For me — and I'm sure there's other people on the on the call that aren't like this — but for me, I don't remember where I picked this up from, but it's actually true, I take a project like I got to do a little project at the house this weekend. It should take 2 hours. Okay, now I'm going to multiply that times two and I'm going to take it to the next increment. So, it should take me two hours — it's going to take me four days. Like it just does. I don't do a lot of house projects, I wish I did more. I love working on the house, and the yard, and everything. But because I don't do it often, I don't have the right tools necessarily.

Juliette: It’s the unexpected.

Shawn: Exactly. It's the unexpected.

So, it's like, okay, I have to mount something into the wall. Well, I don't have a stud finder — see I'm glad we're talking about this, I have to write this down and get the stud finder — but it's kind of the same way with data that because clients don't know what — they don't do data migrations and cleanups and everything for a living, so they don't know what they're going to get into.

So, it's sort of like it can get done — I don't want to scare anybody away from doing enterprise software implementations, that's not what I'm saying. You have to do them sometimes, and even if you don't have to do them but there's a ton of value and you know it, you do have to do it.

But make sure you've got somebody from your technical team, somebody who's a little more technical — that might even be if you're doing a CRM implementation maybe it's a business analyst or it's somebody who's not just a salesperson who's never done anything with implementing systems. Get somebody who's done a little bit of systems implementation to help guide and be there, be available for the project and maybe carve out some time. Maybe it's five hours a week, maybe it's an hour a day to start with to get the plan together, to think about all this data, and really put their attention on what is the quality of the data.

Okay, we're going to have to clean it and kind of work it. That's where the problem comes in, is where clients don't confront this up front and they wait and the vendor is coding and building this beautiful app and they're doing their testing like great, where's my data? Oh yeah, you know, we're waiting for you to get us that file. Okay, well we're halfway through the project. Like, okay, I'm going to call Jane and she's going to go ahead and pull that data and give it to it to you. We call Jane and she's like, well, that data is filthy, it's terrible. Wait a minute it's dirty? Okay, well clean it. Yeah okay, that'll take me three weeks. Well, why? Because you have a day job and there's other stuff. And then she does that. And then she says, okay, I don't know how to extract it because I'm a administrator, I don't know how to extract data out of Salesforce. Probably an administrator would but fine. Then she's got to check her records and talk to a couple people and figure that out. Two weeks later, boom, we've got a file five weeks total later.

And by that time, the rest of the project has moved forward. We're closer to our go-live and it starts to become this data issue is just a major issue. like we have a client right now — it's a big business — and they have lots of different divisions and locations throughout the world, and I'll tell you that the data — we're making the right decisions though about how to get the data right in the new system and a big reason why is because they have a person that's allocated just to help us to go talk to the business users to figure out what they want.

And believe me, it's not like writing a burger where you're like, I want my burger this way. It's sort of like ordering a burger and then saying, well, I actually don't want it with pickles. Okay, good, we'll do it without pickles. Well, you know, now that I see it and I taste it, I don't really want mustard. But we just did this three times for you. I know, but now that I can see it, I don't want the mustard or taste it. So that happens, you have to plan on iterations with end users, too.

Juliette: With practical use.

Shawn: That's right, with practical use the customer that you're building this for can give you better insights. You got it. So, make sure that you allocate resources for it. And I would say at least somebody in a day a week, or even an hour a day, to start just to get the plan together and they should be able to get that plan together pretty quickly, so that then you can look at it and say, oh my gosh, we have all these systems or we have just a couple systems.

And then you're going to have to figure out where to get some resources. I mean, we help a ton with our clients. You can get a contractor or whatever, but hopefully you can use internal people to get it done, too.

Juliette: That's a lot of great information, Shawn, thank you. Is there anything more you'd like to share with us about data migration?

Shawn: Yeah, the concept of data hygiene — again, we're going to talk more about that on our next call, so, I'll save some things for that — but when we look at data, there's three “gotchas” in enterprise software.

One of them is data — absolutely, positively for sure, just generally. And then there's “gotcha, gotcha, gotchas.” Lots of “gotcha, gotcha, gotchas” under data as a topic. And so, it really is something that I hope, and this is the whole reason why we do these calls and these webcasts and everything else, is that please. For our customers it's like listen to us, for people are thinking about data like listen to what I'm saying and confront what you have to do for data early on.

If you need help, call. We'll do a free call, a free console just to get you going. But look at it upfront. Don't ignore it because it will end up killing your implementation. And you can blame the implementation partner or blame the software or whatever, but don't blame yourself because you didn't handle the data early on. At least that's something that you know about now and can handle. That's probably the key thing, I'd say.

Juliette: Thank you so much, Shawn, and thank you everyone for joining us for today's call. If you have any questions please let us know. We're happy to help in any way we can.

Our next call is February 12th: The Science of Proper Data Hygiene. In the next edition of The ERP Advisor, we will discuss the best way to approach a data migration project so you can ensure proper data hygiene to keep your data from getting infected. Please go to our website for more details and to register.

I'm your host, Juliette Welch. Our Audio Engineer is Shaun Orthmann.

ERP Advisors Group is one of the country's top independent enterprise software advisory firms. ERP Advisors Group advises mid to large sized businesses on selecting and implementing business applications from enterprise resource planning, customer relationship management, human capital management, business intelligence, and other enterprise applications which equate to millions of dollars in software deals each year across many industries.

This has been The ERP Advisor.