EFaru Migrating Data from Great Plains to Dynamics AX, SAP Business One, Oracle Notes for Consultant

July 25, 2011 - PRLog -- When you make a decision to implement new Corporate ERP in the near future, you typically stop version updates for your existing accounting application.  When that future time comes legacy application might be so outdated that it is not easy to export its records without consulting help from somebody who is still supporting old releases.  In this small publication we would like to review such popular mid-market ERP as Great Plains in its various clones.  Chances are high that you are on something like GP on Pervasive SQL Server or its predecessor Btrieve 7.5 or earlier versions.  If you have data exported and cleansed it is not such a big deal to migrate it into new system, as either have Excel templates or other popular method for high end Corporate ERP, such as Axapta, Oracle eBusiness Suite, PeopleSoft, JD Edwards and others.  Let’s begin with the methodology in exporting historical documents and master records from Great Plains.  The key concepts here are table structure and connection to the database:

1.   Table Structure could be reviewed if you login user workstation and in menu follow the path: Tools -> Resource Descriptions -> Tables.  Here tables are grouped by series, such as Financials, Receivables, Payables, Payroll, Inventory, etc.  Try to spot the following popular tables: SOP30200 (Sales Order Processing document header), SOP30300 (SOP document line), RM00101 (Customer Master).  This method is not restricted to the core business logic tables (described in Dynamics.dic metadata file), you can analyze table from custom module coded in Great Plains Dexterity, for example Intellisol Advanced Purchase Order Processing.  Second method is to install SDK from legacy CD #2, where you can find detailed table relation diagram

2.   Database Platform puzzle.  Current version of Dynamics GP 2010 is owned by Microsoft, however it was originally architected by Great Plains Software in Fargo, ND.  It is apparent that Microsoft has its own database vehicle SQL Server.  Great Plains Software had to offer its Dynamics on competing database platforms, such as MS SQL, Btrieve/Pervasive, Ctree/Faircom.  Dexterity architecture was designed as smart shell or IDE (Integrated Development Environment) coded in C++ with ability to switch from one DB platform to another as well as from one graphical operating system to another.  In fact in earlier and mid-1990th GP was available not only on Microsoft Windows, but also on Mac OS.  So, having said that, please try to identify your database.  Next paragraphs will review connection options to all of them one-by-one

3.   SQL Server.  It is not really a challenge for technical consultant to export data from SQL Server.  Be sure that you know who to identify the table you need – structure is described in the first paragraph.  Releases that might still be in production are on SQL 2008, 2005, 2000 or 6.5.  GP likely versions are 2010, 10.0, 9.0, 8.0, 7.5, 7.0, 6.0, 5.5, 5.0, 4.0

4.   Pervasive SQL and Btrieve.  Here you might be on version 7.5 or older.  Btrieve technology works via Windows user security and file handlers.  This is why Windows domain user should have appropriate rights to the Dynamics folder, where all the companies are stored in the form of subfolders and tables as BTR files.  Connection could be done via ODBC DSN or directly in Pervasive SQL Control Center, where you could issue SQL Select Statement and save its results to the file.  In both cases you have to produce so called Data Definition Files or abbreviated as DDF (Files.ddf, Fields.ddf and Indexes.ddf, where the last one is optional, however it boosts data extraction performance, especially when you have millions of historical documents).  DDF files could be produced by capturing, dragging and dropping GenDDF.set file on Dynamics.exe.  If you are not comfortable using Pervasive Control Center, we recommend Microsoft Access with linked tables, where the link is based on ODBC DSN.  You will need Pervasive SQL client on your user workstation, this one could be installed from original GP CDs or from Pervasive SQL 2000 CD.  If you are on very old version of Dynamics, when it was still on Btrieve, the good news is the fact, that it is compatible with Pervasive SQL driver and Control center

5.   Ctree/Faircom.  It is probably unlikely to find historical installation which is still on Ctree on Microsoft Windows operating systems.  They were popular in time in Mac environments.  Macintosh servers had some popularity, but later on Mac shops switched to Windows servers with Faircom, keeping Mac Great Plains user workstations.  In order to connect to Ctree database you need to install ODBC driver, available on your installation CD.  Use Microsoft Access to connect to Ctree tables

6.   Importing data to Dynamics AX former Axapta.  Here you typically use Excel Import.  In the case of high data migration volume, consider SSIS (be sure that you are aware about possible data compromise and the measures and recommendations to minimize the risk)

7.   Moving to SAP B1.  Migration tool of choice to consider initially is Data Transfer Workbench, working with CSV/Excel templates, which should be filled in precisely structured manner.  It is also possible to based DTW data conversions and ongoing integrations on ODBC connection to SQL Server View, where you are precisely imitating CSV template file structure

8.   Oracle Financials/Applications.  Here it seems to be possible to do direct import into production tables

9.   Great Plains DOS versions 9.5, 9.2 and earlier.  Here you are Btrieve database.  If you are still in production, chances are high that somewhere around 2000 or 2001 your GPA server files were moved from Novel or Windows NT server to Windows 2003 or even 2008 and redeployed on Pervasive SQL 2000.  GPA had innovative structure in time, which makes it difficult to extract documents.  The problem is in its sharing one Btrieve file with several relative tables.  This is why when you are selecting records from Great Plains for DOS via Pervasive SQL Control Center substantial percentage of lines looks like garbage and doesn’t fit to table definition layout.  In order to extract records from GPA for DOS you have to apply alien data records weeding out restrictions in SQL Select statement in Pervasive SQL Control Center Query

10.   Message from Andrew Karasev, Microsoft Dynamics Great Plains Certified Master.  If you need help with exporting data from Great Plains or its historical versions, down to Great Plains Accounting for Windows, Dos and Mac, feel free to contact real enthusiasts and people, who are supporting all the versions of GP as technical consultants and programmers.  Service model was found as feasible in Nationwide Corporate ERP consulting market in the United States, Canada, Mexico and internationally

11.   Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representatives pick up the phone in Naperville and St. Joseph, MI call center).  help@efaru.com  We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico, Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed)

# # #

Andrew Karasev is Great Plains Certified Master, MVP, help@efaru.com 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project http://www.efaru.com and founder of Alba Spectrum information space