Migrating data is one of the most mysterious parts of switching systems for new accounting software buyers. Even for experienced buyers who have previously implemented software, the big question remains: How easily will our data transfer?
The dream is to transfer data with a couple quick commands, in one try, with no headaches. But there’s always the fear of the nightmare scenario that you’ll need to manually enter data and burn up days or weeks of valuable time in the process. The reality is almost always somewhere in between these extremes.
Dan McGuckin Consultant, TAG Management
In order to pull back the curtain on what goes into transferring data from one accounting system to another, I connected with Dan McGuckin from TAG. TAG is a software provider offering accounting programs from NetSuite and Sage, as well as their own Contractor V product. Dan has 20 years of experience in designing and launching enterprise application initiatives and has personally assisted on hundreds of implementations.
To get a better grip on what you can expect when migrating accounting data between systems, read on to get answers on the following questions:
There’s primarily two reasons that an open-source standard hasn’t developed. First, different types of industries and companies require different data elements within their customer record. Second, the need to export customer data out of an ERP package, for the purposes of an ERP conversion, simply isn’t a high priority for software publishers.
The easiest type of conversion is when only G/L balances need to be converted. Otherwise, the best case scenario is when the data is “pristine,” meaning that:
The worst-case scenario is essentially the opposite of the above. At a certain point, the customer’s entire chart of accounts will need to be redone. This has a cascading effect on everything else since the subsidiary ledgers generally call out G/L accounts.
A common source of conversion complexity is when exported data from the legacy system requires a lot of clean-up. This not only takes time, but mistakes can be made during this clean-up effort. Things can get especially complex when validating fields (i.e. terms codes, vendor codes, valuation codes) when they’re not the same across the legacy and new ERP system. Great care must be taken so that the intermediary Excel spreadsheet that will be the source of the data for the new system is correctly updated to reflect the new coding system. Bringing over complete AR and AP history further complicates things.
Generally, this is meant to describe a data import program that is designed to add records to a “list” type table in the new ERP system. An intermediate holder of data is usually an Excel spreadsheet which holds the data after it is exported from the legacy system and before it is imported into the new system. By virtue of having this Excel spreadsheet, it allows for the editing of data before it is imported into the new system
Conversion utilities may also refer to an API, but since 99% of the time data needs to be modified before being imported, API’s are not generally used.
I’d like to model a data conversion. Can you provide a step-by-step process for how, let’s say, accounts payable vendor records would be transferred into a new program?
The basic steps would be as follows:
The biggest issues is that the staff member would need to have a full data dictionary listing of all the fields in the new ERP database. If there’s any lack of visibility to field properties such as type (number vs. integer), length, or validation, mistakes are inevitable.
In order to estimate the required time to convert data, it’s necessary to estimate both the time involved in data cleansing and the time involved in doing the import.
In regards to the data cleansing time estimate, there’s a number of variables. Can the data be normalized before exporting to Excel? How many records need to be changed? Can the records be changed individually? Answering those questions–which generally needs to be done by the customer–will determine the data cleansing time estimate.
The time estimate for importing data is a bit more predictable. I would budget 6-8 hours per record type. For instance, I’d estimate about 4-8 hours for a customer import and 4-8 hours for the vendor import. For the import of a clean sales history file, budget 12-24 hours.
During the data import, if the new system runs into a bad record (one or more fields do not match the required properties of the field), the system will generally tell the user about the source of the error. The detail of the error, and therefore the amount of time required to figure-out the reason for the record rejection, varies across ERP systems. The other thing that can go wrong is that when a trial transaction is run using the record that you have imported, the system will throw an error.