Skip Headers

Oracle9i OLAP User's Guide
Release 2 (9.2.0.2)

Part Number A95295-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

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');