By Nicholas Mortensen
Gartner has an adoption curve that it applies to emerging technologies. The curve has five stages:
- Innovation trigger
- Peak of inflated expectations
- Trough of disillusionment
- Slope of enlightenment
- Plateau of productivity.
The concept of integration falls somewhere between the “trough of disillusionment” and the “slope of enlightenment.” Integration as a technology has been around for a while. Most people have heard the word enough times that they think they know what it means — until they are asked to define it.
Because “integration” is a term used in everyday business, people are hesitant to admit they don’t fully understand what an integration entails. It could feel like they are admitting they are not good at their jobs. But this fear is leading to ongoing confusion about what integration is, how it works, and what is needed for an integration project to be successful.
Here is a very basic definition of integration: Integration is moving data from one system to another. When I integrate two systems, they are simply sharing data back and forth.
When tackling an integration project, there are four main approaches we tend to see: swivel chair, flat file, EDI and APIs. This post explains the nuts and bolts of integration predominantly through the lens of a file-based integration. (other topics will be covered in other posts in this series).
The Swivel Chair Approach
Before moving into the details of file-based integration, an introduction to swivel chair integration will be useful. Swivel chair is the most common form of integration and one of the key business challenges organizations are trying to address by improving their integration strategy.
You put something in one system, then you swivel in your chair to the other system and manually put the same information in there. So, in our example, I close a deal in Salesforce, open NetSuite, and manually enter the necessary information in my accounting system. But the swivel chair approach is time-consuming and, more importantly, it introduces the greatest risk from human error.
In 2016 alone, bad data cost businesses in the U.S. an estimated $3.1 trillion dollars. Combine those numbers with the fact that human entry is the leading cause of bad data, and it is no wonder there are so many people looking for a better way.
Automating the process of moving information from one system to another is what most people are looking for when they think about integration. It’s a pretty simple request at face value. But it’s what’s below the surface — the workflows, applications, processes and data — that leads to the complexity that seems to plague integration projects.
A Better Way to Quote-to-Cash
Thanks to Microsoft Excel, file-based integrations are the easiest for us to conceptualize and understand. When performing a file-based integration, we rely on the file transfer protocol (FTP).
(We don’t have time to dive into an explanation of FTP here, but know that file-based integrations are frequently referred to as “FTP integrations,” for short. No matter the method you choose to integrate, the same steps outlined below will need to be taken into consideration.)
One of the most common FTP integrations requested is between a customer relationship management (CRM) platform and an enterprise resource planning (ERP) system.
This integration is typically most important for supporting the quote-to-cash process that is so essential for most any company. Salesforce is the dominant cloud-based CRM on the market and NetSuite is the dominant cloud-based ERP on the market, so let’s use those in our example.
When I close a deal in Salesforce, I want that deal in NetSuite so we can bill the customer, recognize the revenue, etc. I need to move information between systems; therefore I need an integration.
Start by Understanding Your Information
A flat file based integration is the most common approach we see. And the most common format used is the comma-separated values (CSV) format. There are other flat file formats like tab delimited and fixed-width, but, for this example, we’ll stick with CSV. Typically, these files come out of a system via a report.
When considering this approach, the first order of business is identifying the information you want to move to the other system. Once that is nailed down, you need to know if the system you want to pull information from is capable of creating the reports you need.
Not all applications have reporting systems robust enough to get you all the information you’ve identified as necessary for your integration. Don’t know if your system has what you need? You can always reach out to a professional for help.
Why do I need a report? Most of us aren’t familiar with navigating databases. From a technical perspective, those reports are just a user-friendly way of pulling data from a database. Once you’ve determined that you can create reports and export their data, the next step is field mapping.
By this point, I should have a spreadsheet of information I have identified as information that needs to move from Salesforce to NetSuite. “Mapping fields” is simply an exercise of determining where in NetSuite each field (column) in your spreadsheet needs to go.
When I close a deal in Salesforce, I need to know what account to bill. So, in a quote to cash integration, I will always need to move the account information to my ERP.
In my spreadsheet, I should have a column for “first name,” “last name,” “email address,” etc. The column “first name” needs to go to the NetSuite customer table field “first name,” the column “last name” maps to the “last name” field, etc. You need to repeat this process for every single column in your spreadsheet.
Test First Before Deploying
Once you’ve figured out how things need to map, it’s good to import a sample set of data as a test. Chances are you’ll end up with some errors. Those errors are usually due to data formatting and data cleanliness. A common example is date formats, which often differ from system to system.
Some systems may require MM/DD/YYYY format, while others may require YYYY-MM-DD format, and yet another may require DD-MM-YY. If the two systems you’re integrating don’t use the same date format, the data needs to be transformed. You have to look at the data coming out of one system and “clean” or “scrub” it to make sure it’s in the right format for the target system.
So, you’ve verified you can export information. You’ve mapped the fields. You’ve scrubbed the data, so it’s in the right format. But…you’re still not done.
The last step you need to consider is how you will identify and handle duplicate data records. If you were to just upload the data as is, you would likely end up with a garbage pile of information that would give you no visibility.
If I sell to a customer named “Nick Mortensen,” you need to find a way to know if this particular Nick Mortensen already exists in NetSuite. If he does, I need to associate the sale to the existing Nick Mortensen, not create a new one.
But this is easier said than done. What if two different people are named Nick Mortensen? What if I now want to go by Nicholas? What if you spelled my last name wrong? The answer is a unique identifier.
Try to Be Unique…With Your Data IDs
To eliminate duplication, you need to determine a unique identifier to keep records in sync between the two systems.
If the unique identifier exists in the receiving system, update the existing record. Don’t create a new one. If the unique identifier doesn’t exist in the receiving system, create a new record.
An email address is often used as a unique identifier for contacts. It’s a quick and dirty method, but there is a better way. Suppose a contact has given a work email address and wants to switch to his or her personal email. How can that be handled in an automated fashion? Without a good unique identifier, you will be stuck building unnecessarily complex processes or handling deduplication manually.
The best unique identifier is an independent ID — a form of identifier that will never change. A good example is the system identifier that’s created in Salesforce or in NetSuite when a record is created.
When you’re integrating between Salesforce and NetSuite, you need to decide which system identifier to use. In this use case, leads are generated in Salesforce so your CRM becomes your system of record for contact and account data. When you’re integrating sales contacts created in Salesforce, it probably makes sense to use the Salesforce ID.
If you are struggling to determine which system should be the system of record, it’s a good starting point to assume the system that creates the record should serve as your source of truth. But when in doubt, phone a friend. There are plenty of Boomi partners that would love to help.
Now that we have identified the Salesforce system ID as our external ID in NetSuite, we want to add a new field to the NetSuite customer table to capture our new identifier. From now on I can use that external ID to determine if the record coming into NetSuite is a new record or an existing record that needs to be updated. With unique identifiers in place, your organization will be able to build simpler, more resilient integrations that scale easily.
Now that we’ve covered the basics, you can now be confident you’ll know the answer the next time anyone asks, “What exactly is an integration.”
This is the first in a series of blog post about the basics of integration. In our next post, we’ll dig deeper into why even simple integrations are more complicated than they seem, as well as the role of APIs and web services for your integration strategy.
About the author: Nick Mortensen is director of development with Eide Bailly Technology Consulting, a Boomi partner and business advisory firm specializing in the implementation, customization and integration of leading ERP, CRM, and cloud technologies.