Oracle OLAP 11g News, and the Vlamis Blog

Whilst I was over at Collaborate'07 last month, one of the biggest bits of product news that I heard about, but couldn't mention fully at the time, was details of the new 11g release of Oracle OLAP. Chris Claterbos from Vlamis Software Solutions was given permission by Oracle OLAP product management to put some slides together on what's coming up with Oracle OLAP 11g, and now that he's got clearance to put the slides up on the Vlamis website, I can talk a bit more about what's currently planned for the product. As is always the case, these are current plans only, don't form a commitment to deliver and could change between now and the release of the software.

The first major new planned feature in 11g OLAP is better integration with query rewrite. In Oracle OLAP, there are really two main use-cases for the product; the first one was your traditional OLAP user, who wanted to access OLAP data using a dedicated OLAP tool (such as Discoverer for OLAP, or "D4O") and who wanted access to rich OLAP metadata such as hierarchies, attributes and levels, whilst the second was users of traditional relational query tools who just wanted to speed up their SQL queries against star schemas.

Where Oracle OLAP came in was that you could potentially take the data in your star schema, load it into an analytic workspace and then aggregate it in there, which theoretically could give you faster performance than a set of materialized views especially when user's queries were more random and unpredicatable. However, in 10g OLAP although you could register a view over aggregated data in an analytic workspace with query rewrite, "normal" queries that used SUM(), AVG() and other aggregation functions, together with GROUP BY, wouldn't get rewritten as the SQL views used over analytic workspaces were based on fully-solved cubes, i.e. the view contained all levels of aggregation and measures were already aggregated. This meant that tools such as Discoverer relational wouldn't work properly with Oracle OLAP and users had to learn to write their SQL queries in a different style to take proper advantage of this relational layer over analytic workspace data.

Now, with this forthcoming release of Oracle OLAP, you can just check a box in Analytic Workspace Manager to enable queries against the source tables for your analytic workspace to be re-written against the summary data in your analytic workspace, and your OLAP cube will then act in the same way as a regular materialized view, meaning the two technologies (in theory) will be interchangeable as a way of summarizing warehouse data. In addition, another checkbox in Analytic Workspace Manager will allow the cube to be refreshed using the DBMS_MVIEW.REFRESH procedure, again making analytic workspaces "work" like materialized views, making it easier for DBAs to use analytic workspaces and materialized views interchangabily, with both of them being available to the query rewrite mechanism and both being refreshed through the same PL/SQL package.

The next new feature is called "cost-based pre-summarization", and will allow you to specify a percentage to pre-summarized the cube (10%, 30%, 100% and so on, selectable in AWM using a slider control) rather than on a dimension level basis. From looking at the screenshots, what would also be useful (and this applies to dimension-level based pre-summarization as well) would be some indication of the disk space required, and time required, to achieve the level of pre-summarization requested, but it's a good start and one that's probably more intuitive than selecting pre-summarization by dimension level.

From an SQL and command-line perspective, two additional new features are a new CUBE_TABLE table function, to complement the existing OLAP_TABLE function, that allows you to select from an AW using just the AW name and AW object name, rather than listing out all the dimension components, cube components, limit map and so on. AWs created in AWM11g also have SQL views automatically created over them (a logical progression from 10g, which automatically created object types over the AW when OLAP_TABLE was used), and I presume from looking at the screenshots and the syntax of CUBE_TABLE that CUBE_TABLE uses these views to provide the dimension and measure selection details that OLAP_TABLE otherwise needed - once we get hold of the software presumably we'll find out more. On a similar subject, all the metadata that in 9i and 10g was held in the OLAPSYS schema will now be moved in to the Oracle data dictionary proper, with views being published in the SYS schema that will allow dimension names to be listed, cubes and measures to be listed and so on.

Other new features include a new calculation builder, with an even more verbose language for creating calculated measures ("Rank members of the PRODUCT dimension and PRIMARY hierarchy based on measure UNITS_CUBE.UNITS. Calculate rank using RANK method with member's level in order lowest to highest") which whilst a positive in that it extends the over-simplistic calculation builder in AWM, D40 and the Spreadsheet Add-in, is yet another calculation interface to learn, and one they'll no doubt now need to add in to D40 11g and the Spreadsheet Add-in 11g. Personally, I think I'd rather just see the current calculation builder extended to cover more advanced functionality, and, if I was given a free choice, I'd be even keener to see Oracle OLAP support MDX and/or XML/A and open it up to the industry standard for calculation and expression building, but as this is somewhat unlikely (especially as Oracle have now got the MDX and XML/A-compatible Essbase, which allows them to satisfy the needs of customers who are asking for this feature), we'll have to see what this new calculation builder brings us.

Added to the new calculation builder are native support for skip-level and ragged hierarchies when building a level-based hierarchy, which was always possible with value-based (parent-child) hierarchies but was difficult to do with level-based ones, and in the past meant creating dimensions with as many levels as the deepest route down the hiearchy, and populating levels that weren't needed with dummy data. I have no idea how Oracle have managed to support skip-level and ragged hierarchies with level-based hierarchies, except if they use this same solution but somehow skip the display of the dummy levels when viewed through D40 11g and other 11g OLAP API-based tools.

The final few new features are an interface in AWM for creating security policies based on hieararchies (avoiding you having to code the same using OLAP DML, PERMIT READ statements and the like) and allowing you to link these security policies with Oracle RDBMS users and roles - a welcome feature that brings the nirvana of integrated AW and relational security a bit closer, and avoids lots of messy coding in OLAP DML which most newcomers to Oracle OLAP won't be familiar with; better EXPLAIN PLAN output of what goes on when an SQL statement queries an analytic workspace; and some more optimizations in the interface between SQL and AWs in that joins between views over AW dimensions and cubes are pushed down to the AW rather than being resolved by the relational engine.

So, an interesting set of features. Potentially the most revolutionary is the interchangeability of AWs in 11g with materialized views - in the past this was theoretically possible using DBMS_ADVANCED_REWRITE, but I'd never heard of this being done in practice and this new functionality should make it possible for DBAs to load, refresh and then have queries rewritten to AWs just the same as MVs. Add to that usability improvements such as auto-creation of SQL views, cost-based pre-summarization, simpler SQL access through CUBE_TABLE and proper support in the data dictionary for OLAP metadata, and it looks like Oracle are now delivering on the "integrated relational-MOLAP database" design goal originally put forward with the 9i release of Oracle OLAP, and we're now starting to move on from what has essentially been a standalone OLAP server running inside the Oracle database into a properly integrated data warehouse performance booster, but with the benefit of all that rich OLAP dimensional model metadata when you need it. Should be an interesting release, although with all these new features and it not yet being in the 11g beta code drop, I'll probably err on the side of caution and not recommend it for production use until at least the first patch release, or looking back at how 10g went with features such as compressed composites and partitioning, until 11g Release 2 when the new features should be fully "shaken out".

Anyway, thanks to Chris and Dan from Vlamis for putting the new slides together, and in addition you can keep up to date with what Vlamis, who are probably our equivalent over in the United States of America, on the new Vlamis Blog.