7 CWM2_OLAP_CATALOG

The CWM2_OLAP_CATALOG package provides procedures for managing measure folders.

Note:

The term catalog, when used in the context of the CWM2_OLAP_CATALOG package, refers to a measure folder.

See Also:

This chapter discusses the following topics:

Understanding Measure Folders
Example: Creating a Measure Folder
Summary of CWM2_OLAP_CATALOG Subprograms

7.1 Understanding Measure Folders

A measure folder is an OLAP metadata entity. This means that it is a logical object, identified by name and owner, within the OLAP Catalog.

Use the procedures in the CWM2_OLAP_CATALOG package to create, populate, drop, and lock measure folders, and to specify descriptive information for display purposes.

Measure folders provide a mechanism for grouping related measures. They can contain measures and nested measure folders. Access to measure folders is schema-independent. All measure folders are visible to any client. However, access to the measures themselves depends on the client's access rights to the underlying tables.

See Also:

Oracle OLAP Application Developer's Guide for more information on measure folders and the OLAP metadata model.

7.2 Example: Creating a Measure Folder

The following statements create a measure folder called PHARMACEUTICALS and add the measure UNIT_COST from the cube SH.COST_CUBE. The measure folder is at the root level.

execute cwm2_olap_catalog.create_catalog
     ('PHARMACEUTICALS', 'Pharmaceutical Sales and Planning');
execute cwm2_olap_catalog.add_catalog_entity
     ('PHARMACEUTICALS',  'SH', 'COST_CUBE', UNIT_COST');

Summary of CWM2_OLAP_CATALOG Subprograms

Table 7-1 CWM2_OLAP_CATALOG Subprograms

Subprogram	Description
ADD_CATALOG_ENTITY Procedure	Adds a measure to a measure folder.
CREATE_CATALOG Procedure	Creates a measure folder.
DROP_CATALOG Procedure	Drops a measure folder.
LOCK_CATALOG Procedure	Locks a measure folder.
REMOVE_CATALOG_ENTITY Procedure	Removes a measure from a measure folder.
SET_CATALOG_NAME Procedure	Sets the name of a measure folder.
SET_DESCRIPTION Procedure	Sets the description of a measure folder.
SET_PARENT_CATALOG Procedure	Sets the parent folder of a measure folder.

ADD_CATALOG_ENTITY Procedure

This procedure adds a measure to a measure folder.

Syntax

ADD_CATALOG_ENTITY  (
               catalog_name     IN   VARCHAR2,
               cube_owner       IN   VARCHAR2,
               cube_name        IN   VARCHAR2,
               measure_name     IN   VARCHAR2);

Parameters

Table 7-2 ADD_CATALOG_ENTITY Procedure Parameters

Parameter	Description
`catalog_name`	Name of the measure folder.
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Name of the measure to be added to the measure folder.

CREATE_CATALOG Procedure

This procedure creates a new measure folder.

Descriptions and display properties must also be established as part of measure folder creation. Once the measure folder has been created, you can override these properties by calling other procedures in this package.

Syntax

CREATE_CATALOG (
               catalog_name       IN   VARCHAR2,
               description        IN   VARCHAR2,
               parent_catalog     IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 7-3 CREATE_CATALOG Procedure Parameters

Parameter	Description
`catalog_name`	Name of the measure folder.
`description`	Description of the measure folder.
`parent_catalog`	Optional parent measure folder.

DROP_CATALOG Procedure

This procedure drops a measure folder. If the measure folder contains other measure folders, they are also dropped.

Syntax

DROP_CATALOG (
               catalog_name     IN   VARCHAR2);

Parameters

Table 7-4 DROP_CATALOG Procedure Parameters

Parameter	Description
`catalog_name`	Name of the measure_folder.

LOCK_CATALOG Procedure

This procedure locks the measure folder's metadata for update by acquiring a database lock on the row that identifies the measure folder in the CWM2 model table.

Syntax

LOCK_CATALOG (
               catalog_name     IN   VARCHAR2,
               wait_for_lock    IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 7-5 LOCK_CATALOG Procedure Parameters

Parameter	Description
`catalog_name`	Name of the measure folder
`wait_for_lock`	(Optional) Whether or not to wait for the measure folder to be available when it is already locked by another user. If you do not specify a value for this parameter, the procedure does not wait to acquire the lock.

REMOVE_CATALOG_ENTITY Procedure

This procedure removes a measure from a measure folder.

Syntax

REMOVE_CATALOG_ENTITY  (
               catalog_name      IN   VARCHAR2,
               cube_owner        IN   VARCHAR2,
               cube_name         IN   VARCHAR2,
               measure_name      IN   VARCHAR2);

Parameters

Table 7-6 REMOVE_CATALOG_ENTITY Procedure Parameters

Parameter	Description
`catalog_name`	Name of the measure folder.
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Name of the measure to be removed from the measure folder.

SET_CATALOG_NAME Procedure

This procedure sets the name for a measure folder.

Syntax

SET_CATALOG_NAME (
               old_catalog_name     IN   VARCHAR2,
               new_catalog_name     IN   VARCHAR2);

Parameters

Table 7-7 SET_CATALOG_NAME Procedure Parameters

Parameter	Description
`old_catalog_name`	Old measure folder name.
`new_catalog_name`	New measure folder name.

SET_DESCRIPTION Procedure

This procedure sets the description for a measure folder.

Syntax

SET_DESCRIPTION (
               catalog_name     IN   VARCHAR2,
               description      IN   VARCHAR2);

Parameters

Table 7-8 SET_DESCRIPTION Procedure Parameters

Parameter	Description
`catalog_name`	Name of the measure folder
`description`	Description of the measure folder.

SET_PARENT_CATALOG Procedure

This procedure sets a parent measure folder for a measure folder.

Syntax

SET_PARENT_CATALOG (
               catalog_name            IN   VARCHAR2,
               parent_catalog_name     IN   VARCHAR2   DEFAULT NULL);

Parameters

Table 7-9 SET_PARENT_CATALOG Procedure Parameters

Parameter	Description
`catalog_name`	Name of the measure folder.
`parent_catalog_name`	Name of the parent measure folder. If the measure folder is at the root level, this parameter is null.