News & Publications
Insights and thought leadership in Odoo

Odoo Open Source Data Migration and Cleansing Tips

For many businesses, the most challenging part of an enterprise implementation involves data migration. In fact, according to a Gartner report, as many as 83% of data migration projects exceed their deadline or budget and in some cases. This leads to failed implementations in which many CIOs and CTOs have lost their job. Even for projects that still manage to get off the ground, the complexity of data migration can cause delays in deployment and adoption. Mistakes lead to diminished data quality, application issues, and decreased productivity.

For many OSI customers, what they thought would be an arduous undertaking turned out to be something less painful and more productive. That’s because they began with the right OSI recommended steps, which OSI now provides to you below. OSI has taken more than than 10 years of experience and lessons learned implementing IT projects and data migrations to confidently recommend the below seven guidelines. With them, you can achieve seamless data migration that fuels your enterprise ERP implementation success.

1. Determine the minimum amount of data to migrate

Take the time to assess precisely what and how much data actually needs to move into the new system. It’s hard to replicate data in a 20-year-old legacy system in a modern, sophisticated new enterprise system. It becomes increasingly complex, costly, and inefficient. Once you take a closer look at how it is currently used and how it will be used in the new system, you should have a good idea of which data to migrate. Ask things like what fields or records are no longer used and can thus be left behind? Is there missing information that needs to be filled in from another data source?

Begin by understanding the difference between master data (product definitions, customer records, etc.) and transactional data (invoices, sales orders, purchase orders, etc. ). Almost all ERP projects can go live with just master data, and little if any transactional data is required for continued business success. Also, remember that if you decide not to migrate transactional data, you will still need the bare minimum of opening balances in accounting and in-progress transactions like open shipping orders, purchase orders, and incoming shipments. You will also need invoice data, regarding which vendors you owe payments to and which customers have open invoices to you.

2. Build data dictionaries

Data dictionaries can be a valuable tool for managing large data listings, especially when migrating data to a new system. Your data dictionaries ensure that the meaning, relevance, and quality of data elements are the same for all of your business users, across departments, and applications. Data dictionaries also provide the information needed by those who build systems and applications that support the data.

Building the dictionary can help simplify the structure for new system data requirements and ensure data integrity and the removal of data redundancy. You can do something as simple as creating a spreadsheet with definitions for the old system as well as definitions for the new system. Make sure to build a data dictionary that includes ranges and acceptable answers to help track and trap bad data. It should provide an organized, comprehensive list that is easily searchable to enable reporting and documentation for data across multiple applications.

3. Is it a one-time or ongoing migration?

You may instantly assume that your migration is a one-time static event. But you have options. Continuous (sometimes referred to as ongoing or online) migration offers a flow of changes from your legacy systems and applications to the new open source ERP destination. You can do this after an initial full dump and load. There may be cases where your legacy systems will live for a protracted period after the launch of the new system, in which case you’ll want an ongoing data migration to populate the new system with the more timely and accurate data available. For instance, you may have a vendor that supplies data weekly; or have a report that you have to generate weekly for a customer. In these instances, you’ll want to build an ongoing migration.

4. Clean data in the source system. Build a validation filter in the target system.

In general, experience shows that if you clean data in the target system, you’re going to have a better end result. You’ll want to perform functions like de-duplicating data and fixing data integrity problems in your source system to prevent porting over issues into the new system. You can build validation filters in your new system that help in your testing process.

A typical example includes customer addresses, where the city and zip code are not stored in a standard format. Your new ERP system maintains separate fields for these and will use them for marketing, sales, shipping, customer service, reporting, taxes, and much more. In such cases, extracting the data into a spreadsheet, and moving those elements to their own columns, will work. And you may be able to take advantage of existing templates that can then be imported into the new ERP system.

5. Keep meta statistics of the data—track quality

Your data migration provides a bountiful opportunity to track and find trends previously unseen. As you migrate data, you can do more than just uncover anomalies—you can further investigate to gain insight and make impactful decisions. For instance, you may find quality trends in a portion of the data that reveal another issue—such as a particular branch not being trained right in data entry, or a temporal or seasonal selling spike or supply chain lull that was previously hidden.

Perform a root cause analysis of data coming into the new system. You’ll want to interrogate your data to look at attributes around quantities, geographies, periods of time, and more. You may end up doing great preventative care for your business and add additional value as part of the implementation process.

6. Keep the legacy system running

Sometimes the best approach involves keeping your legacy system running as read-only until you’re sure you moved over everything that needs to be moved and nothing more. As the implementation proceeds, you can ensure the new solutions contain the right functions and processes for success while having the comfort zone of the legacy system to rely on when needed or to provide a richer testing ground.

Consider keeping your legacy system up and running for 6-18 months if you think that legacy transactional data will need to be accessed. Perform a data-based cost assessment of how much time and resources are required to do a complete transactional data migration. Compare this with the cost of keeping your old system available for 18 months. This will help you decide if it is worth it to migrate transactional data, as well as if and for how long you may keep your legacy system running.

7. Test early and test often

Testing is a critical step in the data migration journey. You need to decide which method to use, whether it’s a simple spreadsheet, scripts for minor automation, or a more robust migration tool or service. Each option has pros, cons, and varying degrees of complexity to set up. No matter the tool, the only way to verify data migration success involves testing, which is a monotonous and time-consuming process where you will discover issues, make corrections, and re-run steps.

Assigning staff for each data type will help split up the work and gain efficiency in the overall effort (e.g., sales personnel owns customer data, accounting staff own chart of accounts, warehouse owns products, etc.). Remember that even if you are only migrating master data, you will still need to do a final migration during the go-live event. Some master data sets, like products and customers, will have new additions up until the last day of using the old system. You have options at this point to only migrate the variances (the new records) or re-run the complete migration steps from scratch. Because you’ve tested your scripts many times by this point, you’ll know how long it takes to do an entire data migration run, and thus you’ll be able to decide if it makes sense to do a complete migration during the go-live or if it’s better to only migrate the new records.


Data migration is often thought of as a challenging process in implementing a new enterprise system implementation. But it doesn’t have to be. After years of successful collaboration with customers in every industry and company size imaginable, OSI has a successful track record of taking the pain out of the process. OSI customers can attest to this. By following these seven proven guidelines, you’ll ensure a transition that’s faster and more ready for a successful launch.

Remember that while data migration involves upfront effort, you’ll achieve a significant return on value in terms of increasing process efficiency, reducing waste, and boosting collaboration. And OSI stands ready to help. Our professional services consultants can carefully craft a data migration strategy that ensures your Odoo open source solution runs on reliable data that delivers better business outcomes.

About the author:

Greg Mader is the founder and president of Open Source Integrators (OSI), a global leader in open source business consulting and IT services. He’s an Army Veteran and entrepreneur, adept at managing large teams and complex projects, delivered on time and budget. His background includes leading and developing large and small teams, with a focus on communication, mission relevance, and improved deliveries. His expertise spans across strategic planning, project management, ERP, GIS, and other enterprise technologies.

Leave a Comment

Please Enter the Captcha Here

Get Started

Take the next step to connect with us and discover the power of Odoo. We look forward to speaking with you!

Financial Accounting with Odoo.
We literally wrote a book about it.

Enhance your Odoo experience with our exclusive open source modules and resources.