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.
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.
Oracle 9i introduced ‘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.
To create a cube that has one measure and uses our one dimension, first 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 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;
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 analytic workspace, type in aw create my_first_aw
In your schema you’ll find a new table with an AW$ prefix, that contains the analytic workspace you’ve just created.
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.