Federated Query Acceleration Using Regenerated Acceleration Tables
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 data analytics system with a middle layer that provides long-term table data storage and periodically regenerates acceleration formats, utilizing a query engine to process queries using a federated approach, optimizing data processing and reducing the computational load on the server.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is extracted, transformed, and loaded from enterprise software applications into data warehouses using traditional methods, then data analytics can be performed, but the process is time and resource intensive
Solution Approach 1:
The system pre-generates acceleration tables from the middle layer data storage in advance, so that when queries are executed, the data is already prepared and optimized for fast retrieval. This preliminary preparation eliminates the need for time-consuming data transformation and loading during query execution, directly resolving the ETL time consumption problem.
Solution Approach 2:
The patent introduces an acceleration table as an intermediary structure between the middle layer data storage and the query engine. This acceleration table serves as a mediator that pre-processes and optimizes data for querying, reducing the computational burden on the query engine and eliminating the need for repeated ETL operations during query execution.
2Productivity
If traditional data extraction and transformation methods are used, then data can be loaded into data warehouses, but the computational load on the server is high
Solution Approach 1:
The system performs data transformation and optimization in advance by generating acceleration tables from the middle layer storage. This preliminary action shifts the computational workload from query-time processing to offline table generation, significantly reducing the server's computational load during actual query execution while maintaining high query processing speed.
Solution Approach 2:
The patent creates acceleration tables as optimized copies of the underlying data from the middle layer. These copies are pre-processed and stored in a format optimized for querying, allowing the query engine to work with lightweight copies rather than processing raw data, thereby reducing computational load while maintaining processing speed.
3Speed
If acceleration tables are generated from the middle layer, then query performance is enhanced, but additional storage and regeneration processes are required
Solution Approach 1:
The acceleration table serves as an intermediary layer between the middle layer storage and the query engine. This intermediary structure simplifies the query processing architecture by providing a pre-optimized data format that the query engine can directly utilize, enhancing query execution speed while adding only one additional layer to the existing architecture.
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.