The 5 Stages of API Integration

An Honest Look at the Reality of Data Integration Projects

In March of this year I attended one of Automation Alley’s Integr8 Roundtables to discuss the topic of Data and Industrial Intelligence. I was excited for the discussion and it didn’t disappoint.

Coming from a marketing background that includes a hefty dose of web development, my experience working with clients in the manufacturing industry on integrating data has largely been with marketing and sales data. This includes integrating website analytics, ecommerce, quotes and orders with CRMs, as well as integrating CRMs and ecommerce platforms with ERPs for order tracking, fulfillment, and inventory management.

This morning, I would also get to hear from people working with integrating CAD, PLM, MES, OEE, and QLM systems – also all relevant to the clients I work with every day. To be honest, I was nervous to sit at a table full of industry leaders and experts speaking on this subject. Did I belong here?

As we got into the conversation, my anxieties alleviated and I found that, not only was the conversation familiar, I had relevant insights to share. What a relief! I figured it was worth taking a risk and dropping the same joke I always drop at every CRM/ERP integration roundtable I’ve ever attended at every conference I go to.

“And then at the end of the day, you still wind up exporting the data into Excel to drill down on the one thing you really want.”
Everyone laughs.

Everyone always laughs at this joke.

It’s funny because it’s true.

I regularly attend industry conferences and tradeshows. People who speak about integrating platforms love to talk about theory. They present a pristine portrait of all of these systems working perfectly and harmoniously together. There are some snappy takeaways from the breakout session that are going to solve all your problems. Everyone nods and takes notes, and then later, when everyone has loosened up over a drink, the shields come down and you hear from the other attendees.

“Man, I wish my company had it together like that.”

“It all sounds great but we’re still struggling with the fundamentals.”

“In theory sure, but actually it’s a complete dumpster fire.”

No one wants to admit, in front of an audience, how much of a struggle it is, and the result is that we all have FOMO, believing that surely it’s just us. Everyone else has it together.

It’s time we took an honest look at the reality of what integrating data across platforms looks like. In doing so, we can encourage more honest conversations and set more realistic expectations for what this looks like in our own organizations.

Where Integrating Data Goes Wrong

Let’s start by discussing some of the common pitfalls that you’re going to run into when you try to integrate systems. There are so many different kinds of integrations I’m not going to try to get into the specifics of each one, which would be virtually impossible in the scope of this piece. Instead we’re going to keep to some high level abstractions that you’re likely to see as part of any integration process.

Human Resistance to Entering Data

One of the places where “integrations” wind up on the rocks is actually before the integration even starts. We bring assumptions to the table about the data that we wish we had, and then when we go to implement the new process or integration we go, “Hey, where’s the data that’s supposed to be in here?” It often doesn’t exist because no one entered it in the first place. We resolve to do better and start entering the data but… the will isn’t there. People don’t like to make more work for themselves, and if your data isn’t being collected automatically, it can be very difficult to persuade staff to build new habits, even if they are good habits.

What this looks like:

• Sales only updates leads that are moving forward because they don’t see the value in adding context to leads that aren’t going to close
• Machine operators log downtime after the fact (or not at all) because manually entering data slows restarting the line
• Only failures in quality inspection get documented (while inspections that pass are waived through), so your OEE data only reflects part of the picture
• CAD revision histories stop getting updated the moment a design moves to the floor, because the engineer has moved on to the next thing
• Work orders in the MES get closed as “complete” before final sign-off, because the system requires closure to move the job forward

Gaps in API Documentation

We are considering an API integration and we look at the API and tell ourselves “Excellent! This is well documented!” But the reality is that the documentation may have been accurate at one time, but is no longer accurate today. The truth with any documentation is that it doesn’t update itself, so if it isn’t being regularly maintained, it could be misleading.

What this looks like:

• The endpoint for pulling real-time machine status was deprecated two versions ago. It’s still in the docs, and it always returns ‘1’
• The PLM API claims to export full BOMs, but nested assemblies beyond two levels simply don’t come through
• Field names in the documentation reference a schema from a prior version, so every value you’re mapping lands in the wrong place
• The webhook that you need to fire on unplanned downtime events only fires on planned ones. The distinction is not documented anywhere.

Unexpected Use Cases

Perhaps the largest single reason that integrations go astray is that we wind up using platforms in ways other than how the designers expected them to be used when they built them. Every business is unique and has its own way of doing things, and somewhere along the line this is going to conflict with how a piece of software was designed. Maybe it’s a point of data entry you didn’t expect, or a step in a procedure that isn’t relevant to your process, or a difference in how your data is structured. Eventually something doesn’t line up. And when it doesn’t, the likelihood that the API supports your unique use-case plummets.

What this looks like:

• Your shop uses a part numbering convention that predates the MES by fifteen years, and the two systems have fundamentally different ideas about how a “part number” should be used.
• You’re trying to track rework loops in a QMS that was designed to record pass/fail outcomes, but your internal process is more granular than black-and-white statuses
• The ERP’s job routing module was built for production runs, but you’re using it for prototype builds, so every step of the process requires a workaround
• Your process requires tracking operator time and machine time separately but your MES isn’t designed to distinguish between the two

(This is the moment when we export that data into Excel, btw…)

No Silver Bullets

Unfortunately, this is the part where I inform you that there are no silver-bullet solutions to these problems. It’s not that it’s hopeless, it’s just more complicated than that and each situation is going to be different.

Integrations take longer than we think because, despite many of our best efforts, we’re all optimists. It’s early days in the process and you think you know everything. You’ve got it all figured out. You get 90% of the way there and then, with just that one last loose end to close, you hit a wall. Some essential component of the integration fundamentally doesn’t line up.

This is super frustrating because it’s really not possible to know how the system is going to perform until you’re asking it to do the thing. Maybe you can even see in the documentation that the function you want is right there and then… it just doesn’t do the thing!

Tragically, the only way to find the wall is to hit it. Then the resulting trauma leaves us casting about for a coping mechanism, which brings us to:

The 5 Stages of API Integration

Denial: “What do you MEAN the API isn’t working!? I can MAKE it work!”

When you hit that wall, Denial is the natural reaction. You insist you aren’t stuck. You keep trying things. Just 5 more minutes and you’ll have it cracked, the solution is right around the corner. This can last for days, hours, or weeks. Just one more thing to try…

Anger: “What’s wrong with this software? Why didn’t these idiots provide these end points in their API!?”

Frustration is a natural part of the process. The word “should” gets used a lot here, but the fact remains: reality does not align with your expectations. Rage all you want, it doesn’t actually change anything.

With no recourse left, you reach out to support for API assistance and it’s time for…

Bargaining: “Surely support can address this. This is just a bug fix right? It’s a feature request? It’s a SMALL feature request!”

Support experiences vary wildly, but what we’ve largely seen is that the last thing anyone wants to support is the API, and especially for unexpected use cases. You can submit a bug report or a feature request, but your vendor’s dev-team’s day-to-day reality is that their leadership is pushing them to get NEW features out the door: something sexy and exciting that grabs headlines vs. “We addressed an edge case in our API,” which is a headline that excites almost no one.

Your issue may get addressed, it may not. What seems like a bug to you is likely a use-case that the software developer doesn’t feel obligated to support. The most likely outcome is a partial solution to the problem and the need to reconcile yourself to a compromise, but we aren’t at that stage yet.

Despair: “It’s never going to happen. This integration is simply impossible.”

Unable to get the perfect solution you were hoping for, black clouds of despair settle in. The temptation here is to just give up. It’s not going to work the way you wanted so, why bother?

Acceptance: “OK it’s not going to happen exactly the way we envisioned, but what we CAN do is…”

You settle for the compromise-solution that you can make work rather than the ideal use-case that isn’t possible.

Concluding…

If you’re working on integrating your data across platforms and it feels frustrating, and messy, and imperfect, just remember: That’s totally normal. Recognize none of this happens in a perfectly controlled environment. Everyone else does not actually have this all figured out better than you. You’re not alone.

Ultimately, the solution is going to involve elements including:

• Acceptance that a partial solution is still better than where you were
• KEEP ITERATING towards a more complete solution
• Make concessions where you can on how your vendor intended their software to be used
• Develop internal workarounds for the things that are essential to how your business functions
• Let go of some of the things you’re never going to get

There are no easy solutions. That doesn’t mean it isn’t worth doing, even imperfectly. Progress can be slow and hard fought, but remain relentless, don’t give up, and you’ll get there. Well, most of the way there. And for that last 2%, there’s always Excel.