Abstract
SQL has attained widespread adoption, but Business Intelligence tools still use their own higher level languages based upon a multidimensional paradigm. Composable calculations are what is missing from SQL, and we propose a new kind of column, called a measure, that attaches a calculation to a table. Like regular tables, tables with measures are composable and closed when used in queries.
SQL-with-measures has the power, conciseness and reusability of multidimensional languages but retains SQL semantics. Measure invocations can be expanded in place to simple, clear SQL.
To define the evaluation semantics for measures, we introduce context-sensitive expressions (a way to evaluate multidimensional expressions that is consistent with existing SQL semantics), a concept called evaluation context, and several operations for setting and modifying the evaluation context.
References
[1]
William D Back, Nicholas Goodman, and Julian Hyde. 2013. Mondrian in Action: Open source business analytics. Manning Publications Company.
[2]
Edmon Begoli, Jesús Camacho-Rodríguez, Julian Hyde, Michael J Mior, and Daniel Lemire. 2018. Apache Calcite: A Foundational Framework for Optimized Query Processing Over Heterogeneous Data Sources. In Proceedings of the 2018 International Conference on Management of Data. ACM, 221--230. https://doi.org/10.1145/3183713.3190662
[3]
E. F. Codd. 1970. A relational model of data for large shared data banks. Commun. ACM 13, 6 (jun 1970), 377--387. https://doi.org/10.1145/362384.362685
[4]
Edgar F Codd. 1993. Beyond decision support. Computerworld (1993).
[5]
George Colliat. 1996. OLAP, relational, and multidimensional database systems. ACM Sigmod Record 25, 3 (1996), 64--69.
[6]
Kedar Dhamdhere, Kevin S. McCurley, Ralfi Nahmias, Mukund Sundararajan, and Qiqi Yan. 2017. Analyza: Exploring Data with Conversation. In Proceedings of the 22nd International Conference on Intelligent User Interfaces (Limassol, Cyprus) (IUI '17). Association for Computing Machinery, New York, NY, USA, 493--504. https://doi.org/10.1145/3025171.3025227
[7]
Google. 2023. Looker Open SQL interface. https://cloud.google.com/looker/docs/sql-interface. [Online; accessed 01-Apr-2024].
[8]
Google. 2024. Looker. https://cloud.google.com/looker. [Online; accessed 12-Apr-2024].
[9]
Jim Gray, Surajit Chaudhuri, Adam Bosworth, Andrew Layman, Don Reichart, Murali Venkatrao, Frank Pellow, and Hamid Pirahesh. 1997. Data cube: A relational aggregation operator generalizing group-by, cross-tab, and sub-totals. Data mining and knowledge discovery 1 (1997), 29--53.
[10]
John Horner, Il-Yeol Song, and Peter P. Chen. 2004. An analysis of additivity in OLAP systems. In Proceedings of the 7th ACM International Workshop on Data Warehousing and OLAP (Washington, DC, USA) (DOLAP '04). Association for Computing Machinery, New York, NY, USA, 83--91. https://doi.org/10.1145/1031763.1031779
[11]
Julian Hyde. 2021. WITHIN DISTINCT clause for aggregate functions. Feature request CALCITE-4483. Apache Calcite. https://issues.apache.org/jira/browse/CALCITE-4483
[12]
Julian Hyde. 2022. Custom time frames. Feature request CALCITE-5155. Apache Calcite. https://issues.apache.org/jira/browse/CALCITE-5155
[13]
JSR-69 2003. Java? OLAP Interface (JOLAP), final draft. Technical Report. JSR-69 Expert Group. https://jcp.org/aboutJava/communityprocess/first/jsr069/index.html
[14]
Ralph Kimball and Margy Ross. 2002. The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (2nd ed.). John Wiley & Sons, Inc., USA.
[15]
Bart Kuijpers and Alejandro Vaisman. 2017. An algebra for OLAP. Intelligent Data Analysis 21, 5 (2017), 1267--1300.
[16]
MDAPI-2.0 1998. MDAPI the OLAP Application Program Interface Version 2.0. Technical Report. The OLAP Council.
[17]
Konstantinos Morfonios, Stratis Konakas, Yannis Ioannidis, and Nikolaos Kotsis. 2007. ROLAP implementations of the data cube. ACM Comput. Surv. 39, 4 (nov 2007), 12--es. https://doi.org/10.1145/1287620.1287623
[18]
Oscar Romero and Alberto Abelló. 2007. On the Need of a Reference Algebra for OLAP. In International Conference on Data Warehousing and Knowledge Discovery. Springer, 99--110.
[19]
Mark Whitehorn, Robert Zare, and Mosha Pasumansky. 2004. Fast Track to MDX. https://api.semanticscholar.org/CorpusID:61077971
[20]
Fred Zemke, Krishna Kulkarni, Andy Witkowski, and Bob Lyle. 1999. Introduction to OLAP functions. Change proposal. ANS-NCTS H2--99--14 (April) (1999).
[21]
Fred Zemke, Andrew Witkowski, Mitch Cherniak, and Latha Colby. 2007. Pattern matching in sequences of rows. Change proposal for ISO 9075--1. ANSI INCITS.
[22]
Calisto Zuzarte, Hamid Pirahesh, Wenbin Ma, Qi Cheng, Linqi Liu, and Kwai Wong. 2003. WinMagic: Subquery elimination using window aggregation. In Proceedings of the 2003 ACM SIGMOD International Conference on Management of Data, San Diego, California, USA, June 9--12, 2003. ACM, 652--656. https://doi.org/10.1145/872757.872840
Recommendations
SQLtoKeyNoSQL: a layer for relational to key-based NoSQL database mapping
iiWAS '15: Proceedings of the 17th International Conference on Information Integration and Web-based Applications & ServicesToday, many applications produce and manipulate a large volume of data, the so-called Big Data. Traditional databases (DB), like relational databases, are not suitable to Big Data management. In order to solve this problem, a new category of DB has been ...
Comments
Information & Contributors
Information
Published In
June 2024
694 pages
ISBN:9798400704222
DOI:10.1145/3626246
- General Chairs:
- Pablo Barcelo,
- Nayat Sanchez-Pi,
- Program Chairs:
- Alexandra Meliou,
- S. Sudarshan
Copyright © 2024 Owner/Author.
Permission to make digital or hard copies of part or all of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for third-party components of this work must be honored. For all other uses, contact the Owner/Author.
Sponsors
Publisher
Association for Computing Machinery
New York, NY, United States
Publication History
Published: 09 June 2024
Check for updates
Author Tags
Qualifiers
- Research-article
Conference
SIGMOD/PODS '24
Sponsor:
Acceptance Rates
Overall Acceptance Rate 785 of 4,003 submissions, 20%
Contributors
Other Metrics
Bibliometrics & Citations
Bibliometrics
Article Metrics
- 0Total Citations
- 264Total Downloads
- Downloads (Last 12 months)264
- Downloads (Last 6 weeks)83
Reflects downloads up to 30 Aug 2024
Other Metrics
Citations
View Options
Get Access
Login options
Check if you have access through your login credentials or your institution to get full access on this article.
Sign in