Data Migration Do’s and Don’ts – One Consultants Option
Having just been part of two huge data migrations in the last month, suffering through the issues, fixes and repetitions and knowing there has to be a better way, I decided to put together thoughts on how to plan and improve data migration projects.
A big focus of my thoughts center on two topics – Master Records and Transactional Data. The goal is to impart knowledge about important fields regardless of your software, provide some understanding on the impact of changes and discuss dealing with transactional data.
I will look at master record setups and what to consider not changing during migration. Also, I will review transactional data recommendations and the big ‘gotcha’. Throughout will be a recurring theme about planning. And last but not least I will do a quick preview of the BC Configuration Packages.
Master Records
Master records are Chart of Accounts, Customers, Vendors and Items for my discussion. For your specific organization, production bill of materials, fixed assets, jobs or something else may be part of the master record classification. Whatever the master record list, what is covered herein should be considered when planning for data migration.
Some fields are crucial, some fields connect from table to table, many fields are very different between software solutions and how you handle each of the fields in your data migration requires planning.
Almost everyone changes their chart of accounts during a data migration. Think of the scenario where the account number’s format is:
WW – Division
XXXXX – Account number
YYY – Department
ZZZZ – Location
When an account number might be 01-23456-789-ABCD. We’ve all seen a segmented chart of accounts like this and when printed the chart of accounts list is pages and pages and pages long.
BC says the account number is 23456 (using the above example) and Division, Department and Location are dimensions. The dimensions are held separately, controlled at the account level and can be used against any account as desired. List of accounts is a fraction of the legacy segmented account list.
What controls the general ledger posting from customer and vendor transactions in the legacy system? How does that correlate to the new system’s settings and do you want changes? This is a vital question to ask, to know and to understand. Other fields to review, payment terms, currency, ship-to addresses for customers, order addresses for vendors, salesperson/purchaser, contacts, countries and associated foreign addresses. The task is to know what data fields are important to you from your legacy data and how that data needs to be migrated.
Keep in mind that the master records (chart of accounts, customers, vendors, items) can and should provide most require datapoint that are important for your final outcomes. Don’t forget that using item categories, posting groups and other fields can provide great filtering options for pages views, reports, etc. Saying this another way…maybe you only need one customer posting group for accounting; but it would be beneficial to run an aging or sales report by type of customer (residential, commercial, governmental.) You can setup three customer posting groups all pointed to the same general ledger account and use this field for filtering. Lots of fields to think about to get those desired outputs.
One of the challenges in data migration is the mapping of legacy fields to your new system. Making sure that accounting, pricing, inventory tracking, etc are working as they used to or better yet as desired for future. Don’t forget reporting and the use of additional fields as desired above. Think outside the box, for what data you wish you had in times past.
You want to make changes in data during data migration but do so with CAUTION! In Business Central, we are lucky as we can setup a mapping in configuration packages to read customer 1234 and convert to ABC123. Why is that valuable because creating the new customer is easy but then when you need to load the beginning AR balances by customer by invoice….you need all those legacy entries converted as well. Same thing for general ledger accounts, vendors, items and dimensions, if they are used.
A big caution if you are a manufacturer and want to change item numbers, don’t forget the Bill of Materials have to be changed as well. And pricing should be considered when changing item numbers.
To recap, change master record numbering with caution and consider all the other data that is associated. Make sure that other fields in master records have the correct values to supporting tables. Example: if you payment terms codes, you have to update customer payment terms data. Plan, plan, plan and test, test, test.
All of the above master record fields lead us to how data will flow through transactions. It is crucial, vital, critical….and every other adjective you can think of for important, to test, test, test transactions from start to finish. Here’s a couple of lists to review:
Transactions that might be part of your initial data migration should be done and tested:
If you take the above list, you can divide into MUST, Maybe and Complicate categories
Must:
Maybe – if the volume is low, rather than automating the migration consider using this as part of training at go live and let users input.
Complicated because these have an impact on beginning inventory quantities and general ledger entries. They must be planned and tested and will generally require user invention.
The NO category included items that affect multiple ledgers and cannot be imported without the far reaching impacts.
Keep in mind that if sales, purchase, production or transfer orders relating to inventory have been partially handled in the legacy system, getting them into the new system in the same state will require a great deal of planning, some manual intervention and most likely, accounting entry corrections.
If its your first BC implementation, don’t try this yourself…..let your partner help you and save time, money and loads of frustration.