Advanced SQL modeling in RDBMS

Abstract

Commercial relational database systems lack support for complex business modeling. ANSI SQL cannot treat relations as multidimensional arrays and define multiple, interrelated formulas over them, operations which are needed for business modeling. Relational OLAP (ROLAP) applications have to perform such tasks using joins, SQL Window Functions, complex CASE expressions, and the GROUP BY operator simulating the pivot operation. The designated place in SQL for calculations is the SELECT clause, which is extremely limiting and forces the user to generate queries with nested views, subqueries and complex joins. Furthermore, SQL query optimizers are preoccupied with determining efficient join orders and choosing optimal access methods and largely disregard optimization of multiple, interrelated formulas. Research into execution methods has thus far concentrated on efficient computation of data cubes and cube compression rather than on access structures for random, interrow calculations. This has created a gap that has been filled by spreadsheets and specialized MOLAP engines, which are good at specification of formulas for modeling but lack the formalism of the relational model, are difficult to coordinate across large user groups, exhibit scalability problems, and require replication of data between the tool and RDBMS. This article presents an SQL extension called SQL Spreadsheet, to provide array calculations over relations for complex modeling. We present optimizations, access structures, and execution models for processing them efficiently. Special attention is paid to compile time optimization for expensive operations like aggregation. Furthermore, ANSI SQL does not provide a good separation between data and computation and hence cannot support parameterization for SQL Spreadsheets models. We propose two parameterization methods for SQL. One parameterizes ANSI SQL view using subqueries and scalars, which allows passing data to SQL Spreadsheet. Another method presents parameterization of the SQL Spreadsheet formulas. This supports building stand-alone SQL Spreadsheet libraries. These models are then subject to the SQL Spreadsheet optimizations during model invocation time.

Towards a Holistic Integration of Spreadsheets with Databases: A Scalable Storage Engine for Presentational Data Management

“…One way export of operations from spreadsheets to databases. There has been some work on exporting spreadsheet operations into database systems, such as Oracle [46], [47], 1010Data [39] and AirTable [40], to improve the performance of spreadsheets. However, the database itself has no awareness of the existence of the spreadsheet, making the integration superficial.…”

“…Related Work. While there have been attempts at combining spreadsheets and relational database functionality, ultimately, all of these attempts fall short because they do not let spreadsheet users perform ad-hoc data manipulation operations [46], [47], [26]; there have also been efforts that enhance spreadsheets or databases without combining them e.g., [37]. We describe this and other related work in detail in Section VIII.…”

multi-dimensional aggregation

“…Witkowski et al [38][39][40] proposed spreadsheet-like computations in RDBMSs through extensions to SQL, similar in spirit to grouping sets and the SQL/OLAP amendment. They also presented optimizations, access structures, and execution models for efficient processing.…”

Anti-Freeze for Large and Complex Spreadsheets

“…Handling Scale. Previous work on supporting spreadsheets at scale has been done by 1010data [2], ABC [29], Airtable [3], DataSpread [8,9], and Oracle [35,36]. While these systems address scale, interactivity for formula computation is not their focus.…”