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 II
July 15, 2005
Mark Rittman

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?


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