11 CWM2_OLAP_DIMENSION_ATTRIBUTE

The CWM2_OLAP_DIMENSION_ATTRIBUTE package provides procedures managing dimension attributes.

This chapter discusses the following topics:

Understanding Dimension Attributes
Example: Creating a Dimension Attribute
Summary of CWM2_OLAP_DIMENSION_ATTRIBUTE Subprograms

11.1 Understanding Dimension Attributes

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

Dimension attributes define sets of level attributes for a dimension. Dimension attributes may include level attributes for some or all of the dimension's levels. For time dimensions, the dimension attributes end date and time span must be defined for all levels.

Use the procedures in the CWM2_OLAP_DIMENSION_ATTRIBUTE package to create, drop, and lock dimension attributes and to specify descriptive information for display purposes.

Several dimension attribute names are reserved, because they have special significance within CWM2. The level attributes comprising a reserved dimension attribute will be mapped to columns containing specific information. The reserved dimension attributes are listed in Table 11-1.

Table 11-1 Reserved Dimension Attributes

Dimension Attribute	Description
`Long Description`	A long description of the dimension member.
`Short Description`	A short description of the dimension member.
`End Date`	For a time dimension, the last date in a time period. (Required)
`Time Span`	For a time dimension, the number of days in a time period. (Required)
`Prior Period`	For a time dimension, the time period before this time period.
`Year Ago Period`	For a time dimension, the period a year before this time period.
`ET Key`	For an embedded total dimension, the embedded total key, which identifies the dimension member. (Required)
`Parent ET Key`	For an embedded total dimension, the dimension member that is the parent of the ET key. (Required)
`Grouping ID`	For an embedded total dimension, the grouping ID (GID), which identifies the hierarchical level for a row of the dimension table. (Required)
`Parent Grouping ID`	For an embedded total dimension, the dimension member that is the parent of the grouping ID. (Required)

The parent dimension must already exist before you can create dimension attributes for it. To fully define a dimension, follow the steps listed in "Creating a Dimension".

See Also:

Chapter 14, " CWM2_OLAP_LEVEL_ATTRIBUTE "
Oracle OLAP Application Developer's Guide for more information about dimension attributes and the OLAP metadata model

11.2 Example: Creating a Dimension Attribute

The following statement creates a dimension attribute, PRODUCT_DIM_BRAND, for the PRODUCT_DIM dimension in the JSMITH schema. The display name is Brand. The short description is Brand Name, and the description is Product Brand Name.

execute cwm2_olap_dimension_attribute.create_dimension_attribute 
     ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_BRAND', 
      'Brand', 'Brand Name', 'Product Brand Name');

The following statement creates a dimension attribute, 'Short Description', for the PRODUCT_DIM dimension in the JSMITH schema. Short Description is a reserved dimension attribute.

execute cwm2_olap_dimension_attribute.create_dimension_attribute 
     ('JSMITH', 'PRODUCT_DIM', 'Short Description', 
      'Short Product Names', 'Short Desc Product', 
      'Short Name of Products', TRUE);

Summary of CWM2_OLAP_DIMENSION_ATTRIBUTE Subprograms

Table 11-2 CWM2_OLAP_DIMENSION_ATTRIBUTE Subprograms

Subprogram	Description
CREATE_DIMENSION_ATTRIBUTE Procedure	Creates a dimension attribute.
DROP_DIMENSION_ATTRIBUTE Procedure	Drops a dimension attribute.
LOCK_DIMENSION_ATTRIBUTE Procedure	Locks the dimension attribute for update.
SET_DESCRIPTION Procedure	Sets the description for a dimension attribute.
SET_DIMENSION_ATTRIBUTE_NAME Procedure	Sets the name of a dimension attribute.
SET_DISPLAY_NAME Procedure	Sets the display name for a dimension attribute.
SET_SHORT_DESCRIPTION Procedure	Sets the short description for a dimension attribute.

CREATE_DIMENSION_ATTRIBUTE Procedure

This procedure creates a new dimension attribute.

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

If the dimension attribute name should be reserved for mapping specific groups of level attributes, you can set the RESERVED_DIMENSION_ATTRIBUTE argument to TRUE. For more information, see Table 11-1, "Reserved Dimension Attributes".

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

Syntax

CREATE_DIMENSION_ATTRIBUTE (
          dimension_owner               IN   VARCHAR2,
          dimension_name                IN   VARCHAR2,
          dimension_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 11-3 CREATE_DIMENSION_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.
`display_name`	Display name for the dimension attribute.
`short_description`	Short description of the dimension attribute.
`description`	Description of the dimension 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 11-1, "Reserved Dimension Attributes". Specify this argument if you want to create your own name for a reserved dimension attribute. `use_name_as_type` a BOOLEAN argument that defaults to FALSE. This argument specifies whether or not the dimension attribute name is a reserved name. If this argument is TRUE, the value of the `dimension_attribute_name` argument must be a reserved name from Table 11-1, "Reserved Dimension Attributes". If you do not specify a value for this argument, the dimension attribute is not reserved.

DROP_DIMENSION_ATTRIBUTE Procedure

This procedure drops a dimension attribute.

Syntax

DROP_DIMENSION_ATTRIBUTE (
         dimension_owner               IN   VARCHAR2,
         dimension_name                IN   VARCHAR2,
         dimension_attribute_name      IN   VARCHAR2);

Parameters

Table 11-4 DROP_DIMENSION_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.

LOCK_DIMENSION_ATTRIBUTE Procedure

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

Syntax

LOCK_DIMENSION_ATTRIBUTE (
           dimension_owner              IN   VARCHAR2,
           dimension_name               IN   VARCHAR2,
           dimension_attribute_name     IN   VARCHAR2,
           wait_for_lock                IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 11-5 LOCK_DIMENSION_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.
`wait_for_lock`	(Optional) Whether or not to wait for the dimension 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 dimension attribute.

Syntax

SET_DESCRIPTION (
          dimension_owner              IN   VARCHAR2,
          dimension_name               IN   VARCHAR2,
          dimension_attribute_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.
`dimension_attribute_name`	Name of the dimension attribute.
`description`	Description of the dimension attribute.

SET_DIMENSION_ATTRIBUTE_NAME Procedure

This procedure sets the name for a dimension attribute.

Syntax

SET_DIMENSION_ATTRIBUTE_NAME (
          dimension_owner                  IN   VARCHAR2,
          dimension_name                   IN   VARCHAR2,
          dimension_attribute_name         IN   VARCHAR2,
          set_dimension_attribute_name     IN   VARCHAR2,
            type                           IN   VARCHAR2             );
            use_name_as_type               IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 11-7 SET_DIMENSION__ATTRIBUTE_NAME Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`dimension_attribute_name`	Original name for the dimension attribute.
`set_dimension_attribute_name`	New name for the dimension 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 11-1, "Reserved Dimension Attributes". Specify this argument if you want to create your own name for a reserved dimension attribute. `use_name_as_type` a BOOLEAN argument that defaults to FALSE. This argument specifies whether or not the dimension attribute name is a reserved name. If this argument is TRUE, the value of the `dimension_attribute_name` argument must be a reserved name from Table 11-1, "Reserved Dimension Attributes". If you do not specify a value for this argument, the dimension attribute is not reserved.

SET_DISPLAY_NAME Procedure

This procedure sets the display name for a dimension attribute.

Syntax

SET_DISPLAY_NAME (
          dimension_owner              IN   VARCHAR2,
          dimension_name               IN   VARCHAR2,
          dimension_attribute_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.
`dimension_attribute_name`	Name of the dimension attribute.
`display_name`	Display name for the dimension attribute.

SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a dimension attribute.

Syntax

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

Parameters

Table 11-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.
`short_description`	Short description of the dimension attribute.