UZH-Logo

theta-Constrained multi-dimensional aggregation


Akinde, Michael O; Böhlen, Michael H; Chatziantoniou, Damianos; Gamper, Johann (2011). theta-Constrained multi-dimensional aggregation. Information Systems, 36(2):341-358.

Abstract

The 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 θ condition an appropriate ordering does not exist, though, and hence expensive join-based solutions are required.In this paper we introduce θ-constrained multi-dimensional aggregation (θ-MDA), which supports multi-dimensional OLAP queries with aggregation groups defined by inequalities. θ-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 θ 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 θ-MDA interacts with other operators of a multi-set algebra. Various techniques for achieving an efficient evaluation of the θ-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 θ-MDA operator and shows performance improvements of up to one order of magnitude over equivalent SQL implementations.Keywords: OLAP; SQL/OLAP; Window functions; Multi-dimensional aggregation

The 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 θ condition an appropriate ordering does not exist, though, and hence expensive join-based solutions are required.In this paper we introduce θ-constrained multi-dimensional aggregation (θ-MDA), which supports multi-dimensional OLAP queries with aggregation groups defined by inequalities. θ-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 θ 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 θ-MDA interacts with other operators of a multi-set algebra. Various techniques for achieving an efficient evaluation of the θ-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 θ-MDA operator and shows performance improvements of up to one order of magnitude over equivalent SQL implementations.Keywords: OLAP; SQL/OLAP; Window functions; Multi-dimensional aggregation

Altmetrics

Downloads

56 downloads since deposited on 09 Feb 2012
9 downloads since 12 months
Detailed statistics

Additional indexing

Item Type:Journal Article, refereed, original work
Communities & Collections:03 Faculty of Economics > Department of Informatics
Dewey Decimal Classification:000 Computer science, knowledge & systems
Language:English
Date:2011
Deposited On:09 Feb 2012 11:26
Last Modified:05 Apr 2016 15:27
Publisher:Elsevier
ISSN:0306-4379 (P) 1873-6076 (E)
Publisher DOI:10.1016/j.is.2010.07.005
Other Identification Number:merlin-id:2494
Permanent URL: http://doi.org/10.5167/uzh-56412

Download

[img]
Content: Published Version
Filetype: PDF - Registered users only
Size: 580kB
View at publisher
[img]
Preview
Content: Accepted Version
Filetype: PDF
Size: 237kB

TrendTerms

TrendTerms displays relevant terms of the abstract of this publication and related documents on a map. The terms and their relations were extracted from ZORA using word statistics. Their timelines are taken from ZORA as well. The bubble size of a term is proportional to the number of documents where the term occurs. Red, orange, yellow and green colors are used for terms that occur in the current document; red indicates high interlinkedness of a term with other terms, orange, yellow and green decreasing interlinkedness. Blue is used for terms that have a relation with the terms in this document, but occur in other documents.
You can navigate and zoom the map. Mouse-hovering a term displays its timeline, clicking it yields the associated documents.

Author Collaborations