Microsoft Dynamics GP Integration Methods: IM, eConnect, Dexterity

Plus, we will try to address options for older versions of Great Plains Dynamics on Pervasive SQL 2000/Btrieve and Ctree.  Please, note that Great Plains Accounting for DOS, Windows and Mac is technically predecessor of Great Plains Dynamics for Windows and Macintosh, however GPA business logic and interface are different from Dynamics core functionality: GL, PM, RM, SOP, POP, Inventory Management.  GPA is out of scope for this article (where we would like to make several observations)

1.       Integration Manager.  We understand that most of Great Plains Technical Consultants know how to setup simple integration, where the source are text tab or comma delimited files, including building document header by grouping text lines (by document date for example: General Ledger Transaction, Sales Order Processing Invoice, etc.).  However, we saw pretty tough situations, where Dynamics GP integration consultants had hard time in complex data conversions, where transactions and master records are in SQL tables or ODBC compliant databases: Oracle, MS Access, Pervasive SQL/Btrieve, Ctree, MySQL, or when they are exported into very complex text files (where you have to weed out formatting lines, as reports are exported in human readable format, which is not something that text integration accepts without data massage).  Good advice in our opinion in such scenarios is to import these text files into MS Excel, weed out formatting lines and then import the data into staging MS SQL Server tables.  Then you create SQL View to pull data from staging tables and use it as a base for Advanced ODBC Data Source.  We had cases, where we were able to import up to the whole year of mid-size company GL transactions via that method.  We would like also to share IM ongoing and close to real time integration methodic via Integration Manager.  If you have in-house programmed ecommerce website application, which requires close to real time or batch mode integration to Sales Order Processing (customer orders, invoices and returns) and new customers creation (where transactions are coming from Linux/PHP/MySQL based ecommerce application) - consider either exporting transactions in text file or even establishing ODBC connection from Dynamics GP hosting MS SQL Server to MySQL via Linked Server mechanism, and schedule IM Integration to run every 20 min or few hours via Windows Macro third party add-ons.  We know that for some of you these complex technical passages might be too deep, but our mission here is to let you (as struggling ecommerce programmer) know what is possible with Dynamics GP ecommerce integration.  And finally, the last observation, before we move on to eConnect technology, please know that IM for GP version 10.0 is available in two connection blends - traditional OLE Server and eConnect connectors.  eConnect connectors are faster by their technology architecture and technically you may be achieving the same results as if you are programming eConnect directly from your ecommerce web application, where the advantage of Integration Manager is apparent - it is user friendly in integration design and doesn't require programming (except simple VBA scripts, or data translation tables, which could be composed in MS Excel)

2.       eConnect Integration Programming.  eConnect was introduced for Dynamics GP version 7.5 and 8.0 as the Software Development Kit, initially dedicated to eCommerce software developers.  eConnect was under certain evolution for Dynamics GP versions 9.0 and 10.0, where it was adopted as integration technical platform strategy (and it was announced by Microsoft Business Solutions that eConnect should replace earlier integration platforms: Integration Manager, Dexterity - as middle tier).  And now as we are writing these lines (October 2009), eConnect is triumphing among Dynamics GP integration low level technologies and connectors.  eConnect technology is in fact simple - it is the collection of SQL Server encrypted stored procedures, replicating Great Plains Dexterity Dynamics GP business logic (often in Object Oriented Programming style, and this is good improvement to Dex, which is not technically Object Oriented Programming compliant with some exceptions).  In our opinion eConnect came to its maturity as reliable Dynamics GP ecommerce and generic integration Software Development Kit with Great Plains Dynamics GP version 10.0 and probably 9.0.  If you got certifications and experience as Microsoft  .Net VB or C# programmer, you should find very useful code samples and prototype Microsoft Visual Studio eConnect projects: Dynamics GP Sales Order import, Customer import and update (including addresses) to give you start up ideas

3.       Dexterity integrations.  Dex is capable to do the ultimate and the most complex programming surgery in Dynamics GP system environment.  If Integration Manager and even eConnect don't do the job or incapable to do the job (and this is possible as these tools have natural limitations).  However, please recognize the challenges of Dexterity.   In Dynamics GP realm there are no limits to Microsoft Dexterity customizations, and if you contact with Great Plains Dexterity programmers (who have access to Dexterity Source Code and have experience in Dex source code programming), please go ahead to evaluate these channels.  The only advise we would like to feature here is fact that Dexterity requires too long learning curve for newcomers, and it is not recommended for small or mid-size companies IT department to do Dexterity custom programming inhouse

4.       Direct SQL Data Feeding.  If you are experienced Microsoft SQL DBA, please review Dynamics GP Tables Structure Tools->Resource Description->Tables (or install Dynamics GP SDK from DC#2, where tables structure is formally documented).  We would like to make comments and recommendations, before you try direct SQL data feed via SQL Stored Procedures.  Please, consider the fact that eConnect is already doing this job via its methods and Stored Procedures.  Also take into consideration the fact that Dynamics GP business logic and data flow is quite complex and it is unlikely that you can do imitate this logic in couple of day, couple of week or even in couple of months.  If you have unlimited IT budget, then please disregard and go all the way through until you consider yourself experienced eConnect programmer with comfort to send records directly to Dynamics GP tables: GL10000, GL10001, SOP10100, SOP10200, SOP30200, SOP30300,etc.

5.       Great Plains Integration options for Old GP versions: 8.0, 7.5, 7.0, 6.0, 5.5, 5.0, 4.0 and Great Plains Dynamics on Pervasive SQL Server 2000/Btrieve and Ctree.  If you are on archaic version, it is unlikely that you can get support directly from Microsoft Business Solutions Dynamics GP subdivision (as you are likely not current in MBS annual enhancement program)

6.       Great Plains Accounting for DOS, Windows or Mac integration options.  GPA has its own integration module, and if this is your Corporate ERP, please contact us separately,  we have access to GPA knowledge base, inherited from Great Plains Software - if your case is known and documented (plus if you can recognize your problem in GPA after 10+ years of GPA and Dynamics GP Consulting), we are uniquely positioned to support Great Plains Accounting for DOS, Windows and Mac: 9.5, 9.2, 9.1 or earlier versions USA and Canada nationwide and internationally most likely Central, South America, Mexico, Caribbean, Pacific, Malaysia, Indonesia, Singapore, Saudi Arabia, Kuwait, Pakistan, Casablanca, Agadir,  India, Bangladesh)

7.       Unicode characters integration notes.  Great Plains Dexterity doesn't support hieroglyphs: Chinese, Japanese, Korean, Mongolian, Thai.  All we can do for you is enable hieroglyphs in Crystal Reports or Microsoft SQL Server Reporting Services (often abbreviated as SSRS)

8.       Dynamics GP Great Plains in Russian Federation, Kazakhstan, Uzbekistan, Azerbaijan, Poland, Czech republic, Latvia, Estonia, Lithuania, Romania, Slovakia, Georgia, Armenia.  Great Plains Software tried to market and localize Great Plains (version 4.0 and 5.0) in Common Wealth of Independent States (most of the former Soviet Union territory, except East Baltic States) .  When Great Plains Software was in its order acquired by Microsoft Business Solutions, MS recommended Navision as the Corporate ERP of choice back in earlier 2000th, and later on MBS localized Axapta Microsoft Dynamics AX for most of Western, Central and Eastern European Corporate ERP markets.  To avoid further complexities, we recommend you to chose SAP Business One in former CIS, Baltic States and in Arabic World as good local branch Corporate ERP, which has simple integration tools and options to your Dynamics GP ERP in the headquarters

9.       How to get help?  Please, feel free to call us: 1-866-528-0577, outside of USA: 1-630-961-5918 or email us help@albaspectrum.com  We are very technical and real Dynamics GP Dexterity, SAP Business One SDK and Microsoft CRM programming gurus.  We have Great Plains Software Development Factory and could support unlimited Dynamics GP Customization and Programming needs.  Plus we speak English, Chinese, Spanish, Portuguese, Arabic, Russian, and not only as native speaking sales folks, but as real technical consultants.  If you prefer skype: albaspectrum