Inside the Oracle 11g Cube organized materialized views

Oracle's acquisition of Hyperion's OLAP software now gives Oracle access to the powerful Essbase engine, which will be integrated with the legacy ?Oracle Express? technology that Oracle acquired from IRI software.

Cube organized materialized views have extra costs

Many extra-cost components have moved inside the 11g kernel software and are tightly integrated to the Oracle11g RDBMS engine.  Because of their tightly-coupled nature, they are installed by Oracle 11g by default, add-on tools such as the Automatic Workload Repository, components of the Oracle 11g BI Suite, Oracle Data Mining (ODM), and the Oracle warehouse builder (OWB).

These are now available by default, but you must be cognizant that these are extra-cost features and their usage can be audited by Oracle Corporation.

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options

Using cube organized materialized views

Oracle has devised a way to use Oracle's materialized view construct to store OLAP cubes, much in the same fashion as materialized views are used to pre-join tables and pre-aggregate table data. 

The Oracle 11g BI documentation notes the interface layer between the conceptual star schema and OLAP cube, and the underlying data storage layer:

In this use of the cube, summary data is managed within the cube and revealed to the application as a cube-organized materialized view. The application continues to query the detailed level data in relational tables, expressing queries for summary level data with an aggregation function and GROUP BY.

The automatic query rewrite feature of the Oracle Database automatically rewrites the query to the cube organized materialized view. This application benefits from improved query performance.

Inside the 11g Business Intelligence Suite, these OLAP cubes are the underlying representation of Oracle's multidimensional star schema.

• Cube-organized materialized views can be accessed by standard SQL queries (with the pivot syntax), the base tables being accessed via the query re-write mechanism.   Cube-organized materialized views can also be accessed via the Oracle Express traditional dimensional queries.
   
• Cube-organized materialized views are supported within RAC/Grid.
   
• OLAP cubes are presented as cube-organized materialized views and can be integrated into a star schema.
   
• Cube-organized materialized views cannot be used with transportable tablespaces.

Access to the cube-organized materialized views is available directly within Oracle SQL.  Oracle 11g SQL has a new ?pivot? SQL operator, replacing the decode function for querying multidimensional data

It uses the same query re-write mechanism, and has the same automatic update mechanism to keep the OLAP cube ?fresh? as data changes within the underlying dimensions.  The refresh mechanism offers several refresh mechanisms:

• Instant cube-organized materialized view refreshing ("on commit" using dbms_mview.refresh)
   

• Refresh after a pre-defined threshold of 'tolerated staleness? is exceeded.
   

• A scheduled refresh, often performed hourly, daily or weekly.

As an example of a cube-organized materialized view, consider the daily collection of sales summary data by region.  This is a standard two-dimensional table, with sales rows, and distinct columns for each region. 

When we add-in the time dimensional, the representation becomes cubic, or three dimensional.  In practice, the third-dimension of an OLAP cube is often a DATE datatype.

Laurent Schneider, co-author of the book "Oracle 11g New Features" gives an example of using the "pivot" operator to replace the archaic decode syntax:

select deptno,
  decode(x,1,'DNAME','LOC') type,
  decode(x,1,dname,loc) value
from dept,
 (select 1 x from dual union all
  select 2 from dual);

    DEPTNO TYPE  VALUE
---------- ----- --------------
        10 DNAME ACCOUNTING
        20 DNAME RESEARCH
        30 DNAME SALES
        40 DNAME OPERATIONS
        10 LOC   NEW YORK
        20 LOC   DALLAS
        30 LOC   CHICAGO
        40 LOC   BOSTON

select name, type, value
from person , property
pivot (max(value)
for type in (
  ?gender? as gender,
  ?category? as category))
where person.id = property.person (+);

select *
from dept
unpivot (value
for type in (
  dname as ?DNAME?,
  loc as ?LOC?));