Dynamic Database Views for Multi-Warehouse Query Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional approaches face challenges in seamlessly accessing and retrieving data from multiple data sources across different vendor platforms, requiring users to define multiple query expressions and perform separate queries for data fields or dimensions stored in various data warehouses.
Innovation Solution
A query system that identifies the data warehouses storing the required fields, generates individual connections, and defines database views for each warehouse, allowing a single query statement to access data from multiple sources by forming a virtual archipelago of views, thereby simplifying the retrieval process.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If multiple separate queries are performed to access data from different data warehouses, then data can be retrieved from multiple sources, but query complexity and operational difficulty increase
Solution Approach 1:
The patent introduces a query processor as an intermediary component that translates user queries into vendor-specific query expressions for different data warehouses. The query processor acts as a mediator between the user and multiple data sources, handling the complexity of query translation and execution without requiring users to directly interact with multiple different query systems.
Solution Approach 2:
The patent segments the query processing into distinct components: a query processor that handles high-level query logic and vendor-specific syntax translation, and separate data warehouse connections for each data source. This segmentation allows the system to maintain simplicity for the user while managing complexity internally through modular query processing.
2Adaptability or versatility
If vendor-specific query expressions are used for each data warehouse, then data can be accessed from multiple platforms, but device complexity increases
Solution Approach 1:
The query processor serves multiple functions: it processes high-level user queries, translates them into vendor-specific query expressions for different data warehouses, and manages connections to multiple data sources. This multi-functionality consolidates what would otherwise be separate complex systems into a single unified interface, reducing overall system complexity while maintaining multi-platform access capability.
3Adaptability or versatility
If multiple query expressions are defined for different data warehouses, then comprehensive data retrieval is possible, but time consumption increases
Solution Approach 1:
The system performs preliminary actions by pre-establishing connections to data warehouses and pre-compiling vendor-specific query expressions. When a user submits a query, the query processor can quickly translate and execute without needing to establish connections or compile queries in real-time, thereby reducing query execution time while maintaining comprehensive data retrieval capability.
Data Source
AI summary
A data query and retrieval system receives a query request and identifies a plurality of data warehouses, or stores, where data needed to satisfy the query is stored. Data items, or fields, called for by the query request are grouped into sets based on fields stored at the same data warehouse. Individual data connections are generated for each data warehouse accessed, and a database view defined for the fields available at each respective data warehouse. The individual views are invoked in a single query statement by a query processor for accessing the respective data warehouses via the corresponding database view. In this manner, a query operator or user need not define multiple query expressions or perform separate queries for accessing data fields or dimensions residing at different data warehouses.