Accounting Data Conversion Highlights and Homework Recommendations

When you are switching from one Accounting or ERP application to another one data migration is one of the key questions to consider. If you are staying within the same application vendor them chances are high that there is migration tool. For example if you are on DOS version of Great Plains Accounting then you can migrate to Dynamics GP via migration tool and then upgrading all the way up to current version 2010. Such migration allows you to avoid expensive and often difficult historical data conversion. If conversion tool doesn’t exist then you have to assess the project and this publication should give you some recommendations and excurse into conversion theory:

1. Master data, open and historical documents. When you are planning migration project we recommend you to prioritize. Typically the highest priority is given to so-called ?master tables’ such as customers, addresses, employees, vendors, bills of materials and GL chart of accounts. Open transactions are AR and AP invoices where payment is not yet recorded. In the case of small company chances are high that the number of open documents is reasonably small and they could be keyed in manually. And the rest of the tables store so-called ?historical’ documents. These are posted and paid vendor and customer invoices with applied payments, GL transactions, Payroll documents and checks and variety of posted transactions in peripheral modules possibly including manufacturing, project accounting, Human Resources or you name it

2. Which documents to migrate? Here we recommend you to take into consideration the cost of conversion. It is reasonably low for master records and often high for historical transactions. Historical document got to be entered or imported in the new ERP application and then processed where processing updates summary tables, creates GL distribution and possibly records in such modules as Bank Reconciliation. We do recommend you to consider avoiding or minimizing the scope of historical records migration. Consider the following approach where you are keeping your legacy accounting system up and running for historical inquiries only. This method should save you a lot of consulting money. Plus from our consulting practice we have to share with you that deep historical records migration often leads to data inconsistency and problems with future version upgrades

3. Data migration technology. Each ERP application has its own integration tool. For example SAP Business One has Data Transfer Workbench with CSV templates. Peach Tree has Excel templates. Microsoft Dynamics GP has Integration Manager which could be acquired for conversion project only. The challenge is often related to the way of exporting data from legacy accounting database. We recommend two approaches where the first one is ODBC connection to legacy tables and the second one is printing reports into text files and then importing these files into custom SQL tables and following cleansing

4. Exporting records from Great Plains. When customers are talking about Great Plains they might be referring Great Plains Accounting for DOS 9.5 or earlier version or Great Plains Dynamics 5.5, 6.0, 7.5. In both cases application was available on Pervasive SQL 2000 or former Btrieve. Since version 8.0 Dynamics GP or in essence rebranded Great Plains Dynamics was available exclusively on MS SQL Server platform. SQL Server data export is not a big challenge. Table structure could be reviewed in GP SDK or in user interface: Tools -> Resource Description -> Tables. If you are on GPA for DOS then tables are files in GPData directory and their names are self-explanatory. ODBC connection could be established in Pervasive SQL Control Center where you need DDF (Data Definition) files. DDF files could be produced for Great Plains Dynamics via GenDDF utility. Please see GP documentation. For GPA DDF generation is described in ODBC manual which could be found on GPA installation CD

5. Exporting documents from SAP Business One. It is popular on MS SQL Server DB platform. Tables and Fields could be explored if you select in menu View -> System Information. Plus SAP B1 allows you to export almost every report into Excel and when you feel that report doesn’t exist then you can create custom query and export results to Excel as well

6. Exporting transactions from small business accounting and ERP packages such as MYOB, Peachtree, QuickBooks and Quicken. These applications are trying to offer high end solution where data might reside in SQL Server. However often you have to deal with proprietary database platform as the cost of SQL Server licenses could be avoided. We have good experience exporting records from QuickBooks by simply printing reports into text files

7. Excel as data cleansing tool. Yes, this is simple one and everyone knows it or at least got college class on Excel. However we had multiple situations when customers had millions plus historical documents and Excel was not ready to process such a volume. If you are facing the same problem we recommend you to import exported records into custom SQL table via data import wizard (SSIS in SQL Server 2008 or 2005) and then do cleansing job via SQL Views

8. Second Opinion in Migration Projects. First of all we would like to stress that there might be no reasons to blame your initial ERP reseller. Local firms are doing great job by popularizing and selling modern accounting packages. The problem is often in over expectation from the customer side. Local consultant has to take every business offer coming to his or her direction including the projects where technical challenge is present. Normal way is subcontracting programming work. But subcontracting brings to the uncertain territory where job completion is not guaranteed. If you basic implementation is done then this is OK to appeal to second opinion and switch to VAR with better technology exposure even if this is nationwide firm offering remote support