Creating a Metadata Command Script

Each procedure in the CWM2_OLAP_EXPORT package accepts a parameter that identifies a metadata command script file. The contents of this script are the commands that build the metadata.

The commands in the command script file may be CWM2 procedure calls, CWM1 procedure calls, CREATE DIMENSION statements to re-create the dimension objects associated with CWM1 dimensions, and CREATE VIEW statements to re-create the views when the metadata is mapped to views of the source dimension tables and fact tables.

You can create a metadata command script without creating an Export parameter file. However, if you run the script in a different database without importing or re-creating the source tables, the metadata will be invalid.

Example 13-1 shows the metadata command script for the GLOBAL.PRODUCT dimension. This dimension was created in Enterprise Manager, therefore it has an associated Oracle dimension object and its metadata was defined using the CWM1 APIs.

Example 13-1 Metadata Command Script for GLOBAL.PRODUCT

The following command creates a metadata command script for the GLOBAL.PRODUCT dimension. It does not create an Export parameter file.

>EXECUTE cwm2_olap_export.export_dimension
             ('GLOBAL', 'PRODUCT', '/myscripts', 'GLOBALPROD_CMD_SCRIPT.SQL');

To re-create the metadata, transfer the GLOBALPROD_CMD_SCRIPT.SQL file to a directory that can be accessed by the database. In SQL*Plus, navigate to this directory and run the script with a command like the following.

>@GLOBALPROD_CMD_SCRIPT.SQL

The contents of GLOBALPROD_CMD_SCRIPT.SQL are shown as follows.

CREATE DIMENSION GLOBAL.PRODUCT
  LEVEL CLASS IS (GLOBAL.PRODUCT_DIM.CLASS_ID)
  LEVEL FAMILY IS (GLOBAL.PRODUCT_DIM.FAMILY_ID)
  LEVEL ITEM IS (GLOBAL.PRODUCT_DIM.ITEM_ID)
  LEVEL TOTAL_PRODUCT IS (GLOBAL.PRODUCT_DIM.TOTAL_PRODUCT_ID)
  HIERARCHY PRODUCT_ROLLUP
  ( ITEM CHILD OF
    FAMILY CHILD OF
    CLASS CHILD OF
    FAMILY CHILD OF
    CLASS CHILD OF
    TOTAL_PRODUCT
  )
  ATTRIBUTE CLASS DETERMINES
  ( CLASS_DSC
  )
  ATTRIBUTE FAMILY DETERMINES
  ( FAMILY_DSC
  )
  ATTRIBUTE ITEM DETERMINES
  ( ITEM_DSC
  )
  ATTRIBUTE TOTAL_PRODUCT DETERMINES
  ( TOTAL_PRODUCT_DSC
  )
  ATTRIBUTE ITEM DETERMINES
  ( ITEM_PACKAGE_ID
  )
  ATTRIBUTE CLASS DETERMINES
  ( CLASS_DSC
  )
  ATTRIBUTE FAMILY DETERMINES
  ( FAMILY_DSC
  )
  ATTRIBUTE ITEM DETERMINES
  ( ITEM_DSC
  )
  ATTRIBUTE TOTAL_PRODUCT DETERMINES
  ( TOTAL_PRODUCT_DSC
  )
  ;
EXECUTE cwm_olap_dimension.Set_Description('GLOBAL', 'PRODUCT', '');
EXECUTE cwm_olap_dimension.Set_Display_Name('GLOBAL', 'PRODUCT', 'Product');
EXECUTE cwm_olap_dimension.Set_Plural_Name('GLOBAL', 'PRODUCT', 'PRODUCT');
EXECUTE cwm_olap_dim_attribute.Create_Dimension_Attribute
           ('GLOBAL', 'PRODUCT', 'Long_Description', 'Long Description', '');
EXECUTE cwm_olap_dim_attribute.Create_Dimension_Attribute
           ('GLOBAL', 'PRODUCT', 'Package', 'Package', '');
EXECUTE cwm_olap_dim_attribute.Create_Dimension_Attribute
           ('GLOBAL', 'PRODUCT', 'Short_Description', 'Short Description', '');
EXECUTE cwm_olap_hierarchy.Set_Description('GLOBAL', 'PRODUCT', 'PRODUCT_ROLLUP', '');
EXECUTE cwm_olap_hierarchy.Set_Display_Name('GLOBAL', 'PRODUCT', 'PRODUCT_ROLLUP', 'Product Rollup');
EXECUTE cwm_olap_dimension.Set_Default_Display_Hierarchy('GLOBAL', 'PRODUCT', 'PRODUCT_ROLLUP');
EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'CLASS', '');
EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'CLASS', 'Class');
EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'FAMILY', '');
EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'FAMILY', 'Family');
EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'ITEM', '');
EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'ITEM', 'Item');
EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'TOTAL_PRODUCT', '');
EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'TOTAL_PRODUCT', 'Total Product');
EXECUTE cwm_olap_level_attribute.Set_Name('GLOBAL', 'PRODUCT', 'CLASS', 'CLASS_DSC', 'CLASS_DSC');
EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute
           ('GLOBAL','PRODUCT','Short_Description','CLASS','CLASS_DSC');
EXECUTE cwm_olap_level_attribute.Set_Description('GLOBAL', 'PRODUCT', 'CLASS', 'CLASS_DSC', '');
EXECUTE cwm_olap_level_attribute.Set_Display_Name('GLOBAL', 'PRODUCT', 'CLASS', 'CLASS_DSC', '');
EXECUTE cwm_olap_level_attribute.Set_Name
           ('GLOBAL', 'PRODUCT', 'FAMILY', 'FAMILY_DSC', 'FAMILY_DSC');
EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute
           ('GLOBAL','PRODUCT','Short_Description','FAMILY','FAMILY_DSC');
EXECUTE cwm_olap_level_attribute.Set_Description
           ('GLOBAL', 'PRODUCT', 'FAMILY', 'FAMILY_DSC', '');
EXECUTE cwm_olap_level_attribute.Set_Display_Name
           ('GLOBAL', 'PRODUCT', 'FAMILY', 'FAMILY_DSC', '');
EXECUTE cwm_olap_level_attribute.Set_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_DSC', 'ITEM_DSC');
EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute('GLOBAL','PRODUCT','Short_Description','ITEM','ITEM_DSC');
EXECUTE cwm_olap_level_attribute.Set_Description('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_DSC', '');
EXECUTE cwm_olap_level_attribute.Set_Display_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_DSC', '');
EXECUTE cwm_olap_level_attribute.Set_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_PACKAGE_ID', 'ITEM_PACKAGE_ID');
EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute('GLOBAL', 'PRODUCT', 'Package', 'ITEM', 'ITEM_PACKAGE_ID');
EXECUTE cwm_olap_level_attribute.Set_Description('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_PACKAGE_ID', '');
EXECUTE cwm_olap_level_attribute.Set_Display_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_PACKAGE_ID', '');
EXECUTE cwm_olap_level_attribute.Set_Name
          ('GLOBAL','PRODUCT','TOTAL_PRODUCT','TOTAL_PRODUCT_DSC','TOTAL_PRODUCT_DSC');
EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute
          ('GLOBAL','PRODUCT','Short_Description','TOTAL_PRODUCT','TOTAL_PRODUCT_DSC');
EXECUTE cwm_olap_level_attribute.Set_Description
          ('GLOBAL','PRODUCT','TOTAL_PRODUCT', 'TOTAL_PRODUCT_DSC', '');
EXECUTE cwm_olap_level_attribute.Set_Display_Name
           ('GLOBAL', 'PRODUCT','TOTAL_PRODUCT','TOTAL_PRODUCT_DSC','');