Query Acceleration Tables for Faster Cloud Analytics ETL
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data analytics systems face challenges in efficiently extracting, transforming, and loading data from enterprise software applications into data warehouses, which is both time and resource intensive, particularly in cloud and SaaS environments.
Innovation Solution
A system and method that includes a middle layer for data storage and periodic regeneration of acceleration tables, allowing for the use of existing accelerated tables to process queries, thereby optimizing data extraction, transformation, and loading processes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is extracted, transformed, and loaded from enterprise software applications into data warehouses using existing ETL processes, then data analytics can be performed, but the process is time and resource intensive
Solution Approach 1:
The system pre-computes and stores acceleration tables in the data warehouse that contain pre-aggregated and pre-processed data. These acceleration tables are generated in advance based on common query patterns and data models, so that when queries are executed, the system can retrieve pre-processed data directly without performing time-consuming ETL operations each time, thus reducing query execution time and resource consumption
Solution Approach 2:
The system creates copies of data in optimized formats (acceleration tables) that replicate the source data but in a pre-processed state suitable for rapid querying. These acceleration tables are regenerated periodically from the source data, providing a reusable copy that eliminates the need for repeated ETL processing during query execution
2Speed
If acceleration tables are periodically regenerated from the middle layer, then query performance is improved, but regeneration time and resource consumption increase
Solution Approach 1:
The system implements periodic regeneration of acceleration tables at scheduled intervals rather than continuous regeneration. This allows the system to balance query performance needs with resource consumption by regenerating tables only when necessary (e.g., after data loads or schema changes), using a configuration parameter to control the regeneration frequency and timing
Solution Approach 2:
The system pre-regenerates acceleration tables during off-peak hours or in advance of expected data changes, so that when queries are executed, the tables are already ready for rapid access. The regeneration process is scheduled to complete before peak query periods, ensuring minimal impact on overall system performance
Data Source
AI summary
In accordance with an embodiment, described herein is a system and method for providing query acceleration with a computing environment such as, for example, a business intelligence environment, database, data warehouse, or other type of environment that supports data analytics. A middle layer is provided as a long-term table data storage format; and one more acceleration formats, or acceleration tables, can be periodically regenerated from the middle layer, wherein a determination can be made as to whether an accelerated table exists for a dataset table, and if so, then the accelerated table is used to process the query.