Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 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

Oracle 10g OLAP New Features

Bud Endress has written an excellent paper on Oracle 10g OLAP new features which is currently available for download from the Oracleworld website. Some of the new features for the 10g OLAP option look pretty exciting, and the benefits of integration with the traditional relational Oracle engine are starting to become apparent.

First up is improvements to the way large Analytic Workspaces can be partitioned, introducing into the Oracle OLAP world some of the advanced partitioning options currently enjoyed by Oracle database users. Currently, Analytic Workspaces, stored as AW$ tables within an Oracle schema, can be partitioned across multiple rows in the AW$ table by specifying a maximum segment size, allowing you to split an individual analytic workspace into (say) 10Gb segments, one in each table row. This table could then be partitioned just like any other Oracle table, allowing you to put one row in one tablespace, another in another, and each of these tablespaces could of course be stored in datafiles on different physical disk units. Although this was of some benefit, splitting by segment size was the only way of partitioning the data, and you couldn't specify what objects within the analytic workspace went in to each partition. Oracle 10g OLAP will now include an enhancement where you can specify exactly which objects within the analytic workspace go in to each partition, and you can further subdivide this by segment size if objects are particularly large.

In a similar fashion, variables within the analytic workspace can now be partitioned, either by range of dimension members, a list of dimension members, or by reference to a CONCAT dimension. The 10g multidimensional engine then stores each variable partition as a separate physical object, which can be directed to separate rows in the AW$ table (allowing you to partition these across different tablespaces and physical disk drives); the variable however appears as just one object to the application, simplifying the data model and allowing Oracle to do all the 'hard work' in the background.

Another excellent new feature, and a real improvement over what was available with Express, is support for multi-user read-write access to individual analytic workspaces. In the past, one drawback with Express was that only one user could attach to an Express database in read-write mode, leading Express developers to develop a whole range of alternative solutions to allow ad-hoc write access to Express databases. In Oracle 10g OLAP, analytic workspaces can be attached in MULTI mode, whereafter applications then ACQUIRE individual variables in the analytic workspace for read-write access. Once an object has been acquired (and locked by the Oracle multidimensional engine), updates can then take place and the application can make whatever modifications are necessary. After all changes have been made, the UPDATE command is issued against the variable, followed by a COMMIT, and then a RELEASE command is issued against the variable to make it available for other applications to write to. It'll be interesting to see how the multidimensional engine handles multi-write access; in the past, with Express, databases could balloon in size when one user had read-write access to a database, and others were accessing it in 'read' mode, as Express had to clone the database for each user to ensure that they had a consistent view of the data; I wouldn't be surprised if individual variables were copied out of a 10g analytic workspace into a temporary workspace whilst updates happened, with updates being propagated back (as with the old Express Excel Add-In) when the changes are finally COMMITted - the key thing here is how database size is dealt with as the old Express way of doing it was less than optimal.

Aggregation has been improved with Oracle 10g OLAP, with formulas now allowed as sources of data for the AGGREGATE command, eliminating the need to calculate and store data at the detail level. Aggregation, particularly dynamic aggregation, is another area where Oracle 9i and now 10g OLAP are a distinct improvement over Express and it's well worth looking at this area in more detail if this is an issue with an existing Express system.

With Oracle Database 10g, a new extension to SQL called the MODEL clause has been introduced to allow the Oracle relational engine to perform complex inter-row and time-series calculations that have traditionally been the preserve of the Express Server world, with its concept of custom dimension members. Whilst the MODEL clause can work against purely relational data sources, Oracle 10g OLAP promises tight integration with this new relational feature, with improvements to the analytic workspace SQL interface designed to optimize this type of query.

Some of Bud Endress' papers around the time of the Oracle 9i OLAP launch hinted at a 9i OLAP feature where analytic workspaces could be used as substitutes for materialized views, although it wasn't clear (to me) at the time exactly how this was to be implemented. All has become clear however with 10g OLAP where a new database feature, 'query equivalence', can be used to direct the database query rewrite mechanism to a summary - in this case, an analytic workspace with SQL Views defined over it - that doesn't share the same SQL syntax as the user's original query but where the results would be the same. This is important when working with SQL views over analytic workspaces, as the SQL View syntax would never normally qualify for query rewrite as it would be completely different to the user's query.

I guess the only feature with 10g OLAP that I was hoping for, but that was not present, was allowing SQL queries to UPDATE an analytic workspace using a SQL View; it's this lack of an UPDATE ability that (I would presume) is holding back 'write-back' for the BI Beans but hopefully it's something that will come in due course.

However what's significant here is that Oracle are starting to provide some pretty compelling reasons to move from Express to Oracle OLAP and, coupled with support for native access to OLAP datasources with Oracle Discoverer, there's no better time to start seriously looking at 9i and 10g OLAP as an upgrade to existing Express Server implementations.

More details can be found at Bud Endress' paper on 10g OLAP over at the Oracleworld website.





Oracle training Excel
Oracle performance tuning software 


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 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.