Example: Creating OLAP Metadata for a Dimension Table

Example 13-1 Creating Metadata for a Dimension Table

In the Sales History sample schema, PRODUCTS is a dimension table with the following columns:

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

The following statements, excerpted from a PL/SQL script, create a logical CWM2 dimension, PRODUCT_DIM, for the PRODUCTS dimension table.

---   Create the PRODUCT Dimension    ---
cwm2_olap_dimension.create_dimension('SH', 'PRODUCT_DIM', 'Product',
          'Products', 'Product Dimension', 'Product Dimension Values');

---   Create Dimension Attributes  ---
cwm2_olap_dimension_attribute.create_dimension_attribute('SH', 'PRODUCT_DIM',
          'Long Description', 'Long Descriptions',
          'Long Desc', 'Long Product Descriptions', true);
cwm2_olap_dimension_attribute.create_dimension_attribute('SH', 'PRODUCT_DIM',
          'PROD_NAME_DIM', 'Product Name',
          'Prod Name', 'Product Name');

---   Create STANDARD Hierarchy  ---
cwm2_olap_hierarchy.create_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD',
         'Standard', 'Std Product', 'Standard Product Hierarchy', 
         'Unsolved Level-Based');

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

---   Create Level Attributes  ---
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);
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);
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);
cwm2_olap_level_attribute.create_level_attribute('SH', 'PRODUCT_DIM',
          'PROD_NAME_DIM', 'L4', 'PROD_NAME_LEV',
          'Product Name', 'Product Name');

---   Add levels to hierarchies  ---
cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 
          'L4', 'L3');
cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 
          'L3', 'L2');
cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 
          'L2', 'L1');
cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD',
          'L1');

---   Create mappings  ---
cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 
          'STANDARD', 'L4',
          'SH', 'PRODUCTS', 'PROD_ID');
cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 
          'Long Description', 'STANDARD', 'L4', 'Long Description', 'SH',
          'PRODUCTS', 'PROD_DESC');
cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 
          'PROD_NAME_DIM', 'STANDARD', 'L4', 'PROD_NAME_LEV', 'SH',
          'PRODUCTS', 'PROD_NAME');
cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 
          'STANDARD', 'L3','SH', 'PRODUCTS', 'PROD_SUBCATEGORY');
cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 
          'Long Description', 'STANDARD', 'L3', 'Long Description', 'SH',
          'PRODUCTS', 'PROD_SUBCAT_DESC');
cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 
          'STANDARD', 'L2','SH', 'PRODUCTS', 'PROD_CATEGORY');
cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 
          'Long Description', 'STANDARD', 'L2', 'Long Description', 'SH',
          'PRODUCTS', 'PROD_CAT_DESC');
cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 
          'STANDARD', 'L1','SH', 'PRODUCTS', 'PROD_TOTAL');