OWB 11gR2 – Cube Organized Materialized Views

Here we will look at building cube organized materialized views for a relational fact table. The illustration uses the Oracle OLAP 11g Sample Schema and rather than the cube organized materialized views being created via the script in the demo, they will be designed and generated from OWB. The cubes and dimensions designed in OWB can serve multiple purposes;

  • physical data warehouse tables may be generated
  • dimensionally aware ETL operators for maintaining data
  • cube organized materialized views generated for summary management and performance
  • derive the OBIEE layers - a great metadata integration scenario.

OWB 11gR2 supports the design of 11g form Analytic Workspaces (as well as 10g form AWs), multidimensional designs can be deployed as 11g cubes PLUS the relational fact table can be summarized using cube organized materialized views.

Firstly let's download the OLAP training example here. Complete the 'Installing the base OLAPTRAIN schema' step, do not install the analytic workspace (I did this on Linux). We must then import the base table metadata into OWB, we will build dimensions and cubes on top of these tables next. There are further performance illustrations based on these examples that we will illustrate leverage on, the Oracle database viewlet can be found on OTN at Improving Query Performance with Oracle OLAP Cube MVs.

owb_olap1

Now execute the OWB OMB script (download here) to create the cubes and dimensions which are bound to these tables. The script creates the dimensional model.

owb_olap2

This will define the dimensions and cubes and setup the storage type which will inform OWB to generate cube organized materialized views.

owb_olap3

For example the SALES_CUBE object is bound to the underlying fact table SALES_FACT below. The dimensional model defined is skeletal, so the dimensions primarily just have their hierarchies defined for demo purposes (ie. not all dimension attributes are defined).

owb_olap4

We can see the cube and dimensions are all defined to be stored as relational using cube organized materialized views, they have the 'ROLAP: with Cube MVs' option enabled. This lets the user select information from both the relational storage options (such as create bitmap indexes) and from the multidimensional storage options such as the name for the analytic workspace.

owb_olap5

As it stands if we deployed the summary in the AW would have the same grain as the base fact table, which is much more than we need for summary management. To illustrate, if the code is generated for the cube we can see the join from the SALES_FACT table to the TIMES table is based on the DAY_KEY and also the expression loaded into the cube is DAY_KEY (see the Expression and JoinCondition properties below). Remember the DAY level is our lowest level for the time dimension in our cube.

owb_olap6

So what about storing summaries at the non-leaf level...? Read on...

Summary levels in cube organized materialized view

If we want to summarize at a non-leaf level in the cube, then we must change the dependent dimensions' summary load level. For example we can see just now in the TIMES_DIM dimension the summary load level is not set (so the leaf is used).

owb_olap7

We can set the MONTH level for the STANDARD hierarchy as the summary level, the base facts are still stored in the relational fact table at DAY, but the summary is loaded into the cube organized materialized view for MONTH.

owb_olap8

Now if we generate the cube we will see the join condition is still the same but the expression for TIMES is now MONTH_ID (see the Expression="TIMES.MONTH_ID" name value pair below).

owb_olap9

Generating the dimensions generates multiple scripts; one script is the relational DDL which goes to the DAY level, and the other script is to build the dimension definitions in the AW which have the summary level in the cube organized view as MONTH.

owb_olap10

What's the point of storing the entire fact table in the MV again? Silly right? Just as well we can define the summary level that is loaded in the cube organized materialized view. We can for example change the PRODUCT_DIM dimension to have a summary level of SUBTYPE rather than the base of ITEM. Note this is done on the dimension, not on the cube.

So why will this generate a materialized view....?

There are configuration properties that tell the OWB code generator to enable the MV creation, see the figure below initially the script created the Enable Query Rewrite with value DISABLE, we must change this to ENABLE.

owb_olap11    ==>Enable==> owb_olap12   

Once this has been changed then when we generate and deploy all the dimensions and cube an AW is created with a cube setup for rewrite using cube organized materialized views.

Show me the MVs....

After the dimensions and cubes have been deployed, you can inspect the Oracle dictionary to see the materialized views created, and even open up AWM and see the cubes and dimensions created by OWB to represent the cube organized materialized view.

Here we see the materialized views created;

SQL> select mview_name from user_mviews;

MVIEW_NAME
------------------------------
CB$SALES_CUBE
CB$CHANNELS_DIM_STANDARD
CB$CUSTOMERS_DIM_STANDARD
CB$PRODUCTS_DIM_STANDARD
CB$TIMES_DIM_STANDARD

In AWM we can see the TIMES_DIM dimension for example does not have the DAY level loaded, the summary in the AW starts at the MONTH level, base fact data is stored in the relational fact table, and is not replicated in the AW.

owb_cubemv_awm1

The cube is defined with all of the desired materialized view options enabled.

owb_cubemv_awm2

 

Refreshing the materialized views...

Above we configured the materialized views to refresh on demand, with this options we have to manually execute the refresh of the materialized views. The views can also be refreshed on commit (when the data is inserted into the fact or dimension table, the summary is automatically refreshed) or periodically using a simple schedule (using start date and next date expressions).

To manually refresh, detach from the AW in AWM. Now let's manually refresh the materialized views from SQLPlus (just like regular database materialized views you can refresh on demand, on commit, on a scheduler etc.).

  • execute DBMS_SNAPSHOT.REFRESH('CB$CHANNELS_DIM_STANDARD','C')
  • execute DBMS_SNAPSHOT.REFRESH('CB$CUSTOMERS_DIM_STANDARD','C')
  • execute DBMS_SNAPSHOT.REFRESH('CB$PRODUCTS_DIM_STANDARD','C')
  • execute DBMS_SNAPSHOT.REFRESH('CB$TIMES_DIM_STANDARD','C')
  • execute DBMS_SNAPSHOT.REFRESH('CB$SALES_CUBE','C')

These steps could be included in a process flow for example or other OWB objects (transformations, pre mapping transformation and so on) and executed.

Summary

In summary we have see how cube organized materialized views can be generated from a relational data warehouse design in OWB 11gR2. The rich metadata definitions captured in the OWB designer provide many benefits from data warehouse design to a rich set of operators built specifically for loading warehouses, to summary management capabilities and business reporting integration. In subsequent postings we'll see the query plans and integration with OBIEE.

Comments:

hi,
thanks for your email but in this link the picture did not show that i dont know it is problem of my internet and browser or there is another problem.

I have another qustion I want to know the structure of datawarehouse is should be on an instance or tow instance because i heared the confighure database for olap is differeft.have you any document about this issue to guide me.

Regards,

Posted by guest on November 10, 2011 at 07:58 AM PST #

Hi,
Please Is it possible to guide me about this question
I have another qustion I want to know the structure of datawarehouse is should be on an instance or tow instance because i heared the confighure database for olap is differeft.have you any document about this issue to guide me.

Regards,

Posted by guest on November 11, 2011 at 02:01 PM PST #

Hi

Its worth doing more background reading such as this 2 day data warehousing reference among others;
http://download.oracle.com/docs/cd/E11882_01/server.112/e25555/toc.htm

Cheers
David

Posted by David on November 15, 2011 at 01:55 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today