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. I've yet to properly look at Tom Kyte's paper on this feature, but from what I've heard so far this looks to be an excellent new feature.
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.
7:01:30 AM 
|
|
