Did You Know Series - Advanced Transformation

OneCloud Advance Transformation.

In our last post, we explored OneCloud’s ASO High-Speed Extractor and Convertor for Oracle Profitability and Cost Management Cloud (PCMCS). In this post, we explore the advanced transformation capabilities of OneCloud and showcase how common data transformation requirements are addressed with out-of-the-box capabilities that streamline data integration and improve data quality.

A key step in data integration is transformation. Transformation builds the relationship between the data models of the systems being integrated. For example, the natural account for computer hardware in the general ledger may be 150630 whereas, in the performance management system, that may be tracked by an item called Computer_HW. Transformation changes the G/L code 150630 to the Performance Management code Computer_HW.

Before we go much further, let’s define some terminology that will be used widely over the coming months:

Functional Transformation refers to the process of associating the data model of one system to the data model of another system. It requires business context to map the field values between the systems. Functional Transformation consistently needs maintenance and updating as both systems usually have new elements added consistently.

Technical Transformation refers to the process of modifying the layout of a data set. Operations such as inserting or deleting a column are examples. Technical Transformation while having some association with business context does not generally change over time or need to be maintained. 

 

In the previous computer hardware example, this would be functional transformation since there is a business context associated with the logic. In comparison, technical transformation is another key category that has some degree of business context but focuses on modifications to the data set. For example, the extract from the general ledger system may contain 15 fields and include a header row indicating the segment or field name. The target performance management system may not support ingesting a data feed that has a header row. Additionally, only 8 of the fields are actually relevant to the performance management system. This is where technical transformation removes the header rows and the columns that are not applicable.

OneCloud has a range of prebuilt capabilities to address both technical and functional transformation. On the technical front, some of the commonly used OneCloud Commands perform operations such as adding or removing a header row, inserting, deleting or combining columns, filtering rows, splitting field values, adding rows numbers, and changing a file delimiter. These operations are common across many different integrations and the OneCloud Transformation BizApp simplifies the effort required to accomplish these operations.

The Transformation BizApp also includes robust capabilities to support functional transformation. A simple functional transformation like the computer hardware example can be accomplished with a simple Find and Replace Command while a more in-depth transformation is generally accomplished using the Advanced Query Command.

FInd_Replace

Advanced Query is incredibly powerful and unleashes a world of possibility for transformation. Consider some of the following common transformations. There is often the need to map multiple source system codes to a single target system code and accumulate the value of all mapped rows to a single data value. This requirement in technical terms is called Group By and Sum. Another common requirement is to leverage hierarchical relationships to map or transform data. Both of these are also easily accomplished using the Advanced Query Command.

Advanced Query works by taking the data set being processed through OneCloud and loading it to an ephemeral (temporary) relational table thereby unleashing the power of SQL. The beauty of Advanced Query is that there is no need to install or maintain a database. OneCloud GroundRunners and CloudRunners utilize an embedded database to leverage the power of SQL to perform any type of SQL transformation operation easily and efficiently.

Let’s explore the operations Group By and Sum as they are very helpful to pre-aggregate data in advance of loading to a target performance management system. In this example, the general ledger extract contains the following data:

Location

Account

Asset Type

Amount

East Region

150630

Laptop

3500

East Region

150630

Server

25000

East Region

150630

Desktop

1500

East Region

150640

Cell Phone

800

East Region

150640

Tablet

1200

West Region

150630

Laptop

7000

West Region

150630

Desktop

2000

 

The system to which data is loaded does not track Asset Type so the data needs to be transformed to the following:

Location

Account

Total

East Region

150630

30000

East Region

150640

2000

West Region

150630

9000

 

The Advanced Query Command makes this transformation very easy to accomplish by leveraging a simple SQL query.

SELECT 

   location,

   account,

   SUM (amount) AS Aggregated_Amount

FROM

   data

GROUP BY 

   location,

   account

In OneCloud, the Command looks like the following:

Advanced_Query_Group_By_Sum

In OneCloud, we can create a simple Chain including a preview of the data before and after the transformation:

Chain_Group_By_Sum-1

Before Group By and Sum:

Before_Group_By_Sum

After Group By and Sum:

After_Group_By_Sum

Continuing with the sample use cases previously outlined, consider a situation where a master data management system such as Oracle EDMCS or DRM defines the structures for both the transactional and reporting systems. The relationship between the transactional segment value (e.g., 150630) and the reporting segment value (e.g., Computer_HW) defined within the MDM solution is in a parent-child relationship. The below image shows how the hierarchy represents a mapping relationship.

Hierarchy
This mapping relationship can be utilized in the transformation process of the Advanced Query Command. By adding another virtual table, a SQL Join can be performed to map the source system code to the reporting system code. Below is the query that has been modified to perform the mapping transformation.

SELECT
   location,
   map.parent AS parent,
   SUM(amount)
FROM
   data,
   map
WHERE map.child = data.account
GROUP BY
   location,
   map.parent


The Advanced Query Command has been modified to also create an additional ephemeral table called map which contains the parent-child relationship that is the basis of the mapping.

Advanced_Query_Map

The result of this modified query is below:

After_Mapping
The possibilities for Advanced Query are nearly limitless and provide OneCloud a powerful mechanism to perform complex transformations with incredible speed. To learn more about Advanced Transformation, please visit the Transformation Quick Start Guide.

In addition to the power that Advanced Query provides, OneCloud will be introducing OneCloud Mapping Studio (OMS) in Q2 2020. This exciting new product will enhance the integration capabilities of OneCloud’s core platform by offering a graphical user interface to enable a business user to perform an array of functional and technical transformations in a graphical user interface. We are very excited to bring this to the market and further enhance the self-sufficiency of business users to integrate their applications. To apply to participate in the OMS beta, please contact us at customersuccess@onecloud.io.

To learn more about Advanced Query or how OMS can help your organization, please contact us at info@onecloud.io.