Eager Aggregation in Star Schema Query Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Distributed databases with star or snowflake schemas face inefficiencies in query processing due to high data volumes and costly joins, leading to latency and impractical computation costs, especially in Business Intelligence applications with high concurrency.
Innovation Solution
The implementation of eager aggregation and analytical view matching, where aggregation operations are moved before joins and pre-computed analytical views are used to optimize query plans, reducing the number of input rows and leveraging pre-aggregated data for faster processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional query processing is used with star schema, then data can be stored in distributed database, but query computation costs become prohibitively high and latency increases
Solution Approach 1:
The system pre-materializes analytical views that aggregate data from fact tables before queries are executed. By performing aggregation operations in advance and storing results in analytical views, the system eliminates the need to recompute joins and aggregations for each query, thereby reducing query latency and improving processing speed.
Solution Approach 2:
The system creates copies of aggregated data by materializing analytical views that store pre-computed aggregate results. Instead of computing aggregates fresh for each query, the system queries pre-computed copies stored in analytical views, significantly reducing query computation time and latency.
2Productivity
If pre-materialization (cubes or analytical views) is used, then query response time improves, but initial computation cost and maintenance latency increase
Solution Approach 1:
The system dynamically determines when to materialize analytical views based on query patterns and data changes. Instead of statically pre-computing all possible aggregates, the system adapts to actual query needs and data modifications, materializing only when necessary and updating incrementally to balance computation costs with query performance.
Solution Approach 2:
The system changes the parameters of aggregation by allowing flexible grouping and measure selections in analytical view definitions. This enables the system to adapt aggregation granularity and scope based on query requirements, optimizing the balance between pre-computation costs and query response times.
3Adaptability or versatility
If joins are performed on fact tables with high concurrency, then data can be accessed, but computation resources are exhausted and system becomes unresponsive
Solution Approach 1:
By pre-materializing analytical views that aggregate fact table data, the system performs computation in advance during low-concurrency periods. This allows the system to handle high-concurrency query workloads without exhausting computation resources, as the heavy aggregation work is already done before queries arrive.
Solution Approach 2:
The system creates copies of aggregated data in analytical views that can be queried independently of the original fact tables. This copying approach allows concurrent queries to access pre-computed aggregates without competing for computation resources on the fact tables, supporting high concurrency while controlling resource consumption.
Data Source
AI summary
A system comprises a computer network and worker machines connected to the computer network. The worker machines store partitions of a distributed database. A master machine is connected to the computer network. The master machine includes a query processor to identify a star query that references a fact table and related dimension tables that characterize attributes of facts in the fact table. Eager aggregation is applied to a query plan associated with the star query. The eager aggregation alters the query plan by moving an aggregation operation before a join operation to form an eager aggregated query plan. An analytical view with data responsive to the eager aggregated query plan is identified. The eager aggregated query plan is revised to form a final query plan. The final query plan references the analytical view. The final query plan is executed to produce query results.