SQLMDX

Eventually it's all about data

Oracle OLAP option. Quick dive.

Posted by sqlmdx on March 26, 2016

There are quite a few tools and ways to build and manage cubes in Oracle so in this post I’ll try to clarify some concepts.
Good top level introduction could be found in article by Arup Nanda – Data Warehousing and OLAP.
Detailed step by step tutorial – Building OLAP Cubes.
The easiest way to start is to explore existing sample schema.

Download GLOBAL schema using this link.
Following objects should be created after installation

SQL> select object_type, object_name
  2    from user_objects
  3   where object_type <> 'INDEX'
  4   order by object_type, object_name;

OBJECT_TYPE     OBJECT_NAME
--------------- ------------------------------
DIMENSION       CHANNEL_DIMENSION
DIMENSION       CUSTOMER_DIMENSION
DIMENSION       PRODUCT_DIMENSION
DIMENSION       TIME_DIMENSION
SEQUENCE        CUBE_BUILD_ID
TABLE           ACCOUNT
TABLE           CHANNEL_DIM
TABLE           CUSTOMER_DIM
TABLE           PRICE_FACT
TABLE           PRODUCT_CHILD_PARENT
TABLE           PRODUCT_DIM
TABLE           TIME_DIM
TABLE           UNITS_FACT

13 rows selected.

To deploy cubes run Analytic Workspace Manager (can be downloaded from this page – Oracle OLAP Downloads: Software, Sample Schemas and Sample Code) and choose menu “Create Analytic Workspace from Template”.
There are two predefined workspaces in the archive – GLOBAL_MV.XML and GLOBAL.XML. First uses cube organized MVs while second does no as implied in the names.
Let’s start with second one.
After importing workspace in AWM the list of user objects looks as following (indexes and partitions excluded)

OBJECT_TYPE     OBJECT_NAME                    CREATED_FROM_AWM
--------------- ------------------------------ --------------------
CUBE            PRICE_CUBE                     Y
CUBE            UNITS_CUBE                     Y
CUBE DIMENSION  CHANNEL                        Y
CUBE DIMENSION  CUSTOMER                       Y
CUBE DIMENSION  PRODUCT                        Y
CUBE DIMENSION  TIME                           Y
DIMENSION       CHANNEL_DIMENSION
DIMENSION       CUSTOMER_DIMENSION
DIMENSION       PRODUCT_DIMENSION
DIMENSION       TIME_DIMENSION
LOB             SYS_LOB0000243395C00009$$      Y
LOB             SYS_LOB0000243399C00004$$      Y
SEQUENCE        CUBE_BUILD_ID
SEQUENCE        GLOBAL_S$                      Y
TABLE           ACCOUNT
TABLE           AW$GLOBAL                      Y
TABLE           CHANNEL_DIM
TABLE           CUBE_TEMPLATES                 Y
TABLE           CUSTOMER_DIM
TABLE           PRICE_FACT
TABLE           PRODUCT_CHILD_PARENT
TABLE           PRODUCT_DIM
TABLE           TIME_DIM
TABLE           UNITS_FACT
VIEW            CHANNEL_PRIMARY_VIEW           Y
VIEW            CHANNEL_VIEW                   Y
VIEW            CUSTOMER_MARKET_VIEW           Y
VIEW            CUSTOMER_SHIPMENTS_VIEW        Y
VIEW            CUSTOMER_VIEW                  Y
VIEW            PRICE_CUBE_VIEW                Y
VIEW            PRODUCT_PRIMARY_VIEW           Y
VIEW            PRODUCT_VIEW                   Y
VIEW            TIME_CALENDAR_VIEW             Y
VIEW            TIME_FISCAL_VIEW               Y
VIEW            TIME_VIEW                      Y
VIEW            UNITS_CUBE_VIEW                Y

36 rows selected.

Last columns is used to highlight whether object was created using initial script or from AWM.
We see that two CUBEs and few CUBE DIMENSIONs were created as well as several views.
Cubes and their dimensions may be queried using cube_table function.

SQL> select * from table(cube_table('GLOBAL.PRICE_CUBE'));

no rows selected

Query above returned zero rows because in order to populate cube with data “Maintain Cube …” command should be run in AWM by clicking right mouse button on corresponding cube.
Once cube is maintained (I’d prefer to call it “processed” though) – all the data is populated in AW$GLOBAL.

SQL> delete from aw$global;

1773 rows deleted.

SQL> select count(*) cnt from table(cube_table('GLOBAL.PRICE_CUBE'));
select count(*) cnt from table(cube_table('GLOBAL.PRICE_CUBE'))
                               *
ERROR at line 1:
ORA-33272: Analytic workspace GLOBAL.GLOBAL cannot be opened.
ORA-01403: no data found


SQL> rollback;

Rollback complete.

SQL> select count(*) cnt from table(cube_table('GLOBAL.PRICE_CUBE'));

       CNT
----------
      4779

One cube is stored in a BLOB column in one row but AW$GLOBAL contains a lot of metadata as well as dimensions’ data – this explains why almost two thousand rows were deleted.

Let’s have a closer look at function cube_table.

SQL> select count(*) cnt from table(cube_table('GLOBAL.PRICE_CUBE'));

       CNT
----------
      4779

SQL> select * from table(dbms_xplan.display_cursor(format => 'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*) cnt from table(cube_table('GLOBAL.PRICE_CUBE'))

Plan hash value: 953371085

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  SORT AGGREGATE  |      |
|   2 |   CUBE SCAN      |      |
---------------------------------


14 rows selected.

cube_table function generates access path “CUBE SCAN” (may generate several other variations but always with the word “CUBE”).
Its definition is following

function cube_table(cube in varchar2) return sys.anydataset pipelined using olapimpl_t;

and declaration of olapimpl_t is following

type olapimpl_t authid current_user as object(shared_ctx raw(8), mutable_ctx raw(8), tdo_ctx sys.anytype, stmt_id number);

Function look like typical one with the ODCITable interface but type’s source code is quite unexpected.
It does not contain any methods and is not even mapped to external library. Let’s try to create the same function with another name

SQL> create or replace function cube_table0(cube in varchar2) return sys.anydataset pipelined using olapimpl_t;
  2  /

Function created.

SQL> select count(*) cnt from table(cube_table0('GLOBAL.PRICE_CUBE'));
select count(*) cnt from table(cube_table0('GLOBAL.PRICE_CUBE'))
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Function has been created successfully but cannot be executed. Looks like there are some internal code with the hardcoded function name.

So just to summarize: by default OLAP engine stores cubes in BLOB column in AW$GLOBAL table and cube_table function is used to access cube measures/dimensions from that BLOB column.
It’s worth to mention that it’s impossible to use parallel execution with such approach.
Also cube_table has negative side effect like oll other function which return anydataset – creation a number of SYSTP%== types.
After importing workspace (deploying cubes) AWM created several views to encapsulate cube_table function.

Now let’s build UNITS_CUBE based on cube organized materialized views.
In order to do that it’s required to change each dimension data constraints to “Star Consistent” and check the box “Enable Materialized View Refresh of the Cube” in cube properties.
Once all changes are applied AWM will create following objects

OBJECT_TYPE          OBJECT_NAME
-------------------- ------------------------------
MATERIALIZED VIEW    CB$CHANNEL_PRIMARY
MATERIALIZED VIEW    CB$CUSTOMER_MARKET
MATERIALIZED VIEW    CB$CUSTOMER_SHIPMENTS
MATERIALIZED VIEW    CB$PRODUCT_PRIMARY
MATERIALIZED VIEW    CB$TIME_CALENDAR
MATERIALIZED VIEW    CB$TIME_FISCAL
MATERIALIZED VIEW    CB$UNITS_CUBE
TABLE                CB$CHANNEL_PRIMARY
TABLE                CB$CUSTOMER_MARKET
TABLE                CB$CUSTOMER_SHIPMENTS
TABLE                CB$PRODUCT_PRIMARY
TABLE                CB$TIME_CALENDAR
TABLE                CB$TIME_FISCAL
TABLE                CB$UNITS_CUBE
TABLE                CR$UNITS_CUBE

15 rows selected.

In order to correctly populate all the aggregations cube must be rebuilt.
If rebuilding executed using SQL statement (from let’s say SQL*PLus) then AW should be detached from AWM – otherwise it will hang on enq: AW – user access for AW.
All AW sessions with corresponding attach modes are listed in v$aw_olap.
Below you can see data in CHANNEL dimension before and after rebuild.

DIM_KEY         LEVEL_N CHANNEL_TOTAL_ID     CHANNEL_CHANNEL_ID
--------------- ------- -------------------- --------------------
CHANNEL_CAT     CHANNEL
CHANNEL_DIR     CHANNEL
CHANNEL_INT     CHANNEL
TOTAL_TOTAL     TOTAL

SQL> exec dbms_cube.build('UNITS_CUBE');

PL/SQL procedure successfully completed.

SQL> select * from cb$channel_primary;

DIM_KEY         LEVEL_N CHANNEL_TOTAL_ID     CHANNEL_CHANNEL_ID   LONG_DESCRIPTIO SHORT_DESCRIPTI
--------------- ------- -------------------- -------------------- --------------- ---------------
CHANNEL_CAT     CHANNEL TOTAL                CAT                  Catalog         Catalog
CHANNEL_DIR     CHANNEL TOTAL                DIR                  Direct Sales    Direct Sales
CHANNEL_INT     CHANNEL TOTAL                INT                  Internet        Internet
TOTAL_TOTAL     TOTAL   TOTAL                                     Total Channel   Total Channel

Following access path was used to query data from cube organized MV

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|   1 |  MAT_VIEW CUBE ACCESS |      |
--------------------------------------

If we have a look at definition of cb$channel_primary

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','CB$CHANNEL_PRIMARY') from dual;
create materialized view "GLOBAL"."CB$CHANNEL_PRIMARY"
(
   "DIM_KEY"
  ,"LEVEL_NAME"
  ,"CHANNEL_TOTAL_ID"
  ,"CHANNEL_CHANNEL_ID"
  ,"LONG_DESCRIPTION"
  ,"SHORT_DESCRIPTION"
)
ORGANIZATION CUBE ON "GLOBAL"
 ( DIMENSION "CHANNEL" IS "DIM_KEY"
     ATTRIBUTE "CHANNEL_LEVELREL" IS "LEVEL_NAME"
     ATTRIBUTE "CHANNEL_CHANNEL_TOTAL_ID_UNIQU" IS "CHANNEL_TOTAL_ID"
     ATTRIBUTE "CHANNEL_CHANNEL_CHANNEL_ID_UNI" IS "CHANNEL_CHANNEL_ID"
     ATTRIBUTE "CHANNEL_LONG_DESCRIPTION" IS "LONG_DESCRIPTION"
     ATTRIBUTE "CHANNEL_SHORT_DESCRIPTION" IS "SHORT_DESCRIPTION"
 )
build deferred
refresh force on demand using enforced constraints
disable query rewrite
as
   (select (case grouping_id(t1."TOTAL_ID", t1."CHANNEL_ID")
               when 1 then to_char(('TOTAL_' || t1."TOTAL_ID"))
               else to_char(('CHANNEL_' || t1."CHANNEL_ID"))
            end)
              "DIM_KEY"
          ,(case grouping_id(t1."TOTAL_ID", t1."CHANNEL_ID")
               when 1 then to_char('TOTAL')
               else to_char('CHANNEL')
            end)
              level_name
          ,t1."TOTAL_ID" "CHANNEL_TOTAL_ID"
          ,t1."CHANNEL_ID" "CHANNEL_CHANNEL_ID"
          ,(case grouping_id(t1."TOTAL_ID", t1."CHANNEL_ID")
               when 1 then max(t1."TOTAL_DSC")
               else max(t1."CHANNEL_DSC")
            end)
              "LONG_DESCRIPTION"
          ,(case grouping_id(t1."TOTAL_ID", t1."CHANNEL_ID")
               when 1 then max(t1."TOTAL_DSC")
               else max(t1."CHANNEL_DSC")
            end)
              "SHORT_DESCRIPTION"
      from global."CHANNEL_DIM" t1
    group by (t1."TOTAL_ID"), rollup((t1."CHANNEL_ID")))

It contains keywords “ORGANIZATION CUBE” but there is no information about this clause in documentation so presumably all such views should be generated using UI tools, in particular AWM or subprograms like DBMS_CUBE.CREATE_MVIEW.
On the other hand it’s possible to create cube organized view manually and query it but it does not make much sense since it should be used along with other dimensions and facts.
Crucial point here is that data for cube organized MV is not stored in segment(s) like ordinary MV. So query below return zero rows.

select * from dba_segments where segment_name like 'CB$%'

Now, it’s interesting to compare performance of cubes stored as BLOBs vs cubes based on cube organized MVs vs warehouse which does not use OLAP option at all.
Generic comparison can be found in this white paper – Comparing Materialized Views and Analytic Workspaces in Oracle Database 11g, more detailed performance analysis is a subject for another blog post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: