19 CWM2_OLAP_VALIDATE

The CWM2_OLAP_VALIDATE package provides procedures for validating OLAP metadata.

See Also:

This chapter discusses the following topics:

About OLAP Catalog Metadata Validation
Summary of CWM2_OLAP_VALIDATE Subprograms

19.1 About OLAP Catalog Metadata Validation

The validation process checks the structural integrity of the metadata and ensures 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 procedures in CWM2_OLAP_VALIDATE validate the OLAP metadata created by Enterprise Manager as well as the metadata created by CWM2 procedures.

See Also:

"Validating and Committing OLAP Metadata" for additional information.

19.1.1 Structural Validation

Structural validation ensures that cubes and dimensions have all their required components parts. All the procedures in CWM2_OLAP_VALIDATE perform structural validation by default.

19.1.1.1 Cubes

To be structurally valid, a cube must meet the following criteria:

It must have at least one valid dimension.
It must have at least one measure.

19.1.1.2 Dimensions

To be structurally valid, a dimension must meet the following criteria:

It must have at least one level.
It may have one or more hierarchies. Each hierarchy must have at least one level.
It may have one or more dimension attributes. Each dimension attribute must have at least one level attribute.

19.1.2 Mapping Validation

Mapping validation ensures that the metadata has been properly mapped to columns in tables or views. All the procedures in CWM2_OLAP_VALIDATE perform mapping validation by default.

19.1.2.1 Cubes

To be valid, a cube's mapping must meet the following criteria:

It must be mapped to one or more fact tables.
All of the cube's measures must be mapped to existing columns in a fact table. If there are multiple fact tables, all the measures must be in each one.
Every dimension/hierarchy combination must be mapped to one of the fact tables.

19.1.2.2 Dimensions

To be valid, a dimension's mapping must meet the following criteria:

All levels must be mapped to existing columns in a dimension table.
Level attributes must be mapped to columns in the same table as the corresponding levels.

19.1.3 Validation Type

All the procedures in CWM2_OLAP_VALIDATE package take a validation type argument. The validation type can be one of the following:

DEFAULT -- Validates the basic structure of the metadata and its mapping to the source tables. To be valid, the metadata must meet the criteria specified in "Structural Validation" and "Mapping Validation".

OLAP API -- Performs default validation plus the following:

Validates that each dimension of an ET-style cube has dimension and level attributes 'ET KEY' and 'GROUPING ID' for all levels.
Validates that time dimensions have dimension and level attributes 'END DATE' and 'TIME SPAN' for all levels.

Summary of CWM2_OLAP_VALIDATE Subprograms

Table 19-1 CWM2_OLAP_VALIDATE

Subprogram	Description
VALIDATE_ALL_CUBES Procedure	Validates all the cubes in the OLAP Catalog.
VALIDATE_ALL_DIMENSIONS Procedure	Validates all the dimensions in the OLAP Catalog.
VALIDATE_CUBE Procedure	Validates an OLAP Catalog cube.
VALIDATE_DIMENSION Procedure	Validates an OLAP Catalog dimension.
VALIDATE_OLAP_CATALOG Procedure	Validates all the cubes and all the dimensions in the OLAP Catalog.

VALIDATE_ALL_CUBES Procedure

This procedure validates all the cubes the OLAP Catalog. This includes validation of all the dimensions associated with the cubes.

Cube validity status is displayed in the view ALL_OLAP2_CUBES.

Syntax

VALIDATE_ALL_CUBES (
               type_of_validation     IN   VARCHAR2 DEFAULT 'DEFAULT',
               verbose_report         IN   VARCHAR2 DEFAULT 'YES');

Parameters

Table 19-2 VALIDATE_ALL_CUBES Procedure Parameters

Parameter	Description
type_of_validation	`'DEFAULT'` or `'OLAP API'`. See "Validation Type".
verbose_report	`'YES'` or `'NO'`. Whether `to` report all validation checks or only major events and errors. By default, all validation checks are reported.

VALIDATE_ALL_DIMENSIONS Procedure

This procedure validates all the dimensions in the OLAP Catalog.

Dimension validity status is displayed in the view ALL_OLAP2_DIMENSIONS.

Syntax

VALIDATE_ALL_DIMENSIONS (
               type_of_validation     IN   VARCHAR2 DEFAULT 'DEFAULT',
               verbose_report         IN   VARCHAR2 DEFAULT 'YES');

Parameters

Table 19-3 VALIDATE_ALL_DIMENSIONS Procedure Parameters

Parameter	Description
type_of_validation	`'DEFAULT'` or `'OLAP API'`. See "Validation Type".
verbose_report	`'YES'` or `'NO'`. Whether `to` report all validation checks or only major events and errors. By default, all validation checks are reported.

VALIDATE_CUBE Procedure

This procedure validates an OLAP Catalog cube. This includes validation of all the dimensions associated with the cube.

The validity status of a cube is displayed in the view ALL_OLAP2_CUBES.

Syntax

VALIDATE_CUBE (
          cube_owner          IN   VARCHAR2,
          cube_name            IN   VARCHAR2,
          type_of_validation  IN   VARCHAR2 DEFAULT 'DEFAULT',
          verbose_report      IN   VARCHAR2 DEFAULT 'YES');

Parameters

Table 19-4 VALIDATE_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
type_of_validation	`'DEFAULT'` or `'OLAP API'`. See "Validation Type".
verbose_report	`'YES'` or `'NO'`. Whether `to` report all validation checks or only major events and errors. By default, all validation checks are reported.

VALIDATE_DIMENSION Procedure

This procedure validates an OLAP Catalog dimension.

The validity status of an OLAP dimension is displayed in the view ALL_OLAP2_DIMENSIONS.

Syntax

VALIDATE_DIMENSION (
          dimension_owner      IN   VARCHAR2,
          dimension_name        IN   VARCHAR2,
          type_of_validation   IN    VARCHAR2 DEFAULT 'DEFAULT',
          verbose_report       IN   VARCHAR2 DEFAULT 'YES');

Parameters

Table 19-5 VALIDATE_DIMENSION Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
type_of_validation	`'DEFAULT'` or `'OLAP API'`. See "Validation Type".
verbose_report	`'YES'` or `'NO'`. Whether `to` report all validation checks or only major events and errors. By default, all validation checks are reported.

VALIDATE_OLAP_CATALOG Procedure

This procedure validates all the metadata in the OLAP Catalog. This includes all the cubes (with their dimensions) and all the dimensions that are not associated with cubes.

VALIDATE_OLAP_CATALOG validates each standalone dimension in alphabetical order, then it validates each cube in alphabetical order.

Syntax

VALIDATE_OLAP_CATALOG (
               type_of_validation     IN   VARCHAR2 DEFAULT 'DEFAULT',
               verbose_report         IN   VARCHAR2 DEFAULT 'YES');

Parameters

Table 19-6 VALIDATE_OLAP_CATALOG Procedure Parameters

Parameter	Description
type_of_validation	`'DEFAULT'` or `'OLAP API'`. See "Validation Type".
verbose_report	`'YES'` or `'NO'`. Whether `to` report all validation checks or only major events and errors. By default, all validation checks are reported.