Example: Creating a Level

The following statements create four levels for the PRODUCT_DIM dimension and assign them to the PRODUCT_DIM_ROLLUP hierarchy.

execute cwm2_olap_level.create_level
     ('JSMITH', 'PRODUCT_DIM', 'TOTALPROD_LVL', 
      'Total Product', 'All Products', 'Total', 
      'Equipment and Parts of standard product hierarchy');
execute cwm2_olap_level.create_level
     ('JSMITH', 'PRODUCT_DIM', 'PROD_CATEGORY_LVL', 
      'Product Category', 'Product Categories', 'Category', 
      'Categories of standard product hierarchy');
execute cwm2_olap_level.create_level 
     ('JSMITH', 'PRODUCT_DIM', 'PROD_SUBCATEGORY_LVL', 
      'Product Sub-Category', 'Product Sub-Categories', 'Sub-Category',
      'Sub-Categories of standard product hierarchy');
execute cwm2_olap_level.create_level 
     ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_LVL', 
      'Product', 'Products', 'Product',
      'Individual products of standard product hierarchy');

execute cwm2_olap_level.add_level_to_hierarchy
     ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP', 
      'PRODUCT_LVL', 'PROD_SUBCATEGORY_LVL');
execute cwm2_olap_level.add_level_to_hierarchy
     ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP', 
      'PROD_SUBCATEGORY_LVL', 'PROD_CATEGORY_LVL');
execute cwm2_olap_level.add_level_to_hierarchy
     ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP', 
      'PROD_CATEGORY_LVL', 'TOTALPROD_LVL');
execute cwm2_olap_level.add_level_to_hierarchy
     ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP', 'TOTALPROD_LVL');

Summary of CWM2_OLAP_LEVEL Subprograms

Table 15-1 CWM2_OLAP_LEVEL Subprograms

Subprogram	Description
ADD_LEVEL_TO_HIERARCHY Procedure	Adds a level to a hierarchy.
CREATE_LEVEL Procedure	Creates a level.
DROP_LEVEL Procedure	Drops a level.
LOCK_LEVEL Procedure	Locks the level metadata for update.
REMOVE_LEVEL_FROM_HIERARCHY Procedure	Removes a level from a hierarchy.
SET_DESCRIPTION Procedure	Sets the description for a level.
SET_DISPLAY_NAME Procedure	Sets the display name for a level.
SET_LEVEL_NAME Procedure	Sets the name of a level.
SET_PLURAL_NAME Procedure	Sets the plural name for a level.
SET_SHORT_DESCRIPTION Procedure	Sets the short description for a level.

ADD_LEVEL_TO_HIERARCHY Procedure

This procedure adds a level to a hierarchy. A hierarchy can have a maximum of 31 levels.

Syntax

ADD_LEVEL_TO_HIERARCHY (
          dimension_owner     IN   VARCHAR2,
          dimension_name      IN   VARCHAR2,
          hierarchy_name      IN   VARCHAR2,
          level_name          IN   VARCHAR2,
          parent_level_name   IN   VARCHAR2  DEFAULT NULL);

Parameters

Table 15-2 ADD_LEVEL_TO_HIERARCHY Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of the hierarchy.
`level_name`	Name of the level to add to the hierarchy.
`parent_level_name`	Name of the level's parent in the hierarchy. If you do not specify a parent, then the added level is the root of the hierarchy.

CREATE_LEVEL Procedure

This procedure creates a new level in the OLAP Catalog.

You must specify descriptions and display properties as part of level creation. Once the level has been created, you can override these properties by calling other procedures in the CWM2_OLAP_LEVEL package.

Syntax

CREATE_LEVEL (
          dimension_owner       IN   VARCHAR2,
          dimension_name        IN   VARCHAR2,
          level_name            IN   VARCHAR2,
          display_name          IN   VARCHAR2,
          plural_name           IN   VARCHAR2,
          short_description     IN   VARCHAR2,
          description           IN   VARCHAR2);

Parameters

Table 15-3 CREATE_LEVEL Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`level_name`	Name of the level.
`display_name`	Display name for the level.
`plural_name`	Plural name for the level.
`short_description`	Short description of the level.
`description`	Description of the level.

DROP_LEVEL Procedure

This procedure drops a level from the OLAP Catalog. All related level attributes are also dropped.

Syntax

DROP_LEVEL (
         dimension_owner     IN   VARCHAR2,
         dimension_name      IN   VARCHAR2,
         level_name          IN   VARCHAR2);

Parameters

Table 15-4 DROP_LEVEL Procedure Parameters

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

LOCK_LEVEL Procedure

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

Syntax

LOCK_LEVEL (
           dimension_owner     IN   VARCHAR2,
           dimension_name      IN   VARCHAR2,
           level_name          IN   VARCHAR2,
           wait_for_lock       IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 15-5 LOCK_LEVEL Procedure Parameters

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

This procedure removes a level from a hierarchy.

Syntax

REMOVE_LEVEL_FROM_HIERARCHY (
          dimension_owner     IN   VARCHAR2,
          dimension_name      IN   VARCHAR2,
          hierarchy_name      IN   VARCHAR2,
          level_name          IN   VARCHAR2);

Parameters

Table 15-6 REMOVE_LEVEL_FROM_HIERARCHY Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of the hierarchy.
`level_name`	Name of the level to remove from the hierarchy.

SET_DESCRIPTION Procedure

This procedure sets the description for a level.

Syntax

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

Parameters

Table 15-7 SET_DESCRIPTION Procedure Parameters

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

SET_DISPLAY_NAME Procedure

This procedure sets the display name for a level.

Syntax

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

Parameters

Table 15-8 SET_DISPLAY_NAME Procedure Parameters

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

SET_LEVEL_NAME Procedure

This procedure sets the name for a level.

Syntax

SET_LEVEL_NAME (
          dimension_owner   IN   VARCHAR2,
          dimension_name    IN   VARCHAR2,
          level_name        IN   VARCHAR2,
          set_level_name    IN   VARCHAR2);

Parameters

Table 15-9 SET_LEVEL_NAME Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`level_name`	Original name for the level.
`set_level_name`	New name for the level.

SET_PLURAL_NAME Procedure

This procedure sets the plural name of a level.

Syntax

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

Parameters

Table 15-10 SET_PLURAL_NAME Procedure Parameters

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

SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a level.

Syntax

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

Parameters

Table 15-11 SET_SHORT_DESCRIPTION Procedure Parameters

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