Oracle data Warehouse materialized views

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.

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).