Data Virtualization / System Integration / Enterprise Architecture
Why Centralizing All Corporate Data Isn’t Always the Best Solution
The development of corporate information systems has traditionally been accompanied by the creation of centralized repositories. Enterprises collect data from ERP, CRM, electronic document management systems, production complexes, and other sources, transform it through ETL, and load it into a Data Warehouse.
This approach allows for the formation of a consistent analytical model, support for historical reporting, and reduction of the load on operational systems.
However, physical consolidation has its limitations. A data transfer procedure needs to be developed for each source. Changes in the structure of the original databases require modifications to ETL. There is a delay between the occurrence of an operation and its appearance in the analytical environment.
Furthermore, not all data needs to be constantly copied. For example, an electronic document management system might require the current counterparty status from the ERP. If the relevant attribute changes only occasionally, creating a separate nightly ETL process for a small set of fields can be excessive.
Another situation involves a management report that combines current CRM client data, ERP balances, and information about agreements from the EDM. For such a task, a new physical repository is not always necessary. Sometimes, creating a logical representation of information from multiple sources is sufficient.
This is precisely the capability that Data Virtualization provides — an integration approach where users and applications get a unified logical view of data without the mandatory prior physical consolidation of all datasets. Data virtualization allows decoupling the way information is accessed from its actual location.
At the same time, it does not eliminate the need for enterprise directories, Data Governance, and performance monitoring.
What Data Virtualization Means
Data Virtualization creates an abstract information layer over multiple systems. To the consumer, it might appear as a set of tables, views, or APIs.
Physically, the information remains in ERP, CRM, DWH, Hadoop, or another source. For example, an enterprise uses Oracle Database for financial accounting, Microsoft SQL Server for CRM, and PostgreSQL for a partner portal.
An analyst needs to obtain a list of active customers along with the sum of their current outstanding debt. Instead of creating a new physical table, the integration layer can form a logical representation that combines the relevant data.
Logical Representation
The user accesses a unified information object.
For example:
SELECT
customer_id,
customer_name,
outstanding_balance
FROM business.customer_financial_overview
WHERE customer_status = 'ACTIVE';
This is a conditional SQL query to a virtual representation. Several independent information sources can be behind it.
The platform determines the necessary requests to each source, performs supported transformations, and returns the result. However, physical information is not necessarily completely copied to the intermediate database.
Virtualization does not mean the absence of any storage
To improve performance, the system can use caches and materialized views. Therefore, it is more correct to talk about the absence of mandatory full physical consolidation, rather than the absolute absence of copies.
For each virtual representation, it is necessary to determine whether caching is allowed and how up-to-date the results should be.
Enterprise Data Virtualization Architecture
A typical virtualization layer resides between information sources and their consumers.
- BI / Reports
- Enterprise APIs
- Analytical Services
Data Virtualization Level
Logical Views
Federated Queries
SQL Optimization
Security & Metadata
- ERP
- CRM
- DWH
- Hadoop
Conceptual Architecture. The actual number of connections and the location of transformation execution depend on the platform and source specifics. At the top level are the information consumers.
They can use SQL, JDBC, ODBC, or specialized APIs. The middle tier is responsible for the logical model, query execution, security, and metadata management.
The bottom tier consists of physical sources. The main architectural advantage is that consumers do not need to know all the details of information placement.
However, the dependence on physical sources does not disappear: it moves inside the integration layer.
Federated Query: executing a query to multiple sources
One of the main mechanisms of Data Virtualization is federated queries. They allow combining information from multiple systems without prior transfer of the entire set.
For example, an enterprise wants to get a list of active contracts with current counterparty details. The document management system contains contracts, and the ERP contains the main directory of legal entities.
The virtual layer can execute queries to both systems and form the result.
Performance Problem
Imagine that the DMS has a million documents, and the ERP has two hundred thousand counterparties. If the virtualization platform initially loads both sets completely and then performs the join, this will create a significant network load.
Therefore, query optimization must be used.
Predicate Pushdown
One of the mechanisms is to pass filtering conditions directly to the source. For example, if only contracts for the current month are needed, the virtual layer can form a corresponding query to the DMS.
This allows reducing the volume of transmitted data. However, the possibility of pushdown depends on the source and supported operations.
Join Pushdown
If two tables reside in the same physical DBMS, their join can be pushed down to the corresponding SQL engine. This is often more efficient than transferring all records over the network.
If sources are physically independent, the platform must choose another execution method. For example, it might first retrieve a small filtered set and then use it to look up matching records in another system.
Source Statistics
Accurate planning requires information about table sizes, indexes, and characteristic value distributions. If statistics are outdated, the optimizer may choose an inefficient plan.
Therefore, Data Virtualization requires regular performance monitoring, just like traditional DBMS.
Denodo Platform 6.0 and the Evolution of Virtualization
One of the industry platforms in this direction is Denodo.
On March 29, 2016, the company introduced Denodo Platform 6.0. It included the development of optimization for federated queries, connectivity to various analytical sources, information search tools, and capabilities for use in a cloud environment.
Therefore, in June 2017, Denodo 6.0 can already be considered for industrial projects.
Logical Data Warehouse
Denodo allows the creation of logical representations over various information sources.
This can be useful if an enterprise wants to provide users with unified access to data that remains in independent systems.
Optimization
The platform attempts to push relevant computations to the sources to reduce network traffic.
However, the result depends on the query structure and the capabilities of specific connectors.
Integration with Big Data
Denodo 6.0 supports connections to a number of analytical platforms, including Hive, Impala, Amazon Redshift, and Apache Spark. This allows combining traditional relational sources with large analytical datasets.
On June 15, 2017, there will also be an announcement about the certification of Denodo 6.0 for Cloudera Enterprise 5; as of the date of this article, this is still a future event.
IBM and Federated Data Access
The idea of federated integration is not new.
IBM has been developing relevant capabilities in enterprise DBMS and integration tools for many years.
For example, IBM Db2 supports federated access mechanisms to external sources through appropriate adapters.
This allows executing defined queries to other systems without necessarily creating a separate DWH. However, it is necessary to consider driver limitations, SQL compatibility, and optimization capabilities.
For large organizations, it is also important to distinguish between federated access and a full-fledged data management system. The presence of an SQL connection does not automatically determine the correct corporate client identifier or profit calculation methodology.
This requires MDM and Data Governance.
Data Virtualization, ETL, and ELT: Not Competitors, But Different Mechanisms
One common mistake is trying to completely replace ETL with virtualization.
In reality, these approaches solve different problems.
| Criterion | Data Virtualization | ETL / ELT |
|---|---|---|
| Data Access | On-demand to physical sources | To pre-loaded datasets |
| Data Freshness | Can be near real-time | Depends on load schedule |
| Source System Load | Occurs during queries | Primarily during loading |
| Historical Reporting | Requires separate design | Well-supported in DWH |
| Complex Transformations | Limited by source and platform capabilities | Can be performed in a separate environment |
| Fault Tolerance | Depends on source availability | Prepared datasets can be autonomous |
| Typical Scenarios | Operational aggregation, logical access | Scheduled analytics, historical snapshots |
For example, obtaining the current contract status can be conveniently done through a virtualized service.
Conversely, generating a sales report for the last five years with numerous historical adjustments is often more practical to perform in a DWH. The load must also be considered.
If hundreds of analysts simultaneously run complex queries to the production ERP through the virtualization layer, this can impact business operations.
Therefore, for critical loads, replicas, caching, or physically prepared analytical sets may be required.
Data Virtualization and MDM
Data Virtualization creates a logical access point but does not automatically provide a unified business identity. For example, CRM and ERP may have different codes for the same customer.
A federated query will not be able to reliably merge records without matching rules.
Master Data Management is used for this purpose.
MDM allows maintaining corporate identifiers and authoritative attributes. The virtualization layer can use these matches to create consistent views.
For example, a corporate service can combine the current CRM customer status with ERP financial information. However, it is necessary to establish what an “active customer” means and which system is responsible for this value.
Therefore, Data Virtualization and MDM are complementary directions.
Information Security and Access Control
Federated access creates additional security requirements. For example, an analyst may have access to a virtual representation that combines data from multiple systems.
It is necessary to determine whether they are allowed to see all relevant attributes.
Authentication
The platform must establish the identity of the user or service application.
For corporate environments, Active Directory, Kerberos, and other mechanisms may be used depending on the platform.
Authorization
Permissions should be set at the view level, and if necessary, at the individual column and row levels.
For example, an analyst can receive financial summaries without full personal customer details.
Pass-Through Security
For certain scenarios, the platform can pass the user context to the physical source. This allows the use of the original system’s appropriate access policies.
However, implementation depends on the connector and authentication mechanisms.
Account Protection
If the virtualization platform accesses all sources through a single privileged account, there is a risk of excessive access.
Therefore, the principle of least privilege must be applied, and configurations must be regularly reviewed.
Practical Scenario: Single Customer View
Consider a hypothetical group of companies using CRM, ERP, and document management systems. Management wants a single customer view.
This view should include contact information, current debt, and existing contracts.
Stage 1. Source Inventory
- CRM contains contacts and interaction history.
- ERP stores financial transactions.
- DMS - contracts and their statuses.
Stage 2. Identifier Reconciliation
MDM is used to create a mapping between local codes.
Stage 3. Logical Model
The architect describes the corporate representation of the client and its attributes.
Stage 4. Connection
The virtualization platform accesses the relevant sources through controlled accounts.
Stage 5. Optimization
For each request, conditions are defined that can be passed to the initial DBMS.
Stage 6. Security
Users are granted access according to their roles.
Stage 7. Testing
The team verifies the actual performance and correctness of information correspondences.
As a result, the enterprise gains the ability to create a logical representation without fully duplicating all initial databases. However, it must be considered that if one of the systems is unavailable, individual attributes may not be returned.
Therefore, for critical scenarios, the order of handling partial failures must be determined.
Economic Consequences
Data Virtualization can reduce the need for separate ETL processes and the duplication of information sets. However, this does not mean the absence of costs.
It is necessary to license and operate the appropriate platform, maintain connectors, optimize queries, and ensure network availability. It is also important to evaluate the performance of the initial systems.
If complex analytical queries create a load on the ERP, the enterprise may be forced to deploy an additional replica or a physical analytical set. Therefore, economic efficiency must be evaluated for specific scenarios.
For example, creating a logical representation for operational status checks may be advisable.
Conversely, executing a daily consolidated report for hundreds of millions of operations without prior data preparation can be inefficient.
Intecracy Group Competencies in Data Virtualization
Enterprise data virtualization requires a combination of information, integration, and infrastructure competencies. IQusion IT operates in the fields of system integration, electronic document management, and BPM. Relevant competencies can be used to incorporate virtualized data into application services and business processes.
Softline IT has competencies in server, network infrastructure, and storage systems. These are important for source performance, network interaction, and platform availability. SL Global Service specializes in cloud integration, virtualization, and hybrid infrastructure. These areas are relevant for combining on-premises and cloud information sources.
At the same time, the choice of technology should be determined by the enterprise’s requirements for information relevance, performance, and reliability.
Practical Guidelines for Implementation
Data Virtualization allows the creation of logical information views on top of independent enterprise systems. This can reduce the need for physical data copying and accelerate the connection of new consumers.
The approach is particularly useful for the operational consolidation of relatively small datasets, the creation of enterprise information services, and the development of hybrid architectures. However, virtualization does not replace MDM, Data Quality, and enterprise DWH.
Complex analytical queries can create excessive load on sources, and the results remain dependent on their availability.
Therefore, for large enterprises, the most rational approach is to combine virtualization with physical integration where necessary.
Data Virtualization is most beneficial when an enterprise can provide consistent access to information without unnecessary data duplication, while maintaining control over its origin, performance, and business meaning.
Author