TABLE OF CONTENTS
- Introduction
- Standardizing Incoming Data To Match Destination Requirements
- Data X
- Data Y
- Standardizing Incoming Data By Creating A New Object
- Data Y1
- Time To Run A Test!
- Removing Irrelevant Data
- Wrapping Up!
Introduction
Data transformation is a widely utilized process of transforming data from one format to another to facilitate seamless integration between a source system and a destination system. Data transformation can be constructive (such as adding, and replicating data), negative (deleting irrelevant data), aesthetic (standardizing incoming data and fine-tuning them to suit destination requirements), or structural (renaming or integrating columns in a database). It is the cornerstone of data integration across organizations and Alumio makes it easy to accomplish this transformation, being a leading iPaaS platform.
Before getting started with mapping and filtering data with Alumio, here are some assumptions we will make:
i) There are two systems- System A & System B,
ii) Certain data is retrieved from system A (payload data) and system B requires it in another format.
So, in this scenario, you have to map the data to the format required by system B. In addition to that, you might also have to filter the payload data since there might be irrelevant information within it that system B doesn’t really require.
Standardizing Incoming Data To Match Destination Requirements
There are many approaches to accomplish this and here is a simple step-by-step guide to do that with Alumio!
Step 0: In the menu you have ‘Connections → Entity transformers’
Step 1: Click on Add entity transformer to create a data transformer by selecting Data, transform data using mappers and conditions from the drop-down menu.
Step 2: Go to Data transformers and select Value setter from the drop-down menu.
Step 3: Move on to Configurations and specify the Key. For example, let’s name it result. Subsequently, you have to write and set up all the information that will eventually be sent to system B (as discussed above).
Step 4: Next, select the Value. In this case, you can select object as the value where you can place an entire object.
Step 5: Copy and paste the payload data (retrieved from System A) within the object.
For example, let’s call this Data X.
Data X
{
“billing_address” :
{
“first_name” : “Soham”,
“last_name” : “Roy”,
“street” : “123 Main Street” ,
“city” : “Austin”,
“state” : “Texas”,
“zip” : “78751”,
“country” : “United States”,
“email” : “sohamroy11@gmail.com”
},
“products” : [
{
“name” : “Mug” ,
“quanity” : 2,
“price_inc_tax” : 50,
“price_ex_tax” : 45
}
}
Now, let’s consider that you need to map the data in the object billing_address (data X) before sending it to system B. As you can see, the order data contains details like the customer’s name (first and last), his email, etc.
Let’s say that the order data object you will be mapping to is called data Y, named billingAddress.
Data Y
“billingAddress”:
{
“customerEmail” : “xxxxxxx.com” ,
“lastname” : “xxxxxx” ,
“firstname” : “xxxxx” ,
“locality” : “xxxx” ,
“Region”: “xxxxx” ,
“postalCode: “xxxxx” ,
“addressCountry”: “xxxxx” ,
“StreetAddress”: “xxxxxx”
}
Here is how to do it!
Step 6: Simply go to the billing_address portion of data X and for every variable under it, replace the values with “&{billingAddress.name of the variable where it will be mapped to in Data Y}”
For example, considering data X, this is how it will look like:
billing_address” :
{
“first_name” : &{billingAddress.firstname} ,
“last_name” : &{billingAddress.lastname},
“street” : &{billingAddress.StreetAddress}
“city” : &{billingAddress.locality}
“state” : &{billingAddress.Region}
“zip” : &{billingAddress.postalCode}
“country” : &{billingAddress.addressCountry}
“email” : &{billingAddress.customerEmail}
}
Standardizing Incoming Data By Creating A New Object
Sometimes, while mapping, you will have multiple lines of data which are irrelevant to you.
For example, consider this destination data (data Y1) where the object products of data X has to be mapped.
Data Y1
“orderedItem” : {
“sku” : “xxxxxxxx” ,
“name” : “xxxxxxxx” ,
“offers” : {
“tax” : 0.34 ,
“price” : 1.90 ,
“basePrice” : 1.50,
“priceSpecification” : {
“base_row_total_incl_tax” : {
“accountId” : “xxxxx” ,
“totalPaymentDue” : 1.90
}
}
}
“productID” : 67878 ,
“orderItemNumber” : 787797
}
“orderQuantity” : 1
}
Step 1: Since you had started off with Chain multiple entity transformers initially, you can add multiple transformers until you get the required data.
Note: An entity transformer starts with an empty Settings dropdown. Here you select Chain multiple entity transformers.
Step 2: Click on Add entity transformer to add another transformer. Select Node, transform nodes from the drop-down menu.
Step 3: Next, you have to define the Accessor. The accessor indicates where the data can be found that needs transformation. Select “Pattern Accessor” from the Accessor drop-down menu.
Step 4: You have to set up the Pattern now. In this case, it is OrderedItem. You can simply type “OrderedItem., where the star() is the placeholder notation for incorporating the objects in Y1 individually.
Step 5: Now, within this entity transformer (Node, transform nodes) , you can add another data transformer by clicking on Add data transformer. Select Value Setter from the drop-down menu. This will allow you to act under the object OrderedItem.
Step 6: Under Configurations, you can create a new Key (the name of the object). For example, let’s name it lineResult in this case.
Step 7: Within it, select the Value again and you can add another object (like you did in step 4 previously).
Step 8: Here, you can copy paste how the product should look like. You can simply copy paste it (refer to data X).
{
“name” : “Mug” ,
“quantity” : 2,
“price_inc_tax” : 50,
“price_ex_tax” : 45
}
Step 9: For every variable under the object lineResult, replace the values with “&{orderedItem.name of the variable where it will be mapped to in Data Y1}”
Step 10: For example, considering data X, this is how it will look like:
{
“name” : “&{orderedItem.name}” ,
“quantity” : “&{orderQuantity}” ,
“price_inc_tax” : “&{orderedItem.offers.price}” ,
“price_ex_tax” : “&{orderedItem.offers.basePrice}”
}
Time To Run A Test!
Well, you have standardized the incoming data X for the corresponding destination data Y & Y1 with Alumio. It’s’ about time to run a test and take a look at how the transformed data is reflected.
Step 1: Click on the Run Test button (right-hand panel) now to visualize the transformed data.
Step 2: Scroll down to the bottom and you will see that the object with the key result incorporates the billing_address (data X) and all of its information has been mapped.
Step 3: However, you won’t find the object with the key lineResult here since it is at the same level as the orderedItem. So, you will find it encapsulated within orderedItem where all of its information has been mapped.
Step 4: Now, you have to merge the two data together (the objects result and lineResult) to send them together to system B.
Step 5: To accomplish that, you can utilize another entity transformer.
Step 5.1: Click on the Add entity transformer and select Data, transform data using mappers and conditions from the drop-down menu.
Step 5.2: Click on the Add data transformer and select Move using a pattern from the drop-down menu.
Step 5.3: Now, you have to specify the Pattern field. Since you are moving the object lineResult which is on the same level as the object orderedItem, enter orderedItem.*.lineResult in the field.
Step 5.4: Next, you have to fill the Replacement field, i.e, the object result. Enter result.products.$1.
Note: The $1 is equal to the sign * you had used in step 3. It denotes the level of the object you are extracting from (orderedItem) while merging.
For example, if you consider orderedItem..lineResult, then you have to write result.products.$1.$2 to extract from the level of lineResult. Previously, the level was set at orderedItem. So, th6ey act as dynamic variables.
Step 5.5: Click on the Run Test button (right-hand panel) again and scroll down.
Step 5.6: Now, you will see that the products (2 in number) have been added.
Removing Irrelevant Data
Let’s consider a scenario where there is bulk data, but we only need to send the result key to system B by trimming off every other data.
Step 1: Click on the Add entity transformer and select Get branches from a pattern from the drop-down menu.
Step 2: Specify result in the Pattern field. It will only keep the data from the object result and every other thing will be eliminated.
Step 3: Click on the Run Test button (right-hand panel). You will see that the bulk data is gone. The result key is eliminated while retaining all of its data. You can now send this to system B.
Wrapping Up!
With Alumio, you can now successfully map and filter data now using advanced mapping and filtering techniques as mentioned in the guide. Seamless data transformation can significantly boost the efficiency of analytic and business processes to enable superior data-driven solutions. Alumio, being an industry-leader in integration efforts, offers state-of-the-art iPaaS solutions to organizations. Stay tuned with us to learn more.