Data Conversion from Old Great Plains to Dynamics AX

Likely in the past you did strategic decision to implement high end ERP system and walk away from Great Plains Dynamics.  And when you finally decided on Axapta Dynamics AX your Great Plains version is really old and it is hosted in Pervasive SQL 2000 and now you are facing the challenge to export historical documents and master records from there then cleanse the data and prepare it for moving into AX.  Let’s review the steps in this process one-by-one:

1. Connection to Btrieve/Pervasive SQL database.  Here you need so-called Data Definition Files with DDF extension.  To produce these files you drag and drop GenDDF.set file and drop it on Dynamics.exe in the directory where your GP user application is installed.  If you do not see GenDDF.set file then you should add this feature.  Use your original installation CD.  If you already have Pervasive SQL Control Center installed then you are in good shape and Btrieve driver is installed.  If you are on old Btrieve you should load Pervasive SQL ODBC driver from CD#2.  You need two parts: server side and client side Pervasive SQL driver.  Please see driver documentation for details on how to create Pervasive SQL 2000 ODBC DSN.  One note here before we continue.  If you do not want to purchase newest version of Pervasive SQL and use your old installation CD then you should do it on 32 bit Windows as Pervasive SQL 2000 was not obviously compatible with 64 computing which was not yet known in the time

2. Decision on the data connection and export tool.  Very popular way is to use Microsoft Access where you are creating linked table.  Here you need to place your DDF files (Fields.ddf, Files.ddf and Indexes.ddf) into your company folder on the server.  You can find out where your Dynamics super folder is locate by reading Dex.ini file in the client program installation directory.  In MS Access select linked table, then ODBC and all the tables from your company database should show up as available for linking.  Select all and create linked tables.  Some of them produce error in linking.  Simply disregard these errors as tables probably do not physically exist and just described in DDF to be created if required.  MS Access is great tool however it is limited to 2GB.  If you have huge volume of historical invoices and purchase orders the limit might be show stopper.  If you plan to export millions of rows we recommend you either do it directly in Pervasive SQL Control Center or deploy MS SQL Server Linked Server or Open Row Set command.  With Pervasive Control Center we were able to export the tables with about two millions records in a matter of twenty minutes and we believe this is probably the way to deal with huge number of historical documents.  SQL Linked Server should also do the job but in SQL 2005 and 2008 Microsoft seems to emphasize SSIS (SQL Server Integration Services) and we had issues with Linked Server especially on 64 bit platform

3. Great Plains Tables Structure.  It is not friendly to human intuition as Great Plains was designed to fit various DB platforms back in 1990th and coding schemas were popular in UNIX and Solaris worlds.  However there is user friendly interface where tables are described.  Login GP user interface and follow the path: Tools -> Resource Descriptions -> Tables.  Here in Product select Great Plains and switch series to Sales.  Change View by to table physical name and scroll down to locate SOP30200 Sales Transaction History and SOP30300 Sales Transaction Amounts History.  These two tables have historical Sales Invoice, Return, Back Order, Sales Order, Quotation header and lines and are the most popular in historical data migration exercises.  If you got so far we believe that you should be good to continue your tables identification on your own

4. I do not want to deal with Pervasive SQL DDF files creation and ODBC connection hurdles.  Is there any other way to export tables from GP?  Yes, in Report Writer you can create custom report and base it on the table that you would like to export.  Simply place the fields you need into the report body and print it into the text file.  At this point you should be able to open and cleanse it in Excel

5. Great Plains DOS versions 9.5, 9.2 and earlier.  This is not Dynamics however it also is based in Btrieve and requires DDF files to establish connection.  In earlier 2000th GPA was recommended to be redeployed on Pervasive SQL 2000 and we still have some number of active customers on Great Plains DOS.  You can move and redeploy it on Windows 2008 Server 32 bit version and such all-covering Windows as Microsoft Small Business Server.  32 bit computing is the key here as old Pervasive SQL 2000 is not compatible with Windows 64.  Data export is complex as one file often hosts several relative tables and when you do select statement to the table all the other tables produce garbage looking rows

6. Preparing Excel Document for Dynamics AX Excel Import.  If you got so far this part should be a piece of cake.  The expectation should be set here to the possibility to have several iterations where the process of importing is repeated in test environment

7. 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)