Creating Cubes with Simple SQL

If you didn't look carefully at some of the Oracle OLAP 11.2 collateral there's a very interesting new feature that you've probably missed, the ability to create Oracle cubes and dimensions using only SQL. This feature is implemented at the CREATE_MVIEW program in the DBMS_CUBE package.

Here's how it works. You define SQL dimension objects and a table-based materialized view and then run the DBMS_CUBE.CREATE_MVIEW program. This program will (1) create an analytic workspace, (2) create OLAP dimensions from the SQL dimensions, (3) create a cube from the table-based materialized view and (4) create a cube-organized materialized view on the cube to enable query rewrite into the cube.

This feature is primarily designed to make it very easy to use the cube as a summary management solution for applications that query relational tables. For example, you might have an Oracle Business Intelligence (or Business Objects, Cognos or MicroStrategy) application that queries tables. If the application queries summary level data, can benefit from a performance boost and queries tables that tables represent data that is dimensional in nature (e.g., a star or snowflake schema), a cube is a great way to manage that summary data.

Let's look at a scenario. There's a star schema with time, product, customer and channel dimension tables and a sales fact table. The hierarchies are as follows:

Time: Day > Month > Quarter > Year
Product: Item > Subtype > Type > Category > Department
Customer: Customer > City > State > Country > Region
Channel: Channel > Class

The fact table contains data at the Day, Item, Customer and Channel levels for measures Sales and Quantity.

The objective is to create a cube and cube-organized materialized view that manages all summary data beginning at Month, Item, City and Channel levels. Note that in this example the detail of the cube will be a summary of the fact table.

Step 1 is to create SQL dimension objects for each of the dimension tables. (If you want to try this script yourself, download and install the OLAPTRAIN schema from the OLAP page on OTN. http://www.oracle.com/technology/products/bi/olap/11g/samples/schemas/readme.html .)

--
-- Create SQL dimension objects for hierarchies and attributes. The cube will
-- use these as the source definitions for cube.
--
-- Time dimension.
--
CREATE DIMENSION times
LEVEL day IS (times.day_key)
LEVEL month IS (times.month_id)
LEVEL calendar_quarter IS (times.calendar_quarter_id)
LEVEL calendar_year IS (times.calendar_year_id)
HIERARCHY calendar (
day CHILD OF
month CHILD OF
calendar_quarter CHILD OF
calendar_year)
ATTRIBUTE day DETERMINES (day_description)
ATTRIBUTE month DETERMINES (month_name, month_end_date)
ATTRIBUTE calendar_quarter DETERMINES (calendar_quarter_name, calendar_quarter_end_date)
ATTRIBUTE calendar_year DETERMINES (calendar_year_name, calendar_year_end_date);
--
-- Product dimension.
--
CREATE DIMENSION products
LEVEL item IS (products.item_key)
LEVEL subtype IS (products.subtype_key)
LEVEL type IS (products.type_key)
LEVEL category IS (products.category_key)
LEVEL department IS (products.department_key)
HIERARCHY departments (
item CHILD OF
subtype CHILD OF
type CHILD OF
category CHILD OF
department)
ATTRIBUTE item DETERMINES (item_name)
ATTRIBUTE subtype DETERMINES (subtype_name)
ATTRIBUTE type DETERMINES (type_name)
ATTRIBUTE category DETERMINES (category_name)
ATTRIBUTE department DETERMINES (department_name);
--
-- Customer dimension.
--
CREATE DIMENSION customers
LEVEL customer IS (customers.customer_key)
LEVEL city IS (customers.city_key)
LEVEL state IS (customers.state_province_key)
LEVEL country IS (customers.country_key)
LEVEL region IS (customers.region_key)
HIERARCHY regions (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
region)
ATTRIBUTE customer DETERMINES (customers.customer_number)
ATTRIBUTE city DETERMINES (customers.city_name)
ATTRIBUTE state DETERMINES (customers.state_province_name)
ATTRIBUTE country DETERMINES (customers.country_name)
ATTRIBUTE region DETERMINES (customers.region_name);
--
-- Channel dimension.
--
CREATE DIMENSION channels
LEVEL channel IS (channels.channel_key)
LEVEL class IS (channels.class_key)
HIERARCHY classes (
channel CHILD OF
class)
ATTRIBUTE channel DETERMINES (channels.channel_name)
ATTRIBUTE class DETERMINES (channels.class_name);

The next step is to create a table-based materialized view that summarizes data to the level you want to load into the cube. Note that you do not need load data into this materialized view (use the BUILD DEFERRED option). The DBMS_CUBE.CREATE_MVIEW program will simply use the table-based MV to understand how to query the source fact table and to find to the measures to be added to the cube.

--
-- Create the table-based materialized view. In this example, the table-based
-- materialized view aggregates data from the day to month levels in time
-- and the customer to city levels in customer.
--
-- MV is build deferred because the cube doesn't need data from the MV (it just
-- uses the MV's definition.
--
CREATE materialized VIEW sales_mon_ite_cit_cha_mv build deferred
AS
SELECT t.month_name,
p.item_name,
cu.city_name,
ch.channel_name,
SUM(f.sales) sales,
SUM(f.quantity) quantity
FROM times t,
products p,
customers cu,
channels ch,
sales_fact f
WHERE t.day_key = f.day_key
AND p.item_key = f.product
AND cu.customer_key = f.customer
AND ch.channel_key = f.channel
GROUP BY t.month_name,
p.item_name,
cu.city_name,
ch.channel_name;

Finally, run the DBMS_CUBE.CREATE_MVIEW program.

--
-- Create and load the cube-organized materialized view.
--
declare
myCubeMv varchar2(32);
begin
myCubeMv :=
dbms_cube.create_mview(
mvOwner =>user,
mvName =>'sales_mon_ite_cit_cha_mv',
sam_parameters=>'logDest=serverout,build=immediate' );
end;
/

There's several parameters to this program, but I found the defaults to be very reasonable. It built my cube as compressed, partitioned by quarter and pre-aggregation settings of 0 for the top partition and 35 for the bottom partition. You can control any of these settings with the sam_parameters argument. You can also defer the loading of the cube and load the cube later with the DBMS_CUBE.BUILD or DBMS_MVIEW.REFRESH programs.

The cube can be viewed and edited in Analytic Workspace Manager, so you can use it to check the design or make changes such as adding custom measures.

As with any other materialized view, you will need to make sure all the necessary constraints are in place on the base tables. I used AWM’s Relational Access Advisor feature (in the Cube / Materialized View tab) to generate constraint recommendations.