Integration as a technology has been around for a while. 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).
Before moving into the details of file-based integration, an introduction to swivel chair integration will be useful.
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 of integration projects.
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).
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.
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.
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.
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.
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.
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.
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.
