Dynamic Query System for Heterogeneous Data Sources
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems require complex and costly processes for data extraction, transformation, and loading, especially when dealing with diverse data sources, limiting flexibility and accessibility, and the shift to unstructured data management increases complexity, requiring improved methods for querying and maintaining data structures.
Innovation Solution
A method and system that allows querying data from multiple sources without preloading data into a database, using dynamically generated query statements that transform and aggregate data on demand, enabling selective access and recursive use of query statements and results, thus reducing the need for extensive data management and structure maintenance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is extracted, transformed and loaded into a database using traditional ETL processes, then data can be queried efficiently with proper indexing and structure, but the process becomes complex and costly when dealing with large variations in data source formats and structures
Solution Approach 1:
The patent inverts the traditional ETL approach by eliminating the transformation and loading steps. Instead of transforming data into a predefined database structure before querying, the system queries data directly from its original heterogeneous sources and performs transformation only on the specific data requested, thereby maintaining query efficiency while dramatically reducing process complexity
Solution Approach 2:
The patent extracts only the specific data needed for the query from heterogeneous sources without requiring complete ETL processing of entire data sets. This selective extraction approach maintains data query efficiency by targeting only relevant information while reducing the complexity and cost associated with processing and storing all source data in standardized database structures
2Productivity
If data is stored in a structured database with predefined schemas, then data retrieval is efficient and targeted, but flexibility to accommodate new data sources and formats is reduced
Solution Approach 1:
The patent implements a dynamic system where query statements can adapt to different data source formats and structures at runtime. The system generates and executes queries against heterogeneous sources directly without requiring predefined schemas, enabling both efficient data retrieval and high adaptability to new data sources through flexible, on-the-fly query generation and transformation
3Quantity of substance
If the number of data tables in a database increases to accommodate more data sources, then more data can be stored and queried, but the cost and complexity of managing and maintaining the database structure increases
Solution Approach 1:
The patent creates a universal query system that can access and process data from multiple heterogeneous sources without requiring separate database tables for each source. The system uses a unified query statement approach that works across different data formats and structures, thereby increasing data storage capacity while reducing the complexity of managing and maintaining multiple specialized database structures
Data Source
AI summary
There is described a method and system for obtaining results from one or more data sources without the need to extract, transform and load the data into a database prior to performing a query. The data may retain its original format and remain in its initial location, and selective access to the data is provided using dynamically generated query statements. Multiple data sources may be accessed concurrently, each data source having its own format for stored data. Query statements are generated to obtain data (query results) from the various data sources by defining data sources, target fields, and parameters to be used for a given query. The statements comprise predefined operations that are applied recursively in order to transform (and sometimes aggregate) the data from the data sources into query results. Query statements and query results may be saved for later use.