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'.
Outlined in Tom Kyte's recent white paper at Oracleworld, 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. An example example taken from Tom's paper would look like;
SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country IN ('Italy','Japan')
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales['Bounce', 2002] = sales['Bounce', 2001] +
sales['Bounce', 2000],
sales['Y Box', 2002] = sales['Y Box', 2001],
sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])
ORDER BY country, prod, year;
The example assumes that a materialized view has been created against a sales star schema, which in the first instance returns the sum of sales over a number of years for two countries, Italy and Japan. The MODEL clause then comes after WHERE part of the SELECT statement, and by using the 'UPDATED ROWS ONLY', limits the eventual output to those rows that were created or updated by the query.
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.
For more details on the SQL Model clause, including examples of more complex models together with examples of syntax, check out Tom Kyte's excellent white paper on the Oracleworld website.
9:39:13 PM 
|
|
