Rittman Mead - Delivered Intelligence

"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.

Posted in Oracle Discoverer |