Research On Spreadsheet Engine

In the applications of On-Line Analytical Processing (OLAP), data are usually analyzed in a multi-dimensional model. And the multi-dimensional computations are frequent. SQL has critical deficiencies in this aspect. Relational OLAP applications have to emulate them using join, CASE expressions, nested queries and union. One of the emphases in OLAP research is designing an effective method to express multi-dimensional computations and the engine to accomplish this sort of computations.A SPREADSHEET clause is designed to express these computations. SQL is extended with this clause, which divides the columns of relation into dimensions and measures to emulate the multi-dimensional model. And it can define formulas with them. Spreadsheet engine, as a part of the OLAP server, can accomplish this kind of computations. The spreadsheet engine takes the queries with SPREADSHEET clause as inputs. Then a query is divided into two parts: the first part is the standard SQL query, which is sent to the query processor of OLAP server; the second part is the multi-dimensional computations defined by the SPREADSHEET clause, which is executed on the result set of the first part by the spreadsheet engine after optimizations.The spreadsheet engine can use several optimizing method. Level generation can divided the formulas into separate levels; in each level formulas can executed in the same scan of the data. Pruning and rewriting formulas can delete the unnecessary computations. Pushing predicates can reduce the size of the middle result set. And using materialized views can reduce the computations of the queries. The spreadsheet engine executes the queries with different algorithms, which is automatic or sequential, according to their kinds. And a two-level hash access structure is used, with which the engine can access each cell effectively when executes the formulas.