Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Positioning OracleBI Discoverer for OLAP - Part I
July 14, 2005
Mark Rittman

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?”


“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?



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational