Unified Query Processing for Heterogeneous Data Sources
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Enterprise software systems face inefficiencies when processing and displaying result sets from heterogeneous data objects, as simple Structured Query Language (SQL) statements cannot effectively handle disparate database structures, leading to inefficiencies and the need for custom user interface logic for each combination of searched data objects.
Innovation Solution
The synthesis of disparate columns into a homogeneous key column/value column pair format allows for the generation of a unified result set, enabling user interface tools to display results coherently regardless of the data providers' structures, by generating a combined query language statement that unions results from multiple data providers into a uniform format.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a simple UNION operation is used to combine heterogeneous data tables, then the result set can be generated, but the efficiency decreases exponentially with increasing numbers of heterogeneous data objects and many allocated but unused cells are created
Solution Approach 1:
The patent segments the heterogeneous data processing into two distinct phases: first, generating a schema representation that identifies common columns across all data objects; second, processing only those common columns through the UNION operation. This segmentation avoids the exponential inefficiency of processing all columns from all tables by dividing the work into schema analysis (done once) and data processing (done only on common columns).
Solution Approach 2:
The patent performs preliminary action by generating a schema representation before executing the UNION operation. This schema representation pre-identifies which columns are common across all data objects, allowing the system to prepare a optimized query structure in advance. This preliminary schema generation prevents the exponential waste that would occur during the actual data processing phase.
2Adaptability or versatility
If a generic user interface tool is used to display heterogeneous data structures, then it can handle multiple data objects, but custom logic is required for each possible combination of searched data objects and column structures
Solution Approach 1:
The patent creates a universal schema representation that can represent any combination of heterogeneous data objects through a standardized key column/value column pair structure. This universal representation allows a single generic user interface to handle any data combination without requiring custom logic, as the schema abstraction layer translates all heterogeneous structures into a common format that the UI can consistently process.
Solution Approach 2:
The schema representation acts as an intermediary between the heterogeneous data sources and the generic user interface. Instead of the UI directly handling diverse data structures, the schema representation mediates by translating all data objects into a standardized format with key columns and value columns, eliminating the need for custom UI logic for each data combination.
3Loss of information
If all columns from multiple heterogeneous tables are included in the result, then complete data is captured, but many allocated but unused cells increase inefficiency
Solution Approach 1:
The patent extracts only the common columns that appear across all data objects into the final result set, while excluding columns that are not universally present. This extraction approach maintains data completeness for the shared information while eliminating the unused cells that would result from including all columns from all tables, thereby improving processing efficiency without losing essential information.
Solution Approach 2:
The patent applies local quality by treating different columns differently based on their universality: common columns are included in the result set with full processing, while non-common columns are excluded. This selective approach ensures that resources are spent only on data that is relevant to all queried objects, improving efficiency while maintaining completeness of the shared data.
Data Source
AI summary
A system includes reception of a first query to search two or more heterogeneous data providers, generation of a first query language excerpt to define a combined key column based on key columns associated with a first data provider of the two or more heterogeneous data providers, for each of two or more columns of the first data provider, generation of a second query language excerpt to map the column of the first data provider to a respective result set key column and a respective result set value column, generation of a third query language excerpt to identify two or more of the result set value columns to search, and generation of a first query language statement based on the first, second and third query language excerpts.