-
Product
- Solutions
-
Customers
Data integration is the process of combining data from multiple sources in order to extract additional value. The process usually involves joining, cleansing, validating, and enriching the data along the way.
Even smaller organizations may use dozens of applications within their business, and that number can reach the thousands for larger organizations. The ability to work with all data across each of those different applications is crucial. Data integration makes this possible.
For example, a simple data integration task might consist of combining various Excel spreadsheets with the information in an Access database. At the other end of the scale, an extensive BI (business intelligence) platform needs to work with data from a customer database, web analytics platform, invoicing system, stock management platform, and third-party systems such as address databases – all in sync and without overlap.
Data consolidation is a process that combines data from multiple systems and stores it in one location. The goal is to simplify the application landscape and provide a single data point for other applications to access without needing to deal with the complexities – and often impossibilities – of dealing with data at the original source. Consolidating data makes processes such as BI, reporting, and data mining far more straightforward.
The standard example of data consolidation in action is a data warehouse. The primary objective of a data warehouse is to aggregate information from multiple systems and present that data to reporting or data mining systems to extract maximum value, all from a single data source.
Data propagation is the process of copying data from one system to another, often while adding further information from elsewhere to enrich the data and prevent duplicate entries across multiple systems.
For example, enriching a licensing application with data from a CRM application can allow teams that manage licenses to serve customers more effectively by avoiding the need to enter customer information into two different applications.
Data ingestion vs data integration: What's the difference?Another typical example is the propagation of data from production systems to QA or pre-production labs to support end-to-end testing of various in-house applications during regular development and maintenance cycles.
Such data propagation often involves logic that anonymizes data or selects only a subset of the source data due to security or volume concerns.
Data virtualization provides a near real-time unified view of data across multiple different systems without having to store all of that data in a single location.
When data is queried, the virtualization system queries the source systems and builds a unified view of the data on demand. Various caches are often employed to ensure that queries are fast and do not overload the source applications.
Being able to expose virtualization queries through multiple mechanisms, including easily configured API endpoints, is vital to make the query results as accessible as possible.
There are several ways in which data integration can be completed, ranging from the manual integration approach with little or no automation to the more comprehensive common data storage technique (e.g., a data warehouse).
Users operate with all the relevant information, accessing all the source systems or web page interfaces. No unified view of the data exists.
This approach requires particular applications to implement all the integration efforts. The downside of this technique is that it's only manageable when there’s a minimal number of applications.
This approach transfers the integration logic from particular applications to a new middleware layer. Although the integration logic is not implemented in the applications anymore, the applications still need to participate in the data integration partially.
Read more on the importance of a data integration layer:
Download the white paper: Designing Data Applications the Right Way
The uniform data access – or virtual integration – technique leaves data in the source systems. It defines a set of views to provide and access a unified view of the customer across the whole enterprise.
For example, when a user accesses customer information, the particular details of that customer are transparently acquired from the respective system.
The main benefits of virtual integration are:
However, drawbacks can include:
This usually means creating a new system that keeps a copy of the source system’s data to store and manage independently.
The most well-known example of this approach is a data warehouse, the benefits of which include data version management and being able to combine data from very different sources (mainframes, databases, flat files, etc.) The physical integration, however, requires a separate system to handle the vast volumes of data.
Any data integration initiative should be driven by business, not IT. The project needs a thorough analysis of the requirements so it can be designed in a way that will achieve business goals. This includes having a business champion who understands the company's data assets and can lead discussions about the long-term aims of the data integration project.
Blog: The 6 Biggest Data Integration Challenges (and How to Solve Them)The most significant data integration challenge is often integrating data from disparate, often incompatible, sources. To help design a successful project, you need to analyze thoroughly:
As well as the technical integration, you’ll need to determine who within the organization will be responsible for managing the new system and what the support and SLAs are. Before the project begins, you must also have a plan for future development – who will fund and manage ongoing maintenance and upgrades?
Considering your future requirements early on in the process can save expensive headaches further down the line. Can your integration be scaled to handle larger data volumes or new sources?
As the number of systems involved in your integrations grows, the possibility of failure also increases. Having a strategy for handling errors will help manage issues such as downtime or poor data quality.
Building in automated error notification processes also means that errors can be detected and fixed quickly, whether automatically, as part of an error management pipeline, or flagged up for manual resolution.
White Paper: How to Design Your Systems to Effectively Control Bad DataCustom-coded data integration solutions can work perfectly well with simple or smaller projects. However, as the code grows, maintainability becomes a severe challenge. It's essential to be aware of the potential pitfalls of a custom-coded approach, especially if your data needs increase:
Data integration works with live systems that usually cannot be shut down while the integration is querying or updating the data. Proper resource monitoring will help ensure that the integration doesn’t negatively impact the involved applications. For example, monitoring response times and disk or memory usage can all help detect overloads of production systems.
Since data integration is a continuous process, implementation needs to be as robust as possible, created through rigorous testing before deployment to production.
It is good practice to test production data by copying it and configuring testing instances of all involved applications or systems. Such end-to-end testing can be beneficial not only to the integration itself but to the other systems involved in the integration.
When designing a data integration, it is always important to decide how to represent the data. If the integration copies data from one application to another (data propagation), the design is given by the applications involved.
However, for data consolidation and virtualization efforts, selecting the suitable representation of each entity can make or break a project.
If the data representation is hard to understand, it will be avoided, resulting in applications adding point-to-point interfaces rather than using a centralized data location.
Building a comprehensive, unified data model may sound like a good idea, but these attempts almost always fail due to sheer complexity (even with just a handful of applications). In practice, it’s best to start small, using only the minimum required data that serves the business needs.
ETL stands for Extract, Transform, and Load, and is a process for getting data from one place (the source system) to another (the target) while performing some kind of transformation on the data in the middle.
There’s also often a Cleaning step in the process to ensure the data maintains a high level of quality.
Whereas data integration can define any process that combines different data sources, ETL is most often used to describe the process of transporting data into a data warehouse specifically. However, ETL can also be used to talk about any process where the data is transformed between the source and target system.
The first step covers getting the data from the source system and making it available for processing, ideally using as little resource as possible and in a way that doesn’t negatively affect the source system’s performance, response times, or any kind of locking.
Extracts can be done in several ways, either by taking only the data that’s been changed since the last extract or a complete extract of the data, which is then compared with the previous extract to identify changes.
The frequency with which these extracts are performed is fundamental, as there can be a massive amount of data to parse. For example, full extracts can often involve volumes that reach tens of gigabytes.
Cleaning the extracted data is essential to maintain the quality of the target system. Data cleaning should perform basic data unification rules, such as:
What's the difference between data ingestion and ETL?
The transform step applies a set of rules to transform the data from the source to the target. This includes converting any data to the same dimension (i.e., conformed dimension) using the same units so that they can later be joined.
The transformation step can also involve joining data from several sources, generating aggregates; generating surrogate keys, sorting, deriving new calculated values, and applying advanced validation rules.
The load step involves loading the cleaned, transformed data into the target system (often a database). During this step, it's important to ensure the load is performed correctly and use as little resource as possible. To make the process efficient, good practice dictates that any constraints and indexes are turned off before the load and enabled again only after the load is completed.
ETL? ELT? What's the deal?
What's the difference between ETL and ELT?
Using specialist data integration software can make dealing with a large number of data sources, formats, or transformations easier. It can also help automate your data integration processes to save manual effort, increase repeatability and reliability, and build in error monitoring and handling.
Choosing the right software to suit your organization's requirements can be a complicated decision. Before you start the process, it’s crucial to have a solid understanding of what you want to achieve. Other things to consider are:
Your data integration process should be as streamlined and straightforward as possible. Key to that is automation. By replacing ad-hoc Python scripts, sprawling spreadsheets and other unmanaged tools with an automated and reliable data pipeline, it becomes possible to:
CloverDX has helped many businesses with their data integration, including helping clients move away from Excel spreadsheets into automated integration, bringing thousands of systems together while tracking large volumes of data, or even helping one business recoup six working days a month.
Data integration is a broad term, covering a considerable number of challenges and outcomes. We speak with you to learn what you hope to achieve and consider what might suit your business.
Some questions to consider will include:
We can learn more about your data integration needs by asking the above questions and others. Every business is unique, and your data may vary in quality, quantity, storage, format, etc.
From building robust architecture to frameworks, data integration takes many forms. Moving on from scripts or Excel can seem daunting, but our customers set the pace and direction of travel. You decide what you need; we find a way to make it happen.
In summary, your business defines what data integration means to you. Want to save time and resources? You can. Are you hoping to reduce instances of error and enable better collaboration? It’s possible.
Whatever your goals, we’ll always do our best to help you achieve them and create data pipelines that are automated and scalable. The ultimate goal is to save on time, cost, and stress for your business.
To learn more about how CloverDX helps modernize your ETL, click here.
Our demos are the best way to see how CloverDX works up close.
Your time is valuable, and we are serious about not wasting a moment. Here are three promises we make to everyone who signs up:
Get in touch for a personalized demo.
