What is Data Transformation?

Data transformation is the foundation of a data-driven business system. With it, you can take a vast range of different types of data in disparate formats and turn it into something the business can use to grow or operate more efficiently. This is pivotal in the current data-driven era because understanding your business’s data allows you to make strategic adjustments that can keep you ahead of the competition. You can even use data transformation to enable new or more effective products or services.

What Is Data Transformation?

Data transformation is the process of adjusting the data format so that its structure is easier for people to analyze and systems to use in the decision-making process. Transformation usually occurs after data is extracted or loaded (ETL/ELT).

In some instances, you can use data transformation before storing or presenting it, as well. It’s an essential facet of business process integration, cloud migration, and many modernization solutions that leverage business information. In the context of this data transformation definition, the process typically involves cleaning or merging data so it’s in a form that you can use software — or human wisdom — to analyze.

Transformations tend to involve converting your raw data into one that’s been cleansed and validated so people or systems can use it [1]. This makes data transformation crucial to data management, data integration, data migration, data wrangling, and data warehousing processes. Data transformation can be:

• Constructive – data is replicated, added, or copied
• Destructive –  records and fields are deleted
• Aesthetic – data is standardized to meet requirements
• Structural – data is reorganized by renaming, combining, or moving columns

Data transformation also involves:

• Converting data into a format that’s easier to understand or use in an application
• Cleaning data to make it usable or easier to interpret
• Structuring data so data scientists can use it in integrations

All of these initiatives make data transformation a core component of an organization’s growth strategy, especially if your next evolution involves data-powered insights or innovations.

The Role of Data Transformation in Data Management

Data transformation gives your data management systems data that’s more actionable [2].

To illustrate, let’s say a shipping company has two locations, one on the West Coast of the U.S. and another on the East. Upper management wants to empower the decision-makers at each location with the data they need to assess their performance. However, all of the data is combined, and there’s no way to tell which items were shipped from where.

Using data transformation, you can create a system that automatically identifies shipments according to which fulfillment center they originated from. You can then automatically store this information in a data lake that the ERPs of the two locations use. Now, when decision-makers open their ERPs, they only get the data pertaining to their facility.

In this way, data transformation can turn disorganized, hard-to-use information into a process improvement tool.

Data Transformation in Action

As a simple example, suppose you work for a car parts manufacturer, and you have a spreadsheet that outlines the number of different kinds of units sold over a year. You want to figure out which types of items sold the most and when. But if the data is disorganized, there’s no way of telling which kinds of items sold and when at a glance.

You could organize the items into categories, such as “Drive train,” “Steering,” “Suspension,” “Wheels and tires,” etc.

Now, you open up several possibilities:

• You can quickly sum up the sales figures for each category
• You can see, at a glance, which kinds of items sold the most and when
• You can produce charts according to each kind of item you sold
• You can program another spreadsheet or an enterprise resources planning (ERP) system to pull sales data and integrate it with another system, such as an inventory management solution.

The process of converting, cleaning, and structuring your data can be laborious and prone to error. Hunting through hundreds of lines of code to troubleshoot an error can push your project beyond its deadline and delay processes that depend on it. You can overcome these challenges using LANSA Composer. As a no-code solution, LANSA Composer makes data transformations accessible to stakeholders who aren’t familiar with coding languages. Its visualization tools also make it a convenient solution for adjusting your transformations as your needs change.

Data Transformation Process

The data transformation process involves extracting data from a specific source, converting it, and then delivering the data you’ve converted to a destination. During the extraction phase, you pull data into a repository from one or more sources. At this point, the data is still raw and unusable by the processes or people that depend on it. To make it usable, you have to put it through a series of steps, and some of these may involve cleaning the data before you transform it. This can eliminate fields with missing values or inconsistencies in the data sets you’re working with.

Data Discovery

The data discovery process is where analysts look at a dataset and figure out:

• The data they want to use
• Challenges the transformation process needs to overcome [3]
• Which people or systems will benefit from this data and how to best transform it for their use
• Information about the data that should be taken into consideration, such as which people or applications gathered it, when, and any other pertinent information.

After the team has finished the data discovery process, they understand how the data can be used and what needs to be done to transform it.

Data Mapping

Data mapping describes the transformation steps in detail. It includes a map of where the data is coming from and where it’s going. The current structure and the type of transformation are also planned during this phase. You also set up business rules that tell the system how to manage certain kinds of data (modified, joined, aggregated, etc)

The data mapping process wraps up with schema design, which involves planning how the transformed data will be structured. This process prioritizes how people or software will use the data.

Code Generation

Once you’ve gone through data discovery and mapping, you have a plan as to what you need to do. The next step is to write or compile the code needed to get the job done. Here are some of the more common things analysts use code to accomplish:

Execution

Code execution allows the data to be converted into the chosen format. The data is then transformed as planned during the mapping stage: aggregation, format conversion, and merging. The target system, which may be a dataset or data warehouse, receives the converted data.

During this phase, you run the scripts you’ve written to perform the data transformations outlined in your mapping documentation. Also, if the code doesn’t execute properly due to bugs, you would also fix your code during this phase.

Some transformation types include:

• Filtering. Data filtration involves specifying which columns of data to load during the transformation process.
• Enriching. This typically involves taking existing data and making it more useful. For example, a column that contains full names can have its data broken down into three separate columns: First name, Middle name, and Last name.
• Splitting and combining columns. You can have one column split out into two or more types of data. For instance, a column entitled “State” can be split into three separate ones: Eastern, Midwestern, and Western. You could also do the opposite, uniting multiple columns into a single one.
• Joining data from multiple sources. This may involve gathering data from two or more different spreadsheets or business processes into a single, unified dataset.
• Removing duplicate data. By getting rid of duplicate data, you consolidate your information and make it easier for both people and software to work with.

Review

During the review phase, you evaluate the data you’ve transformed to make sure the conversion was successful and aligns with your goals. The review process also involves making adjustments if you didn’t achieve the outcomes you were aiming for.

Data Transformation Benefits

Data transformation can enable success from several different angles, especially because it alchemizes potentially useless information into insight gold.

Enriched Data Quality

With data transformation, you can drastically improve the quality and usability of your data, especially for advanced business intelligence or analytics.

For example, you may have a customer relationship management (CRM) system that automatically populates based on what users enter in an online form. However, some users register more than once with different email addresses. This could result in an unreliable estimation of how many leads or customers you have in your system.

With data transformation, you can clean this information, eliminating duplicates, so you know exactly how many users have registered.

Reduced Errors

Human error can turn helpful data into confusing or even misleading information. With data transformation, you can quickly eliminate existing mistakes. You can even prevent human error from impacting data-based systems.

For instance, suppose a sales team enters the amount of each sale into a form that then sends that information to the finance department. You can set up a data transform rule that gives the salesperson an alert when an item they sell is more than 125% of the unit’s sale price. This could prevent people from putting in an extra zero while entering figures.

Effective Data Organization and Management

Because transformation gives you a system for organizing your data, people who need it know where it is and how to use it.

Returning to the sales and finance example above, suppose the finance team needs to know the average monthly sales figures so they can ascertain which interest rates are acceptable as they negotiate interest rates with a bank. Thanks to data transformation, they have this information right in their system, and with a few clicks, they can gauge the company’s sales volume each month.

Greater Application Compatibility

A number of apps can consume and process data, making it easier to make business-critical decisions or incorporate data into other systems. For example, businesses may use IBM i data sources in an integration initiative. To make the data produced within IBM i usable by an external application, the team can transform it into a compatible format. This disproves one of the most common misconceptions about IBM i—that it makes innovation difficult.

Data transformation also helps you ensure compatibility between applications, databases, and systems that need to use the data.

To illustrate, a hospital may use an optical character recognition (OCR) system to read the handwriting on prescriptions and turn it into data that software can process. A human may only have to quickly check how the software interprets each scanned image to make sure it’s accurate instead of manually entering the information themselves.

Faster Data Processing

Transformation enables faster data processing because you don’t have to manually comb through reams of data before using it. This makes it possible to automate business decisions using real-time data, as well as create systems that analyze performance, production rates, and other helpful metrics.

Accurate Insights and Predictions

Without data transformations, much of the data you have to deal with is useless. As in the CRM example above, duplicates can invalidate large sets of data. But you may also have high-value data at your fingertips that, with transformation, can enable revenue-supporting insights and predictions.

For instance, let’s say a retailer has a web app that processes customer reward points. They can use purchase data combined with geolocation information to see which items are the most popular in different geographical locations. The company can then predict sales volumes or even adjust what it offers in specific regions.

Data Transformation Techniques

The data transformation technique you use will differ based on how you’re going to leverage your data. However, one or more of the following techniques tend to be useful for analysts and the organizations they work for.

Data Smoothing

With data smoothing, you eliminate outliers and anomalous data that could skew your analysis. For instance, you can use moving averages to calculate the average of adjacent data points. This results in a smoother curve with fewer drastic spikes or dips.

Attribution Construction

Attribute Construction involves adding new attributes or features using ones that already exist. For example, suppose a manufacturer is working with a dataset, and two of the fields are “Money-Paid” and “Products-Assembled.” You could then construct a new attribute called “Salary_ROI.” The formula for this new attribute could be:

SalaryROI=MoneyPaid / Products_Assembled

Data Generalization

In data generalization, low-level attributes are transformed into high-level attributes. You can generate a broader categorization of your data to create a more general view of the insights or trends it provides. For example, suppose you have a dataset that has the ages of employees at your company.

The original data is: 19, 25, 36, 58, 42, 32, 48, 43, 18, 55, 51

Your generalized data may look like this:

Ages:

• 18-20 (2)
• 20-29 (1)
• 30-39 (2)
• 40-49 (3)
• 50-59(3)

Data Aggregation

Data aggregation involves categorizing data into higher-level groups. For example, you can segment demographic data according to the ages of customers or the city in which they live. With this kind of aggregation, you can hone in on what’s working well for specific groups of people or how you can better meet their needs.

Data Discretization

When you use data discretization, you separate continuous data into bins that a system can analyze individually. For instance, you can set up a system that puts data into bins of the same size, such as units of 10,000. You could also organize the data into equal intervals. For instance, you could separate users into age groups that are each ten years long, such as 21 to 30, 31 to 40, 41 to 50, and so on.

Data Normalization

Data normalization, in the context of an organization’s data transformation, refers to the process of converting source data into a different format in a way that reduces the amount of duplicated data. In the data normalization process, unstructured data and redundancies are eliminated.

For instance, you can use data normalization to get rid of duplicate phone numbers, street addresses, and website URLs in a dataset used in a customer relationship management (CRM) solution. If one phone number is written as “617-359-2117” and “6173592117,” for instance, the normalization process can present it in one common format, such as “(617)359-2117.”

Data Integration

Data integration aims to unify diverse sets of data so you can view it all at once. For example, you can have multiple databases automatically send data to one central application.

You could also set up a system where you consolidate data from dozens of users, collecting it in a single analytical system.

Data Manipulation

Data manipulation enables you to change data according to specific criteria. For instance, you can combine data from multiple datasets into a single table. You could also set up filters that generate subsets of data according to the criteria you program into the system.

Data Transformation Tools

While there are plenty of data transformation tools available, it’s important to pick the right one for your organization’s needs. One common solution is LANSA Composer, which gives users a visual mapping tool they can use to transform data as it enters or exits a system. Using LANSA Composer, you can also dictate which data you send to individual applications or areas in a database. Because you have a visual representation of each source and destination, mapping out the transformation steps is more intuitive.

Data Transformation Challenges

Data transformation also comes with some hurdles that you should keep in mind while architecting your solution. Some of these include:

• The high cost of professional expertise and transformation tool
• Data transformation can consume significant compute resources, with the intensity of some on-premises transformation processes having the potential to slow down other operations.
• It can be difficult to recruit and retain the skilled data professionals required for this work, with data professionals being some of the most in-demand workers in the current business climate.
• It can be difficult to properly align data transformation activities to the business’s data-related priorities and requirements.

Do Businesses Need Data Transformation?

Businesses need data transformation because they produce large volumes of data every day. But they can’t leverage it to generate insights or support business growth unless it’s transformed into a format they can use. Some of the most compelling reasons for data transformation include:

• It facilitates compatibility between disparate sets of data in different formats
• Transformation makes data migration easier because it transforms source data into a format that the target destination can use
• It helps when consolidating data, whether it’s structured or unstructured
• Transforming data can enhance data quality, making it easier to use it to generate insights

Real Examples of Data Transformation

While many organizations keep their data transformation statistics company secrets, data transformation happens all the time in the business world. Here are a few data transformation examples:

• UPS used data from its trucks’ travel routes, turning regular GPS information into data they could use to discover more efficient routes.[4]
• T-Mobile partnered with Google Cloud, collecting then transforming tons of data from consumers to gauge sentiment. The communications giant was able to engage with customers faster and also use data to train AI-powered conversation.[5]
• Netflix uses “Validation” and then “Enrichment” steps to transform data regarding cybersecurity events. This enables the streaming leader to produce a risk score that experts can analyze and use to stop threats.[6]

Conclusion

Data transformation empowers organizations to turn large volumes of information into actionable insights. The transformation process involves data discovery, mapping, code generation, and then code execution. Designing a transformation process can be labor-intensive, especially when you have to incorporate all of these elements manually.

Using LANSA Composer, however, you can enable transformation using no-code tools. As you drag and drop your way to an effective transformation process with Composer, you save time and produce a system that’s easy to adjust and duplicate as needed. Contact LANSA today to learn more.

References

[1] R. A. Irizarry, “Chapter 28 Smoothing | Introduction to Data Science.” https://rafalab.dfci.harvard.edu/dsbook/smoothing.html

[2] “IBM documentation.” https://www.ibm.com/docs/en/tnpm/1.4.2?topic=data-aggregation

[3] “IBM documentation.” https://www.ibm.com/docs/en/ias?topic=moments-background#:~:text=The%20discretization%20process%20assigns%20a,possible%20from%20the%20original%20attribute.

[4] “UPS – Digital Innovation and Transformation,” Digital Innovation and Transformation, Apr. 05, 2017. https://d3.harvard.edu/platform-digit/submission/ups/

[5] G. Cloud, “T-Mobile Teams up with Google Cloud to Transform Customer Experiences,” PR Newswire, Oct. 11, 2022. [Online]. Available: https://www.prnewswire.com/news-releases/t-mobile-teams-up-with-google-cloud-to-transform-customer-experiences-301645530.html

[6] N. T. Blog, “How data inspires building a scalable, resilient and secure cloud infrastructure at Netflix,” Medium, Dec. 08, 2021. [Online]. Available: https://netflixtechblog.com/how-data-inspires-building-a-scalable-resilient-and-secure-cloud-infrastructure-at-netflix-c14ea9f2d00c?gi=2ed31e745fed