Tuesday, April 26, 2011

A Tragedy In Integration Land: How to Spot a Bad Dynamics GP Integration

Trivia Question:  What is one of the fastest ways to spot a bad Dynamics GP .NET integration? (answer at the bottom)

I've seen some strange things in my days working with Dynamics GP and developing integrations.  I've seen several crazy GP macro generators that are used to "enter" data rather than using an integration tool.  I've seen .NET projects that are wrappers for the entire eConnect assembly, essentially re-writing all of the eConnect interfaces, for no apparent reason.  I've seen half-baked integrations that have transaction distributions hard-coded, with DR and CR going to the same account!  And I've seen the 1980s vintage integrations that used "technology" (and alot of duct tape) that was so old I didn't even know it existed.  All of these fall into the category of "What were they thinking???".

So when I was told that a client needed help with an existing VB-based integration, I thought "Excellent!", finally a modern .NET Dynamics GP integration.  Surely it will be well designed and use current integration tools.  And then I saw it.

Oh. My. Gosh.  How could I be more wrong?

So apparently back in 2006, the client's former "Gold Certified" Dynamics GP partner "developed" this ".NET" integration between the client's operational system and Dynamics GP.  It's really quite simple:  Import AR invoices and GL journal entries.  Piece of cake, right?

Fortunately, the client has a copy of the source code from their prior partner.  But when I start looking through the Visual Studio files, I don't see any code.  No classes and no modules--just a single VB form.  Hmmmm.

So I grab a copy of the entire solution and open it on my development server.  Sure enough, essentially no .NET code.  But what I do see are a bunch of SQL files that have been included in the project.

20 SQL files,  to be exact, each containing a stored procedure.  Some of these stored procedures are pages long, and horribly complex looking.  Dozens of parameters.  Inserts, updates, deletes.  And oh yes, a very good sign that the developer was out of his league:  Lots, and lots of SQL cursors. 

After staring at pages of SQL stored procedure code in a Visual Studio project, I finally realize what this supposed "developer" at the supposed "Gold Certified" Dynamics GP partner had done.

He had written, from scratch, a 100% SQL based direct-to-table integration to Dynamics GP using 20 stored procedures.

Read that last sentence one more time...

In case that didn't quite sink in, let me state that another way.  Instead of using one of several of the standard, supported, documented, tested, efficient Microsoft or ISV Dynamics GP integration tools or technologies that already exist, this developer apparently decided that the only way to get AR invoices and GL journal entries into Dynamics GP was to write custom stored procedures to shove the data into the SQL tables.  Using lots of cursors no less!

When it finally dawned on me the carnage of code that I was looking at, I was speechless.

The unbelievable tragedy of this nightmare integration is that the client trusted their Dynamics GP partner.  The client trusted the "Gold Certified" status of the Dynamics GP partner, who claimed to know something about Dynamics GP.  And in my humble opinion, the partner betrayed that client's trust.

The partner billed the customer, presumably an obscene amount of money, for this "solution", which is going to need to be scrapped and re-written so that it can be supported going forward.

Some might say that I shouldn't be exposing the egregious behavior of a GP partner, perhaps arguing that it makes the Dynamics GP community look bad, or it serves to reduce potential customer's trust in the GP partner community. 

Rather than pretend this never happens, let's rid the GP community of such problems.  If these stories can help customers learn and become more informed, and partners can be better educated, these hopefully rare situations will become less common, and the 'bad' partners will either clean up their act or go out of business.

Customers should get second opinions, and perhaps even shop around for quotes on a large project.  And partners should know better than to have a rogue developer write bad integrations--although I don't have any great ideas on how that self-realization might occur.

In the meantime, I'm stuck trying to fix a problem with this nightmare integration so that it can continue to work until the client upgrades to GP 2010 and replaces it.

Answer:  In my experience, the fastest way to spot a potentially 'bad' .NET GP integration is to look at the Windows Forms in the solution.  If the form includes a big image of the partner's logo or a really "pretty" user interface with fancy graphics, that should be a warning sign.  I've been asked to fix several horrible integrations, and the one thing they all had in common was that the developer clearly spent time making the forms look pretty and including a ludicrously large image of the partner logo.  And when a developer is wasting time doing that, I've found that they typically didn't spend adequate time on the code, often having no idea how to properly develop a Dynamics GP integration.  Sure enough, this integration consisted of one windows form, with one button (that only called a stored procedure), and a giant image of the partner's logo. 

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.



Unknown said...

Now, now. There is plenty of blame to go around. In more than one case I've see the company using GP refuse to buy a preferred solution and insist on something else, even though it may be more expensive and less robust.

There can also be timing issues. I've seen companies carrying forward solutions built for GP 6.0 into newer versions of GP. I just stopped one of my clients from using a solution built for GP 6.0, carried to 7.5 and then to 9.0 from continuing it in 2010. There better options now than there were in 2001.


Mariano Gomez said...

Hillarious, but true! I am called numerous times about these "artifacts" created by so called "Gold Certified" partners and I can confirm that the common theme is the logo and the form. I never stopped to think about it until you mentioned it in this article, but it now makes sense. After all, what else would you have to show for such poorly designed integration.

How about the dude (or dudette) that writes 500 thousand lines of code to validate the data before it gets to eConnect? Or the partner that makes the customer buy BizTalk Server to integrate this "complex text file"?

These are just some of the ones that I have come across, but certainly I would imagine there is more than one case out there.

Great article!

Mariano Gomez, MVP

Steve Endow said...

Hi Mark,

Yes, there are definitely situations where archaic customizations or integrations are carried forward multiple versions. I have seen that, and understand that it is often much cheaper to keep those in place than attempt to rewrite them.

Or if it's an unsupported Project Accounting object or transaction, sure, you have to "off road" and develop those without eConnect.

But in this case, developed new in 2006 for GP 9 and ostensibly written with VB.NET, there was no excuse to not use eConnect--it is clear that the developer was totally unaware of eConnect. Hundreds of lines of SQL were devoted to things that eConnect can do with one line of code--either in SQL or .NET.

I did try to think of any reason why it was done in SQL, but I haven't been able to find any defense for the developer who created this particular monster.


Jen Kuntz said...

Great article Steve. I agree with you that posting things like this, even if there may have been other circumstances we don't know about, as Mark points out, it does make customers more informed. If customers think they have no choices, that is sad indeed!