.png "Employee"){kind=icon}
Introduction
When creating visualizations based on multiple source systems, tables must be aligned to achieve a wholistic view of a business. For example, organizations that migrate from one ERP to another align invoice tables from each ERP to see all invoices for the business in one place. We explore different ways to solve for this in the article Multi-Source Table Design.
In this article we will discuss the process of data harmonization. This refers to normalizing data points within columns once they are aligned. For example, a product may be represented with one name in the first ERP while it is represented with a different name in the second ERP. If the two names are not represented with a single label, we cannot filter and aggregate our data effectively.
What you need to know before reading this article
Let's Go
Step 1: Create a Reference Table
We will create a reference table to translate the names from one table to match the names from another. This can be accomplished using a CSV, Excel file, Google Sheet, etc. In this example we will use a Google Sheet.
The Google Sheet will include a tab for each table that requires updates. Let's focus on the Business Unit tab. Below is an example of the two columns we define. The first (BU_Code) contains the value that will be used to identify a business unit. The second (Conformed_Value) contains the desired value to display in the visualization. This should be consistent with the item value used by the business moving forward.
| BU_Code | Conformed_Value |
| 1000 | Vision Sweden |
| 6000 | Vision Operations |
| 3000 | Vision Italy |
| R300 | US Federal Government |
| 0001 | Progress Administration |
| 0005 | Progress Master |
Step 2: Connect the Sheet to Incorta and Import to Schema
Next, we will connect to the Google Sheet and load it into a schema. It makes sense to load this into the same schema where our supporting multi-source tables reside. In our example we have a schema titled MultiSource with an Incorta SQL Table as our bridge between the two sources.
Step 3: Create a New Field to Look up the Value
We will now apply conditional logic to translate the Item_Code to our Conformed_Value depending on the source of the data. This will be accomplished in a business schema. We will add two new views: one for our EBS attributes and one for our SAP attributes. To learn more about this design, read Business Schema Design for Multi-Source.
Our SAP attributes will be translated to the conformed values we established in our google sheet. The following formula shows an example to translate the Business Unit code to our conformed value.
lookup(
MultiSource.BU.Conformed_Value,
MultiSource.BU.BU_Code,
SAPECC_AP.BSAK_BSIK.BUKRS,
SAPECC_AP.BSAK_BSIK.BUKRS
)
The formula in english says: Return MultiSource.BU.Conformed_Value where MultiSource.BU.BU_Code matches SAPECC_AP.BSAK_BSIK.BURKS. If there is no match, return SAPECC_AP.BSAK_BSIK.BURKS.
As a result, the values from our SAP business unit tables will be translated to match those from EBS.
Related Material
