Creating OLAP Catalog Metadata, 6 of 6

Creating Metadata Using PL/SQL

OLAP Catalog Release 2 includes a number of related PL/SQL packages for creating CWM2 metadata. These packages contain stored procedures that can create metadata for a wide variety of data warehouses. For example, you can create metadata for parent/child dimension tables and for data stored in analytic workspaces. Before using these packages, make sure that you have performed any required preprocessing steps, as listed in "Choosing the Right Metadata Creation Method".

CWM2 Packages

The following packages contain procedures that create metadata for dimension tables:

CWM2_OLAP_DIMENSION contains procedures for creating OLAP dimensions and providing display names and descriptions.
CWM2_OLAP_HIERARCHY contains procedures for creating OLAP hierarchies and providing display names and descriptions.
CWM2_OLAP_LEVEL contains procedures for creating levels and providing display names and descriptions.
CWM2_OLAP_LEVEL_ATTRIBUTE contains procedures for creating level attributes and providing display names and descriptions.
CWM2_OLAP_DIMENSION_ATTRIBUTE contains procedures for creating dimension attributes and providing display names and descriptions.

The following packages contain procedures that create metadata for fact tables:

CWM2_OLAP_CUBE contains procedures for creating cubes and providing cube display names and descriptions.
CWM2_OLAP_MEASURE contains procedures for creating and deleting measures and providing display names and descriptions.

The following package contains procedures that create metadata for analytic workspace objects:

CWM2_OLAP_AW_OBJECT contains procedures for creating AW objects.

The following packages contain procedures that create the mapping between logical metadata entities and the warehouse structures where the data is stored:

CWM2_OLAP_TABLE_MAP contains procedures that map metadata entities to relational fact tables and dimension tables.
CWM2_OLAP_AW_MAP contain procedures that map metadata entities to multidimensional objects in analytic workspaces.

See Also:
Part IV, "OLAP Catalog Metadata API Reference" for comprehensive syntax and descriptions of these packages.

Example 5-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 PL/SQL calls create a logical metadata dimension object, PRODUCT_DIM, for the PRODUCTS dimension table.


---   Create the PRODUCT Dimension    ---
cwm2_olap_dimension.create_dimension('SH', 'PRODUCT_DIM', 'Product',
          'Products', 'Product Dimension', 'Product Dimiension 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');

Example 5-2 Creating Metadata for a Fact Table

In the Sales History sample schema, COSTS is a fact table with the following columns:

Column Name	Data Type
`PROD_ID`	`NUMBER`
`TIME_ID`	`DATE`
`UNIT_COST`	`NUMBER`
`UNIT_PRICE`	`NUMBER`

The following procedures create a logical metadata cube object, ANALYTIC_CUBE, for the COSTS fact table. The dimensions of the cube are: PRODUCT_DIM, shown above, and TIME_DIM, a time dimension mapped to a table TIME.

---   Create the ANALYTIC_CUBE Cube  ---
cwm2_olap_cube.create_cube('SH', 'ANALYTIC_CUBE', 'Analytics', 'Analytic Cube',
         'Unit Cost and Price Analysis');

---  Add the dimensions to the cube  ---
cwm2_olap_cube.add_dimension_to_cube('SH', 'ANALYTIC_CUBE', 
         'SH', 'TIME_DIM');
cwm2_olap_cube.add_dimension_to_cube('SH', 'ANALYTIC_CUBE', 
         'SH', 'PRODUCT_DIM');

---   Create the measures  ---
cwm2_olap_measure.create_measure('SH', 'ANALYTIC_CUBE', 'UNIT_COST', 
          'Unit Cost','Unit Cost', 'Unit Cost');
cwm2_olap_measure.create_measure('SH', 'ANALYTIC_CUBE', 'UNIT_PRICE', 
          'Unit Price','Unit Price', 'Unit Price');

---   Create the mappings  ---

cwm2_olap_table_map.Map_FactTbl_LevelKey 
     ('SH', 'ANALYTIC_CUBE','SH', 'COSTS', 'LOWEST LEVEL',
      'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID;
       DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;');
cwm2_olap_table_map.Map_FactTbl_Measure 
     ('SH', 'ANALYTIC_CUBE','UNIT_COST', 'SH', 'COSTS', 'UNIT_COST',
      'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID;
       DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;');
cwm2_olap_table_map.Map_FactTbl_Measure 
     ('SH', 'ANALYTIC_CUBE','UNIT_PRICE', 'SH', 'COSTS', 'UNIT_PRICE',
      'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID;
       DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;');