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