Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines


Mark Rittman

The New Oracle 10g SQL MODEL Clause

Apart from the announcement that a future version of Discoverer will fully support Oracle OLAP multidimensional queries, the most important new business intelligence feature of Oracle 10G is the 'SQL Model Clause'.

The SQL Model clause allows users to embed 'spreadsheet-like' models in a SELECT statement, in a way that was previously the domain of dedicated multidimensional OLAP servers such as Oracle Express and Oracle 9i OLAP. The SQL Model clause brings an entirely new dimension to Oracle analytical queries and addresses a number of traditional shortcomings with the way SQL normally works.

The SQL Model clause has been designed to address the sort of situation where, in the past, clients have taken data out of relational databases and imported it into a model held in a spreadsheet such as Microsoft Excel. Often, these models involve a series of macros that aggregate data over a number of business dimensions, over varying time periods, and following a set of complex business rules that would be difficult to express as normal SQL. I've worked on many a client engagement where the limitations of SQL meant that a number of standalone Excel spreadsheets had to be used, and whilst these gave the client the analytical capabilities they required, the usual issues of scalability, reliability of replicated data, and lack of overall control often became apparent after a while.

The aim of the SQL Model clause is to give normal SQL statements the ability to create a multidimensional array from the results of a normal SELECT statement, carry out any number of interdependent inter-row and inter-array calculations on this array, and then update the base tables with the results of the model.

Directly after the 'MODEL' clause, the 'PARTITION BY', 'DIMENSION BY' and 'MEASURES' elements define the logical divisions in the data, the way in which individual data items are divided up, and the actual data items that will be included in the model itself. Whilst the PARTITION BY clause is probably familiar from the existing Oracle 8i and 9i analytic functions, the DIMENSION BY and MEASURES clauses are particularly powerful as they allow the model that follows to access data in a logical, easy to understand way. The RULES clause that then follows can then reference individual measures by referring to combinations of dimension values, in a similar way that spreadsheet macros refer to worksheet cells by reference to lookups and ranges of values. In the above example, the view that provides the initial data only holds sales data for 2000 and 2001, and the model that is then specified in the RULES clause provides the instructions on how figures for 2002 should be calculated.

The power of the SQL Model clause becomes apparent when you consider that, as well as outputting forecasts and calculations to a query tool such as Oracle Discoverer, it can also write back to the underlying tables, inserting or updating values according to the model's set of interdependent calculations and business rules. Models can be built up that iterate many times over the same set of data, feeding the results of one calculation into another and drawing together data across many different time and dimension ranges. All of these calculations are handled natively by the Oracle database kernel, avoiding expensive database joins and unions and taking advantage of such features as parallelism and partitioning where they are available. Coupled with the existing analytic features within the Oracle 8i and 9i database, the Model clause gives the Oracle 10g database powerful mathmatical modelling capabilities not found in competitor products such as SQL Server and DB2.

What's also particularly interesting is the prospect of integration between the SQL Model clause and the existing modelling capabilities within the Oracle OLAP multidimensional engine. Bud Endress' paper on the new features for Oracle 10g OLAP in particular hints about close integration between the Model clause and the SQL interface to Analytic Workspaces, such that queries can be handled at the detail level by the relational engine, and at the aggregated level by the multidimensional engine, in a way that is seamless to the user and with the minimum of overhead.

Of course, experienced Oracle Financial Analyzer developers will probably be more than familiar with the concept of multidimensional financial models, as Express Server has supported these for many years and they form the basis for most OFA implementations. From speaking to colleagues who've worked with OFA, the SQL Model clause is functionally much the same as the modelling capability within Express and it's more than likely that it's being introduced to support the forthcoming Enterprise Planning and Budgeting, the replacement for OFA that will be based on the Oracle 9i OLAP platform. By now including sophisticated modelling capabilities within the relational engine as well as the multidimensional engine, the option is opened up with Oracle 10g to allow future versions of EPB to use both a relational and multidimensional data store for its OLAP calculations.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational