Dynamics GP Integration Manager Data Conversion and Migration Cases

However, as we were speaking with numerous Great Plains customers and consultants, IM is often considered as something, limited to working with just text files.  Through our Great Plains consulting practice we were able to help customers to do such state of the arts as e-commerce quasi real time integration, bring over several years of GL transactions in in initial data migration, alter integration logic for Collection agency (where customers are reversed in their nature and could be viewed as vendors), deploy IM in EDI data channel and other titanic integration scenarios.  Let’s go down to the paragraphs:
1. Shifting from Text only Integrations paradigm.  Imagine the situation, where you have no control over what is coming from your legacy system and you have report in pretty complex form in the text file.  The only thing you know for sure is the fact that each data line (which needs to be integrated) has the same pattern (begins in the same position and has several fields, separated by fixed number of empty space characters).  This puzzle might seems as really complex and not resolvable in such end user friendly tool, as Dynamics GP Integration Manager.  Well, let’s try the following technique.  Create Advanced ODBC data source, base your ODBC query on Microsoft Text driver (there are few known issues about the driver, but it should do the job, if you got specific problem, we published resolutions in previous papers) and then create SQL Select statement, where you are weeding out irrelevant lines (coming typically from report header, footer and space lines in the body).  To give you more flexibility – you can even use union SQL construction, especially when your report body lines have several repetitive patterns.  If you try these techniques, you will see how powerful is Integration Manager even when you are working with simple text file (coma or tab delimited, or even you can specify records delimiting character – this option is available in IM)
2. And now to SQL compliant data source.  As we demonstrated in above paragraph, even report-style text file could be parsed in SQL queries.  Let’s go beyond the concept of text file.  Microsoft Dynamics GP is obviously restricted to Microsoft Windows platform, including MS SQL Server.  However, ODBC concept allows you to cross Windows borders and pull data from such non-Microsoft platforms as Linux/Unix, PHP, MySQL, Oracle, Pervasive SQL/Btrieve.  In Microsoft SQL Server Management Studio you can create linked server to the mentioned databases via ODBC connection.  If you are done with linked server, next step is to create cross-platform SQL view (sometimes OPENROWSET construction is required).  To give you more flexibility, IM can even mark row as integrated in event driven VBA scripts – this is especially helpful in EDI (Electronic Document Interchange) scenarios
3. Integration Manager and challenging data conversions.  Often you may see the situation, where customer tells you – please convert “everything” from my legacy Accounting (MYOB, Quickbooks, PeachTree, GPA for DOS, SAP Business One, and sometimes in the case of downsizing Oracle Financials, PeopleSoft, Accpac, etc.).  As general wisdom it is good to say – this is not recommended way for the new Corporate ERP system implementation (common sense recommends you to convert only master records, such as GL Accounts, Customers, Vendors, Employees, Addresses, Inventory Items).  These recommendations should be honored, but when customer says No – I have no way to do it like that.  Here are your options with Integration Manager.  Bring all GL transactions into monthly batches, have your customer review Batch Edit list and when they are satisfied post the batches (of course you should have fiscal periods open for posting).  If Sales and Purchasing history is also required, be sure to disable posting to GL (in Posting settings) integrate historical customers and vendors invoices and post them just in the modules (without posting to GL)
4. Quasi Real Time integrations.  Normally Great Plains Consultant recommends you to initiate Integration Manager integration manually by opening IM user interface and click on run integration.  In modern IT infrastructure you may need more control over integration timing.  Microsoft Business Solutions has IM scheduling module, where you can call your integration every exact number of hours or even minutes.  In our opinion, you may deploy shareware or even freeware software available on the market to schedule IM integration via DOS command with parameters.  By the way – this strategy was successful with ecommerce shopping cart quasi real time integration via IM integration call
5. IM technology excurse.  eConnect as new technology was introduced back in 2003 or earlier 2004.  The idea was simple – to open Great Plains objects and transactions for eCommerce programmer.  Few years later eConnect  (especially in the time of Microsoft Project Green) was sort of recommended for non Microsoft.Net compliant technologies (such as Modifier with VBA and Dexterity, and even Integration Manager VBA scripts) to be phased out.  As we see it right now after president Bush and even current Obama slow down – the stick to traditional Corporate ERP technology might be crucial.  Our future stakes are in XML technology and integration possibilities
6. Remote connection and web session obsession.  This is noticeable trend in the last years.  We are not really the drivers in this trend, we simply follow the public offer to see if web sessions are good for our customers.  Some of them prefer to isolate their computer network from the outside World, meaning internet (all the computer viruses  malware, service pack issues could be resolved by that approach).  We respect that kind of the decision.  However in that scenario we have to drive onsite to support the customer
7. OK, now as we could record too much public protest in Washington, Chicago, Los Angeles, Detroit, Las Vegas, San Francisco, Denver, Phoenix, Miami, Houston, Seattle, Reno.  Please, feel free to call us 1-866-528-0577, or email us help@albaspectrum.com 
8. As the last flint.  As I am trying to register Dynamics GP implementation internationally.  At this point, October 2010, MBS sends us a signal that GP is only localized for English speaking countries, plus for Spanish speaking Latin America. Please, be advised, that Dynamics GP is not localized for such markets as Brazil, China, Russia, Western Europe.  According to David Musgrave, one of the Microsoft Dynamics GP future architects, Great Plains is not really a candidate in the list to support Unicode characters (Great Plains Dexterity was introduced at the time, when English ASCII based alphabet was a dominant standard and such exotic alphabets, as Hieroglyph based Chinese, Japanese, Korean were not really in radars)