Data Warehouse Materialized View Query Routing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current data warehouse frameworks lack high performance reporting capabilities without requiring the investment of the ETL process, leading to suboptimal performance in retrieving and reporting information.
Innovation Solution
A computer-implemented method that determines the presence and freshness of materialized views within a data warehouse, translating queries to utilize materialized views when available and relevant, or sending them to original data storage if not, thereby bypassing the need for ETL processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If materialized views are used to improve query performance, then reporting speed is improved, but system complexity increases due to view management and validation
Solution Approach 1:
The patent introduces a materialized view as an intermediary structure between the original data storage and the query processing system. This materialized view pre-computes and stores aggregated results, acting as a mediator that speeds up query processing while the validation mechanism ensures data integrity without requiring full ETL processes.
Solution Approach 2:
The system performs preliminary actions by pre-computing and materializing query results in advance. The materialized view is created and validated before actual query execution, so when queries are run, they can directly utilize the pre-computed results, significantly improving performance without repeating expensive computation operations.
2Reliability
If ETL processes are implemented to ensure data quality, then data reliability is improved, but processing time and resource consumption increase
Solution Approach 1:
Instead of implementing complete ETL processes, the patent applies partial validation actions. The system validates only the necessary aspects of data quality relevant to the materialized view, performing sufficient validation to ensure reliability without the overhead of comprehensive ETL processing for all data aspects.
3Loss of information
If materialized views are frequently updated to maintain freshness, then data currency is improved, but computational overhead increases
Solution Approach 1:
The system implements periodic validation and updates of materialized views rather than continuous updates. The validation occurs at scheduled intervals or triggered by specific events, maintaining data freshness while avoiding the constant computational overhead of continuous synchronization and validation processes.
Data Source
AI summary
A computer-implemented method, system and computer program product for improving performance for retrieving and reporting information from a data warehouse. A query (e.g., SQL query) is received. If a materialized view is present, and if such a materialized view was generated within the designated time frame, then the query is translated to query a materialized view. The translated query is then sent to a first data storage database of the data warehouse storing materialized views to be processed to obtain the materialized object associated with the query. If, on the other hand, the materialized view is not present, or the materialized view is present but has not been generated within the designated time frame, then the query is sent to the second data storage database of the data warehouse storing the original data without any modification to retrieve and report the appropriate information from the second data storage database.