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.
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.
This will define the dimensions and cubes and setup the storage type which will inform OWB to generate cube organized materialized views.
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).
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.
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.
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).
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.
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).
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.
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.
==>Enable==> 
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.
The cube is defined with all of the desired materialized view options enabled.
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.
