Example: Creating Level Attributes

The following statements create a color attribute for the lowest level and long descriptions for all four levels of the PRODUCT_DIM dimension.

execute cwm2_olap_level_attribute.create_level_attribute
     ('JSMITH', 'PRODUCT_DIM', 'Product Color', 'PRODUCT_LVL', 'Product Color',
      'PROD_STD_COLOR', 'Prod Color', 'Product Color');

execute cwm2_olap_level_attribute.create_level_attribute
     ('JSMITH', 'PRODUCT_DIM', 'Long Description', 'PRODUCT_LVL', 
      'Long Description','PRODUCT_STD_LLABEL', 'Product', 
      'Long Labels for individual products of the PRODUCT hierarchy', TRUE);

execute cwm2_olap_level_attribute.create_level_attribute
     ('JSMITH', 'PRODUCT_DIM', 'Long Description', 'PROD_SUBCATEGORY_LVL',
      'Long Description', 'PROD_STD_LLABEL', 'Product Sub Category', 
      'Long Labels for subcategories of the PRODUCT hierarchy', TRUE);

execute cwm2_olap_level_attribute.create_level_attribute
     ('JSMITH', 'PRODUCT_DIM', 'Long Description', 'PROD_CATEGORY_LVL',
      'Long Description', 'PROD_STD_LLABEL', 'Product Category', 
      'Long Labels for categories of the PRODUCT hierarchy', TRUE);

execute cwm2_olap_level_attribute.create_level_attribute
     ('JSMITH', 'PRODUCT_DIM', 'Long Description', 'TOTALPROD_LVL',
      'Long Description', 'PROD_STD_LLABEL', 'Total Product', 
      'Long Labels for total of the PRODUCT hierarchy', TRUE);

Summary of CWM2_OLAP_LEVEL_ATTRIBUTE Subprograms

Table 16-2 CWM2_OLAP_LEVEL_ATTRIBUTE Subprograms

Subprogram	Description
CREATE_LEVEL_ATTRIBUTE Procedure	Creates a level attribute.
DROP_LEVEL_ATTRIBUTE Procedure	Drops a level attribute.
LOCK_LEVEL_ATTRIBUTE Procedure	Locks the level attribute metadata for update.
SET_DESCRIPTION Procedure	Sets the description for a level attribute.
SET_DISPLAY_NAME Procedure	Sets the display name for a level attribute.
SET_LEVEL_ATTRIBUTE_NAME Procedure	Sets the name of a level attribute.
SET_SHORT_DESCRIPTION Procedure	Sets the short description for a level attribute.

CREATE_LEVEL_ATTRIBUTE Procedure

This procedure creates a new level attribute in the OLAP Catalog and associates the level attribute with a level and with a dimension attribute.

If the level attribute is reserved, you can specify the reserved name as the level attribute name or as a type associated with a name that you specify. The reserved level attributes are listed in Table 16-1, "Reserved Level Attributes".

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

Syntax

CREATE_LEVEL_ATTRIBUTE (
          dimension_owner            IN   VARCHAR2,
          dimension_name             IN   VARCHAR2,
          dimension_attribute_name   IN   VARCHAR2,
          level_name                 IN   VARCHAR2,
          level_attribute_name       IN   VARCHAR2,
          display_name               IN   VARCHAR2,
          short_description          IN   VARCHAR2,
          description                IN   VARCHAR2,
            type                     IN   VARCHAR2             );
            use_name_as_type         IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 16-3 CREATE_LEVEL_ATTRIBUTE Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`dimension_attribute_name`	Name of the dimension attribute that includes this level attribute.
`level_name`	Name of the level.
`level_attribute_name`	Name of the level attribute.
`display_name`	Display name for the level attribute.
`short_description`	Short description of the level attribute.
`description`	Description of the level attribute.
`type` or`use_name_as_type`	This argument can be one of the following: `type` a VARCHAR2 argument whose value is one of the reserved names from Table 16-1, "Reserved Level Attributes". Specify this argument if you want to create your own name for a reserved level attribute. `use_name_as_type` a BOOLEAN argument that defaults to FALSE. This argument specifies whether or not the level attribute name is a reserved name. If this argument is TRUE, the value of the `level_attribute_name` argument must be a reserved name from Table 16-1, "Reserved Level Attributes". If you do not specify a value for this argument, the level attribute is not reserved.

DROP_LEVEL_ATTRIBUTE Procedure

This procedure drops a level attribute from the OLAP Catalog.

Syntax

DROP_LEVEL_ATTRIBUTE (
         dimension_owner            IN   VARCHAR2,
         dimension_name             IN   VARCHAR2,
         dimension_attribute_name   IN   VARCHAR2,
         level_name                 IN   VARCHAR2,
         level_attribute_name       IN   VARCHAR2);

Parameters

Table 16-4 DROP_LEVEL_ATTRIBUTE Procedure Parameters

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

LOCK_LEVEL_ATTRIBUTE Procedure

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

Syntax

LOCK_LEVEL_ATTRIBUTE (
           dimension_owner            IN   VARCHAR2,
           dimension_name             IN   VARCHAR2,
           dimension_attribute_name   IN   VARCHAR2,
           level_name                 IN   VARCHAR2,
           level_attribute_name       IN   VARCHAR2,
           wait_for_lock              IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 16-5 LOCK_LEVEL_ATTRIBUTE Procedure Parameters

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

Syntax

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

Parameters

Table 16-6 SET_DESCRIPTION Procedure Parameters

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

SET_DISPLAY_NAME Procedure

This procedure sets the display name for a level attribute.

Syntax

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

Parameters

Table 16-7 SET_DISPLAY_NAME Procedure Parameters

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

SET_LEVEL_ATTRIBUTE_NAME Procedure

This procedure sets the name for a level attribute.

Syntax

SET_LEVEL_ATTRIBUTE_NAME (
          dimension_owner             IN   VARCHAR2,
          dimension_name              IN   VARCHAR2,
          dimension_attribute_name    IN   VARCHAR2,
          level_name                  IN   VARCHAR2,
          level_attribute_name        IN   VARCHAR2,
          set_level_attribute_name    IN   VARCHAR2,
            type                      IN   VARCHAR2             );
            use_name_as_type          IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 16-8 SET_LEVEL_ATTRIBUTE_NAME Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`dimension_attribute_name`	Name of the dimension attribute.
`level_name`	Name for the level.
`level_attribute_name`	Original name for the level attribute.
`set_level_attribute_name`	New name for the level attribute.
`type` or`use_name_as_type`	This argument can be one of the following: `type` a VARCHAR2 argument whose value is one of the reserved names from Table 16-1, "Reserved Level Attributes". Specify this argument if you want to create your own name for a reserved level attribute. `use_name_as_type` a BOOLEAN argument that defaults to FALSE. This argument specifies whether or not the level attribute name is a reserved name. If this argument is TRUE, the value of the `level_attribute_name` argument must be a reserved name from Table 16-1, "Reserved Level Attributes". If you do not specify a value for this argument, the level attribute is not reserved.

SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a level attribute.

Syntax

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

Parameters

Table 16-9 SET_SHORT_DESCRIPTION Procedure Parameters

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