When you are switching from one software vendor to another one chances are high that you have to do data export, cleansing and migration to newly selected accounting application. SAP B1 is hosted in Microsoft SQL Server and data import is typically done in Data Transfer Workbench by filling CSV templates each per logical table.
In our opinion real challenge is in exporting legacy accounting transactions especially when they are hosted in proprietary database platform or maybe in Btrieve with complex data organization. One of the most puzzling legacy system in the sense of ODBC connection and data export is Great Plains Accounting for DOS, Windows or Macintosh which was popular in late 1990th and still in production in versions 9.5, 9.2, and earlier. In this small publication we would like to discuss data connection and export steps:
1. DDF files for ODBC connection via MS Exchange or directly via Pervasive SQL Control Center. In our opinion Pervasive Control Center is better as the fact that GPA is sharing the same Btrieve file to host several relative tables requires data cleansing in SQL Select statement. As you might be aware that Pervasive Software is successor of Btrieve. And for Btrieve DB platform ODBC connection requires so-called ‘Data Definition Files’ or DDF in abbreviation: File.ddf, Field.ddf and Index.ddf where the last one is optional but recommended as it improves query performance. There are instructions in ODBC related manuals available for review on GPA installation CD. Instructions require you to have original floppies which were shipped to you back in later 1990th but customers often don’t have them anymore
2. Historical data export. We usually try to discourage customers from historical documents conversion as this might lead to millions and plus sales or purchasing invoices and their lines to be exported, cleansed and imported to Business One. Instead we suggest to consider an option to keep Great Plains Accounting running on Windows XP workstation or possible on one of the second priority servers for data inquiry only in case if old-good-days customer calls and requests the status or maybe in case of state or federal audit. If you think that historical records migration is absolutely required then be prepared to cleanse data in complex SQL Select statements with where clauses to separate Invoice header, lines, tax and addresses information – all these tables are hosted in the same file: ORDHST.DAT located in OE folder on the server
3. Appealing to consulting help. One of the first ideas might be the one to request technical consultant to export historical data into Excel. This idea is great up to the point where you have to export hundred thousands or millions of documents and lines where Excel simply is not geared to process such amount of rows. In the case of massive migration we recommend exporting into text file and then importing it into custom MS SQL Server table via SSIS data import wizard
4. Alternatives if you feel that data migration is too challenging. Try to think about staying on GP platform and migrate from Great Plains Accounting all the way to Microsoft Dynamics GP version 2010 which is current version as we are writing these lines in November 2011. Migration tool exists and you should be able to get Dynamics GP software licenses with reasonable discount (in our experience around 40% but situation is in hand of Microsoft Business Solutions so we recommend you to talk to your GP reseller). Second alternative is to keep GPA for data inquiry and plan on minimal data conversion project in SAP BO
5. Versions reference. SAP Business One was available in the United States in the following versions: 2005A, 2007A, 8.8 and currently in 8.81. Please note that version 8.8X merged A and B releases into one and there is no need to plan for two MS SQL Servers for A and B. As we already mentioned GPA might be still popular on versions 9.5, 9.2, 9.1 or earlier. There are also customers on so-called ‘Great Plains Dynamics’ versions 7.5, 7.0, 6.0, 5.5, 5.0, 4.0 on Pervasive SQL Server 2000, Ctree or Btrieve. In exotic cases you can also find Dynamics version 4.0 on Ctree/Fairom server on Mac platform
6. Notes on the international Great Plains releases. Prior to being acquired by Microsoft Great Plains Software was expanding internationally with Dynamics and reasonable success was achieved in such countries and regions as Russia, Poland, Eastern Europe, France, Germany, Spanish speaking Latin America, Arabic world and all English speaking countries. With acquisition of Navision Software Microsoft Business Solutions restricted Great Plains to USA, Canada, Australia and the rest of English speaking world where Navision and later on Axapta were internationalized. Having this historical excurse presented we expect high volume of Great Plains accounting flavors to be phased out and converted to SAP B1, Dynamics AX, QuickBooks, Peachtree, MYOB, NetSuite and other accounting systems
7. Last flint or Dynamics GP formerly known as Great Plains Dynamics internationalization. Microsoft Business Solutions was in thinking about mode back in 2004 through 2006. Our company was invited to localize Great Plains version 7.5 for Brazilian ERP market with reasonable support. Around 2005 MBS were about to change its mind to recommend Navision instead in Brazil and internationally including Russian Federation. And soon it again changed its mind in order to recommend Axapta in those days already rebranded as Dynamics AX. Please note that SAP Business One is not localized in Arabic speaking countries
8. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representative pick up the phone in St. Joseph, MI call center). help@efaru.com. We have local presence in Chicagoland, Southern California, South West Michigan and Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico and Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian and Chinese. We feature our expertise is in International Business. We provide second opinion in SB1 data migration, customization and reporting
Dexterity Customization for Dynamics GP Evaluation Level Paper
When you are developer it is always a good idea to read technical manuals. But if you was just assigned to the IT team to decide if Dexterity is the right tool to customize your ERP application then first you need something which is in style of ‘easy reading papers’ or FAQ
Planning Dynamics GP Customization in Large Corporation
If you are reading this page then chances are high that you were not able to find ISV add-on and need customization project. Let’s talk about planning, quality assurance and future event such as version updates.
Dynamics GP Invoice Logo Attributed to Specific Company or Crossing the Borders of Three SOP Forms
Initial Great Plains Dynamics architecture had three SOP Invoice forms: Long, Short and Blank. Modern GP is popular in scenarios where you have more than three companies under one business entity umbrella
