Oracle9i OLAP User's Guide Release 2 (9.2.0.2) Part Number A95295-02 |
|
|
View PDF |
In the Sales History sample schema, PRODUCTS
is a dimension table with the following columns:
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');