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 I
July 5, 2005
Mark Rittman

I've been playing around with one of the OWB "Paris" betas, and one of the things that most struck me about this version of OWB compared to previous ones is the degree to which it utilizes the OLAP Option. Current and  previous versions of OWB primarily created relational data warehouses, with the addition of CREATE DIMENSION statements to help with aggregate navigation. If you wanted to enable your OWB projects for the OLAP Option you had to use the "OLAP Bridge" which either created CWM1 metadata for a relational OLAP implementation or an analytic workspace together with CWM2 metadata for a multidimensional implementation. Particularly in the case of relational OLAP, you had to do lots of (non-intuitive) things to make your facts and dimensions work with tools such as Discoverer for OLAP or BI Beans, such as adding particular suffixes to column names to create the long and short descriptions needed, and there was no support whatsoever for the type of materialized view required when using the OLAP Option. In short, if you use current versions of OWB together with the OLAP Option, you need to be somewhat "in the know" and carry out lots of post-OWB steps to make your cubes work properly.

I was pleasantly surprised therefore to note the degree of support that OWB "Paris" has for creating OLAP Option-ready cubes and dimensions. Some of these changes that have been put in place to provide this support will however be initially disorientating for existing OWB developers, and therefore I thought it worth going through some of what's coming up and highlight what the point of this all is.

Just like existing OWB projects, a "Paris" design repository will consist of a number of projects that contain one or more source and warehouse target modules. A warehouse target module contains one or more dimensions, together with one or more cubes containing measures of the same dimensionality. Dimensions themselves are created using a Dimension Wizard, as before, and one of the first question the developer is asked is whether the dimension is to be implemented relationally (ROLAP) or multidimensionally (MOLAP).

I mentioned this the other week, and the point to note here is that the decision you make isn't final; you can change the storage type later on and the definition of the dimension would still stand. What you are defining at this point is the logical dimensional model and this applies to both relational and multidimensional OLAP. The difference here however is that you can create a multidimensional implementation without going through the intermediate step of building a relational version, an improvement that's down to the new AWXML Java API that was first introduced with the OLAP "A" patchset.

With existing versions of OWB, the next step would be to specify all the levels that the dimension uses, across all hierarchies. With OWB "Paris" though, your next step is to specify all the attributes that will be used by your dimension, across all levels. One change from existing OWB versions is that you define a set of attributes, and you then choose to implement each of these at each of your dimension levels. For example, each level might implement the ID attribute, plus the NAME and DESCRIPTION attribute - these are all pretty much mandatory. Then, your might define two more measures, CUSTOMER SEGMENT and CUSTOMER PROFIT BAND that will then go on to be implemented just by the lowest CUSTOMER level, and another REGION_HOUSEPRICE_GROWTH_BAND that goes on to be implemented by just the REGION level.

This idea of having attributes defined in terms of the whole dimension, then implemented (or not) at each dimension level as required, is something that comes from the Oracle Express / Oracle OLAP world. When you implement your dimension relationally, each level-attribute combination turns into a separate column in the table, and if you implement into an analytic workspace, well that's just how attributes are natively stored.

You might also have noticed that the ID and NAME attributes are specified as Surrogate and Business attributes. What's happening here is that each dimension level will have to implement both a surrogate, synthetic ID and a business ID, meaning for example that your REGION level would not only have the REGION code from your source system (together optionally with the REGION DESCRIPTION), it would also have a synthetic region ID generated at load time by OWB. The reason for this is again down to how dimensions are stored in Express and analytic workspaces - in a relational star schema dimension, each level is stored in a separate column, and the IDs for each level member only has to be unique within the level, whereas analytic workspaces store all dimension level members within a single dimension object and the level IDs need to be unique across the whole dimension, hence the need for a synthetic ID at all levels in the dimension. However, as you'll see later, OWB "Paris" handles all these synthetic keys automatically and all you have to bring across is the business key.

Also, note the significance of the NAME and DESCRIPTION field. The NAME field is actually the one that you put the business key into (such as REG10) whilst the DESCRIPTION field is where the descriptive text goes (such as NORTH-EAST). The NAME field becomes the SHORT_DESCRIPTION whilst the DESCRIPTION field becomes the LONG_DESCRIPTION, and it's these that you'll see in tools like Discoverer for OLAP when you drill into your dimension hierarchy.

The next page of the wizard lets you define the levels that your dimension implements. Note the TOTAL_CUSTOMERS level.


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