2 Creating OLAP Catalog Metadata with CWM2

The OLAP Catalog CWM2 PL/SQL packages provide stored procedures for creating, dropping, and updating OLAP metadata. This chapter explains how to work with the CWM2 procedures. For complete syntax descriptions, refer to the reference chapter for each package.

This chapter discusses the following topics:

OLAP Metadata Entities
Creating a Dimension
Creating a Cube
Mapping OLAP Metadata
Validating and Committing OLAP Metadata
Invoking the Procedures
Directing Output
Viewing OLAP Metadata

2.1 OLAP Metadata Entities

OLAP metadata entities are: dimensions, hierarchies, levels, level attributes, dimension attributes, measures, cubes, and measure folders. A separate PL/SQL package exists for each type of entity. The package provides procedures for creating, dropping, locking, and specifying descriptions for entities of that type. For example, to create a dimension, you would call CWM2_OLAP_DIMENSION.CREATE_DIMENSION; to create a level, you would call CWM2_OLAP_LEVEL.CREATE_LEVEL, and so on.

Each entity of metadata is uniquely identified by its owner and its name.

When you create an OLAP metadata entity, you are simply adding a row to an OLAP Catalog table that identifies all the entities of that type. Creating an entity does not fully define a dimension or a cube, nor does it involve any mapping to warehouse dimension tables or fact tables.

Note:

All OLAP Catalog metadata entities are defined as VARCHAR(30).

To fully construct a dimension or a cube, you must understand the hierarchical relationships between the component metadata entities.

2.2 Creating a Dimension

Creating a dimension entity is only the first step in constructing the OLAP metadata for a dimension. Each dimension must have at least one level. More typically, it will have multiple levels, hierarchies, and attributes. Table 2-1 shows the parent-child relationships between the metadata components of a dimension.

Table 2-1 Hierarchical Relationships Between Components of a Dimension

Parent Entity	Child Entity
dimension	dimension attribute, hierarchy, level
dimension attribute	level attribute
hierarchy	level
level	level attribute

Note:

OLAP Catalog dimensions created with theCWM2 procedures are purely logical entities. They have no relationship to database dimension objects. However, OLAP Catalog dimensions created in Enterprise Manager are associated with database dimension objects.

2.2.1 Procedure: Create an OLAP Dimension

Generally, you will create hierarchies and dimension attributes after creating the dimension and before creating the dimension levels and level attributes. Once the levels and level attributes are defined, you can map them to columns in one or more warehouse dimension tables. The general steps are as follows:

Call procedures in CWM2_OLAP_DIMENSION to create the dimension.
Call procedures in CWM2_OLAP_DIMENSION_ATTRIBUTE to create dimension attributes. In general, you will need to define dimension attributes for 'long description' and 'short description'.

The OLAP API requires the following dimension attributes for embedded total dimension tables (for example, views of analytic workspaces):'ET Key', 'Parent ET Key', 'Grouping ID', and 'Parent Grouping ID'. For more information, see Table 11-1, "Reserved Dimension Attributes".
Call procedures in CWM2_OLAP_HIERARCHY to define hierarchical relationships for the dimension's levels.
Call procedures in CWM2_OLAP_LEVEL to create levels and assign them to hierarchies.
Call procedures in CWM2_OLAP_LEVEL_ATTRIBUTE to create level attributes and assign them to dimension attributes. For 'long description', 'short description' and other reserved dimension attributes, create level attributes with the same name for every level.

The OLAP API requires the following level attributes for embedded total dimension tables (for example, views of analytic workspaces):'ET Key', 'Parent ET Key', 'Grouping ID', and 'Parent Grouping ID'. For more information, see Table 14-1, "Reserved Level Attributes".
Call procedures in CWM2_OLAP_TABLE_MAP to map the dimension's levels and level attributes to columns in dimension tables.

2.2.2 Example: Create a Product Dimension

The PL/SQL statements in Example 2-1 create a logical CWM2 dimension, PRODUCT_DIM, for the PRODUCTS dimension table in the SH schema.

The following table shows the columns in the PRODUCTS table.

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`

Example 2-1 Create an OLAP Dimension for the Products Table

---   CREATE THE PRODUCT DIMENSION    ---
exec cwm2_olap_dimension.create_dimension
          ('SH', 'PRODUCT_DIM', 'Product','Products', 'Product Dimension',
           'Product Dimension Values');

---   CREATE DIMENSION ATTRIBUTES  ---
exec cwm2_olap_dimension_attribute.create_dimension_attribute
          ('SH', 'PRODUCT_DIM', 'Long Description', 'Long Descriptions',
          'Long Desc', 'Long Product Descriptions', true);
exec cwm2_olap_dimension_attribute.create_dimension_attribute
          ('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'Product Name',
          'Prod Name', 'Product Name');

---   CREATE STANDARD HIERARCHY  ---
exec cwm2_olap_hierarchy.create_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'Standard', 'Std Product',
           'Standard Product Hierarchy', 'Unsolved Level-Based');
exec cwm2_olap_dimension.set_default_display_hierarchy
          ('SH', 'PRODUCT_DIM', 'standard');

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

---   CREATE LEVEL ATTRIBUTES  ---
exec 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);
exec 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);
exec 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);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'L4', 'PROD_NAME_LEV',
          'Product Name', 'Product Name', 'Product Name');

---   ADD LEVELS TO HIERARCHIES  ---
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L4', 'L3');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L3', 'L2');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L2', 'L1');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L1');

---   CREATE MAPPINGS  ---
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L4',
          'SH', 'PRODUCTS', 'PROD_ID');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD',
           'L4', 'Long Description', 'SH', 'PRODUCTS', 'PROD_DESC');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'STANDARD', 'L4', 
          'PROD_NAME_LEV', 'SH', 'PRODUCTS', 'PROD_NAME');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L3','SH', 'PRODUCTS', 
          'PROD_SUBCATEGORY');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD', 'L3', 
          'Long Description', 'SH', 'PRODUCTS', 'PROD_SUBCATEGORY_DESC');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L2','SH', 'PRODUCTS', 
          'PROD_CATEGORY');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD', 'L2', 
          'Long Description', 'SH', 'PRODUCTS', 'PROD_CATEGORY_DESC');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'PRODUCT_DIM', 'STANDARD', 'L1','SH', 'PRODUCTS', 
          'PROD_TOTAL');

2.2.3 Procedure: Create a Time Dimension

When constructing metadata for your time dimension tables, you will follow the same general procedure as for any other OLAP dimension. However, several additional requirements apply. The general steps for creating a time dimension are as follows:

Call procedures in CWM2_OLAP_DIMENSION to create the dimension. Specify 'TIME' for the dimension type parameter.
Call procedures in CWM2_OLAP_DIMENSION_ATTRIBUTE to create dimension attributes. In addition to the dimension attributes needed for regular dimensions, define an 'End Date' attribute and a 'Time Span' attribute.
Call procedures in CWM2_OLAP_HIERARCHY to define hierarchical relationships for the dimension's levels. Typical hierarchies are Calendar and Fiscal.
Call procedures in CWM2_OLAP_LEVEL to create levels and assign them to hierarchies. Typical levels are Month, Quarter, and Year.
Call procedures in CWM2_OLAP_LEVEL_ATTRIBUTE to create level attributes and assign them to dimension attributes. In addition to the level attributes needed for regular dimension attributes, create 'End Date' and 'Time Span' attributes for each level and associate them with the 'End Date' and 'Time Span' dimension attributes.
Call procedures in CWM2_OLAP_TABLE_MAP to map the dimension's levels and level attributes to columns in dimension tables. Map the 'End Date' level attributes to columns with a Date data type. Map the 'Time Span' level attributes to columns with a numeric data type.

2.2.4 Example: Create a Time Dimension

The PL/SQL statements in Example 2-1 create a logical CWM2 time dimension, TIME_DIM, for the TIMES dimension table in the SH schema.

The TIMES table includes the following columns.

Column Name	Data Type
`TIME_ID`	`DATE`
`TIME_ID_KEY`	`NUMBER`
`DAY_NAME`	`VARCHAR2(9)`
`CALENDAR_MONTH_NUMBER`	`NUMBER(2)`
`CALENDAR_MONTH_DESC`	`VARCHAR2(8)`
`CALENDAR_MONTH_DESC_KEY`	`NUMBER`
`END_OF_CAL_MONTH`	`DATE`
`CALENDAR_MONTH_NAME`	`VARCHAR2(9)`
`CALENDAR_QUARTER_DESC`	`CHAR(7)`
`CALENDAR_QUARTER_DESC_KEY`	`NUMBER`
`END_OF_CAL_QUARTER`	`DATE`
`CALENDAR_QUARTER_NUMBER`	`NUMBER(1)`
`CALENDAR_YEAR`	`NUMBER(4)`
`CALENDAR_YEAR_KEY`	`NUMBER`
`END_OF_CAL_YEAR`	`DATE`

Example 2-2 Create an OLAP TIme Dimension

---   CREATE THE TIME DIMENSION 
exec cwm2_olap_dimension.create_dimension
          ('SH', 'TIME_DIM', 'Time','Time', 'Time Dimension',
           'Time Dimension Values', 'TIME');

---   CREATE DIMENSION ATTRIBUTE END DATE
exec cwm2_olap_dimension_attribute.create_dimension_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'End Date',
          'End Date', 'Last date of time period', true);

---   CREATE CALENDAR HIERARCHY 
exec cwm2_olap_hierarchy.create_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Calendar', 'Calendar Hierarchy',
           'Calendar Hierarchy', 'Unsolved Level-Based');
exec cwm2_olap_dimension.set_default_display_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR');

---   CREATE LEVELS 
exec cwm2_olap_level.create_level
          ('SH', 'TIME_DIM', 'MONTH', 'Month', 'Months', 'Month','Month');
exec cwm2_olap_level.create_level
          ('SH','TIME_DIM','QUARTER','Quarter','Quarters','Quarter','Quarter');
exec cwm2_olap_level.create_level
          ('SH', 'TIME_DIM', 'YEAR','Year','Years', 'Year', 'Year');

---   CREATE LEVEL ATTRIBUTES  ---
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'Month', 'END DATE',
          'End Date', 'End Date',
          'Last date of time period', TRUE);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'Quarter', 'END DATE',
          'End Date', 'End Date',
          'Last date of time period', TRUE);
exec cwm2_olap_level_attribute.create_level_attribute
          ('SH', 'TIME_DIM', 'END DATE', 'Year', 'END DATE',
          'End Date', 'End Date',
          'Last date of time period', TRUE);

---   ADD LEVELS TO HIERARCHIES
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Month', 'Quarter');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Quarter', 'Year');
exec cwm2_olap_level.add_level_to_hierarchy
          ('SH', 'TIME_DIM', 'CALENDAR', 'Year');

---   CREATE MAPPINGS 
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'TIME_DIM', 'CALENDAR', 'Year',
          'SH', 'TIMES', 'CALENDAR_YEAR_ID');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'TIME_DIM', 'END DATE', 'CALENDAR',
           'Year', 'END DATE', 'SH', 'TIMES', 'END_OF_CAL_YEAR');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'TIME_DIM', 'CALENDAR', 'Quarter','SH', 'TIMES', 
          'CALENDAR_QUARTER_NUMBER');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'TIME_DIM', 'END DATE', 'CALENDAR',
           'Quarter', 'END DATE', 'SH', 'TIMES', 'END_OF_CAL_QUARTER');
exec cwm2_olap_table_map.Map_DimTbl_HierLevel
          ('SH', 'TIME_DIM', 'CALENDAR', 'Month','SH', 'TIMES', 
          'CALENDAR_MONTH_NUMBER');
exec cwm2_olap_table_map.Map_DimTbl_HierLevelAttr
          ('SH', 'TIME_DIM', 'END DATE', 'CALENDAR',
           'Month', 'END DATE', 'SH', 'TIMES', 'END_OF_CAL_MONTH');

2.3 Creating a Cube

Creating a cube entity is only the first step in constructing the OLAP metadata for a cube. Each cube must have at least one dimension and at least one measure. More typically, it will have multiple dimensions and multiple measures.

2.3.1 Procedure: Create a Cube

The general steps for constructing a cube are as follows:

Follow the steps in "Procedure: Create an OLAP Dimension" for each of the cube's dimensions.
Call procedures in CWM2_OLAP_CUBE to create the cube and identify its dimensions.
Call procedures in CWM2_OLAP_MEASURE to create the cube's measures.
Call procedures in CWM2_OLAP_TABLE_MAP to map the cube's measures to columns in fact tables and to map foreign key columns in the fact tables to key columns in the dimension tables.

2.3.2 Example: Create a Costs Cube

The PL/SQL statements in Example 2-3 create a logical CWM2 cube object, ANALYTIC_CUBE, for the COSTS fact table in the SH schema. The dimensions of the cube are PRODUCT_DIM, shown in Example 2-1, and TIME_DIM, shown in Example 2-2.

The COSTS fact table has the following columns.

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

Example 2-3 Create an OLAP Cube for the COSTS Fact Table

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

2.4 Mapping OLAP Metadata

OLAP metadata mapping is the process of establishing the links between logical metadata entities and the physical locations where the data is stored. Dimension levels and level attributes map to columns in dimension tables. Measures map to columns in fact tables. The mapping process also specifies the join relationships between a fact table and its associated dimension tables.

Note:

The dimension tables and fact tables may be implemented as views. For example, the views you can generate using the DBMS_AWM package may be the data source for OLAP metadata. These views project an image of relational fact tables and dimension tables over an analytic workspace, where the data actually resides. For more information, see "CREATE_AWCUBE_ACCESS Procedure".

2.4.1 Mapping to Columns

The CWM2_OLAP_TABLE_MAP package contains the mapping procedures for CWM2 metadata. Dimension levels, level attributes, and measures can be mapped within the context of a hierarchy or with no hierarchical context.

2.4.1.1 Mapping Dimensions

Each level maps to one or more columns in a dimension table. All the columns of a multicolumn level must be mapped within the same table. All the levels of a dimension may be mapped to columns in the same table (a traditional star schema), or the levels may be mapped to columns in separate tables (snowflake schema).

Each level attribute maps to a single column in the same table as its associated level.

2.4.1.2 Mapping Measures

Each measure maps to a single column in a fact table. All the measures mapped within the same fact table must share the same dimensionality.

When more than one hierarchical context is possible within a cube (at least one of the cube's dimensions has multiple hierarchies), each combination of hierarchies may be mapped to a separate fact table. In this case, each table must have columns for each of the cube's measures, and the measure columns must appear in the same order in each table.

2.4.2 Joining Fact Tables with Dimension Tables

Once you have mapped the levels, level attributes, and measures, you can specify the mapping of logical foreign key columns in the fact table to level key columns in dimension tables.

The MAP_FACTTBL_LEVELKEY procedure defines the join relationships between a cube and its dimensions. This procedure takes as input: the cube name, the fact table name, a mapping string, and a storage type indicator specifying how data is stored in the fact table.

The storage type indicator can have either of the following values:

'LOWESTLEVEL' A single fact table stores unsolved data for all the measures of a cube (star schema). If any of the cube's dimensions have more than one hierarchy, they must all have the same lowest level. Each foreign key column in the fact table maps to a level key column in a dimension table.
'ET' Fact tables store completely solved data (with embedded totals) for specific hierarchies of the cube's dimensions. Typically, the data for each combination of hierarchies is stored in a separate fact table. Each fact table must have the same columns. Multiple hierarchies in dimensions do not have to share the same lowest level.

An embedded total key and a grouping ID key (GID) in the fact table map to corresponding columns that identify a dimension hierarchy in a solved dimension table. The ET key identifies the lowest level value present in a row. The GID identifies the hierarchy level associated with each row. For more information, see "Grouping ID Column" . For more information on mapping the key relationships between fact tables and dimension tables, see "MAP_FACTTBL_LEVELKEY Procedure".

The OLAP API requires certain attributes for ET dimensions. See Table 11-1, "Reserved Dimension Attributes".

When the fact table and dimension tables are joined with a storage type of LOWESTLEVEL, the cube's hierarchies have a solved_code of 'UNSOLVED LEVEL-BASED'.

When the fact tables and dimension tables are joined with a storage type of ET, the cube's hierarchies have a solved_code of 'SOLVED LEVEL-BASED'.

See "SET_SOLVED_CODE Procedure".

2.5 Validating and Committing OLAP Metadata

None of the CWM2 procedures that create, map, or validate OLAP metadata includes a COMMIT.

To prepare metadata for the OLAP API, your script should first execute all the statements that create and map new metadata, then validate the metadata, then refresh OLAP API Metadata Reader tables. The refresh process includes a COMMIT. See "Refreshing Metadata Tables for the OLAP API".

If you are preparing OLAP metadata for other types of applications, your script should include a COMMIT after creating, mapping, and validating the metadata.

2.5.1 Validating OLAP Metadata

To test the validity of OLAP metadata, use the CWM2_OLAP_VALIDATE and CWM2_OLAP_VERIFY_ACCESS packages. The validation procedures check the structural integrity of the metadata and ensure that it is correctly mapped to columns in dimension tables and fact tables. Additional validation specific to the OLAP API is done if requested.

The CWM2_OLAP_VERIFY_ACCESS package performs two additional checks after validating a cube. It checks that the CWM2 metadata for the cube is consistent with the cached metadata tables queried by the OLAP API Metadata Reader. Additionally, it checks that the calling user has access to the source tables and columns.

See Also:

Note:

Remember to validate metadata created or updated in Enterprise Manager as well as CWM2 metadata.

When running the validation procedures, you can choose to generate a summary or detailed report of the validation process. See "Directing Output" for information about viewing output on the screen or writing output to a file.

Example 2-4 shows the statements that validate the PRODUCT dimension in XADEMO and generate a detailed validation report. The report is displayed on the screen and written to a log file.

Example 2-4 Generate a Validation Report for the PRODUCT Dimension

set echo on
set linesize 135
set pagesize 50
set serveroutput on size 1000000

execute cwm2_olap_manager.set_echo_on;
execute cwm2_olap_manager.begin_log('/users/myxademo/myscripts' , 'x.log');

execute cwm2_olap_validate.validate_dimension
          ('xademo','product','default','yes');

execute cwm2_olap_manager.end_log;
execute cwm2_olap_manager.set_echo_off;

The validation report would look like this.

Validate Dimension: XADEMO.PRODUCT    Type of Validation: DEFAULT    Verbose Report: YES
Validating Dimension in OLAP Catalog 1
ENTITY TYPE                     ENTITY NAME                STATUS   COMMENT
  Dimension                     .                          VALID
  Dimension                     XADEMO.PRODUCT             VALID
        LevelAttribute          PROD_STD_TOP_LLABEL        VALID    DimensionAttribute "Long Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_TOP_LLABEL"
        LevelAttribute          PROD_STD_TOP_SLABEL         VALID    DimensionAttribute "Short Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_TOP_SLABEL"
    Hierarchy                   STANDARD                   VALID
      Level                     L4                         VALID    Hierarchy depth 1 (Lowest Level)
        LevelMap                                           VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_PRODUCT"
        LevelAttribute          PROD_COLOR                 VALID    DimensionAttribute "Color"
          LevelAttributeMap                                VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_COLOR"
        LevelAttribute          PROD_SIZE                  VALID    DimensionAttribute "Size"
          LevelAttributeMap                                VALID    Mapped to Column "XADEMO.XADEMO_
                                                                       PRODUCT.PROD_SIZE"
        LevelAttribute          PROD_STD_PRODUCT_LLABEL    VALID    DimensionAttribute "Long Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_PRODUCT_LLABEL"
        LevelAttribute          PROD_STD_PRODUCT_SLABEL     VALID    DimensionAttribute "Short Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_PRODUCT_SLABEL"
      Level                     L3                         VALID    Hierarchy depth 2
        LevelMap                                           VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_GROUP"
        LevelAttribute          PROD_STD_GROUP_LLABEL      VALID    DimensionAttribute "Long Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_GROUP_LLABEL"
        LevelAttribute          PROD_STD_GROUP_SLABEL       VALID    DimensionAttribute "Short Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                     .PROD_STD_GROUP_SLABEL"
      Level                     L2                         VALID    Hierarchy depth 3
        LevelMap                                           VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_DIVISION"
        LevelAttribute          PROD_STD_DIVISION_LLABEL   VALID    DimensionAttribute "Long Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_DIVISION_LLABEL"
        LevelAttribute          PROD_STD_DIVISION_SLABEL    VALID    DimensionAttribute "Short Description"
          LevelAttributeMap                                 VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_DIVISION_SLABEL"
      Level                     L1                         VALID    Hierarchy depth 4 (Top Level)
        LevelMap                                           VALID    Mapped to Column "XADEMO.XADEMO_PRODUCT
                                                                      .PROD_STD_TOP"

Note:

When a metadata entity is invalid, the Comment column of the validation report indicates whether the problem originates with this entity or with a different entity on which it depends. For example, if a level is invalid, its dependent level attributes will also be invalid.

2.5.2 Viewing Validity Status

You can check the validity status of cubes and dimensions by selecting the INVALID column of the ALL_OLAP2_CUBES and ALL_OLAP2_DIMENSIONS views. One of the following values is displayed:

Y -- The cube or dimension is invalid.

N -- The cube or dimension has met basic validation criteria.

O -- The cube has met basic validation criteria and additional criteria specific to the OLAP API.

For more information, see "ALL_OLAP2_CUBES" and "ALL_OLAP2_DIMENSIONS".

2.5.3 Refreshing Metadata Tables for the OLAP API

To make your metadata accessible to the OLAP API, use the CWM2_OLAP_METADATA_REFRESH package to refresh the OLAP API Metadata Reader tables.

Views built on these tables present a read API to the OLAP Catalog that is optimized for queries by the OLAP API Metadata Reader. The Metadata Reader views have public synonyms with the prefix MRV_OLAP2. For more information, see Chapter 16.

Note:

You must refresh the Metadata Reader tables to ensure access by the OLAP API.

If you have scripts that call the CWM2 APIs to create OLAP metadata, include calls to validate the metadata and refresh the Metadata Reader tables.

If you use Enterprise Manager to create OLAP metadata, you must run the validate and refresh procedures separately, after the metadata has been created.

2.6 Invoking the Procedures

When using the OLAP Catalog write APIs, you should be aware of logic and conventions that are common to all the CWM2 procedures.

2.6.1 Security Checks and Error Conditions

Each CWM2 procedure first checks the calling user's security privileges. The calling user must have the OLAP_DBA role. Generally, the calling user must be the entity owner. If the calling user does not meet the security requirements, the procedure fails with an exception. For example, if your identity is jsmith, you cannot successfully execute CWM2_OLAP_HIERARCHY.DROP_HIERARCHY for a hierarchy owned by jjones.

After verifying the security requirements, each procedure checks for the existence of the entity and of its parent entities. All procedures, except CREATE procedures, return an error if the entity does not already exist. For example, if you call CWM2_OLAP_LEVEL.SET_DESCRIPTION, and the level does not already exist, the procedure will fail.

2.6.2 Size Requirements for Parameters

CWM2 metadata entities are created with descriptions and display names. For example, the CREATE_CUBE procedure in the CWM2_OLAP_CUBE package requires the following parameters:

CREATE_CUBE (
          cube_owner            IN   VARCHAR2,
          cube_name             IN   VARCHAR2,
          display_name          IN   VARCHAR2,
          short_description     IN   VARCHAR2,
          description           IN   VARCHAR2);

Entity names and descriptions have size limitations based on the width of the columns where they are stored in the OLAP Catalog model tables. The size limitations are listed in Table 2-2.

Table 2-2 Size Limitations of CWM2 Metadata Entities

Metadata Entity	Maximum Size
entity owner	30 characters
entity name	30 characters
display name	30 characters
short description	240 characters
description	2000 characters

2.6.3 Case Requirements for Parameters

You can specify arguments to CWM2 procedures in lower case, upper case, or mixed case.

If the argument is a metadata entity name (for example, dimension_name) or a value that will be used in further processing by other procedures (for example, the solved_code of a hierarchy), the procedure converts the argument to upper case. For all other arguments, the case that you specify is retained.

2.7 Directing Output

There are several tools and settings you can use to help you develop and debug your CWM2 scripts.

You can echo the output and messages from CWM2 procedures to the SQL buffer. Use the following statement.

SQL>exec cwm2_olap_manager.set_echo_on;

By default, echoing is turned off. Once you have set echoing on, you can turn it off with the following statement.

SQL>exec cwm2_olap_manager.set_echo_off;

You can set SQL*Plus to display the contents of the SQL buffer on the screen with the following statement.

SQL>set serveroutput on

The default and minimum size of the SQL buffer is 2K. You can extend the size up to a maximum of 1MG with the following statement.

SQL>set serveroutput on size 1000000

You should set serveroutput to its maximum size to prevent buffer overflow conditions.

See Also:

SQL*Plus User's Guide and Reference for more information on setting serveroutput.

To accommodate larger amounts of output, you should direct output to a file. Use the following statement.

SQL>exec cwm2_olap_manager.begin_log('directory_path','filename');

For directory_path you can specify either a directory object to which your user ID has been granted the appropriate access, or a directory path set by the UTL_FILE_DIR initialization parameter for the instance.

To flush the contents of the buffer and turn off logging, use the following statement.

SQL>exec cwm2_olap_manager.end_log;

2.8 Viewing OLAP Metadata

A set of views, identified by the ALL_OLAP2 prefix, presents the metadata in the OLAP Catalog. The metadata may have been created with the CWM2 PL/SQL packages or with Enterprise Manager. The ALL_OLAP2 views are automatically populated whenever changes are made to the metadata.

A second set of views, identified by the MRV_OLAP prefix , also presents OLAP Catalog metadata. However, these views are structured specifically to support fast querying by the OLAP API's Metadata Reader. These views must be explicitly refreshed whenever changes are made to the metadata.

See Also:

Chapter 5, " OLAP Catalog Metadata Views " for more information on the ALL_OLAP2 views.
Chapter 16, " CWM2_OLAP_METADATA_REFRESH " for more information on refreshing metadata tables for the OLAP API.