Principles of database and knowledge-base systems, Vol. I
Principles of database and knowledge-base systems, Vol. I
An overview of data warehousing and OLAP technology
ACM SIGMOD Record
An array-based algorithm for simultaneous multidimensional aggregates
SIGMOD '97 Proceedings of the 1997 ACM SIGMOD international conference on Management of data
Fundamentals of Database Systems
Fundamentals of Database Systems
Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals
Data Mining and Knowledge Discovery
On Efficiently Implementing SchemaSQL on an SQL Database System
VLDB '99 Proceedings of the 25th International Conference on Very Large Data Bases
Storage and Querying of E-Commerce Data
Proceedings of the 27th International Conference on Very Large Data Bases
SchemaSQL - A Language for Interoperability in Relational Multi-Database Systems
VLDB '96 Proceedings of the 22th International Conference on Very Large Data Bases
Spreadsheets in RDBMS for OLAP
Proceedings of the 2003 ACM SIGMOD international conference on Management of data
An enterprise directory solution with DB2
IBM Systems Journal
GPIVOT: Efficient Incremental Maintenance of Complex ROLAP Views
ICDE '05 Proceedings of the 21st International Conference on Data Engineering
Bridging the gap between OLAP and SQL
VLDB '05 Proceedings of the 31st international conference on Very large data bases
Extending RDBMSs To Support Sparse Datasets Using An Interpreted Attribute Storage Format
ICDE '06 Proceedings of the 22nd International Conference on Data Engineering
DADA: a data cube for dominant relationship analysis
Proceedings of the 2006 ACM SIGMOD international conference on Management of data
Business modeling using SQL spreadsheets
VLDB '03 Proceedings of the 29th international conference on Very large data bases - Volume 29
PIVOT and UNPIVOT: optimization and execution strategies in an RDBMS
VLDB '04 Proceedings of the Thirtieth international conference on Very large data bases - Volume 30
| Hi-index | 0.00 |
In recent years, a horizontal table with a large number of attributes is widely used in OLAP or e-business applications to analyze multidimensional data efficiently. For efficient storing and querying of horizontal tables, recent works have tried to transform a horizontal table to a traditional vertical table. Existing works, however, have the drawback of not considering an optimized PIVOT operation provided (or to be provided) in recent commercial RDBMSs. In this paper we propose a formal approach that exploits the optimized PIVOT operation of commercial RDBMSs for storing and querying of horizontal tables. To achieve this goal, we first provide an overall framework that stores and queries a horizontal table using an equivalent vertical table. Under the proposed framework, we then formally define 1) a method that stores a horizontal table in an equivalent vertical table and 2) a PIVOT operation that converts a stored vertical table to an equivalent horizontal view. Next, we propose a novel method that transforms a user-specified query on horizontal tables to an equivalent PIVOT-included query on vertical tables. In particular, by providing transformation rules for all five elementary operations in relational algebra as theorems, we prove our method is theoretically applicable to commercial RDBMSs. Experimental results show that, compared with the earlier work, our method reduces storage space significantly and also improves average performance by several orders of magnitude. These results indicate that our method provides an excellent framework to maximize performance in handling horizontal tables by exploiting the optimized PIVOT operation in commercial RDBMSs.