Basics and Myths about Oracle OLAP

Mark Whitehorn explores The myths and legends of the holy land of the database 

The database world has more myths and legends than the court of King Arthur. The current myths tend to be less about dragons and dungeons and more about features and performance, such as:

Oracle can’t do MOLAP.
Oracle is as easy to tune as a cathedral organ.
DB2 only runs on mainframes.
SQL Server doesn’t scale.
Oracle is eye-wateringly expensive.
Access isn’t a relational database.
SQL Server cannot row lock.

Although, sometimes there is more than a hint of scandal:

Microsoft was caught stealing secrets from Borland.
Oracle was caught cheating at the TPC benchmarks.
Philippe Kahn once bought Hawaiian shirts for 3,000 database conference delegates just to annoy Microsoft.


Here is an article about Oracle’s OLAP technology and how it fits in the database. 
The OLAP part is on page 2. 

Here is an excerpt

OLAP (On-Line Analytical Processing) is a way of organising data so it can be analysed very rapidly by users. It takes a base set of relational data (say, sales of individual products to particular customers, day by day) and calculates a set of aggregated values (eg sales of products to all customers in Essex for each month). The combination of the base and aggregated data is known as an OLAP cube.

The point of building an OLAP cube is that when users ask to see, say, the sales of fridges, month by month in a particular county, the answer comes back very rapidly. OLAP can be implemented in three ways:

ROLAP is Relational OLAP and means you are storing the base data and the aggregations in relational tables and managing it using a relational database engine.

MOLAP stores both the base and aggregated data as a multi-dimensional array of data. This data must be managed by a specialised multi-dimensional database engine.

HOLAP is a hybrid between the two. The base data is held in relational tables and the aggregations in a multi-dimensional array.

Why three different ways to implement? Well, ROLAP means you can use the engine that you know and love. It is safe, secure and familiar. In addition, when OLAP started, there were no multi-dimensional database engines, so ROLAP was all we had.

MOLAP is a more modern approach. It usually generates aggregations faster and the cube it generates is generally faster to query. As a result, MOLAP is now very popular.

HOLAP can be useful where you have a massive set of base data and you find the users of the systems very often query just the aggregated data.

It is a really good and quick read, it clarifies some of the OLAP techniques used by Oracle

Well, the base data is, as always, a set of data stored in text and numeric fields in relational tables. The “normal” ROLAP process creates a set of aggregated values and stores them as numeric and text fields in a relational table. When Oracle creates an OLAP cube it generates the aggregations as a set of multi-dimensional arrays and stores these, not as a structure to disk, but in BLOB (Binary Large Object) fields within a relational table. The fact that Oracle creates multi-dimensional arrays as the aggregation store tells us that this is definitely MOLAP – the only oddity is where those arrays are stored.

Here is the complete article <>

Mark Whitehorn has written books on Essbase and MDX, as well as writing about technology.

Leave a Comment