multi-dimensional aggregation

Abstract

a b s t r a c tThe SQL:2003 standard introduced window functions to enhance the analytical processing capabilities of SQL. The key concept of window functions is to sort the input relation and to compute the aggregate results during a scan of the sorted relation. For multi-dimensional OLAP queries with aggregation groups defined by a general y condition an appropriate ordering does not exist, though, and hence expensive join-based solutions are required.In this paper we introduce y-constrained multi-dimensional aggregation (y-MDA), which supports multi-dimensional OLAP queries with aggregation groups defined by inequalities.y-MDA is not based on an ordering of the data relation. Instead, the tuples that shall be considered for computing an aggregate value can be determined by a general y condition.This facilitates the formulation of complex queries, such as multi-dimensional cumulative aggregates, which are difficult to express in SQL because no appropriate ordering exists. We present algebraic transformation rules that demonstrate how the y-MDA interacts with other operators of a multi-set algebra. Various techniques for achieving an efficient evaluation of the y-MDA are investigated, and we integrate them into concrete evaluation algorithms and provide cost formulas. An empirical evaluation with data from the TPC-H benchmark confirms the scalability of the y-MDA operator and shows performance improvements of up to one order of magnitude over equivalent SQL implementations.

Efficient Evaluation of Ad-Hoc Range Aggregates

“…The support is based on the ordering of tuples. Currently, SQL does not support the efficient evaluation of range aggregates over multiple dimensions [1].…”

“…The θ-constrained multi-dimensional aggregation (θ-MDA) operator [1] extends the MDJ and presents a detailed cost model together with algebraic transformation rules. θ-MDA outperforms SQL for complex multi-dimensional aggregation queries, such as range aggregates over multiple dimensions.…”

“…To efficiently process such queries, various techniques have been proposed, including extensions to SQL [7], generalized projections [8], pre-aggregation of data cubes [9], and (relative) prefix sums [6,10]. θ-MDA [1] is a flexible and efficient operator for ad-hoc multi-dimensional aggregation queries, where pre-computed aggregates are not available and a scan of the detail table is required.…”

“…For instance, aggregate x 3 .C2 is the average duration of a stay over all detail tuples in stays with D ≤ 01/02/13 and U ≤ 2, i.e., the tuples {r 1 , r 2 , r 4 , r 5 , r 6 , r 7 , r 8 }. While one-dimensional range aggregates can be solved efficiently with SQL window functions, multi-dimensional range aggregates require expensive joins with range predicates [1].…”

“…The θ-MDA operator [1] offers a succinct formulation and efficient computation of ad-hoc multi-dimensional aggregation queries. The operator separates the specification of the aggregation groups in a base table b (first two columns in table x in Fig.…”