OLAP EXTENSIONS TO SQL

only for RuBoard - do not distribute or recompile

There are a set of proposed enhancements to SQL to enable some OLAP functions to be performed. The SQL standards authority is aware of the importance of data warehousing to industry generally and is keen to rise to the challenge that it brings to the state of database technology. I describe here the major proposals in outline form. The requirements and the proposed solutions have been well thought through. The details (syntax, etc.) are still being tested .

Moving averages. It will be possible to specify the number of rows to be included in the moving average. For instance, in a result set containing monthly sales, we can create moving averages for any number of months. We can even look ahead in the data to, for instance, create a three-month moving average that contains the current month (i.e., current row pointed at by the cursor), previous month, and following month.

Aggregation groups. Whereas a moving average is a kind of bounded aggregation function, it is possible to define unbounded aggregations to enable cumulative (e.g., year to date) columns to be defined. A further development of this idea will enable what is described as uncentered aggregation groups, which means that we can specify aggregation columns that don't include the current row at all. For instance, we could compare this month's sales with the average for the preceding three months.

Ranking functions. This has long been a bugbear in data warehousing. We often need to know who are the top n customers, products, sales people or regions . The rank function that is proposed will allow these to be specified.

only for RuBoard - do not distribute or recompile