Data mapping is the process of assigning a new home for a data element as it moves from one application to another. A seemingly simple example: A sales order comes in from an ecommerce site, such as Amazon, and the “customer name” data element needs to go to the “customer name” box (or field) in your ERP system.
So far, so good.
But what if Amazon sends you the customer name data element in the form of two fields: First Name and Last Name? In other words, Bob Smith comes in as “Bob” and “Smith”—two separate fields. And what if your ERP always records customer names in one field, with both the first and last name in a single field (e.g., “Bob Smith”)?
Now you have an integration problem, and you need a way to fix it so that “Bob” and “Smith” ends up combined in one field, as “Bob Smith.”
To further complicate the issue, what if the labels for each field are different? Let’s say you sell via your own website, using a shopping cart application such as Shopify, and you also accept orders from a marketplace site, such as Amazon. Both types of orders need to flow smoothly into your ERP.
But suppose Shopify calls customers “users” and the ecommerce application calls them “buyers”? And, what if your own ERP system refers to them as “business partners”?
Welcome to data mapping spaghetti.
Remember, so far, we have only been talking about one field—the name of the customer. Every document (sales order, order acknowledgement, invoice, packing slip, etc.) has multiple fields, all of which need to flow smoothly from one system to the other. And, for every incoming document there is often a “response document” that must be sent as acknowledgement (confirming the receipt of an order, for example). We have also just been talking about one integration situation.
Our example of customer name transformation is just one of many obstacles you will encounter during your data integration project. For example—imagine your ecommerce site captures addresses with state names spelled out (e.g. “New York”), and your ERP system requires states be stored as two character abbreviations (“NY”).
You can understand why a data integration project typically costs thousands of dollars and months of effort, which we describe briefly below.
The Traditional Solution for Data Mapping
Traditionally, a data integration project takes several months and involves many different parties, including a subject matter expert (or SME) for each of your applications and a specialized consultant who will create custom code and help to manipulate the data when required.
Once you’ve assembled your data mapping team, you can begin the process of mapping fields from system to system. Typically, the steps involved include:
- Identifying which entities (or tables) will be moved over, along with the specific fields
- Distinguishing the format of the data before and after it is transferred
- Deciding what will trigger the transfer (manual or automatic)
- Determining the frequency of the transfer (for integrations)
These steps don’t look too intimidating, but keep in mind that you’ll be repeating them for every document and response—and this process will be multiplied by the number of applications you have.
And even after months have been spent developing a custom data mapping solution, you’re still not done. What happens when your ecommerce application has a system update, and an already-mapped field has changed its format? More development work must be done to remap the field in the appropriate format. And, what if your ERP system has an update?
As software updates are not always in sync, you can see how this would be a never-ending project.
And you’re not the only one who has an integration problem—or the only one looking for simpler solutions. As DataVersity reports, “Approximately 41 percent of business users find data integration technologies complex to use,” often causing more complications and frustration than the actual data mapping integration itself.
Until recently, system integration problems such as these have been ridiculously difficult to solve. Fortunately, there are new, smart tools that automate much of the data mapping process, and make it time- and cost-effective.
An Alternative Approach to Data Integration
Wouldn’t it be nice if data mapping teams documented their approaches and developed a tool to make this simpler? The Alluvia data mapping tool does just that. It allows business managers of any skill level to set up ERP integration systems themselves and easily adapt as situations change or new integration partners are brought into the fold.
To create Alluvia, we identified the most popular ecommerce, shopping cart, CRM, and shipping systems used in the industry today. Then, we created templates for the essential document types (sales order, packing slip, etc.) for each of these platforms.
Alluvia takes these templates and, using an easy mapping wizard, maps each field in a document to their respective homes in SAP Business One. With a few clicks, business managers can map the majority of the fields required to import sales orders to SAP Business One from Magento, ShipStation, Shopify, Salesforce, and more.
Let’s use our same examples from above, of customer name and state, and see how the data mapping would be done using Alluvia. In this example, let’s say you want to map shipping updates from ShipStation to your SAP Business One system.
First, find the sales order template for ShipStation from the catalogue of pre-defined templates.
You can see that Alluvia offers several out-of-the-box integrations to SAP Business One. Once you’ve selected ShipStation, now select the appropriate template for the integration.
Here is where all those months of a using a traditional data mapping solution get boiled down to a matter of minutes. Once you’ve chosen the mapping template, source, and endpoint systems, the Template Wizard will guide you through the field-mapping process starting with what it thinks are the obvious matches. With all the source system fields on the left and target system fields on the right, you can simply drag and drop any additional fields to the mapping. In this case, let’s drag “customerId” into our mapping instance.
Remember, our example integration conflict was that our source system sent the customer name as “Bob” and “Smith”—two separate fields. And our ERP records customer name in a single field (e.g., “Bob Smith”). To convert the source fields to fit into the destination field, you’ll create a Transformation.
When a set of characters (or string) needs to be combined with another string, a formula called CONCATENATE is often used to do this Transformation. The Template Wizard includes an option to apply custom formulas to fields as necessary. There are many formula types available in the tool, including left trim, right trim, split, default value, and Excel formula. This last option is especially powerful—if you know how to use Excel, you already know how to write Alluvia formulas.
For our example, we’ll use an Excel formula to concatenate “Bob” and “Smith” from ShipStation before mapping it to the customerId field in SAP Business One. And with those few steps, our integration problem with customer name is now resolved.
Alluvia has a list of “Global Transformations”, which includes some common transformation values that a user can activate and apply to a field. To address our earlier issue of mapping the state field of an address, we can use a transformation type of UnitedStatesCodes to convert our spelled out state names to abbreviations for our SAP Business One to consume.
As you can see, using the Alluvia Template Wizard is like having an expert guide you through the entire process. Using this simple example of mapping the customer name field from one system to another is just the beginning—there are endless other scenarios in which the Alluvia Template Wizard can make a data integration project complete quicker, and without added stress.