Example: Creating a CWM2 Dimension

The following statement creates a CWM2 dimension entity, PRODUCT_DIM, in the JSMITH schema. The display name is Product, and the plural name is Products. The short description is Prod, and the description is Product.

execute cwm2_olap_dimension.create_dimension
     ('JSMITH', 'PRODUCT_DIM', 'Product', 'Products', 'Prod', 'Product');

The following statements change the short description to Product and the long description to Product Dimension.

execute cwm2_olap_dimension.set_short_description
     ('JSMITH', 'PRODUCT_DIM', 'Product');
execute cwm2_olap_dimension.set_description
     ('JSMITH', 'PRODUCT_DIM', 'Product Dimension');

Summary of CWM2_OLAP_DIMENSION Subprograms

Table 11-1 CWM2_OLAP_DIMENSION Subprograms

Subprogram	Description
CREATE_DIMENSION Procedure	Creates a dimension.
DROP_DIMENSION Procedure	Drops a dimension.
LOCK_DIMENSION Procedure	Locks the dimension metadata for update.
SET_DEFAULT_DISPLAY_HIERARCHY Procedure	Sets the default hierarchy for a dimension.
SET_DESCRIPTION Procedure	Sets the description for a dimension.
SET_DIMENSION_NAME Procedure	Sets the name of a dimension.
SET_DISPLAY_NAME Procedure	Sets the display name for a dimension.
SET_PLURAL_NAME Procedure	Sets the plural name for a dimension.
SET_SHORT_DESCRIPTION Procedure	Sets the short description for a dimension.

CREATE_DIMENSION Procedure

This procedure creates a new dimension entity in the OLAP Catalog.

By default the new dimension is a normal dimension, but you can specify the value TIME for the dimension_type parameter to create a time dimension.

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

Syntax

CREATE_DIMENSION (
          dimension_owner       IN   VARCHAR2,
          dimension_name        IN   VARCHAR2,
          display_name          IN   VARCHAR2,
          plural_name           IN   VARCHAR2,
          short_description     IN   VARCHAR2,
          description           IN   VARCHAR2,
          dimension_type        IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 11-2 CREATE_DIMENSION Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`display_name`	Display name for the dimension.
`plural_name`	Plural name for the dimension.
`short_description`	Short description of the dimension.
`description`	Description of the dimension.
`dimension_type`	(Optional) Type of the dimension. Specify the value `TIME` to create a time dimension. If you do not specify this parameter, the dimension is created as a normal dimension.

DROP_DIMENSION Procedure

This procedure drops a dimension entity from the OLAP Catalog. All related levels, hierarchies, and dimension attributes are also dropped.

Syntax

DROP_DIMENSION (
         dimension_owner     IN   VARCHAR2,
         dimension_name      IN   VARCHAR2);

Parameters

Table 11-3 DROP_DIMENSION Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.

LOCK_DIMENSION Procedure

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

Syntax

LOCK_DIMENSION (
           dimension_owner     IN   VARCHAR2,
           dimension_name      IN   VARCHAR2.
           wait_for_lock       IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 11-4 LOCK_DIMENSION Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`wait_for_lock`	(Optional) Whether or not to wait for the dimension 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_DEFAULT_DISPLAY_HIERARCHY Procedure

This procedure sets the default hierarchy to be used for display purposes.

Syntax

SET_DEFAULT_DISPLAY_HIERARCHY  (
          dimension_owner     IN   VARCHAR2,
          dimension_name      IN   VARCHAR2,
          hierarchy_name      IN   VARCHAR2);

Parameters

Table 11-5 SET_DEFAULT_DISPLAY_HIERARCHY Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of one of the dimension's hierarchies.

SET_DESCRIPTION Procedure

This procedure sets the description for a dimension.

Syntax

SET_DESCRIPTION (
          dimension_owner     IN   VARCHAR2,
          dimension_name      IN   VARCHAR2,
          description         IN   VARCHAR2);

Parameters

Table 11-6 SET_DESCRIPTION Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`description`	Description of the dimension.

SET_DIMENSION_NAME Procedure

This procedure sets the name for a dimension.

Syntax

SET_DIMENSION_NAME (
          dimension_owner        IN   VARCHAR2,
          dimension_name         IN   VARCHAR2,
          set_dimension_name     IN   VARCHAR2);

Parameters

Table 11-7 SET_DIMENSION_NAME Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Original name of the dimension.
`set_dimension_name`	New name for the dimension.

SET_DISPLAY_NAME Procedure

This procedure sets the display name for a dimension.

Syntax

SET_DISPLAY_NAME (
          dimension_owner     IN   VARCHAR2,
          dimension_name      IN   VARCHAR2,
          display_name        IN   VARCHAR2);

Parameters

Table 11-8 SET_DISPLAY_NAME Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`display_name`	Display name for the dimension.

SET_PLURAL_NAME Procedure

This procedure sets the plural name of a dimension.

Syntax

SET_PLURAL_NAME  (
          dimension_owner     IN   VARCHAR2,
          dimension_name      IN   VARCHAR2,
          plural_name         IN   VARCHAR2);

Parameters

Table 11-9 SET_PLURAL_NAME Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`plural_name`	Plural name for the dimension.

SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a dimension.

Syntax

SET_SHORT_DESCRIPTION (
          dimension_owner       IN   VARCHAR2,
          dimension_name        IN   VARCHAR2,
          short_description     IN   VARCHAR2);

Parameters

Table 11-10 SET_SHORT_DESCRIPTION Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`short_description`	Short description of the dimension.