Creating a Dimension

Creating a dimension entity is only the first step in constructing the OLAP Catalog metadata for a dimension. Each dimension must have at least one level. More typically, it will have multiple levels, hierarchies, and attributes. Table 2-1 shows the parent-child relationships between the metadata components of a dimension.

Table 2-1 Hierarchical Relationships Between Components of a Dimension

Parent Entity	Child Entity
dimension	dimension attribute, hierarchy, level
dimension attribute	level attribute
hierarchy	level
level	level attribute

Note:

OLAP Catalog dimensions created with the CWM2 procedures are purely logical entities. They have no relationship to database dimension objects. However, OLAP Catalog dimensions created in Enterprise Manager are associated with database dimension objects.

Procedure: Create an OLAP Dimension

Generally, you will create hierarchies and dimension attributes after creating the dimension and before creating the dimension levels and level attributes. Once the levels and level attributes are defined, you can map them to columns in one or more warehouse dimension tables. The general steps are as follows:

Call procedures in CWM2_OLAP_DIMENSION to create the dimension.
Call procedures in CWM2_OLAP_DIMENSION_ATTRIBUTE to create dimension attributes. In general, you will need to define dimension attributes for 'long description' and 'short description'.

The OLAP API requires the following dimension attributes for embedded total dimension tables (for example, views of analytic workspaces): 'ET Key', 'Parent ET Key', 'Grouping ID', and 'Parent Grouping ID'. For more information, see Table 12-1, "Reserved Dimension Attributes".
Call procedures in CWM2_OLAP_HIERARCHY to define hierarchical relationships for the dimension's levels.
Call procedures in CWM2_OLAP_LEVEL to create levels and assign them to hierarchies.
Call procedures in CWM2_OLAP_LEVEL_ATTRIBUTE to create level attributes and assign them to dimension attributes. For 'long description', 'short description' and other reserved dimension attributes, create level attributes with the same name for every level.

The OLAP API requires the following level attributes for embedded total dimension tables (for example, views of analytic workspaces): 'ET Key', 'Parent ET Key', 'Grouping ID', and 'Parent Grouping ID'. For more information, see Table 16-1, "Reserved Level Attributes".
Call procedures in CWM2_OLAP_TABLE_MAP to map the dimension's levels and level attributes to columns in dimension tables.

Example: Create a Product Dimension

The PL/SQL statements in Example 2-1 create a logical CWM2 dimension, PRODUCT_DIM, for the PRODUCTS dimension table in the SH schema.

The following table shows the columns in the PRODUCTS table.

Column Name	Data Type
`PROD_ID`	`NUMBER`
`PROD_NAME`	`VARCHAR2`
`PROD_DESC`	`VARCHAR2`
`PROD_SUBCATEGORY`	`VARCHAR2`
`PROD_SUBCAT_DESC`	`VARCHAR2`
`PROD_CATEGORY`	`VARCHAR2`
`PROD_CAT_DESC`	`VARCHAR2`
`PROD_WEIGHT_CLASS`	`NUMBER`
`PROD_UNIT_OF_MEASURE`	`VARCHAR2`
`PROD_PACK_SIZE`	`VARCHAR2`
`SUPPLIER_ID`	`NUMBER`
`PROD_STATUS`	`VARCHAR2`
`PROD_LIST_PRICE`	`NUMBER`
`PROD_MIN_PRICE`	`NUMBER`
`PROD_TOTAL`	`VARCHAR2`

Example 2-1 Create an OLAP Dimension for the Products Table

---   CREATE THE PRODUCT DIMENSION    ---
exec cwm2_olap_dimension.create_dimension
          ('SH', 'PRODUCT_DIM', 'Product','Products', 'Product Dimension',
           'Product Dimension Values');

---   CREATE DIMENSION ATTRIBUTES  ---
exec cwm2_olap_dimension_attribute.create_dimension_attribute
          ('SH', 'PRODUCT_DIM', 'Long Description', 'Long Descriptions',
          'Long Desc', 'Long Product Descriptions', true);
exec cwm2_olap_dimension_attribute.create_dimension_attribute
          ('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'Product Name',
          'Prod Name', 'Product Name');

---   CREATE STANDARD HIERARCHY  ---
exec cwm2_olap_hierarchy.create_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'Standard', 'Std Product',
           'Standard Product Hierarchy', 'Unsolved Level-Based');
exec cwm2_olap_dimension.set_default_display_hierarchy
          ('SH', 'PRODUCT_DIM', 'standard');

---   CREATE LEVELS  ---
exec cwm2_olap_level.create_level
          ('SH', 'PRODUCT_DIM', 'L4', 'Product ID', 'Product Identifiers',
          'Prod Key','Product Key');
exec cwm2_olap_level.create_level
          ('SH', 'PRODUCT_DIM', 'L3','Product Sub-Category',
          'Product Sub-Categories','Prod Sub-Category', 
          'Sub-Categories of Products');
exec cwm2_olap_level.create_level
          ('SH', 'PRODUCT_DIM', 'L2','Product Category',
           'Product Categories', 'Prod Category', 'Categories of Products');
exec cwm2_olap_level.create_level
          ('SH', 'PRODUCT_DIM', 'L1', 'Total Product', 'Total Products', 
           'Total Prod', 'Total Product');

---   CREATE LEVEL ATTRIBUTES  ---
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'PRODUCT_DIM', 'Long Description', 'L4', 'Long Description',
          'PRODUCT_LABEL', 'L4 Long Desc',
          'Long Labels for PRODUCT Identifiers', TRUE);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'PRODUCT_DIM', 'Long Description', 'L3', 'Long Description',
          'SUBCATEGORY_LABEL', 'L3 Long Desc', 
          'Long Labels for PRODUCT Sub-Categories', TRUE);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'PRODUCT_DIM', 'Long Description', 'L2', 'Long Description',
          'CATEGORY_LABEL', 'L2 Long Desc', 
          'Long Labels for PRODUCT Categories', TRUE);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'L4', 'PROD_NAME_LEV',
          'Product Name', 'Product Name', 'Product Name');

---   ADD LEVELS TO HIERARCHIES  ---
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L4', 'L3');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L3', 'L2');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L2', 'L1');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L1');

---   CREATE MAPPINGS  ---
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L4',
          'SH', 'PRODUCTS', 'PROD_ID');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD',
           'L4', 'Long Description', 'SH', 'PRODUCTS', 'PROD_DESC');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'STANDARD', 'L4', 
          'PROD_NAME_LEV', 'SH', 'PRODUCTS', 'PROD_NAME');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L3','SH', 'PRODUCTS', 
          'PROD_SUBCATEGORY');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD', 'L3', 
          'Long Description', 'SH', 'PRODUCTS', 'PROD_SUBCATEGORY_DESC');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L2','SH', 'PRODUCTS', 
          'PROD_CATEGORY');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD', 'L2', 
          'Long Description', 'SH', 'PRODUCTS', 'PROD_CATEGORY_DESC');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L1','SH', 'PRODUCTS', 
          'PROD_TOTAL');

Procedure: Create a Time Dimension

When constructing metadata for your time dimension tables, you will follow the same general procedure as for any other OLAP dimension. However, several additional requirements apply. The general steps for creating a time dimension are as follows:

Call procedures in CWM2_OLAP_DIMENSION to create the dimension. Specify 'TIME' for the dimension type parameter.
Call procedures in CWM2_OLAP_DIMENSION_ATTRIBUTE to create dimension attributes. In addition to the dimension attributes needed for regular dimensions, define an 'End Date' attribute and a 'Time Span' attribute.
Call procedures in CWM2_OLAP_HIERARCHY to define hierarchical relationships for the dimension's levels. Typical hierarchies are Calendar and Fiscal.
Call procedures in CWM2_OLAP_LEVEL to create levels and assign them to hierarchies. Typical levels are Month, Quarter, and Year.
Call procedures in CWM2_OLAP_LEVEL_ATTRIBUTE to create level attributes and assign them to dimension attributes. In addition to the level attributes needed for regular dimension attributes, create 'End Date' and 'Time Span' attributes for each level and associate them with the 'End Date' and 'Time Span' dimension attributes.
Call procedures in CWM2_OLAP_TABLE_MAP to map the dimension's levels and level attributes to columns in dimension tables. Map the 'End Date' level attributes to columns with a Date data type. Map the 'Time Span' level attributes to columns with a numeric data type.

Example: Create a Time Dimension

The PL/SQL statements in Example 2-1 create a logical CWM2 time dimension, TIME_DIM, for the TIMES dimension table in the SH schema.

The TIMES table includes the following columns.

Column Name	Data Type
`TIME_ID`	`DATE`
`TIME_ID_KEY`	`NUMBER`
`DAY_NAME`	`VARCHAR2(9)`
`CALENDAR_MONTH_NUMBER`	`NUMBER(2)`
`CALENDAR_MONTH_DESC`	`VARCHAR2(8)`
`CALENDAR_MONTH_DESC_KEY`	`NUMBER`
`END_OF_CAL_MONTH`	`DATE`
`CALENDAR_MONTH_NAME`	`VARCHAR2(9)`
`CALENDAR_QUARTER_DESC`	`CHAR(7)`
`CALENDAR_QUARTER_DESC_KEY`	`NUMBER`
`END_OF_CAL_QUARTER`	`DATE`
`CALENDAR_QUARTER_NUMBER`	`NUMBER(1)`
`CALENDAR_YEAR`	`NUMBER(4)`
`CALENDAR_YEAR_KEY`	`NUMBER`
`END_OF_CAL_YEAR`	`DATE`

Example 2-2 Create an OLAP TIme Dimension

---   CREATE THE TIME DIMENSION 
exec cwm2_olap_dimension.create_dimension
          ('SH', 'TIME_DIM', 'Time','Time', 'Time Dimension',
           'Time Dimension Values', 'TIME');

---   CREATE DIMENSION ATTRIBUTE END DATE
exec cwm2_olap_dimension_attribute.create_dimension_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'End Date',
          'End Date', 'Last date of time period', true);

---   CREATE CALENDAR HIERARCHY 
exec cwm2_olap_hierarchy.create_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Calendar', 'Calendar Hierarchy',
           'Calendar Hierarchy', 'Unsolved Level-Based');
exec cwm2_olap_dimension.set_default_display_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR');

---   CREATE LEVELS 
exec cwm2_olap_level.create_level
          ('SH', 'TIME_DIM', 'MONTH', 'Month', 'Months', 'Month','Month');
exec cwm2_olap_level.create_level
          ('SH','TIME_DIM','QUARTER','Quarter','Quarters','Quarter','Quarter');
exec cwm2_olap_level.create_level
          ('SH', 'TIME_DIM', 'YEAR','Year','Years', 'Year', 'Year');

---   CREATE LEVEL ATTRIBUTES  ---
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'Month', 'END DATE',
          'End Date', 'End Date',
          'Last date of time period', TRUE);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'Quarter', 'END DATE',
          'End Date', 'End Date',
          'Last date of time period', TRUE);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'Year', 'END DATE',
          'End Date', 'End Date',
          'Last date of time period', TRUE);

---   ADD LEVELS TO HIERARCHIES
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Month', 'Quarter');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Quarter', 'Year');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Year');

---   CREATE MAPPINGS 
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'TIME_DIM', 'CALENDAR', 'Year',
          'SH', 'TIMES', 'CALENDAR_YEAR_ID');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'TIME_DIM', 'END DATE', 'CALENDAR',
           'Year', 'END DATE', 'SH', 'TIMES', 'END_OF_CAL_YEAR');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'TIME_DIM', 'CALENDAR', 'Quarter','SH', 'TIMES', 
          'CALENDAR_QUARTER_NUMBER');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'TIME_DIM', 'END DATE', 'CALENDAR',
           'Quarter', 'END DATE', 'SH', 'TIMES', 'END_OF_CAL_QUARTER');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'TIME_DIM', 'CALENDAR', 'Month','SH', 'TIMES', 
          'CALENDAR_MONTH_NUMBER');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'TIME_DIM', 'END DATE', 'CALENDAR',
           'Month', 'END DATE', 'SH', 'TIMES', 'END_OF_CAL_MONTH');