Dynamics GP Data Migration from Your Legacy Accounting Application

You should be aware that modern Corporate ERP systems, such as Dynamics GP, AX, SAP Business One have high level of abstraction and among the three mentioned, only Dynamics GP allows you direct data inserting, while Axapta and Business One do not allow you alter data on the table records level (you may try and see what will happen…).  Let’s now concentrate on Great Plains Dynamics GP.  For initial data conversion we recommend you to consider Integration Manager.  If you plan only initial one-time data conversion, you may consider IM Conversions license, or if you plan ongoing integration (especially in ecommerce daily shopping carts, Point Of Sale transactions import, or even in simple custom EDI, where you would like to integrate Electronic Data Interchange files without purchasing expensive EDI integration capable products) – consider IM full license (or you may choose Financials or Distributions only).  In some cases, when you are walking away from legacy accounting and implementing GP, there might be migration tool, especially when you are on GP track (such as if you are on Great Plains Accounting for DOS, you may upgrade it all the way through Dynamics GP 2010; or if you are on Great Plains Dynamics on Pervasive SQL 2000/Btrieve or Ctree – no need for data conversion – upgrade route will lead you to the current Dynamics GP version 2010 or previous version 10.0; there is also Rapid Migration Tool, which should help you to migrate from QuickBooks or Peachtree to Dynamics GP).  Let’s now review data migration and conversion steps and recommendations:

1. Decision on the Conversion Scope.  Some customers may say – why don’t we just migrate “everything”?  or “everything possible”?  The answer may be in the cost.  Theoretically you may migrate all the data from your legacy accounting, but it might be impractical, especially when you have huge amount of historical data and you do not expect to use it (some queries might be needed, maybe if you expect future company audit, but other than that…).  We recommend you to consider keeping your legacy Corporate ERP application on the stand alone computer for possible queries and begin from the new page with your new ERP system, Microsoft Dynamics GP.  Consider moving just master records: Chart of Accounts (you may decide to re-haul its structure and clean it up a little bit), Customers, Vendors, Employees, Addresses and beginning balances in General Ledger.  This approach should allow you to minimize data conversion budget and be ready to start using your new Accounting in the matter of several weeks.  If you would like to see GL reports, where you compare current year versus last year (typically P&L or Balance Sheet in FRx or Microsoft Management Reporter) – then you may decide to convert GL transactions for the last year (the easiest way is to combine all the transactions for the fiscal period, often it is calendar month, into one and post them into the open previous year).  Conversion is relatively straight forward to be designed in Integration Manager.  If you think that this conversion method doesn’t work for you and you really need historical transactions to be converted in such modules as Sales Order Processing, Purchase Order Processing, Inventory Control, Payroll, let’s try to review that in the next paragraph

2. Historical Data Conversion.  It might be required, try to answer the following questions.  Do I really need to base my Pricing for specific customer on historical orders?  How far would I like to go to the history – one, two, five years, prior to resetting the customer price list to my current standard pricing?  Do I want to negotiate the price with my supplier, based on my historical purchase orders.  If my customer have self-service on my B2B ecommerce portal, what is the historical time span for them to see?  If you have the answer, where you indicate that one or several of these questions have answer “Yes” – you may need historical data migration.  Let’s review how historical data could be migrated

3. Integration Manager in Historical Data Conversion.  It is not really difficult to integrate Sales, Purchasing, Inventory, Payroll transactions in IM.  The challenge is typically related to the volume of historical data, that is the subject to migration.  If you have several millions of Sales transactions in the past ten years – you may expect several hours or even days to move them into GP, as IM validated business logic for each one and you may expect the migration process to be repeated several time, until you are satisfied with the results.  Another recommendation in historical data conversion – please, consider unchecking Posting Through and To General Ledger, as the most likely GL beginning balances or even historical years are handled directly in GL historical data conversion

4. Some facts about Integration Manager for programmers.  The easiest way to setup integration is to base it on text file (tab, comma or special character delimited).  However IM is not limited to text files only.  Source Query could be based on simple or even so-called Advanced ODBC, where you can pull data from such Microsoft Databases as MS SQL Server, Access, Excel, Word, plus from non-Microsoft DB platforms, such as MySQL/Linux, FoxPro, Pervasive SQL, Ctree, Oracle, DBII to name a few.  Advanced ODBC also could help you with manipulating text file (Microsoft Text File ODBC Driver) – you can use such constructions as Union, Group By, Having, etc.  In Integration Manager you can also tune integration logic via Events triggered VBA scripts: Before Document, Before Document Commits…

5. Technical Side of Integration Manager.  Dynamics GP is kind of in transition to deploy as much SQL Stored Procedures as possible (and expose itself to such technologies as .Net, XML, Web Services, C#).  IM is not an exception, with Dynamics GP 10.0 we see strong influence of eConnect SDK (the background technology for eConnect are encrypted SQL Server Stored Procedures).  With the introduction of the version 10.0 and especially with 2010/11.0 eConnect is recommended alternative to traditional Integration Manager Queries, based on OLE Server (old-good-days Great Plains consultants know – when you are launching IM all the Windows should be closed and GP application goes into background mode, enabling GP as OLE Server).  eConnect is based on SQL Stored Procedures, and it doesn’t require you to have Great Plains user workstation running (and consuming one concurrent user license).  Plus eConnect connectors for Integration Manager are naturally faster (OLE Server is very high level abstraction and it fill in all the fields and then submits the form for processing as you would do it in user interface, where eConnect simply call encrypted SQL stored procedure)

6. Ongoing Integration.  Let’s review this routine for such popular case as ecommerce shopping cart integration into SOP Invoice.  Integration Manager could be triggered to launch SOP integration on demand, or it could be scheduled to be called every ten minutes (quasi real time integration).  We do believe that quasi real time integration is feasible option for most of the ecommerce retailers and wholesalers.  In the case, when you really have to see the ecommerce shopping cart originated transaction in the matter of seconds, you may decide to program it in eConnect libraries for Microsoft Visual Studio.Net

7. To request further support, please call us 1-866-528-0577, help@albaspectrum.com We need to discuss your cards in order to recommend you the best solutions, which is not contingent to our preferences.  We serve you USA/Canada nationwide via remote support (web sessions and phone/Skype conferences).  Local service is available in Western Michigan, Chicagoland, Southern California (LA, Orange County, San Diego), Houston area of the state of Texas