Spreadsheets in RDBMS for OLAP

Witkowski, Andrew; Bellamkonda, Srikanth; Bozkaya, Tolga; Dorman, Gregory; Folkert, Nathan; Gupta, Abhinav; Shen, Lei; Sambandam, Sankar

doi:10.1145/872766.872767

Cited by 16 publications

(13 citation statements)

References 0 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…The importance of correlated stream aggregates as well as methods to evaluate these have been presented in [16]. Using the spreadsheet paradigm for OLAP over databases has been identified as a valuable query language ( [21], [22]). Recently, a new programming paradigm called MapReduce has attracted a lot of attention due to its simplicity and efficiency ( [14]).…”

Section: Background and Related Workmentioning

confidence: 99%

COSTES: Continuous spreadsheet-like computations

Chatziantoniou¹,

Pramatari²,

Sotiropoulos³

2008

2008 IEEE 24th International Conference on Data Engineering Workshop

View full text Add to dashboard Cite

AutoID technology has been extensively used for a diversity of applications ranging from access control systems to airport baggage handling, livestock management systems, automated toll collection, theft-prevention, and automated production systems. Being able to efficiently perform complex real-time analysis on top of streams of RFID events is a key challenge. This will provide management with a novel data analysis mechanism to allow better, tactical, on time, wellinformed decision-making. Challenges involve both syntactic and evaluation issues. In this paper we propose a set of linguistic requirements for RFID data management (RFDM) queries and describe an SQL-like construct to address these. We argue that a large and useful class of (potentially complex) RFDM queries can be easily expressed and efficiently evaluated using this formality: start from a relational expression and successively extend it with columns representing aggregates over dynamically updated ordered sets. This incremental construction of a query is similar to formula definitions in spreadsheets. We present a fully functional prototype called COSTES (COntinuous SpreadsheeTlikE computationS) implementing spreadsheet-like queries and discuss its architecture and performance. We describe our system in the context of a supply chain management application.

show abstract

Section: Background and Related Workmentioning

confidence: 99%

COSTES: Continuous spreadsheet-like computations

Chatziantoniou¹,

Pramatari²,

Sotiropoulos³

2008

2008 IEEE 24th International Conference on Data Engineering Workshop

View full text Add to dashboard Cite

show abstract

“…Our work goes beyond by combining pivoting and aggregating, which automates an essential task in OLAP and Data Mining applications. SQL extensions to perform spreadsheet-like operations with array capabilities are introduced in [8]. Those extensions are not adequate to compute percentage aggregations because they have the purpose of avoiding joins to express formulas, but are not optimized to handle two-level aggregations or perform transposition.…”

Section: Related Approachesmentioning

confidence: 99%

Vertical and horizontal percentage aggregations

Ordóñez

2004

Proceedings of the 2004 ACM SIGMOD International Conference on Management of Data

View full text Add to dashboard Cite

Existing SQL aggregate functions present important limitations to compute percentages. This article proposes two SQL aggregate functions to compute percentages addressing such limitations. The first function returns one row for each percentage in vertical form like standard SQL aggregations. The second function returns each set of percentages adding 100% on the same row in horizontal form. These novel aggregate functions are used as a framework to introduce the concept of percentage queries and to generate efficient SQL code. Experiments study different percentage query optimization strategies and compare evaluation time of percentage queries taking advantage of our proposed aggregations against queries using available OLAP extensions. The proposed percentage aggregations are easy to use, have wide applicability and can be efficiently evaluated.

show abstract

“…There has been work following this direction to cluster gene data [17], with basically the same idea. Some SQL extensions to perform spreadsheet-like operations were introduced in [24]. Those extensions have the purpose of avoiding joins to express formulas, but are not optimized to perform partial transposition for each group of result rows.…”

Section: Related Workmentioning

confidence: 99%

Horizontal aggregations for building tabular data sets

Ordóñez

2004

Proceedings of the 9th ACM SIGMOD Workshop on Research Issues in Data Mining and Knowledge Discovery

View full text Add to dashboard Cite

In a data mining project, a significant portion of time is devoted to building a data set suitable for analysis. In a relational database environment, building such data set usually requires joining tables and aggregating columns with SQL queries. Existing SQL aggregations are limited since they return a single number per aggregated group, producing one row for each computed number. These aggregations help, but a significant effort is still required to build data sets suitable for data mining purposes, where a tabular format is generally required. This work proposes very simple, yet powerful, extensions to SQL aggregate functions to produce aggregations in tabular form, returning a set of numbers instead of one number per row. We call this new class of functions horizontal aggregations. Horizontal aggregations help building answer sets in tabular form (e.g. point-dimension, observation-variable, instancefeature), which is the standard form needed by most data mining algorithms. Two common data preparation tasks are explained, including transposition/aggregation and transforming categorical attributes into binary dimensions. We propose two strategies to evaluate horizontal aggregations using standard SQL. The first strategy is based only on relational operators and the second one uses the "case" construct. Experiments with large data sets study the proposed query optimization strategies.

show abstract

Spreadsheets in RDBMS for OLAP

Cited by 16 publications

References 0 publications

COSTES: Continuous spreadsheet-like computations

COSTES: Continuous spreadsheet-like computations

Vertical and horizontal percentage aggregations

Horizontal aggregations for building tabular data sets

Contact Info

Product

Resources

About