Data Query Method Using Pre-aggregated Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current data query methods based on relational online analytical processing (ROLAP) face challenges with slow response speeds due to substantial pre-aggregation calculation and large data storage requirements, as they store numerous summary tables for various combinations of dimensions.
Innovation Solution
A data query method that determines a target physical table from a set of physical tables, including a basic table and a pre-aggregated table, based on the data query request, allowing for reduced pre-aggregation calculation and efficient storage by recording data of multiple granularities in a single pre-aggregated table.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If pre-aggregation calculation is performed according to all combinations of dimensions, then data query accuracy is improved, but calculation amount increases substantially
Solution Approach 1:
The patent performs pre-aggregation calculations in advance and stores the results in a pre-aggregated table, so that when a data query request comes in, the pre-computed results can be directly used without performing substantial calculations again. This resolves the contradiction by doing the heavy calculation work beforehand (preliminary action) so that actual query operations become fast and accurate without repeating the substantial calculation.
Solution Approach 2:
The patent maintains both a basic table with detailed data and a pre-aggregated table with summary data. For different query scenarios, the system can selectively use either the basic table or the pre-aggregated table, or combine both. This partial action approach allows the system to use pre-aggregated data when appropriate to avoid substantial calculations, while still having access to detailed data when needed, thus balancing query accuracy with calculation reduction.
2Loss of information
If summary tables are stored for all combinations of dimensions, then data query completeness is improved, but data storage amount increases
Solution Approach 1:
The patent merges the functionality of multiple summary tables (for different dimension combinations) into a single pre-aggregated table that can serve multiple query scenarios. This pre-aggregated table is designed to answer various dimension combination queries without requiring separate tables for each combination, thus reducing data storage amount while maintaining data query completeness through proper table design and metadata management.
Solution Approach 2:
The pre-aggregated table is designed with universal structure and metadata that enables it to serve multiple dimension combination query scenarios simultaneously. Rather than creating specialized tables for each dimension combination, this single table structure can handle various query types through its flexible schema and associated metadata, reducing the need for multiple tables while maintaining complete query capabilities.
3Adaptability or versatility
If multiple physical tables are stored in the database, then data query flexibility is improved, but the number of tables increases
Solution Approach 1:
The patent segments the database into two distinct parts: a basic table for detailed data and a pre-aggregated table for summary data. This segmentation allows the system to maintain flexibility by choosing which table to use based on query requirements, while avoiding the complexity of having numerous intermediate tables. The metadata layer provides the flexibility to map different query scenarios to the appropriate table structure.
Solution Approach 2:
The patent introduces metadata as an intermediary layer between the query request and the physical tables. The metadata stores information about the pre-aggregated table structure, dimension combinations, and aggregation logic. This intermediary enables the system to maintain data query flexibility by interpreting and routing queries appropriately without requiring the physical tables themselves to be highly complex or numerous.
4Measurement precision
If pre-aggregation calculation is performed for all dimension combinations, then query response accuracy is improved, but response speed decreases
Solution Approach 1:
The patent performs pre-aggregation calculations in advance and stores the results in a pre-aggregated table, so that when a data query request comes in, the pre-computed results can be directly used without performing substantial calculations again. This resolves the contradiction by doing the heavy calculation work beforehand (preliminary action) so that actual query operations become fast and accurate without repeating the substantial calculation.
Data Source
AI summary
A data query method is provided. A data query request for a target data model is obtained. Based on the data query request and target metadata corresponding to the target data model, a target physical table corresponding to the data query request is determined from a plurality of physical tables corresponding to the target data model. The target metadata is determined based on the plurality of physical tables. The plurality of physical tables includes a basic table and a pre-aggregated table. The basic table is configured to record basic data of one granularity, and the pre-aggregated table is configured to record data of a plurality of granularities. Target query data corresponding to the data query request is obtained based on the data query request and the target physical table.