New Oracle OLAP Product Directions Announced At Open World

Now that I'm back from Open World, there's a chance now to start taking a look through all of the papers and presentations available to download from the conference website. One of the features of actually being at Open World is that, apart from the presentations that you go to, there's little time to actually take a look at what else is being presented (and as there's up to 45 concurrent sessions going on, that's quite a few) and therefore it's a good idea to download and take a look at the other presentations when you get a chance. Over the next few weeks and months I'll be gradually working through the ones that caught my interest, but the ones that I usually turn to first are the papers by Bud Endress and Anthony Waite, Director of Product Management and a Product Manager respectively within Oracle's OLAP product management team. Bud and Anthony's papers are usually the best look forward to what's coming with Oracle's OLAP products, and with this in mind I'll first take a look at Anthony's paper, "Analytic Workspace Manager and Oracle OLAP 10g".

If you came along to my talk during Open World or took a look at the accompanying paper, you'll be aware that there are some radical changes coming up to how OLAP data is created and maintained with Oracle 10g. Whilst the OLAP Option with the first release of Oracle 10g (10.1.0.2) is much the same as Oracle 9i OLAP - except that you've now got additional partitioning options, and multi-writer support - the 10.1.0.3 patch added a set of new functionality to Oracle OLAP through the provision of the new Java AWXML API. AWXML allows you to define objects within an analytic workspace such as dimensions, hierarchies, levels, attributes and measures, through the use of an XML template file, with the AWXML API then taking these XML definitions and using them to create analytic workspace objects. These XML definitions can also contain the mapping definitions for these objects, additionally specifying the tables, views or external tables that contain the source data for the objects.

A forthcoming release of Oracle 10g, referred to sometimes as the 10.1.0.3B OLAP patch, or more likely now as the 10.1.0.4 patch, will extend this functionality and deliver something referred to as the "Model View", a way of holding and describing OLAP metadata that does away with the need for the OLAP Catalog for analytic workspaces. With this forthcoming release of Oracle OLAP, the OLAP API metadata, together with the SQL views required to access analytic workspace objects, will be stored as additional objects within the analytic workspace rather than in the OLAP Catalog within the OLAPSYS schema, and will be created dynamically by the Oracle database as objects are created. What this means for Oracle OLAP developers is as follows:

• You will no longer need to separately enable analytic workspace objects for use with the OLAP API - it'll all be done dynamically without your intervention
• The traditional "fragility" associated with the OLAP Catalog will no longer be an issue (one hopes...)
• It'll be easier to transport analytic workspaces between databases, because all of the metadata will be contained within the AW (no dependencies on the OLAPSYS schema), and you'll be able to save template files directly from AW's, allowing you to feed them back into the AWXML API to recreate them
• You'll no longer need to source your AW data from CWM1-compliant star schemas - AWXML happily takes data from tables, views, external tables (not sure about tables across DBLINKS though)

The other nice thing that this forthcoming release enables is the new 10g version of Analytic Workspace Manager, which is streets ahead of the Original 9.2.0.4 / 10.1.0.2 AWM.

Anthony's paper talks about the new "Model View" within 10g OLAP, and goes through a walkthrough of creating an analytic workspace using AWM10g, defining all the levels, hierarchies and cubes, and then displaying the results through Discoverer "Drake". Looking through Anthony's paper, and having had a play around with AWM10g for a few weeks, it certainly seems to go much of the way to replacing the functionality within Express Administrator, and comes close to the functionality you get with Microsoft Analysis Services' "Cube Editor". The bits that are still missing though, and that hopefully will come along in future versions, are the ability to graphically build a hierarchy using dimension members, and some form of cube viewer. However, it's a world apart from the existing version of AWM and in my opinion makes building AW-based cubes now easier in fact than building their relational cousins. Also, new with the forthcoming OLAP release is additional options for dealing with very sparse cubes and for partitioning, which I won't go over now but are fully detailed in Anthony's paper.

The other paper that I wanted to take a look at was Bud Endress' "OLAP Option To Oracle 10g - When To Use The OLAP Option To Enhance Content And Improve Performance Of A Business Intelligence Application". I went along to Bud's talk earlier in the week at Open World and later on wrote up a summary of his talk, which looked to answer the question - "When should I use the OLAP Option?". Bud said during the talk that he'd recently been asked to sum up, in one slide, what the OLAP Option was all about, and that the slide should contain a graph with two lines on it. As the request came from a senior executive at Oracle, Bud had to take it seriously, and came up with the following graph:

What this graph is saying is that, when your application makes lots of predicatable, simple calculations (such as those carried out by static reporting applications) there is little benefit from using the OLAP Option, and later on in fact that paper mentions that you might actually lose out by using OLAP due to the increased aggregation times. However, when your application typically makes lots of ad-hoc, unpredictable queries, against random parts of the data model, and your queries are typically more complex, then you get startling performance benefits from using dedicated OLAP engines such as the OLAP Option. The paper goes through lots of background information, and has other graphs and test results to back up this statement, and comes to the conclusion that, the reason you should use the OLAP Option is to ensure that your BI application gives a uniform, fast response time irrespective of the type of query or the data being accessed.

My understanding is that the new OLAP patch, and AWM10g, should be due for release in the first half of 2005. In the meantime, take a look at the papers and get a bit of background information before the products get launched.