Oracle OLAP dimensions and cubes

A reader asks:

"I am new to OLAP and Oracle OLAP and I have a question. I have an Oracle 10g Database with relational tables in it. I want to create dimensions and cubes from that data. Do I need to create separate dimension and fact tables from that relational data, or do I just use the tables from the relational tables?? I am VERY confused about all of the documentation. It's not helping me much."

This is an interesting question, and probably one that many people have had at some point or other. Part of the confusion is due to the way that OLAP has developed within the Oracle database over the years, and therefore it's probably a good idea to take a bit of a history lesson.

Disregarding Oracle Express for the time being, OLAP first became a feature of the Oracle database back with Oracle 8i Enterprise Edition, when some relational OLAP (ROLAP) features were added to the database. Oracle 8i Enterprise Edition came with a database feature known as dimensions, which were an additional layer of metadata you could put over a table or set of tables, to define hierarchical relationships between columns. For example, you could say that one column, 'COUNTRY', was the parent of another column 'REGION', which itself was the parent of another column, 'CITY'. A single dimension could contain multiple hierarchies and the database could contain multiple dimensions, unique within each schema.

Dimensions reference existing tables, and do not contain any data themselves - they merely add additional metadata to existing database objects. For example, to create a product dimension, you'd first create the table that contains the data, and then create the dimension afterwards.

CREATE TABLE products (
prod_id NUMBER(6) NOT NULL,
prod_name VARCHAR2(50 byte) NOT NULL,
prod_desc VARCHAR2(4000 byte) NOT NULL,
prod_subcategory VARCHAR2(50 byte) NOT NULL,
prod_subcat_desc VARCHAR2(2000 byte) NOT NULL,
prod_category VARCHAR2(50 byte) NOT NULL,
prod_cat_desc VARCHAR2(2000 byte) NOT NULL,
prod_weight_class NUMBER(2),
prod_unit_of_measure VARCHAR2(20 byte),
prod_pack_size VARCHAR2(30 byte),
supplier_id NUMBER(6),
prod_status VARCHAR2(20 byte) NOT
NULL,
prod_list_price NUMBER(8, 2) NOT NULL,
prod_min_price NUMBER(8, 2) NOT NULL,
product_total VARCHAR2(13 byte)
);

CREATE DIMENSION products
LEVEL product IS (products.prod_id, products.prod_desc, products.prod_list_price, products.prod_min_price, products.prod_name, products.prod_pack_size, products.prod_status, products.supplier_id, products.prod_unit_of_measure, products.prod_weight_class)
LEVEL subcategory IS (products.prod_subcategory, products.prod_subcat_desc)
LEVEL category IS (products.prod_category, products.prod_cat_desc)
HIERARCHY prod_hier (product CHILD OF subcategory CHILD OF category)
ATTRIBUTE product DETERMINES (products.prod_category, products.prod_id)
ATTRIBUTE subcategory DETERMINES (products.prod_category, products.prod_subcategory)
ATTRIBUTE category DETERMINES products.prod_category;

Dimensions created in this way are then used by the query rewrite mechanism within the Enterprise Edition of the database to perform more complex forms of rewrite - specifically, to allow the rewrite mechanism to aggregate up from summaries at lower levels in a hierarchy to levels higher up. In addition, dimensions help the Oracle 8i summary adviser to recommend materialized views, as the dimension and it's hierarchies define how data 'rolls up' when aggregates are required.

Oracle 9i introduced something called the 'OLAP Option'. The OLAP Option integrated the Oracle Express Server multidimensional engine into the Oracle relational database, and also introduced a further layer of OLAP metadata, known as the OLAP Catalog, together with a Java OLAP API, to provide programmatic and SQL access to OLAP data.

If you use Oracle 9i without the OLAP Option, but you have licensed the Enterprise Edition, you can create dimensions in the same was as with Oracle 8i. As with 8i, you create your tables first, then define your dimensions, which reference columns in the tables. However, if you license the OLAP Option, you now have additional options open to you that can however slightly complicate matters.

One of the key features of the Oracle 9i OLAP Option is that your OLAP data can be stored in either relational tables, or in multidimensional datatypes held within what's termed 'Analytic Workspaces'. Either way, both are accessed using the same Java OLAP API, which in turn decides either to retrieve its data from relational tables or from analytic workspaces, depending on how you've stored the data. Just to complicate matters, analytic workspaces are themselves stored within LOBs in Oracle relational tables, but the way they are created and maintained is quite different to data in relational tables.

With Oracle 9i OLAP Option, to create a relational OLAP dimension, you'd create the table and dimension object as before:

CREATE TABLE products (
prod_id NUMBER(6) NOT NULL,
prod_name VARCHAR2(50 byte) NOT NULL,
prod_desc VARCHAR2(4000 byte) NOT NULL,
prod_subcategory VARCHAR2(50 byte) NOT NULL,
prod_subcat_desc VARCHAR2(2000 byte) NOT NULL,
prod_category VARCHAR2(50 byte) NOT NULL,
prod_cat_desc VARCHAR2(2000 byte) NOT NULL,
prod_weight_class NUMBER(2),
prod_unit_of_measure VARCHAR2(20 byte),
prod_pack_size VARCHAR2(30 byte),
supplier_id NUMBER(6),
prod_status VARCHAR2(20 byte) NOT
NULL,
prod_list_price NUMBER(8, 2) NOT NULL,
prod_min_price NUMBER(8, 2) NOT NULL,
product_total VARCHAR2(13 byte)
);

CREATE DIMENSION products
LEVEL product IS (products.prod_id, products.prod_desc, products.prod_list_price, products.prod_min_price, products.prod_name, products.prod_pack_size, products.prod_status, products.supplier_id, products.prod_unit_of_measure, products.prod_weight_class)
LEVEL subcategory IS (products.prod_subcategory, products.prod_subcat_desc)
LEVEL category IS (products.prod_subcategory, products.proc_subcat_desc)
HIERARCHY prod_hier (product CHILD OF subcategory CHILD OF category)
ATTRIBUTE product DETERMINES (products.prod_category, products.prod_id)
ATTRIBUTE subcategory DETERMINES (products.prod_category, products.prod_subcategory)
ATTRIBUTE category DETERMINES products.prod_category;

but you'd now also call some additional PL/SQL packages to create Oracle OLAP metadata (known as 'CWM Lite' metadata), to enable access via the Java OLAP API, like this:

begin
cwm_classify.remove_entity_descriptor_use(28, cwm_utility.DIMENSION_TYPE, 'SH', 'PRODUCTS');
cwm_olap_dimension.set_plural_name('SH', 'PRODUCTS', 'PRODUCTS');
cwm_olap_dimension.set_default_display_hierarchy('SH', 'PRODUCTS', 'PROD_HIER');
cwm_olap_dimension.set_display_name('SH', 'PRODUCTS', 'PRODUCTS');
cwm_olap_level.set_display_name('SH', 'PRODUCTS', 'PRODUCT', 'PRODUCT');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'PRODUCT', 'PROD_CATEGORY', 'PROD_CATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'PRODUCT', 'PROD_ID', 'PROD_ID');
cwm_olap_level.set_display_name('SH', 'PRODUCTS', 'SUBCATEGORY', 'SUBCATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_CATEGORY', 'PROD_CATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_SUBCATEGORY', 'PROD_SUBCATEGORY');
cwm_olap_level.set_display_name('SH', 'PRODUCTS', 'CATEGORY', 'CATEGORY');
cwm_olap_level_attribute.set_name('SH', 'PRODUCTS', 'CATEGORY', 'PROD_CATEGORY', 'PROD_CATEGORY');
cwm_olap_hierarchy.set_display_name('SH', 'PRODUCTS', 'PROD_HIER', 'PROD_HIER');
cwm_olap_dim_attribute.create_dimension_attribute('SH', 'PRODUCTS', 'Long_Description', 'Long_Description', '');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.DIMENSION_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'Long_Description');
cwm_olap_dim_attribute.create_dimension_attribute('SH', 'PRODUCTS', 'Short_Description', 'Short_Description', '');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.DIMENSION_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'Short_Description');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Long_Description', 'PRODUCT', 'PROD_ID');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Short_Description', 'PRODUCT', 'PROD_CATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Long_Description', 'SUBCATEGORY', 'PROD_SUBCATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Short_Description', 'SUBCATEGORY', 'PROD_CATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Long_Description', 'CATEGORY', 'PROD_CATEGORY');
cwm_olap_dim_attribute.add_level_attribute('SH', 'PRODUCTS', 'Short_Description', 'CATEGORY', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'CATEGORY', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_SUBCATEGORY');
cwm_classify.add_entity_descriptor_use(41, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'PRODUCT', 'PROD_ID');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'PRODUCT', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'SUBCATEGORY', 'PROD_CATEGORY');
cwm_classify.add_entity_descriptor_use(42, cwm_utility.LEVEL_ATTRIBUTE_TYPE, 'SH', 'PRODUCTS', 'CATEGORY', 'PROD_CATEGORY');
commit;
end;

All of this additional work is carried out for you automatically, when you use the Oracle Enterprise Manager GUI to create your dimension, or you can enter the commands manually, as listed above.

With the Oracle 9i OLAP Option, as well as dimension objects, you can also create cube objects. Cube objects are one or more measures, that are dimensioned by by a common set of dimension objects. Cubes are then used by the Java OLAP API, and tools that use the API such as Oracle Business Intelligence Beans, and Discoverer 10.1.2 'Drake', as the basic building blocks of OLAP reports.

To create a simple cube that has one measure and uses our one dimension, first of all create a table to contain the measure

CREATE TABLE sales_measure (
prod_id NUMBER(10) NOT NULL,
amount_sold NUMBER(10) NOT NULL
);

Then run some additional PL/SQL to create our OLAP API objects.

begin cwm_utility.set_object_in_error(null, null, null, null);
end;
ALTER TABLE SH.SALES_MEASURE
ADD CONSTRAINT FK_ON_0PRODUCTS_PROD_ID_SALES_ FOREIGN KEY(PROD_ID)
REFERENCES SH.PRODUCTS(PROD_ID, PROD_DESC, PROD_LIST_PRICE, PROD_MIN_PRICE, PROD_NAME, PROD_PACK_SIZE, PROD_STATUS, SUPPLIER_ID, PROD_UNIT_OF_MEASURE, PROD_WEIGHT_CLASS) RELY DISABLE NOVALIDATE
declare PRODUCTS number;
tmp number;
begin
CWM_OLAP_CUBE.Create_Cube('SH', 'SALES', 'SALES', '');
PRODUCTS := CWM_OLAP_CUBE.Add_Dimension('SH', 'SALES', 'SH', 'PRODUCTS', 'PRODUCTS');
CWM_OLAP_CUBE.Set_Default_Calc_Hierarchy('SH', 'SALES', 'PROD_HIER', 'SH', 'PRODUCTS', 'PRODUCTS');
CWM_OLAP_CUBE.Map_Cube('SH', 'SALES', 'SH', 'SALES_MEASURE', 'FK_ON_0PRODUCTS_PROD_ID_SALES_', 'PRODUCT', 'SH', 'PRODUCTS', 'PRODUCTS');
CWM_OLAP_MEASURE.Create_Measure('SH', 'SALES', 'AMOUNT_SOLD', 'AMOUNT SOLD', '');
CWM_OLAP_MEASURE.Set_Column_Map('SH', 'SALES', 'AMOUNT_SOLD', 'SH', 'SALES_MEASURE', 'AMOUNT_SOLD');
tmp:= cwm_utility.create_function_usage('SUM');
cwm_olap_measure.set_default_aggregation_method('SH', 'SALES', 'AMOUNT_SOLD', tmp, 'SH', 'PRODUCTS', 'PRODUCTS');
commit;
end;

Now, we've created dimensions and cubes using relational tables, and the Oracle OLAP Option, and we can then go on to analyse these using OLAP API-aware tools such as BI Beans, Discoverer 'Drake' and the Excel Add-in.

As an alternative to storing dimensions and measures in relational tables, we can also store them in analytic workspaces. Analytic Workspaces are multidimensional workspaces held within LOBs in Oracle tables, that store data using a technology originally introduced with Oracle's Express line of products. Oracle Express was originally a product designed and sold by a company called IRI, who sold the technology to Oracle in 1995 who then rebadged it and sold it as a specialist OLAP server product for high-end analysis. Eventually, Oracle took this technology and incorporated it into Oracle 9i, and you can now store your OLAP data in these Express-derived analytic workspaces if your application requires high-end analysis, forecasting, analysis or OLAP calculations. Because the OLAP Option is based on the Express Server calculation engine and multidimensional datatypes, it brings across all the Express functionality such as forecasts and demand plans, support for financial models, allocations and budgeting, and support for what-if analysis. Also, unlike relational OLAP cubes, multidimensional OLAP Option cubes are usually "fully solved," with all aggregations computed at load time, giving a faster, more predictable response time for users' queries.

The first step in working with multidimensional datatypes is to create an analytic workspace.

To create your first analytic workspace, start up Analytic Workspace Manager (a standalone download from OTN), log in as your schema owner, then select Tools > OLAP Worksheet from the menu bar. Think of the OLAP Worksheet as the equivalent to the SQL Worksheet; commands you type are in the bottom pane, with the output displayed in the top pane.

The OLAP Worksheet uses a special language for Oracle OLAP, called OLAP DML. Based on the Express SPL, OLAP DML is a language not unlike PL/SQL that's been around for over 20 years, and allows you to create, query, and programmatically control multidimensional datatypes using procedural constructs such as conditions, loops, and subroutines. You can enter OLAP DML commands using the OLAP Worksheet, or you can execute them from PL/SQL using the DBMS_AW.EXECUTE procedure. All the while you're in the OLAP Worksheet you're actually working within an Oracle schema, and in fact you can switch between OLAP DML and SQL if you need to execute an SQL command.

To create your first analytic workspace, type in

aw create my_first_aw

Your analytic workspace should now be created. If you take a look in your schema, you'll find a new table with an AW$ prefix, that contains the analytic workspace you've just created.

Next, we need to create some dimensions. In our case we want to create two dimensions, a new one called "Geography" and one called "Product.", like the one we created relationally beforehand. Note however that these two product dimensions are completely separate and have no relation between each other.

Unlike the Oracle CREATE DIMENSION statement that defines all the dimension levels and the hierarchies in one go, with OLAP DML, you define all the levels as individual dimensions, wrap this up in a "concat" dimension that concatenates the values in individual dimensions, then create what's called a "relation" object that describes the hierarchical relationship between the level values. For example, with our Geography dimension, we'd type in

CREATE city DIMENSION TEXT
CREATE county DIMENSION TEXT
CREATE country DIMENSION TEXT

DEFINE geography DIMENSION CONCAT (country county city)

DEFINE geography.parents RELATION geography <geography>

The key differences between OLAP Option dimensions and relational dimensions are that relational dimensions use level-based dimensions, whilst OLAP Option dimensions are parent-child based. Level-based Dimensions' hierarchies are defined by the relationship between levels, and levels map to columns in relational tables. Oracle OLAP dimensions, however, use parent/child relationships between levels, where dimension members map to a parent column and a child column. The parent/child combination in a given row expresses a hierarchical relationship, and this relationship is stored in the analytic workspace relation object. The advantage of the parent/child approach is that unbalanced or ragged hierarchies can be more easily used, as each route down the hierarchy doesn't need to contain the same number of levels and can be individually defined for each dimension member.

The values that are to be contained in the dimensions are loaded in a later process, together with the links between dimension members that are loaded into the relation object.

Now, do the same for our products dimension

CREATE sku DIMENSION TEXT
CREATE category DIMENSION TEXT
CREATE class DIMENSION TEXT

DEFINE products DIMENSION CONCAT (class categoy sku)

DEFINE products.parents RELATION products <products>

Once we have created our dimension objects, we next create a variable to hold our transactional data. A variable is like a fact table with one fact column, and is defined thus:

DEFINE sales VARIABLE NUMBER (10,2) < geography products>

This tells the OLAP Option to create a variable called "sales," and dimension it by our geography and products concatenated dimensions.

What we've done so far with our analytic workspace is create some dimensions, and a variable to hold some sales data. One problem we've got, however, is that none of this is so far visible to the Java OLAP API, and to make it visible, we've got to create some additional metadata within the analytic workspace.

With Oracle 9i, is a bit of a complicated task, as it has to be done manually, and consists of a two-step process.

Create relational views of the data. These views take the place of fact tables and dimension tables where they do not exist (in the case of multidimensional data).
Use the OLAP Catalog CWM2 PL/SQL procedures to generate the metadata.

Creating this metadata is out of scope of this article, but for more details, take a look at the white paper "Migrating Express Applications To Oracle 9i"

Happily however, this entire process is made considerably easier with Oracle 10g. Like Oracle 9i, Oracle 10g includes an OLAP Option, but with Oracle 10g any objects created in analytic workspaces are automatically enabled for Java OLAP API analysis, so there's no manual process to go through.

So what we've shown here is several things. Firstly, with Oracle 8i, you could create basic relational OLAP dimensions, which were used by the query rewrite and materialized view mechanisms to make summary management more effective. With Oracle 9i, you can still create these dimensions, but if you use the OLAP Option , you can also create 'enhanced' relational OLAP dimensions, and now also cubes, that work with the Java OLAP API. In addition, you can create your dimensions and cubes within analytic workspaces, if you want to take advantage of the advanced OLAP features of the Oracle OLAP/Express engine.

Going back then to the original question, first of all, if you're creating relational OLAP dimensions and cubes, you don't need to create additional tables to hold your data, as your dimensions and cubes are just additional metadata that sits on top of existing tables that is later used by either the query rewrite mechanism, the summary advisor, or by OLAP tools that use the Java OLAP API. If, however, you're creating multidimensional OLAP dimensions and cubes, and storing them in analytic workspaces, you'll need to create dimension and variable objects within the analytic workspace, and you'll either need to manually enable them for the OLAP API if you're using Oracle 9i, or they'll be automatically enabled for you, if you're using Oracle 10g.

For more details on the various OLAP implementations with Oracle 9i and 10g, take a look at these further articles;