The History Of Oracle Discoverer’s OLAP Support

"Hi, Mark,

You paper 'Oracle 9i OLAP Uncovered' inspired me to explore more on this exciting subject. First let me confirm with you that Discoverer's own OLAP feature is implemented based on (or using) 9i/10g ROLAP. Is it true? Regardless of your answer to my first question, I think I have two other valid questions:

#2) What's the performance difference between Discoverer on ROLAP vs on MOLAP and how significant it is?

#3) What's the functional difference between the two implementation?

Hope to see you and discuss related issues at OracleWorld in two weeks. Thanks"

This is a question that's particularly relevant now as Discoverer "Drake" is most probably going to be launched at Open World next month. One of the key features of Discoverer "Drake" is the OLAP integration, and given that Discoverer has supposed to have OLAP support for several versions now, it's worth taking a bit of time out to explore just what this OLAP integration involves.

First of all, Discoverer has always had a form of OLAP support, in that it allows you to put OLAP queries together against relational tables in your Oracle database. These tables can just be regular Oracle tables - no additional metadata, no additional storage types - and you just define hierarchies on some tables (which are then effectively your dimensions) and select measures from other tables (which are then your fact or cube tables). If you're used to high-end OLAP tools such as Oracle Express or Hyperion Essbase, a Discoverer crosstab report is recognisably an "OLAP report", but the engine underneath it isn't a specialist OLAP engine, it's just a relational database with maybe a few summary tables or materialized views to improve response times.

When Oracle 9i OLAP was launched, one of it's key features was support for SQL queries against 9i OLAP's "Analytic Workspaces", which was made possible through SQL views that used the OLAP_TABLE function to retrieve data from 9i OLAP cubes. Because there were no native OLAP query tools that could access Oracle 9i OLAP, Oracle billed Discoverer 9iAS as a query tool for 9i OLAP, with regular business areas and folders being defined against these SQL views, and tools such as Analytic Workspace Manager and Oracle Warehouse Builder 10g came with utilities to create these SQL views and Discoverer business areas automatically.

The problem with this approach was that firstly, it was very clunky, and it wasn't unknown to get OLAP_TABLE errors when querying the views if something within the views or analytic workspace was slightly out of line. Queries that brought together analytic workspace data and relational data were a bit unpredictable, and worst of all, queries against analytic workspace data had a noticable "lag" as the views did their thing, meaning that performance was no better (and often infact worse) than regular relational queries.

All this is now history with the imminent launch of Discoverer 10.1.2, a.k.a. Discoverer "Drake", which natively accesses 9i and 10g OLAP analytic workspaces using Oracle's Business Intelligence Beans technology. Like Discoverer 9i, there's a thin-client version known as Discoverer Viewer, and in fact two editions of Discoverer Plus, one that queries regular relational tables, and one called Discoverer Plus OLAP that works against analytic workspaces. Discoverer Plus OLAP comes with what's called the "Discoverer Catalog", an OLAP version of the Discoverer EUL, and both versions have exactly the same look and feel and you can set up links and drilling between the two versions.

In answer to the specific questions:

1. "What's the performance difference between Discoverer on ROLAP vs on MOLAP and how significant it is?"

The answer of this is of course "it depends". It depends on whether the ROLAP cube or MOLAP cube have been preaggregated, the type of query and whether you're calculating values or dimension members on the fly. However, normally, the MOLAP version should be faster and certainly more productive when you're putting OLAP queries together. Also, whilst it's down to how you construct your cubes, it's usually the case that MOLAP cubes are more likely to be preaggregated than ROLAP cubes and therefore they usually end up running faster.

2. "What's the functional difference between the two implementation?"

Assuming we're comparing regular Discoverer Plus 9iAS and Discoverer Plus "Drake" against Discoverer Plus OLAP "Drake", apart from the method of access the main functional difference is in what's called the Query Builder. With Discoverer as you know it, the queries you build up are based around an SQL statement, so when you enter a parameter such as "GEOGRAPHY = "AMERICA", this is used as the predicate in an SQL statement. If your query is complex and uses values from multiple dimensions, multiple hierarchies and involves some inter-row calculations, things soon get a bit sticky, and features such as forecasting, time-series analysis and analytic functions require a good knowledge of SQL. With Discoverer Plus OLAP running against a MOLAP analytic workspace, you now get access to a full OLAP query builder, which allows you to put OLAP-aware queries together using regular English expressions.

Although Discoverer "Drake" isn't due to be formally announced until Open World next month, you can get a feel for how Drake works by taking a look at these demo viewlets over on OTN, this set of powerpoint slides, and my recent paper for the UKOUG conference.