15 CWM2_OLAP_MEASURE

The CWM2_OLAP_MEASURE package provides procedures for managing measures.

This chapter discusses the following topics:

Understanding Measures
Example: Creating a Measure
Summary of CWM2_OLAP_MEASURE Subprograms

15.1 Understanding Measures

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

Measures represent data stored in fact tables. The fact tables may be relational tables or views. The views may reference data stored in analytic workspaces.

Measures exist within the context of cubes, which fully specify the dimensionality of the measures' data. Measures are fully described in .

Use the procedures in the CWM2_OLAP_MEASURE package to create, drop, and lock measures, to associate a measure with a cube, and to specify descriptive information for display purposes.

The parent cube must already exist in the OLAP Catalog before you can create a measure.

See Also:

Chapter 9, " CWM2_OLAP_CUBE "
Oracle OLAP Application Developer's Guide for more information about measures and the OLAP metadata model

15.2 Example: Creating a Measure

The following statements create the SALES_AMOUNT and SALES_QUANTITY measures for the SALES_CUBE cube.

execute cwm2_olap_measure.create_measure
     ('JSMITH', 'SALES_CUBE', 'SALES_AMOUNT', 'Sales Amount', 
      '$ Sales', 'Dollar Sales');
execute cwm2_olap_measure.create_measure
     ('JSMITH', 'SALES_CUBE', 'SALES_QUANTITY', 'Sales Quantity', 
      'Sales Quantity', 'Quantity of Items Sold');

Summary of CWM2_OLAP_MEASURE Subprograms

Table 15-1 CWM2_OLAP_MEASURE Subprograms

Subprogram	Description
CREATE_MEASURE Procedure	Creates a measure.
DROP_MEASURE Procedure	Drops a measure.
LOCK_MEASURE Procedure	Locks a measure's metadata for update.
SET_DESCRIPTION Procedure	Sets the description for a measure.
SET_DISPLAY_NAME Procedure	Sets the display name for a measure.
SET_MEASURE_NAME Procedure	Sets the name of a measure.
SET_SHORT_DESCRIPTION Procedure	Sets the short description for a measure.

CREATE_MEASURE Procedure

This procedure creates a new measure in the OLAP Catalog.

A measure can only be created in the context of a cube. The cube must already exist before you create the measure.

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

Syntax

CREATE_MEASURE (
          cube_owner            IN   VARCHAR2,
          cube_name             IN   VARCHAR2,
          measure_name          IN   VARCHAR2,
          display_name          IN   VARCHAR2,
          short_description     IN   VARCHAR2,
          description           IN   VARCHAR2);

Parameters

Table 15-2 CREATE_MEASURE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Name of the measure.
`display_name`	Display name for the measure.
`short_description`	Short description of the measure.
`description`	Description of the measure.

DROP_MEASURE Procedure

This procedure drops a measure from a cube.

Syntax

DROP_MEASURE (
         cube_owner      IN   VARCHAR2,
         cube_name        IN   VARCHAR2,
         measure_name    IN   VARCHAR2);

Parameters

Table 15-3 DROP_MEASURE Procedure Parameters

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

LOCK_MEASURE Procedure

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

Syntax

LOCK_MEASURE (
           cube_owner        IN   VARCHAR2,
           cube_name         IN   VARCHAR2.
           measure_name       IN   VARCHAR2,
           wait_for_lock     IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 15-4 LOCK_MEASURE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Name of the measure to be locked.
`wait_for_lock`	(Optional) Whether or not to wait for the measure 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.

SET_DESCRIPTION Procedure

This procedure sets the description for a measure.

Syntax

SET_DESCRIPTION (
          cube_owner     IN   VARCHAR2,
          cube_name      IN   VARCHAR2,
          measure_name   IN   VARCHAR2,
          description    IN   VARCHAR2);

Parameters

Table 15-5 SET_DESCRIPTION Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Name of the measure.
`description`	Description of the measure.

SET_DISPLAY_NAME Procedure

This procedure sets the display name for a measure.

Syntax

SET_DISPLAY_NAME (
          cube_owner     IN   VARCHAR2,
          cube_name      IN   VARCHAR2,
          measure_name   IN   VARCHAR2,
          display_name   IN   VARCHAR2);

Parameters

Table 15-6 SET_DISPLAY_NAME Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Name of the measure.
`display_name`	Display name for the measure.

SET_MEASURE_NAME Procedure

This procedure sets the name for a measure.

Syntax

SET_MEASURE_NAME (
          cube_owner        IN   VARCHAR2,
          cube_name         IN   VARCHAR2,
          measure_name      IN   VARCHAR2,
          set_cube_name     IN   VARCHAR2);

Parameters

Table 15-7 SET_MEASURE_NAME Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Original name of the measure.
`set_cube_name`	New name for the measure.

SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a measure.

Syntax

SET_SHORT_DESCRIPTION (
          cube_owner            IN   VARCHAR2,
          cube_name             IN   VARCHAR2,
          measure_name          IN   VARCHAR2,
          short_description     IN   VARCHAR2);

Parameters

Table 15-8 SET_SHORT_DESCRIPTION Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`measure_name`	Name of the measure.
`short_description`	Short description of the measure.