Validating and Committing OLAP Catalog Metadata

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

Scripts that create OLAP Catalog metadata should first execute all the statements that create and map new metadata, then validate the metadata, then execute a COMMIT.

If the metadata will be used by the OLAP API to access data stored in relational tables, you must refresh the OLAP API Metadata Reader tables after validating the metadata. The refresh process includes a COMMIT. See "Refreshing Metadata Tables for the OLAP API".

Validating OLAP Catalog Metadata

To test the validity of OLAP Catalog 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.

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".

Refreshing Metadata Tables for the OLAP API

If your metadata will be used by the OLAP API to access relational data, 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 19.

Note:

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