Preferred Aggregate Store for Multidimensional Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional approaches to executing datacube queries often store aggregates in sub-optimal locations, leading to increased storage and processing costs, as well as access issues due to network distance and write accessibility limitations.
Innovation Solution
Designating a preferred aggregate store that is known to have favorable access and proximity to the requesting user, thereby directing aggregate creation to this location to minimize costs and access failures.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If aggregates are stored in available storage volumes based on data set locations, then storage simplicity is maintained, but query performance deteriorates due to sub-optimal access paths and increased network distance
Solution Approach 1:
The system performs preliminary actions by designating a preferred aggregate store in advance and predicting which aggregates will be needed for future queries. This allows aggregates to be pre-computed and stored in optimal locations before they are actually needed, improving query response time without adding complexity during query execution.
Solution Approach 2:
The query planner acts as an intermediary between the user's query request and the physical data storage locations. It designates a preferred aggregate store and determines optimal storage locations for intermediate aggregates, mediating between the need for fast query performance and the complexity of distributed data management.
2Loss of energy
If aggregates are stored in remote data warehouse locations, then data consistency is maintained, but access costs increase due to network distance and storage fees
Solution Approach 1:
The system applies local quality by designating a preferred aggregate store with favorable access characteristics for each query workload. Instead of using a uniform storage location for all aggregates, the query planner identifies and uses locally optimal storage locations that minimize network distance and access costs for specific query patterns.
Solution Approach 2:
The system changes the parameter of aggregate storage location from fixed (data warehouse location) to variable (preferred aggregate store designation). The query planner dynamically determines optimal storage parameters based on query characteristics, data location, and access patterns, reducing both costs and failure risks.
3Loss of time
If aggregates are created for current query execution, then immediate query satisfaction is achieved, but future query performance optimization is lost
Solution Approach 1:
The system performs preliminary actions by predicting which aggregates will be needed for future queries and creating them in advance at the preferred aggregate store. This preliminary creation of aggregates optimizes future query performance without impacting current query execution, as the prediction and creation occur separately from the immediate query processing.
Solution Approach 2:
The system segments aggregate creation into two distinct phases: (1) prediction of future aggregate needs based on query patterns, and (2) actual creation and storage of predicted aggregates. This segmentation allows current queries to execute immediately while future queries benefit from pre-computed aggregates, resolving the conflict between immediate and future performance.
Data Source
AI summary
A preferred aggregate store for a multidimensional database receives and stores intermediate data generated as part of a larger query for avoiding repetitive or redundant data retrievals incurring costly data paths. Aggregates include intermediate tables and sets of data, often generated from a join of two or more distinct data tables, combined for generating a query result. In the event an aggregate is called for, certain storage locations may be preferable to others due to network distance, fees for storage and processing, as in the case of cloud storage, or write accessibility. Designation of a preferred aggregate store ensures that aggregates called for by the query processing are stored in a beneficial location for cost and efficiency.