When is it time to rethink the aggregate configuration of your OLAP server?

Katja Hose, Daniel Klan, Matthias Marx, Kai-Uwe SattlerPVLDB 2008 · DOI

TL;DR

This paper introduces an "alerter" system for OLAP servers that continuously monitors query workloads and proactively suggests when to reconfigure materialized aggregate tables for better performance. By operating at the OLAP query level (MDX) rather than SQL, it simplifies analysis and offers a more dynamic approach to physical database design tuning.

What Problem Does This Paper Address?

Online Analytical Processing (OLAP) servers are crucial for data warehousing, enabling complex analytical queries. A common technique to speed up these queries is the use of materialized aggregate tables. These are pre-computed summaries of data that significantly reduce query response times. However, choosing the optimal set of these aggregate tables, known as the view selection problem, is computationally challenging (NP-complete).

Existing commercial OLAP systems offer tools to recommend aggregate tables, but these are typically static. They require manual intervention and re-running after data or workload changes. This is akin to static index tuning, while other areas like index tuning have moved towards more autonomous, online approaches. The paper addresses the need for a dynamic, continuous system that alerts users or DBAs when the current aggregate configuration is no longer optimal for the observed workload, prompting a potential reconfiguration.

Methods

The core of the proposed solution is a lightweight alerter approach integrated directly into the OLAP server (specifically, the Mondrian OLAP server in their implementation). This approach has two main components:

  1. Aggregation Lattice: This data structure represents all possible aggregation nodes (materialized or not) and their derivability relationships. Unlike static approaches, this lattice is built incrementally as queries are processed.
  2. Cost Matrix: This matrix stores the estimated costs of processing each intercepted query against each aggregation node.

The system works by:

  • Intercepting MDX Queries: Every query is captured.
  • Dynamic Lattice Construction: For each query, the corresponding aggregation node is added to the lattice if it doesn't exist.
  • Cost Estimation: The system estimates the cost of processing the query on various aggregation tables. This is done by leveraging the OLAP server's ability to directly measure query execution costs on base tables and then estimating costs on aggregate tables based on cardinality, tuple size, and other factors.
  • Benefit Calculation: The benefit of an aggregate table is calculated based on the cost savings it provides for queries that can utilize it.
  • Alerter Trigger: The alerter is triggered by changes in query patterns (e.g., query counter changes) or when new aggregation nodes are added.
  • Configuration Suggestion: When triggered, the alerter evaluates minimal changes to the current configuration (e.g., adding a single new aggregate table) and identifies a new configuration that offers the highest improvement in overall benefit. This suggested configuration is presented to the user with a quantified alert indicating the expected cost reduction.

A key novelty is performing view selection at the MDX query level instead of the SQL level. This simplifies containment checks and query rewriting by leveraging the semantic understanding of multidimensional queries.

Datasets

Not discussed in the paper. The paper focuses on a system that operates on arbitrary workloads and uses its own internal monitoring and cost estimation mechanisms.

Key Insights and Ideas

  • OLAP-Level Tuning is Simpler and More Powerful: By operating at the MDX level, the system can leverage multidimensional schema information (like measure attributes and aggregation levels) that is not directly available at the SQL level. This simplifies analysis and allows for more effective query rewriting.
  • Proactive "Alerter" Model: Instead of requiring users to constantly re-optimize, the system acts as an intelligent assistant, notifying them when a change is beneficial. This shifts from a reactive to a proactive approach for physical database design.
  • Lightweight Monitoring for Dynamic Workloads: The system's ability to continuously monitor and adapt to changing workloads is crucial for real-world scenarios where query patterns are not static.
  • Bridging the Gap for Non-Materialized-View DBMS: The OLAP-level approach allows systems that don't natively support materialized views (like many open-source RDBMS) to still benefit from aggregate table optimization.

Key Results

The paper presents a system architecture and a demonstration of its functionality. While specific quantitative results on performance improvements are not detailed in terms of percentages or speedups, the core result is the successful implementation and demonstration of a dynamic, workload-driven alerter for OLAP aggregate configuration. The demonstration shows how the system visualizes the aggregation lattice, cost matrix, and alerts the user with a gauge indicating the potential benefit of a new configuration. The system can suggest a "fast but not optimal" configuration based on immediate alerts or a more exhaustive optimization.

Limitations and Future Work

  • Cost of Reoptimization: While the alerter is lightweight, the paper acknowledges that determining the optimal set of aggregation tables is still an expensive process. The alerter aims to trigger this expensive reoptimization only when necessary.
  • Static vs. Dynamic Configuration: The alerter suggests changes, but the final decision to reconfigure rests with the user. The paper mentions strategies for dealing with materialization costs and workload drifts, implying ongoing research into more autonomous tuning.
  • Future Work: The authors suggest exploring more sophisticated strategies for handling materialization costs and workload drifts. They also hint at the possibility of integrating this approach with other physical design tuning aspects. The demonstration itself is a key part of their ongoing work, showcasing the system's practical application.

Summary updated

BibTeX
@article{hose2008when,
  author = {Katja Hose and Daniel Klan and Matthias Marx and Kai-Uwe Sattler},
  title = {{When is it time to rethink the aggregate configuration of your OLAP server?}},
  journal = {Proc. VLDB Endow.},
  year = {2008},
  doi = {10.14778/1454159.1454210},
}
Figure