Capture Actionable Data From Anywhere

Data integration is a business-driven technological solution to the problem of having lots of data that is not actionable. Why isn’t it actionable? Because there are numerous sources of data, all owned by different parts of the organization. Each source file can use a different format, which makes data from different sources incompatible with each other. 

A partial list of potential data sources includes:

• Point of sale system

• Web traffic

• Web sales

• Outbound call center

• Customer service

• Product information

• Direct mail

• Sales partners

• Data censors

• CRM

This list could be much longer, but you get the point. Just because you have collected all of this data doesn’t mean it can work together.

Data integration makes data actionable. The most common method of integration is called ETL, and it works in this order:

• Extract the data from the original source file

• Transform it into one common format

• Load the transformed data into a new target file from which all the data is accessible, regardless of its source

The resulting target file builds an accessible 360° view of the data that is actionable for business intelligence and analysis activities such as real-time reporting, data mining and predictive analytics.

With data intelligence you have the ability to craft real-time data pipelines where new data becomes actionable as it is entered. This is how websites are able to suggest products to you based on items you looked at just a short time earlier. 

As CRM records are updated, orders received, payments entered, etc., the data is automatically processed and becomes actionable immediately. What’s more, by employing Artificial Intelligence (AI) and Machine Learning (a subset of AI), the underlying data models are updated with the newly acquired data, improving the decisions, segmentations and predictions the models optimize.

So, the new data is immediately and automatically leveraged to:

• Present an up-to-the-minute view of the organization

• Provide a better customer experience for the online shopper

• Improve models used to predict which marketing scenarios will produce the best outcomes

• Inform better decision making

This is very exciting, but before you jump into the technological part of data integration, you have to develop a business case for integration. Because in spite of all the technology, and while IT will be involved, data integration is a business project, not an IT project. 

The sheer amount of data that companies have at their disposal makes it impractical to simply integrate all the data. Even if software existed that allowed you to click a button and wait for the results, you’d be waiting a very long time and the results would be too unwieldy to be of value. 

For businesses making a complete transition from analog to digital, Gartner notes that, “Through 2020, integration work will account for 50% of the time and cost of building a digital platform.” This quote isn’t meant to scare you, but it should make clear the degree to which data integration is necessary for modern businesses and how big an impact it has on an organization.

This is why data integration needs to be part of a wholistic, long-term business strategy and implemented as a series of  integrations, with each integration designed to address a specific business case. 

Data integration should be led by a champion of the organization’s various business interests who also understands the different data assets in play. 

In building a business case for integration, each of these questions should be answered:

• Why is the data integration being done? 

• What business processes need to be analyzed?

• What are the objectives and deliverables?

• What are the business rules?

• What is the quality of the data?

• Who will be the owner of the system?

• How will maintenance and upgrades be funded?

Engaging the relevant business interests in a discussion about these questions helps reduce the amount of data you need to extract for the integration. Answers to these questions also tell you what the target data file should look like. It is like building a highly selective pipeline to channel only the data you need out of an indiscriminate sea of data.

Once the rules for your first pipeline are ready, it’s time to start the extraction process.

Theoretically, data integration can be done manually, which involves hand coding all of the scripts necessary to extract the data from the source files, transform it into a conformed (or normalized) format and load it to a target file. There are three serious impediments to a manual approach:

1. Efficiency | Performing a data integration by hand consumes a tremendous amount of time. Modern ETL tools can do the job much faster, thereby reducing the time it takes for the extraction to produce value.

2. Human error | People make mistakes. When a developer makes a mistake in hand coding a data integration, it takes time to go through all the code until the error is found. When a mistake is made using an ETL tool, it is usually a mistake in one of the rules. Fix the rule and you fix the problem.

3. Continuity | In data integration, continuity means doing all the coding the same way in each integration project. That is almost impossible if the person who codes the first integration isn’t available for those that follow. Someone has to learn how to write the code exactly the same way as the original coder. 

While there may be times when manual coding needs to be used to a limited degree in conjunction with an ETL tool, modern ETL tools that Extract, Transform and Load data from many sources to one centralized source are much more efficient and effective than manual data integration. Let’s take a look at each phase individually.

As noted above in What Is Data Integration, organizations collect and store data in a variety of locations and formats. JSON, XML and CSV are all common data formats. In addition to various software formats, there are the ways the source data is stored. One file might put all street address information in one column, while another might break it up into two or three columns. A single name column could contain first and last names in one file, while another might have separate columns for each part of the name. 

The extraction process copies all the data from every file source, determines the source file’s refresh rate (velocity) and the priority of each source (extract order) in relation to the others and places it in the ETL environment. 

Once the data has been extracted, data profiling gives you a picture of the data that includes:

• Size of the data set

• Column heads

• Data type of each column

• Relationships between columns

• Range of values in each column

• Frequency of missing data

• Number of rows

The data profile shows you how much work needs to be done before and during transformation to make the data usable and actionable. Much of this will be cleansing the data to remove errors and duplicates. 

There also may be columns where the frequency of missing values renders the whole column useless in its current state. In those cases, you will discuss with the owner of that source data how best to handle the issue. You will need to ask if they can provide more data, or suggest how to fill in the missing values. It may be necessary to remove some of those columns completely.  The more precisely you create rules for the transformation, the better the outcome.

In the transformation phase, columns in the source files are mapped to columns in the target file, but with the source data conformed to match the formatting of the target columns.

• Dates are consolidated into buckets

• Java strings are parsed to provide business meanings

• Transactions are modeled as events

• Useless data and errors are set aside so they don’t reach end users

• Personally identifiable information is masked to meet privacy requirements

In plain English, that means that the ETL tool can read data in all the different source file formats. It can also rewrite the data to the specified formats of the target file. So, mapping the columns from the source file to the target file also tells the tool how the data needs to be transformed. If you have eight different source files and each one has a different way of dealing the customer addresses, all of those differences will be eliminated and the address data will all be presented the same way. 

If there are columns of data that are set up identically in all the source files in the desired format of the target file, those columns can be moved directly to the target file without having to go through the transformation process. 

Conforming data accomplishes two major goals by:

1. … breaking down the silos that separate different operating units and prevent operational transparency.

2. … reducing the time needed to poll all the data by creating one source of truth for every record

Once the data has been transformed it can be loaded to the target file and put to work. One choice to make at this point is whether to load all the data at once (full load) or at scheduled intervals (incremental load). 

Full loading creates new records for all incoming data, so it comes with the potential that the process will be made difficult to maintain due to the surge of data sets. By contrast, incremental loading compares incoming data with data already accessible and only generates new records when unique data is added. In addition to being easier to manage, the smaller footprint of the incremental load costs less to warehouse.

Once the data has been loaded, the target file becomes the new source file for all business intelligence activities, making the insights that have been lurking in the data immediately accessible to all business units.

ETL

It should be noted that when data integration is done via the cloud, loading takes place before transformation, so the process is called ELT instead of ETL.