Why Your Data Migration Will Probably Take Longer Than You Think  - A Case Study from a Sage 50 to Sage X3 Migration

I got brought in as the ETL consultant. That's Extract, Transform, Load - basically, I take messy data from old systems and make it work properly in new ones.

The parent company had their Sage X3 technical team sorted. But they needed someone to handle all the data work - extracting it, cleaning it up, and getting it ready for the new system.

The client's assumption was understandable - same software family, successful business. 

But from experience I knew better than to take that at face value. I always use a systematic approach - my Flow7 method.

I always start with a proper assessment. You can't fix what you don't understand.

When I dug into their systems, here's what I found:

• Stock levels in Sage 50 didn't match what was actually in the warehouse
• Processes had grown organically over years with zero documentation
• New products got added daily with just the bare minimum info
• Working remotely via their VPN was painfully slow

Not exactly migration-ready!

Once I extracted the initial data, the real picture emerged:

Product Data Problems:

• 7,000+ product records that needed serious clean-up
• No product assembly instructions for (BOMs) at all
• Different product types missing crucial data
• Critical knowledge trapped in a few long-term employees' heads

Missing Information:

• Sage X3 required loads of data that Sage 50 simply didn't track
• No recorded warehouse locations in Sage50
• Sales orders and Purchase orders with random text instead of proper product codes

The Killer: Most of this wasn't just data cleaning. It needed people to create entirely new information from scratch.

I needed a controlled way to manage all this data. Here's what I was up against:

• Problem 2: Needed loads of new data that didn't exist anywhere in the companies processes. 
• Problem 3: Who was going to create all this missing data? 
• Problem 4: New data had to integrate with parent company's existing live system from day one. 
• Problem 5: All imports had to be done manually (no "press of a button" solution as often assumed by clients!)

Lucky for me, I'd worked with the parent company before. I knew their Sage X3 setup and had a good grasp of how the data structures worked.

Also the project was being delivered by an implementation partner that needed to move through tasks in their own project plan; they were always only too happy to support the data, process or field checking queries when raised by me, these were timely and on point. It was good working with them. No messing, no fuss.

I created basic mapping templates using the base Sage X3 Excel Templates that implementation partner provided. This became my starting point for the mapping & transformation work.

This is my favourite bit. Taking messy, scattered data and making it work properly.

Sage X3 was completely different from their simple Sage 50 setup. I needed to:

• Restructure product info for X3's complex requirements
• Create data, fields and categories that didn't exist in Sage 50
• Convert informal knowledge into proper structured records

I used Excel for high-level mapping (why not?). It was perfect for discussing details with the Sage 50 team and they were using that daily anyway. Column B is my S50 tables/master data set  and Column K is from X3.

Then I transferred everything to SQL views.

My ETL structure in SQL:

• Source views (from Sage 50 tables)
• Transformation source and combined views (where the cleaning happens)
• Output views (that match import template requirements)

Basic data architecture. It works for me. There are other data architectures, but for this type of work this principle just works.

Original plan: 2 to 3 months (before any proper assessment!)

My analysis: Completely unrealistic. Despite the Source not being a large data set, it was very lacking in information for Sage X3

The Real Work Required:

• 7,500+ product records needing individual attention
• 1,500+ assembly instructions to create from scratch
• Hundreds of new data fields for processes that didn't exist in Sage 50
• Warehouse and manufacturing rules living in people's heads
• Thousands of purchase/sales orders needing clean-up
• Customer and supplier records too!

More Realistic Timeline: 5 months minimum. Driven by new data creation, not technical complexity.

Caution: The management team was sceptical about the timeline and data state - why wouldn't they be, its natural. 

So I ran tests to show exactly how long the cleaning actually took by the staff doing it and they flagged completed SKUs as they went.. providing an easy checking number.

It helped the staff as well, because they all had day jobs to fit around the data cleaning work. 

The Numbers don't lie. This became a resource and knowledge issue - that then affected my own ETL timelines and the data migration project as a whole.

Early testing is crucial. I always test the import templates first - make sure they actually work with every data set.

Key things to know:

• Once records are in Sage X3, they get new IDs - map these to the unique IDs in the source system.
• Need unique identifiers from the old system for updates - create your own don't rely on e.g. Product skus or Customer numbers despite them appearing as unique.
• Sage X3 error checking can be a nightmare during go-live so head this off early!
• ​​Expect Iteration between testing and final data requirements

I create my own personal Data Loading Plan and tested it multiple times as we went through DEV and UAT testing Sandboxes that were invaluable. When stakeholders want updates, they need to understand the time it takes and process - remind them, they do forget and will question the loading times. Although the data loading tests were a moving target the total load time could be estimated quite accurately. ETL can be iterative by nature so if you follow a similar approach to Flow7 you may reassess new data, then add or combine in the master data set to then feed through to testing.

The GUI in SAGE X3 was limited to 1000 records display so it was better to chunk up data for error checking.

Pro tip: Get access to the front-end system. You need to see what users see.

The company chose a full cutover over a long weekend. Stop the old system, start processing in the new one, that was live already for the parent. There products were structured very differently from the new subsidiary.

As soon as they downed tools in Sage 50, I get to work with my plan to hand. 

My example Loading Order (this matters in Sage X3 and sometimes comprises multiple imports for each ):

1. Suppliers/Contacts
2. Customers/Addresses/Contacts
3. Products main record
4. Supplier Products
5. Customer Loan products
6. Locations
7. Product sites
8. Product costing
9. BOMs (4 different types)
10. Work centres/operations
11. Purchase Orders
12. Sales Orders
13. Stock receipts
14. Financial data

Data loading can depend on fields or values in  previous data, so this may have to be exported back to the master set, as part of the load process An example is the Supplier Ids from SAGE X3 to prepopulate Supplier Products.

Caution: Make sure to align record counts with the business team that you are importing. This is one key check for me and I often repeat it, during the project like a stuck record (so I've been told, I'm the same with Backups of the data!)

During the go live loading, different team members are waiting on the data to populate to start running their checks and tests. Whether, you the business, are managing the Go Live Data Plan or your implementation partner its a crucial time. Your team needs to be fully informed and are crucial to the success of your data migration project.

1. Never Accept Data Assumptions - Have a look!

Complex Data Situations:

• Data scattered across multiple systems
• Inconsistent formats and quality
• Legacy systems with years of problems

System Transformation Projects:

• Moving from simple to sophisticated systems
• Company mergers needing data consolidation
• Any situation where "it should be straightforward"

Time and Budget Pressure:

• Unrealistic stakeholder expectations
• Need to prove real scope of work
• Can't afford to get it wrong

I hope this case study demonstrates why not to overestimate your data suitability before you have a good look at it.

Start with a thorough assessment - Surface-level assumptions about data quality are usually wrong. 

For your next migration project, especially in acquisition scenarios: invest time upfront to understand the true data picture before committing to timelines and budgets.

Plan for business continuity - Phased approaches can keep operations running during migrations

Test everything systematically - Proper testing prevents go-live disasters

The lesson here isn't about just assuming the data is okay - it's about having a proven process to identify what you're really dealing with.