Wednesday, August 20, 2014

The difficulties of synchronous integrations with Dynamics GP

By Steve Endow

Let's say that you have a custom "operational" system that runs much of your business.  Let's say it's a transactional system that is specific to your industry--suppose it's an Auto Insurance Policy management system.

So this system manages all of the crazy rules and regulations and requirements for quoting and issuing and managing auto insurance in multiple states, and it also issues invoices.  You have hundreds of insurance agents that sell your insurance in multiple states, so the agents are your customers, and you may invoice them for hundreds or thousands of customer policies per month.

When you issue invoices from the auto insurance system, you want the invoices automatically created in Dynamics GP.  So after you prepare the invoices for the agent in Atlanta, you want to be able to click a button in your operational system that sends all of his invoices to GP.

Your operational system was developed using a non-Microsoft development platform, so you can't directly invoke eConnect, and it can't communicate via web services.  But it can call a COM component.  So great, you create a COM visible .NET assembly that reads data from a database and imports transactions into Dynamics GP via eConnect.  Works great, and you are happy!  You click a button and an invoice magically appears in GP, instantly!  Yay!

This sounds good, except when you use it with real data.  What if that agent in Atlanta has 2,000 invoices?  The import into Dynamics GP will certainly take more than 30 seconds, and could take a few minutes.  So if you click on that magic button, your operational system may hang for several minutes as it waits for a response from the COM component.  Not a good experience for users.

And what if there is an error with 10 invoices and they fail to import into Dynamics GP?  Now you have 1,990 invoices in GP, but you are missing 10.  How do you handle that?  Do you then route the user to some additional window where they can review and potentially correct the issues?  Do you add some additional code to re-import the 10 that failed?  What if the error is due to a missing GL account or Class ID or Item in GP that will take some time to setup?  Is your operational system really the place for users to deal with the integration issues directly?

What if GP is offline or there is an issue that prevents the import from running?  You then get an error when you click on the magic button.

In short, it can get messy.

This scenario is what I call a "synchronous" system integration--sometimes people call it a "real time" integration.  The operational system is attempting to 'directly' import data into Dynamics GP and is waiting for the import to finish so that it can receive a response.  For some simple situations, this might work well--say creating customers or vendors or inventory items.  But for transaction imports, and especially high volume imports, it can become problematic.

I therefore recommend "asynchronous" system integrations.  Usually, this involves some type of data repository that acts as a queue.  The operational system might export a CSV file every hour, or perhaps a CSV file for each batch of invoices, or a file for each customer that needs to be invoiced.  The operational system could also write records to a staging table in SQL Server.

A separate process is then run to import the operational data into Dynamics GP.  It can be scheduled using Task Scheduler, or can be triggered via command line, or it could be user initiated through an EXE or GP AddIn.

This approach decouples the two systems and allows them to operate completely independently, with the integration being a discrete system and process that allows the operational system to interface with Dynamics GP.

One downside to asynchronous integrations is that they aren't "real time".  If you schedule the integration to run every 5 minutes, there is a 5 minute delay between the invoices being created in the operational system and them being imported into GP.  In my experience, there are very few customers that need integrations to run more frequently than every few minutes. (A common exception is with inventory--some customers need receipts and transfers processed immediately to complete a subsequent transaction with that inventory)  And even if you think you need something imported into GP every 30 seconds, don't forget that you have to post transaction batches in GP--so that has to be done manually or using a batch posting tool like Post Master Enterprise, another process which takes some time depending on the batch size.  So "real time" is a misnomer at best.

Yes, you still have to deal with errors and reprocessing failed transactions with asynchronous integrations, but they no longer hold up your operational system, and you have to deal with those errors in either case.

I recently when down this path with a customer, but they had to experience it themselves to understand the complications and caveats of the synchronous approach.  I am now providing a new version of the integration that will allow them to trigger the GP import, which will run asynchronously.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

No comments: