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 








How OWB "Paris" Enables the OLAP Option - Part II
July 6, 2005
Mark Rittman

Current versions of OWB don't make you specifically define TOTAL levels for your dimensions, as you can obtain total figures for a particular dimension by just omitting the "where" clause in your SELECT statement. However, OLAP tools such as Discoverer for OLAP and BI Beans (and before those, tools such as OFA or Express Analyzer) expect a TOTAL level for each dimension, otherwise you'd not have anywhere in your measure to store data aggregated up to the TOTAL level;

also, you need to define a specific TOTAL level in the dimension for it to appear as a selectable hierarchy node in your query builder, as such:

Once you've defined your levels, you then get to define which levels implement which attributes:

In my case the CUSTOMER level will implement all of the attributes except the REGION_HOUSEPRICE_GROWTH_BAND one, which only applies at the REGION level, whilst the REGION level will implement the mandatory ID, NAME and DESCRIPTION ones, the REGION_HOUSEPRICE_GROWTH_ONE one but not the others which only apply at the CUSTOMER level.

Once you've defined your levels and Paris has implemented these as a default hierarchy, you might then want to move on to the mapping editor to bring some data into the dimension.   Note that I've added a few more levels into the dimension to illustrate the example:

Remember, what we're loading here is a logical model of the dimension, which does not necessarily have to be implemented as a relational OLAP star schema table. It could be a relational value-based "a.k.a. parent-child" dimension, or it could be a dimension within an analytic workspace, which are also value-based. Notice within each level of the dimension there are items that relate to the level above, so that when you come to populate the dimension, you populate it level-based and also, if you need to, value-based as well. By putting this in place Oracle have made it possible to create mappings that can support data loads into both relational OLAP dimensions and multidimensional dimensions if needed, but from what I've seen so far if you don't need to support this, you can just populate the regular level-based items and leave the value-based ones empty.

Also, note that you don't now need to explicitly set up sequences to provide values for the surrogate keys in each dimension level. OWB Paris sets up the sequences for you and plugs their values into the dimension mapping "behind the scenes", taking away the need for you to create these constructs within the mapping.

Another new OLAP Option feature that is pretty special for relational OLAP users is the support for creating DBMS_ODM materialized views. Normal materialized views don't work too well with tools such as Discoverer for OLAP, as the SQL generated by the OLAP API uses the GROUPING SETS feature to bring back complete sets of aggregated data, a feature your regular MVs won't usually have used. If you want to summarize data for use with Discoverer for OLAP you have to use DBMS_ODM and up until now there's been no GUI tool for doing this.

When you bring up a dimension definition using Paris and you've previously chosen for it to be implemented relationally, you can specify what dimension / level combinations within the cube are pre-aggregated. Looking at the SALES cube that uses our CUSTOMER dimension, we can choose to pre-aggregate at some dimension levels, and have Oracle perform the remaining aggregations on the fly, very much like you get with analytic workspaces.


Paris will then go off an generate calls to DBMS_ODM to implement the grouping sets-using materialized view, and register the aggregations with the OLAP Catalog.

update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'CUSTOMERS' and level_name not in ('PROVINCE','SUBREGION','TOTAL');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'PRODUCTS' and level_name not in ('TOTAL','SUBCATEGORY');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'CHANNELS' and level_name not in ('CLASS','CHANNEL');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'PROMOTIONS' and level_name not in ('CATEGORY');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'TIMES' and level_name not in ('YEAR','MONTH');

I thought this was all quite interesting as this is the first time we've seen proper support for the OLAP Option within Warehouse Builder. Apart from the direct support of multidimensional analytic workspaces (as covered in more detail in these two articles published earlier) this particular support for the relational implementation of Oracle OLAP is something pretty special, and something you just can't really do with the current generation of OLAP developer tools.


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