Great Plains Data Export Tools And Recommendations

in Sql-server

If you are working with ERP version, where your data resides in Microsoft SQL Server, data export or table access in reporting is out of the question. There are a lot of folks, who are still on older versions of Great Plains, including Dynamics Select on Pervasive SQL (often referred as Btrieve, which was original vendor) or Ctree. And also we would like to discuss here Great Plains DOS version, referred as GPA for DOS. In exotic cases we see limited number of customers using GPA on Mac platform, or running its archaic Windows interface. Lets sort out the methodic, that you may try, depending on what is your situation. Feel free to skip sections, describing something that you do not have and go directly to the paragraph with your solution:

1.Btrieve data access overview and GP historical versions compatibility. This DB platform was traditionally considered as cheaper, comparing to high end SQL (such as Oracle, Microsoft SQL Server, DB2, Progress, Sybase, etc.). It has some attributes of client server architecture (Btrieve Server and Requester as client application or driver). However the user is controlling the data selection, update and record creation via her/his security rights on the file management/operating system level. Great Plains Software had versions on Btrieve platform. These were Great Plains Dynamics 3.2, 4.0, 5.0, 5.5, 6.0, 7.0 and 7.5; plus GPA for DOS, Windows: 9.5, 9.2, and earlier

2.Ctree. This DB platform was considered as even more budget one, comparing to Pervasive SQL (see paragraph above). If you are ERP consultant with ten plus years of experience, you probably remember yourself back in 1990th installing GP on your laptop on Ctree for Sales presentations and features demos. It is less likely to find customers, who are still in production with Corporate ERP on Ctree. Historically there was popular trend around 1995, when Apple Computer introduced its PowerPC version, where Dynamics was available for Mac on Ctree/Faircom (later on customers preferred to switch to Windows Server on Btrieve, due to slow transaction processing)

3.Btrieve/Pervasive Data Access Technology. It is typically via ODBC connection (System DSN) where the connection itself requires so-called Data Definition Files or DDF (File, Field and Index DDF files, where Index might be optional, but helpful in achieving top performance). The confuse with ODBC connection to Pervasive SQL database is often related to the fact, that you are not sure (have no idea or experience) on how to obtain/create these files. Keep reading

4.DDF files of Great Plains Dynamics (7.5, 7.0, 6.0, 5.5, 5.0 and earlier). There is DDF.Set file in your user workstation directory (typically C:\Dynamics). Simply catch it with the computer mice, drag and drop it on Dynamics.exe file to initiate DDF files creation. Please note, that you can create these ODBC required files not only for the core dictionary (such as Dynamics.dic), but also for add-ons. Popular third party ISV modules were Intellisol Project Accounting and Advanced Purchase Order Processing, Icontrol Manufacturing, Fixed Assets. DDF files could be created as well for your in-house (or uniquely programmed for you by Dexterity developer) coded modules

5.Ctree ODBC driver. This is straight forward step. Simply install the ODBC driver from Dynamics CD

6.Crystal Reports design for archaic versions of GP. If your report is simple by its requirements, you can follow report wizard to link the tables. For example, if you need just to list customers and their addresses, you can do it via Wizard. However if in Crystal, SSRS, MS Access reporting you need to do cross modules report design, we recommend you to deploy abstract SQL View or Stored procedure, which could be created via MS SQL Server Linked Server construction (where linked server has ODBC nature)

7.Great Plains Accounting for DOS ODBC reporting and data extraction. Through the several years of our post GPA support (when Microsoft Business Solutions expired Great Plains DOS support in earlier 2000th) consulting practice we realized that these customers are typically not computer technology advanced (there IT department might be vacant or have only one guru, who has to cover all the aspects of IT and Networking support). We could give your DDF files to enable data access to your legacy Corporate ERP, however we prefer recommend you to upload your zipped GPS folder (where your accounting data resides) we will convert required tables into Excel, CSV, Access or another format. If you are very accurate IT manager, you may have original floppies still available. In this case you can read ODBC manual and follow the instructions to create DDF files and get connection on your own

8.Location Translation Issue. This might happen to you, when you are trying to redeploy Dynamics on another file server. The issue is associated with the fact, that Btrieve or Ctree have to record initial installation path to your database (UNC or hardcoded physical path, but in the majority of the cases it is UNC path). We have several older articles dedicated to the location transaction problem, please read those (as we have to follow article genre space restrictions)

9.Data Repair for the old releases. Our preferred tool is Microsoft Access, where you could connect to Linked Tables via ODBC DSN

10.FRx Financial Reporter. This tool was around since earlier 1990th. There are versions of FRx with data connectors to General Ledger for Btrieve and Ctree. Currently as we are writing these lines in April of 2011, Microsoft Business Solutions recommends switch to Dynamics ERP Management Reporter from FRx

11.Recommendations to the customers on the currently supported versions: 2010/11.0, 10.0. Please, review table, form and field structure by opening Dynamics GP -> Resource Description -> Tables/Fields/Forms. Additional info could be gathered in SDK

12.Upgrade Option. Every GP version has the opportunity to be upgraded up to the current release 2010/11.0. There might be one or several subjects requiring decision making with your Great Plains Consultant. For example, Intellisol went out of business, you might need to pick upgrade path (via migration from Pervasive to SQL Server and then Service pack dependent update there)

13.Notes on Small Business Financials (9.0, 8.0, 7.5, 7.0 known as Small Business manager). The Corporate ERP product line was terminated with the recommendation to upgrade to Dynamics GP Business Ready. SBF was available on MSDE and SQL Server Standard

14.Migrating from GP to SAP Business One, Axapta, Navision, Solomon, Oracle Financials, PeopleSoft, QuickBooks, MYOB, Peach Tree. In one of those situations you should hire GP technical consultant to work on migrating your historical Accounting, ERP and MRP data to the format recommended by your new ERP solution

15.Support Options. If you are located in USA, Canada, European Union, China, Korea, India, Japan, Burma, Pakistan, Iran, Saudi Arabia, Egypt, Brazil, Latin America, Russia we are happy to provide remote support via Gotomeeting, Skype and Web conferences

16.Sure Step. This is where you should consider project management automation for Microsoft Dynamics projects with AX, CRM, GP, NAV, SL, RMS (the family is growing and you should let the options to accommodate the future)

17.If you feel that your concerns are not addressed or your question is not answered, please feel free to contact our office. Support domestically in the USA, Canada, Mexico and internationally. This option is possible via Web Sessions, Skype or Phone conferences and direct visits onsite (in the case of the large scale project). Our consulting team speaks English, Chinese, Portuguese, Spanish, Russian, Filipino. Feel free to call us 1-866-304-3265, 1-269-605-4904, or email
Author Box
Andrew Karasev has 1 articles online

Andrew Karasev is Great Plains Certified Master, MVP, 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project and founder of Alba Spectrum information space

Add New Comment

Great Plains Data Export Tools And Recommendations

Log in or Create Account to post a comment.
Security Code: Captcha Image Change Image
This article was published on 2011/03/31