A Practical Perspective on Data Migration – Dynamics 365 for Operations
“You’re supposed to migrate two years of history, right?” That’s the question I always get from bewildered IT managers at the beginning of an implementation. The last thing they want is the dreaded “consulting” answer of “It depends…” But if there’s ever been a truer answer, I don’t know it. There are many approaches to data migration, but a highly practical approach has served me well for years.
The question I always lead with is “Is the old system going away the day you go live?” Sometimes the answer is legitimately “yes,” either because it’s a SaaS (cloud) system or because the license gets shut off in some other way. But the vast majority of my clients answer “no,” and then we get into what I affectionately refer to as "data negotiations."
Data Negotiation 1 – Master Records
This is the easiest part, hence our first. Master Records typically consist of vendors, customers, items/products, employees, general ledger accounts (list not exhaustive). Most of the time you need these things in your new system. And most of the time there’s more than a dozen of them, so you want to export them from your old system and pull them into the new. Sure, there’s plenty of discussion around “Should we keep those awful old customer numbers or give them shiny new ones?” and discussion around whether or not to migrate master records that haven’t been used for half a dozen years. But this is the easiest part because you need vendors, customers, items, etc. to run your business. Simple. We import them.
Data Negotiation 2 – Supporting Records
Now we’re starting to get a little squishier. Supporting records include warehouses, inventory locations, payment terms, purchase prices, pay codes, etc. This list is long, and the supporting records in your old system likely don’t map over in a simple 1:1 a lot of the time. But the good news is that though the list itself is long, the data entry burden of these items is probably fairly light compared to master records. So unless you have a thousand warehouses (and you might), most of these items are good candidates for manual entry into a “golden template” ( the version of the software used to copy from one environment to another with lots of nice configurations completed). So a little trickier because there’s a lot of variety, modules, etc. here but still relatively straightforward because we have a rule of thumb. Key them in unless there are a lot.
Data Negotiation 3 – Open/Current Transactions
This one is all about conversion efficiency. Open transactions would be things like un-shipped or partially shipped sales orders, production WIP, unpaid vendor invoices, on-hand inventory cost/quantity, or current year GL account balances. Data migration can be easy or hard depending on a bajillion factors. One that comes to mind is massive data mapping. If you’re substantially changing your inventory item structure, it might be a real pain to build a translation in the data conversion tools to tell the system how to translate the old item numbers into the new. In that case, if you only have 100 open sales orders with an average of ten lines apiece, it might make sense to hunker down over the weekend and just bang those things in for cut-over. Conversely, if your items haven’t changed much or at all and you’ve got 1,000 open orders, it’s worth the effort to be able to build out the import ahead of time and just push the button at go live.
Another consideration is where the bottlenecks are on the project. If your IT manager is also your project manager AND data migration gal, she’s probably going to be juuuuuust a little busy at cut-over. So it might not make sense to migrate stuff that’s super easy to enter like GL balances, open AP invoices, etc. For years and years, I’ve shown people with little to no training how to just key that stuff in and git’er done. It might take longer overall, but if you can take the sales manager, CEO, and receptionist, sit them down in a room for four hours, and get all that stuff entered with only fifteen minutes of instruction, it can be a big win for that constrained, critical IT manager.
Our rule of thumb here is to weigh volume and complexity to determine to import or hand key.
Data Negotiation 4 – History
And now the question of history finally rears its ugly head. This is the hairiest one because unless the old system is going away immediately, there has to be a really good business reason for migrating the data. Isn’t the CFO saying “Thou shalt have two years of financial history” a good enough reason, you ask? Probably not. The yardstick for history is “Will it cause the business to not be able to operate? If so, what are the alternatives?” Let’s go through some fun examples.
GL monthly balance history is the easiest. Let’s start there.
Me: "Why do we need it?"
CFO: "Comparative financials."
Me: "Is having it in the system the only way to accomplish that? No."
CFO: "How else would we possibly do that?"
Me: "Wellllll…you could have your assistant controller export the financials to Excel and manually put in the prior year comparison for one year after go live (twelve times)."
CFO: *quietly chokes*
I know this sounds gross but it’s an important example of a business question. Is the cost of the assistant controller doing that twelve times higher than the cost of consulting and IT time to migrate that data? Is there a business risk to having that be a manual process rather than something that lives in the system? I’m a software person; my brain says, “Please, please, please, put it in the software where it belongs!” But it doesn’t always make business sense.
Let’s do another example. GL transaction detail. Same actors.
Me: "Why do we need it?"
CFO: "Because we might need to look something up one day."
Me: "Is having it in the new system the only way to accomplish that? No."
CFO: "Ok. I think I can read you now. You’re going to tell me to look it up in the old system, aren’t you?"
Me: *thumbs up*
Unless your auditors, a government, or some other authority absolutely requires you to migrate ledger transaction history OR your old system is going away, there’s typically very, very little business bang for your buck in migrating that type of history.
Ok. Last example, I promise. Sales history.
This one is the best (or worst, depending on how you look at it). Note the new actors and larger audience.
Me: "Why do we need it?"
Purchasing manager: "Because our purchasing team looks at what we sold last year and does a bunch of fancy math to figure out what to buy EVERY DAY!"
Me: "You know what I’m gonna say, right?"
Purchasing manager: "Yeah, yeah, that can be done outside the system. But that will be 1,825 days of misery over the next five years of mashing up all that data every day."
Sales manager: "We use that too, though. And not just to look things up. We use it to calculate pricing for customers individually."
Me: "Ok, keep it coming. Anyone else use sales history too?"
Production manager: "Yeah! For our make-to-stock product lines, master planning is gonna figure that out for us based on what we sold in the past."
IT manager: "Um, guys….you realize we all agreed to change our item structure pretty radically. And we used to use dummy sales orders instead of quotes in the old system. How are we going to clean up and convert all that?"
Group: *uncomfortable silence*
This is the best example of a time when it really makes some business sense to take a look at importing history. It’s unfortunately also the best example of when data migration can be the hardest. There can be a negative business impact if this type of data isn’t available to users and real, quantifiable pain as an alternative. It’s critical to have a good understanding of what the alternative procedures are going to be and who they are going to impact. If the plan will be for IT to build a bunch of new reports that unify old system data and new, that needs to be addressed early and tested often as the implementation progresses. If the plan is to figure out how to clean and convert that data, don’t wait. Get crackin’.
Happy migrating, folks!
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.