Dynamics GP Integration Manager Advanced ODBC Notes

Link is based on Document number.  However, what if you have only one file, where document number is present on each line?  Or if you have to deal with text file, which is coming from legacy billing system in the form of text report, readable by human, but it has paragraphs, page numbers, headers and lines (and all these are coming from Unix box, which you cannot change)?  How about this scenario – you have Excel table, where you have several items listed on the same row?  What if data is sitting somewhere in Oracle Database and it is so huge, that it might be impractical to pull it out into the text file – instead you need to pull it directly from Oracle?  In those or similar situations you should consider deploying integration based on Advanced ODBC query.  Let’s try to analyze examples given above and how they should be dealt with:

1. How to build the integration from one source file, where line has document number and the item number, description, quantity and price?  This example is present in IM sample integrations, review GL Entry with one source file.  You have to group just one column document number and this will give you header table query.  In certain situations you do not have document number, but probably have some kind of grouping column (transaction date, if you build daily invoices, if you have multiple customers per day, than add customer ID into grouping as well)

2. CSV or Excel file, where you have several items with quantities on the same row.  Here you are jumping into advanced ODBC queries.  In advanced ODBC query you can use such SQL constructions as Union.  You can unionize several Select statements, where first one gets item number and quantity from the first and second columns, the second Select gets item and quantity from the third and fourth columns and so on

3. Text report coming from the Unix box in the form of readable by human Invoice, Purchase Order document.  Is it really possible to through something like that directly to the Great Plains Integration Manager.  Somebody from GP consulting community might say – No way!  However, lets cool down and speculate a little bit about SQL and document parsing.  How do you know which text lines you would like to extract?  All of them are probably starting from the same position, plus they have several columns each of them also begins on the fixed position of the document.  And now, probably all the headers, space lines and page numbers have empty character on the same first position, where you item lines have valid character.  So, the SQL statement is probably not very complex, you simply apply criteria Where first character of the field number one is not like space character…  And by the way, the same result of weeding out space lines, headers and other non-valid lines – could be achieved in VBA scripting in Before Document event – but this is something you can try on your own if you are comfortable in VBA scripting

4. Integration  Manager as the tool to read EDI formatted text file.  In EDI (Electronic Document Interchange format) you may have more challenge, however IM gives you the power to specify dividing character (it might be Coma, Tab or you name the character, required by EDI format).  But even if your EDI protocol is really challenging to parse, please review Select statement with precise field formatting

5. Reading data directly from SQL based ODBC compliant source.  Whatever it is – Oracle, MySQL/PHP, Microsoft Access, SQL Server, Pervasive SQL/Btrieve, Ctree, you name it, assuming that there is ODBC driver to get connection from Microsoft Windows operating system (as Integration Manager is available on Microsoft operating system platform only) to your database.  One of the approaches we like to suggest is this.  Consider building Linked Server construction in MS SQL Server Management Studio 2008, 2005 (or Enterprise Manager if you are on SQL 2000).  Linked Server will provide connection via ODBC driver (usually System DSN) to your third party database.  Second step is this – you build cross-platform SQL View.  And then you build your Advanced ODBC query based on the view, just created in Microsoft SQL Server.  Such views might be so powerful that you may try pretty deep historical Sales and Purchasing data conversion, by importing huge Sales butch of documents (for, say one historical month) and then post them in Dynamics GP user workstation (should probably consider uncheck posting to and through General Ledger, as you will do either GL initial balances or consolidated monthly transactions for historical GL data migration)

6. VBA Event Handling Scripts.  If you think, that you are not so comfortable to code complex SQL Views (with conditions and case statements, stuff like that) – you can do the same in VBA event handlers.  In our opinion, SQL is something where technology behind is capitalizing on aggregation nature and due to this feature – SQL is a way faster, comparing to cursor driven logic, which you have when doing VBA scripting (SQL may still use aggregation, but each document should stop and wait until VBA script in event handler does its job).  So, if you plan to bring historical data from your legacy accounting system (Peach Tree, QuickBooks, MYOB, SAP Business One, Accpac, etc.), where the volume of data is huge and you may need to repeat test conversions numerous time, prior to the final conversion – we recommend you to do data massage in SQL Views directly, versus VBA event handlers

7. Advanced ODBC integration case studies.  One of them would be eCommerce quasi real time integration, where you read ecommerce shopping cart transactions every, say ten minutes and bring them over to Dynamics GP through Integration Manager SOP document integration.  IM could be scheduled to fire integration every ten minutes via Windows macro routine and scheduled tasks.  If you use new technology – eConnect connectors for the integration – these integrations do not even require Dynamics GP user workstation to be always on (opposite to traditional IM technology, where Great Plains workstation was used as OLE Server and this is why it had to be running, consuming one precious Great Plains concurrent user license)

8. eConnect or how you can break through old Integration Manager performance hurdles.  In Dynamics GP version 10.0 (and of course current version 2010/11.0) in Integration Manager you can now deploy integration targets, based on eConnect connectors.  Let’s try to come through some excurse into eConnect technology itself.  eConnect was associated with Microsoft Project Green (later transformed into Microsoft Dynamics project), where the idea was to deemphasize such traditional Corporate ERP shells, such as Great Plains Dexterity, coded in C programming language, and instead rewrite business logic in SQL Stored Procedures.  SQL Stored Procedure should run faster, comparing to Dexterity cursor, where computation first happens in Dexterity Sanscript code, then it translates into C++ code and then finally into Microsoft Assembler to transfer instructions to your AMD or Intel processor .  New style eConnect connector calls SQL stored procedures to perform the integration, comparing to old technology, where first you have to initiate Dynamics GP OLE Server, then open Great Plains form (on OLE level), fill in each field, then submit it for validation and if everything is fine, save the record through the form Save button click

9. If we don’t have Integration Manager module license, is it OK just to do direct SQL Insert statement?  In this situation you should understand your options and danger zone.  Great Plains Dynamics and its successor Microsoft Dynamics GP allow you to do data repair and insertion directly into SQL company database.  It is a great power, however, by doing so you may compromise business logic and destroy your Corporate ERP documents.  This is why we believe that purchasing Integration Manager software licenses will be better option

10. If we are on old version of Great Plains and we do have Integration Manager license, what is possible and what is not, comparing to newest version of Dynamics GP 2010? Well, Integration Manager was introduced with one of the first releases of Great Plains Dynamics, where it was based on OLE Server technology.  The only limitation we could mention is probably eConnect data connectors, the rest should be very similar and we do not see the problems to deploy Integration Manager

11. We are upgrading our Great Plains Dynamics (version 5.0, 6.0, 7.0, 7.5, 8.0) to Microsoft Dynamics GP 10 (9.0, 2010/11.0) and we use several Integrations.  Is there upgrade procedure for the Integration Manager?  There is upgrade procedure, it is not recommended for internal upgrade, you should call your Microsoft Dynamics GP Consultant

12. I am programmer and I would like to learn how to program VBA handlers for Integration Manager.  Where should I start?  First of all let us mention that modern VB.Net programming language is a bit different (or maybe better to say a way more different), comparing to old Visual Basic.  VBA (Visual Basic for Applications) scripting is sort of subset of old Visual Basic.  You should look at such technologies as ADO (not the ADO.Net, but traditional one).  VBA scripting is the way to modify such Microsoft applications as Excel, Word, MS Access (where VBA scripting is the tool to animate MS Access Forms and Reports).  There is also VBA programmer manual, available on Dynamics GP DVD/CDs, where you can find examples of VBA event handling codes

13. To request further support, please call us 1-866-528-0577, help@albaspectrum.com We need to discuss your cards in order to recommend you the best solutions, which is not contingent to our preferences.  We serve you USA/Canada nationwide via remote support (web sessions and phone/skype conferences).  Local service is available in Western Michigan, Chicagoland, Southern California (LA, Orange County, San Diego), Houston area of the state of Texas