Understanding Oracle OLAP Dimensions And Cubes

Mark Rittman

• 6 min read

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

Sign up for more like this.

Enter your email
Subscribe