Aggregate Join Index Hierarchical Roll-Up for Query Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional OLAP approaches face scalability challenges and high maintenance overhead due to the need for extra storage and query computation, especially when dealing with large data volumes and complex queries that require aggregate data at lower levels of aggregation.
Innovation Solution
The partial covering method utilizes functional dependencies among columns in dimension tables to enable the use of aggregate join indexes (AJIs) at higher levels of aggregation, allowing for automatic roll-up and joining with dimension tables to satisfy queries, thereby reducing storage and computation overhead.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If aggregate join indexes (AJIs) are used at the lowest level of aggregation to answer queries, then query response time is improved, but storage overhead and maintenance complexity increase
Solution Approach 1:
The patent introduces a new dimension to the traditional AJI structure by creating a hierarchical multi-level AJI system. Instead of using a single flat AJI at the lowest aggregation level, the invention organizes AJIs into multiple hierarchical levels that mirror the dimension hierarchy in the data warehouse. This dimensional transformation allows queries to be answered from the highest possible level in the hierarchy, minimizing storage requirements while maintaining fast response times through selective use of pre-computed aggregates at optimal hierarchical levels.
2Productivity
If AJIs are materialized in table format for fast query response, then query performance is improved, but maintenance overhead increases
Solution Approach 1:
The patent implements a dynamic multi-level AJI maintenance strategy where the system automatically determines which hierarchical levels require materialization based on query patterns and data characteristics. Rather than statically maintaining all AJI levels or only the lowest level, the invention dynamically adjusts the materialization strategy, maintaining AJIs at multiple hierarchical levels only when beneficial. This dynamic approach reduces maintenance overhead while preserving query performance through selective materialization at optimal levels in the hierarchy.
3Speed
If precalculated aggregate data is stored using multi-dimension arrays (MOLAP), then query performance is improved, but scalability is limited
Solution Approach 1:
The patent creates a universal multi-level AJI framework that combines the advantages of both MOLAP and ROLAP approaches. The hierarchical AJI structure can function as pre-computed aggregate tables (like MOLAP) for fast query response, while simultaneously maintaining compatibility with relational database systems (like ROLAP) for scalability. By organizing AJIs in a hierarchical structure that mirrors dimension relationships, the invention enables the system to scale with large data volumes while still providing fast query performance through selective use of pre-computed aggregates at multiple hierarchical levels.
Data Source
AI summary
A system and method include obtaining a query and identifying an aggregate join index (AJI) at a high level of aggregation. The dimension table may be rolled-up with the grouping key being the union of the grouping key in the AJI and the grouping key of the query. The identified AJI is joined with the rolled-up dimension table to obtain columns in the query that are not in the identified AJI. The joined AJI and rolled-up dimension table are then rolled up to answer the query.