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 Catalog metadata created by Enterprise Manager as well as the metadata created by CWM2 procedures.

Structural Validation

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

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.

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.

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.

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.

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.

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.

Using Wildcards to Identify Metadata Entities

You can use wildcard characters to validate cubes and dimensions whose names meet certain criteria.

Wildcard characters are the underscore "_" and the percent sign "%". An underscore replaces any single character, and a percent sign replaces any zero or more characters. An underscore, but not a percent sign, is also a legal character in a metadata owner or entity name. Any underscore character in the owner or entity name is treated as a wildcard, unless you precede it with a backslash "\" which acts as an escape character.

For example, the following command validates all the cubes belonging to the owner 'GLOBAL'.

execute cwm2_olap_validate.validate_cube('GLOBAL', '%');

The following command validates all the cubes in the GLOBAL schema whose names start with 'a'.

execute cwm2_olap_validate.validate_cube('GLOBAL', 'a%');

If your database includes users 'TESTUSER1' and 'TESTUSER2', you could validate the 'TEST' cube belonging to each of these users with the following command.

execute cwm2_olap_validate.validate_cube('TESTUSER_', 'TEST');

If your database includes users 'TEST_USER1' and 'TEST_USER2', you could validate the 'TEST' cube belonging to each of these users with the following command.

execute cwm2_olap_validate.validate_cube('TEST/_USER_', 'TEST');

Summary of CWM2_OLAP_VALIDATE Subprograms

Table 22-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 one or more cubes in the OLAP Catalog.
VALIDATE_DIMENSION Procedure	Validates one or more dimensions in the OLAP Catalog.
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 22-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 22-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 a cube or group of cubes in the OLAP Catalog. This includes validation of all the dimensions associated with the cubes.

You can identify a group of cubes by specifying wildcard characters in the cube_owner and cube_name parameters. See "Using Wildcards to Identify Metadata Entities".

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 22-4 VALIDATE_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube. See "Using Wildcards to Identify Metadata Entities".
`cube_name`	Name of the cube. See "Using Wildcards to Identify Metadata Entities".
`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 a dimension or group of dimensions in the OLAP Catalog.

You can identify a group of dimensions by specifying wildcard characters in the cube_owner and cube_name parameters. See "Using Wildcards to Identify Metadata Entities".

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 22-5 VALIDATE_DIMENSION Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension. See "Using Wildcards to Identify Metadata Entities".
`dimension_name`	Name of the dimension. "Using Wildcards to Identify Metadata Entities".
`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 22-6 VALIDATE_OLAP_CATALOG Procedure Parameters

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