Rittman Mead - Delivered Intelligence

A number of clients I’ve worked with recently have been looking to implement
OracleBI Discoverer 10.1.2 for OLAP as their general ad-hoc query tool. Whether
running against relational OLAP data or data in analytic workspace, Discoverer
for OLAP comes with a slick new interface, an OLAP-aware query builder and works
against a logical dimensional model that mirrors the way that users picture
their business.

Given all the publicity around Discoverer for OLAP, most people
aren’t actually aware that the relational version of Discoverer Plus, previously
known as Oracle Discoverer Plus 9iAS, as now been updated to version 10.1.2, and
is now known as OracleBI Discoverer Plus 10.1.2. This new version has much the
same look and feel as Discoverer for OLAP, but runs against an End User Layer
and queries regular tables and columns.

Both versions are installed alongside each other and are reached
using the same URL -
http://yourserver:port/discoverer/plus,
with the user selecting which version to run from a drop-down menu.

So why have Oracle done this, and why would you still want to
use the relational Discoverer Plus version rather than Discoverer for OLAP?

The benefits of using Discoverer for OLAP, and the
OLAP
Option, are fairly well understood; what’s not so apparent for most
Discoverer users is what Discoverer Plus offers that Discoverer for OLAP cannot.
I’ve been thinking about this a lot recently and I think it comes down to two
factors, and this is disregarding for the moment the cost issue of implementing
the OLAP Option.

• The logical dimensional model, though powerful, does not lend itself to
  including dimension attributes in a report, and
• Discoverer for OLAP, as it always generates a crosstab report, is not a
  valid solution when you need to produce tabular reports.

Because of this, I believe that the vast majority of Discoverer installations
will require both Discoverer for OLAP and Discoverer Plus to be implemented. Let
me explain further:

To understand the role of the different versions of Discoverer,
you need to think about what OLAP is, what sort of reports it generates, and how
these map to your organisation’s requirements. Discoverer for OLAP works against
a logical dimensional model, which organises your data into measures (the things
you add up, like sales, headcount, income and insurance claims), dimensions
(such as customers, products, time, sales territories). Within Oracle OLAP, an
analytic workspace consists of a number of dimensions, and a number of cubes,
which are containers for measures that share the same dimensionality. A typical
cube could be drawn as this:

where you have a measure, "Sales", dimensioned by four
dimensions, "Customer", "Time", "Products" and "Channel". Each of these
dimensions will have attributes, such as "Customer Name", "Customer Type",
"Customer Profit Segment", "Product Description", "Product Colour" and so on,
and will usually have levels and hierarchies so that you can drill into your
data and produce summaries at meaningful levels. Note here that attributes
exists as parts of dimensions, and there purpose with regard to Discoverer for
OLAP is to allow you to refine the subset of data that you are looking to
analyze.

When you think of your data dimensionally and you have added useful
attributes to these dimensions, you can formulate dimensional queries using
terms familiar to business people. For example:

What was the percent change in
revenue
for a grouping of my top 20% of products
during a year ago rolling three month time period
as compared to the current period this year
for each region of the world?

or

what are my top ten customers

without worrying about how the data is actually stored in the database,
instead using the logical dimensional model-aware query wizard that comes with
Discoverer for OLAP.

This flexibility and ability to create subsets of data through
reference to dimensional attributes is a key feature for Discoverer for OLAP,
and means that you can take a set of figures, analyze and model it through
refining your dimension member selection, without having to worry about outer
joins, analytic SQL or query performance. For more details on what the benefits
are of a true OLAP data source, check out Bud Endress’s OpenWorld paper

"Oracle
OLAP 10g: Enhance Content and Improve Performance".

The thing is though, that although with Discoverer for OLAP you can build
pretty powerful data retrieval queries using selections of attributes, it’s not
so easy to have these attribute values displayed in your workbook. This is
because attributes exists as a means to make dimension member selections, not as
something that appears in your report - those things are called measures. Take
an example, where I want to produce a crosstab that displays the value of
product sales, broken down by product and channel (I’m using the

Global Sample Schema that you can download from OTN). So far, no problem -
but what if I want to include the names of the product marketing managers
in the report, subdividing the products by these product managers?

Using Discoverer Plus, this isn’t a problem. Although Marketing Manager is a
dimension attribute (a column in the CUSTOMER_DIM table) I can drop it onto the
crosstab and see it in the report:

Using Discoverer for OLAP though, I’m stuck, as although Marketing Manager is
a dimension attribute, I can’t display it in the crosstab - I can only use it to
make dimension member selections:

If you’re an old Express user and you’re reading this, you’re
probably thinking "what about one dimensional text variables - they’re our way
to include labels in crosstabs". What this involves is using AWM10g to create an
additional cube in your analytic workspace, with one dimension (in our case,
Product) and a single measure defined as a text variable. Then, whenever you
display products on the screen, you can include your "attribute measure" in the
report as well. Traditionally, this is done as a way of adding text annotations
to cubes, with the annotation being held in a variable dimensioned the same way
as the measure.

Note that the measure is nonadditive, which means that it will
only exist at the bottom level of the product dimension.

Next, we add the measure to the crosstab:

and then the measure appears - but it gets added alongside the
Sales measure, and then is only visible when you drill down to the bottom level.
So - it’s not really where we were expecting it to appear, and it’s not being
used as a way of subdividing our products into those managed by particular
product managers.

Now I’m sure there are probably ways of getting around this -
maybe making product managers into a hierarchy of their own, or there may be
some other way of achieving this. But what this says to me is that, when you use
the logical dimensional model and you load up your dimension with attributes,
it’s not all that straightforward to have the attributes appear on the report,
or use them to further group or subdivide your data. It’s just the way the
logical dimensional model works - it’s got it’s advantages (logical grouping of
data into measures, dimensions, attributes and so on) but it’s not as freeform
as an SQL query tool that lets you group data by just about anything.

The other issue is around the production of tabular reports. As
you know, Discoverer for OLAP is designed around crosstab reports - indeed it’s
the only option that you get when putting together a new workbook. But what if
your organisation wanted to produce a report that lists out the products that
you sell, who is the marketing manager, grouped into product categories and
listed in order of sales volumes. Using Discoverer Plus, you could produce
something like this:

A straightforward listing report, group sorted by category and
with the category, product name, marketing manager (remember, an attribute) and
sales total on the report.

With Discoverer for OLAP, although you can’t choose to create a
tabular report, you can achieve something like this by creating a worksheet
using only one dimension (product) and hiding the others.

Then, by bringing in the Marketing Manager measure that we
created earlier, we can put something together that is "sort of like" our
relational tabular report:

However, it’s probably not what we were really after. The
product name and category are part of the dimension crosstab element, not the
report, the report is all spread out rather than available on one page, and
there’s lots of cells that have no values in. It’s probably not what the users
were expecting and probably won’t be acceptable for production use.

Now none of this is meant to detract from what Discoverer for
OLAP, and the OLAP Option, offers. It’s just that not all enterprise reporting
fits neatly into the category of OLAP analysis of cubes of financial data, and
formal OLAP structures aren’t always the most appropriate for ad-hoc, freeform
reporting. What this says to me is that, in reality, most Discoverer
implementations are going to consist of a bit of relational Discoverer analysis,
and a bit of OLAP Discoverer analysis; Discoverer for OLAP will be best suited
to analysis of cubes of financial or performance data, where you want to drill
into and investigate how the figures are made up using complex multidimensional
selections, whilst Discoverer Plus is for when your reports need a lot of
textual data, you need to produce simple listings of data, or your users want
the freedom to have just about anything that’s in the underlying database appear
in the report.

Any comments from anyone, perhaps who’s worked on a Discoverer
10.1.2 implementation or perhaps who has used Express to meet some of these
non-OLAP reporting needs?

Posted in Oracle Discoverer |