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 








Drilling From OLAP to Relational Workbooks Using Discoverer 10.1.2 - Part I
July 27, 2005
Mark Rittman

"I have a question about drilling from an OracleBI Discoverer for OLAP 10.1.2 worksheet to a Discoverer Plus Relational worksheet. When you pass values from an OLAP worksheet you pass either the dimension name or the dimension value to the associated parameter in the relational worksheet. Obviously, in OLAP this dimension is treated as an object, and we have no idea which level the user may have picked before he drills out. On the other hand, in the relational world, each level of the dimension would be split out as a separate parameter. Could you run through a simple example where you drill from an OLAP worksheet to a relational worksheet and show how this is done?"

One of the key features of OracleBI Discoverer 10.1.2 is the integration between the OLAP and the relational data. If you've taken a look at any of the promotional material available around Discoverer 10.1.2's launch, you'll probably have seen that it's now possible to drill directly from an OLAP workbook, running against data in a multidimensional OLAP cube, to a relational workbook, running against a regular end user layer.

Once you come to try this out though, you come across an interesting problem. Your OLAP worksheet can set up a drill to a relational Discoverer Plus or Viewer worksheet, and can pass across the dimension values that apply to the cell that the user is drilling from. However, in the OLAP world, a dimension consists of all the dimension values for all levels, with no distinction between values from one level in the dimension hierarchy or another. When you come to pass these dimension values out to the relational worksheet, assuming you've got a customer dimension with customer, region, warehouse and total customer levels, your value might be from any of those levels - a single customer, a customer region, warehouse or even the "total customer" indicator. The problem comes when trying to apply this dimension value to the relational worksheet, where your customer dimension is actually represented by four separate items, and your OLAP parameter will apply to only one of those items. How to you set parameters up in the relational workbook when there's this mis-match between the way that the OLAP worksheet passes across your customer dimension selection, and the way that the relational workbook works?

To show how this works, I'm using the GLOBAL Sample Schema that you can download from OTN, which gives me an analytic workspace template definition and source data that can be loaded into Analytic Workspace Manager 10.1 and then used to build the GLOBAL schema. I've also created an End User Layer, GLOBAL_EUL, that has a business area defined over the GLOBAL source tables. I will create an OLAP workbook using the GLOBAL analytic workspace, and a relational workbook over the GLOBAL_EUL end user layer.

The relational workbook I'm going to drill to is a tabular listing of product sales to customers, with group sorts applied to each of the product and customer items. This is the layout of the workbook before any parameters or conditions are applied, and the report contains details of every sale in the UNITS_HISTORY_FACT table.

In this worksheet I'm displaying the Region, Country (warehouse) and customer (ship_to) from the CUSTOMER_DIM table, and product class (class), product family (region) and product (item) from the PRODUCT_DIM table. Now, as the dimension value that comes from the OLAP worksheet could be for any of these columns (or indeed for the total customer or total product items in the business area but not included on the worksheet) I next need to create a parameter that refers to each one.

Starting with ship_to first, note that the parameter is based on the customer_dim.ship_to_dsc item, and that the "Create condition with operator" tickbox is deselected. This is very important. Do the same for the other items so that you have a total of 8 parameters, one for each of the product and customer items that relate to the levels in the OLAP product and customer dimensions. Note also that I'm basing the parameters on the descriptions, not the values - more on this later.


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