ERP Data Migration Best Practices

“…ERP data quality was a contributing factor to the eventual bankruptcy of Target Canada.” [1]

Data migration can be one of the biggest challenges when it comes to keeping your ERP implementation on time and on budget. It can be difficult to predict ahead of time how long it will take and what really needs to be retained from your existing system. It often doesn’t get prioritized compared to other activities, and it’s only until right before it’s time to cut over to the new environment that we discover how challenging ERP Data Migration can get.

• Time to read: 15 min
• Drink Pairing: Venti Rose Latte
• You are: NetSuite Administrator, Development Lead, CTO. 

Need immediate help? Reach out to us via the form below, via the chat box at the bottom right or via our Contact Us Page.

Time and time again evidence has shown that inattention to ERP data migration can be disastrous for a project. For example, as part of Target’s rollout to Canada [1], their ERP software implementation process was incorrectly managed. It involved taking data from the proprietary system they used in the U.S. and manually entering large amounts of data into their new ERP software. This resulted in significant supply chain issues that partly stemmed from inaccurate product details such as pricing, physical dimensions, numerous other fields and even the test data that was still in the old system got moved to the new Enterprise Resource Planning. Their ERP software data quality was a contributing factor to the eventual bankruptcy of Target Canada.

Our team at Trajectory, Inc. clocks in countless hours every year on ERP data migration, with a variety of Enterprise Resource Planning implementations from Private Equity deals, biotechnology firms, marketing shops, pharmaceutical companies and manufacturers.

Here’s a write-up on the common elements that you absolutely must focus on when it comes to ERP Data Migration, especially when working with NetSuite ERP software.

1. Don’t Assume You Know Your Data

When you start down the path of migrating to a new Enterprise Resource Planning platform, you need to ask what your data really looks like first. It’s a common mistake to assume that there aren’t going to be challenging with the data quality because there are no day-to-day problems with inaccurate or missing records. Take time to analyze completeness, consistency, accuracy, and look for duplication. Most companies have data issues that they aren’t aware of, and if these issues aren’t identified before creating a project timeline then they can significantly extend the timeline of an ERP data migration project. Here are some basic steps for inspecting your data:

1. Establish a view of your data – Some platforms might allow you to view your data in a list view. Find some way to get a bird’ eye view of your data.
2. Check completeness – See how often certain fields are populated. It’s possible to have important fields that are rarely used but more often than not, a field that isn’t used isn’t needed.
3. Do spot checks on format – Especially unvalidated text fields that are supposed to contain a specific set of values or need to be in a specific format. For example, do your email fields sometimes have notes written in them? Do your address have inconsistent state formats (NY, N.Y., New York, New York State, NYS, etc.)?
4. Identify duplicates – When it comes to duplicates you can choose to resolve the problem before migrating or you can bring them with you. Importing high-quality data is always the best option when possible. Planning to de-duplicate records at a later date might seem appealing, but be aware that it can take more time to resolve it in the long run.
5. Determine the format required by the new system – Does it validate the format of the email address that your old system didn’t? Are there new mandatory fields that weren’t in your old system? You might not be able to answer all these questions ahead of time but you will need to at some point. This the primary reason behind the above checks.

2. Be Discerning With What You Bring With You

Don’t pay to move things that you don’t use anymore. Identify irrelevant and obsolete data. Only migrate attributes that are still in use. It’s common to receive blanket requests to indiscriminately “everything”. A good practice is to assume you don’t need to migrate records unless you can first come up with a good reason to. Ensure that you’re not re-creating custom solutions for something that exists out-of-the-box in the platform you’re migrating to. Here are some example questions to ask yourself when qualifying what should be kept:

1. Is this information already stored somewhere that makes more sense?
2. How often do you record this information? E.g. Half the time or 1 in 1000 times?
3. How often do employees go back and reference this information after it’s been populated?
4. How much value does it add to having it available on a regular basis?
5. If the data is historical and you kept it as an archive (in an excel file export for example) would there be much of a difference?

3. Don’t Delay

Procrastination and under prioritizing ERP data migration are 2 of the biggest mistakes you make when it comes to finishing your project on time. Simply stated: start early. Close to the beginning of your ERP project. Data migration is very time-consuming and can extend the deadline of your implementation if it isn’t started at the beginning of the project. Many tasks are not dependent on each other and can be done in parallel with the basic Enterprise Resource Planning setup. Start building the ETL process and cleansing data as soon as possible.

4. Get Everyone Involved

The technical processes behind moving data from one system to another will take a substantial amount of time. However, being an expert when it comes to extracting and transforming data doesn’t make you a master at understanding what you’re manipulating. Having one person responsible for all aspects of ERP data migration is a recipe for disaster. When it comes to testing in NetSuite, our recommendation is to have the person who does the invoicing, validate open invoices whereas an accountant inspects fixed asset data. The person who actually touches the records on a daily basis needs to be the one to give the green light that everything is working. These specialists are also the ones who can help prevent migrating things you don’t need in the new system and can help prevent re-creating processes that aren’t currently working. Here are some must-have roles to get ERP data migration done right:

1. Decision Maker – This can be the lead for the ERP project implementation or anyone else who has availability and the authority to make decisions that might not always be popular when it comes to what gets migrated.
2. Technical Expert(s) – One or more employees who can do the leg work when it comes to actually create the ETL process and executing it.
3. Subject Matter Experts – The employees who understand the data that is is being migrated. They need to be the ones to validate and analyze that things are working on the new system.

5. Plan Resourcing for Stakeholders Ahead of Time

The idea of getting people engaged in ERP data migration is one thing but making it happen is a whole other thing! It takes a tremendous amount of effort and planning to make ERP Data Migration successful. Many of the experts, who are crucial for a successful migration, are likely to be mostly preoccupied. Without planning they might not be available to review the newly imported data. When push comes to shove, tight deadlines often result in not being able to properly confirm that everything is working. Try to secure some of their time well in advance and be aware of other projects that they are committed to.

6. The ETL Process Must Be Flexible and Repeatable

Extract, transform load (ETL). That’s all there is to it, right? No matter how much planning you put into it, the ERP data migration might not work on the first attempt. Trajectory’s recommendation is to perform test migrations (also known as mock migrations) before the full go-live ERP data migration. Perform a test using a small set of records as early as possible, even if the data cleanup isn’t 100% complete or if a couple of fields are still being decided on. Don’t wait for the last minute to realize that something isn’t working as expected, or to find out that you need technical expertise that you don’t have in the house.

For better understanding, let’s breakdown the ETL process for an ERP data migration project (keep in mind the ETL process is also pretty universal):

6.1 Extracting Data From Your Source System

You might realize that extracting things from your legacy system is trickier than expected. With most modern ERP platforms there is some way to get your data out of the system but with older or proprietary systems it might be a different story. Find out ahead of time if you have an easy way to export data. When creating an ETL process, remember that you should be able to accommodate small changes to the data structure and to the output format. Adding new fields to an ETL process will still require some effort but there is a balance between having a robust process and one that’s inflexible.

6.2 Transform Your Data Format

The format of data that your existing platform outputs might be quite different than what is required by your new ERP software. If you’re lucky, you’ll be able to have your source system output the data in the format you need. If not, you’ll need to find an alternative way to do this. Either way, transforming and shaping the data can take trial and error. You might find that you need to make simple changes like date formats for records that are fairly standardized across platforms such as invoices, or much bigger changes for other types of data. Data transformation also shouldn’t involve lots of manual work that can’t be easily repeated if the exact details of what needs to be migrated change. Lots of manual manipulation also increases the likelihood of human errors that can be difficult to identify later on.

6.3 Load Your Data Into the New System

You might need to familiarize yourself with how to load data into the new Enterprise Resource Planning system. NetSuite requires that you know the order in which records need to be imported due to dependencies (e.g. customers before sales orders and sales orders before invoices). There are also some things that might need to be entered manually such as quotas, promotion codes, a list of competitors and a few other records. It represents a smaller subset of what needs to be migrated but some record types in NetSuite can only be created by manually creating them in the UI. This partly depends on the method you use. The easiest and standard approach is through CSV imports, which support the most commonly imported record types but not every single one.

7. Test Thoroughly

When it comes to testing, it’s easy to test one record or even 20 records of the same type and still miss a potential issue. The key is to first identify representative subsets of data, this means finding specific types of orders, customers, thinking of different scenarios, etc. Perform tests with a variety of use cases to ensure that they all work – regression, spot and functional testing. Remember that data can migrate without any technical errors but still be inaccurate or not be complete enough to use properly. Here are some common categories of tests:

1. Record Count – Was the correct number of records migrated?
2. Completeness – Did everything populate that was expected?
3. Correctness – Is the information in your fields correct and in the expected format?
4. Functionality – Do things function as expected? You might not be able to test this one out of the gate but it is very important. One thing you can do ahead of time is to look at the fields that the import didn’t populate and make sure that nothing critical is being excluded from the ETL process.

8. Don’t End up Back Where You Started

After you’ve put all of this effort into cleaning data and identifying unused records, don’t let it go to waste. Take advantage of the situation and maintain the high integrity of your data going forward. Assign specific individuals to maintain the integrity of the types of data that they work with. Periodically verify what is still in use versus what can be discarded. Look at old fields, forms, and irrelevant records. Some good practices for maintaining your Enterprise Resource Planning are:

1. Establish Ownership – Assign someone to be accountable for different types of data. As you make old products obsolete, stop using certain custom fields or retire old reports, there should be someone to manage this data so it doesn’t just sit there slowly adding more clutter and making it harder to tell what is relevant and what isn’t.
2. Document Complex Processes – Document technical functions but also business processes. Make sure to at least note processes that partially involve your Enterprise Resource Planning so that they aren’t forgotten when future changes are made (e.g. manual entry of data from another system). When creating custom solutions you should have a way of identifying what the components are and what function they perform. Doing this will enable you to more easily upgrade the customizations but also to retire them when they are no longer needed, with no forgotten components left lingering.
3. Maintain Organization-Wide Alignment and Direction – Having employees frequently make ad-hoc changes is another rabbit hole to stay out of. The changes made by one department have unintended effects on another. They may also end up resulting in a series of band-aid fixes when a more holistic one would have been better. Using release cycles and a centralized process for evaluating changes is one way to make sure you don’t end up with 50 custom fields on your custom records that are rarely ever used.

Key Takeaways

• The fundamental necessity of having a successful ERP software implementation is a commitment to starting early and focusing on what is being migrated. It’s better to start early when you can detect obstacles and fix them with smaller course corrections than to make drastic changes to the project plan right before going live.
• A planned and structured ERP data migration plan with contingencies is the cornerstone of any successful ERP implementation.
• Look ahead to see potential problems while there is more time to address them.
• Build contingencies and fail-safe plans into your ERP data migration plan so that you can stay calm when things don’t exactly go as expected.
• Be diligent with maintaining your data going forward, to keep your ERP platform usable.

When you finish the data migration process is about time to realize how to handle a new ERP system. Honestly, once your team learns how to use it, the benefit would worth it, but we understand that it is not an easy transition.