Example: Creating a Hierarchy

The following statement creates a dimension hierarchy PRODUCT_DIM_ROLLUP, for the PRODUCT_DIM dimension in the JSMITH schema. The display name is Standard. The short description is Std Product, and the description is Standard Product Hierarchy. The solved code is SOLVED LEVEL-BASED, meaning that this hierarchy will be mapped to an embedded total dimension table, and that the fact table associated with this dimension hierarchy will store fully solved data.

execute cwm2_olap_hierarchy.create_hierarchy 
     ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP', 
      'Standard', 'Std Product', 'Standard Product Hierarchy', 
      'SOLVED LEVEL-BASED');

Summary of CWM2_OLAP_HIERARCHY Subprograms

Table 14-1 CWM2_OLAP_HIERARCHY Subprograms

Subprogram	Description
CREATE_HIERARCHY Procedure	Creates a hierarchy.
DROP_HIERARCHY Procedure	Drops a hierarchy.
LOCK_HIERARCHY Procedure	Locks the hierarchy for update.
SET_DESCRIPTION Procedure	Sets the description for a hierarchy.
SET_DISPLAY_NAME Procedure	Sets the display name for a hierarchy.
SET_HIERARCHY_NAME Procedure	Sets the name of a hierarchy.
SET_SHORT_DESCRIPTION Procedure	Sets the short description for a hierarchy.
SET_SOLVED_CODE Procedure	Sets the solved code for a hierarchy.

CREATE_HIERARCHY Procedure

This procedure creates a new hierarchy in the OLAP Catalog.

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

Syntax

CREATE_HIERARCHY (
          dimension_owner       IN   VARCHAR2,
          dimension_name        IN   VARCHAR2,
          hierarchy_name        IN   VARCHAR2,
          display_name          IN   VARCHAR2,
          short_description     IN   VARCHAR2,
          description           IN   VARCHAR2,
          solved_code           IN   VARCHAR2);

Parameters

Table 14-2 CREATE_HIERARCHY Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of the hierarchy.
`display_name`	Display name for the hierarchy.
`short_description`	Short description of the hierarchy.
`description`	Description of the hierarchy.
`solved_code`	Specifies whether or not the hierarchy includes embedded totals and whether it is mapped to a level-based dimension table or a parent-child dimension table. For information about mapping hierarchies with different solved codes, see "Joining Fact Tables with Dimension Tables". Values for this parameter are: `UNSOLVED LEVEL-BASED,` for a hierarchy that contains no embedded totals and is stored in a level-based dimension table `SOLVED LEVEL-BASED,` for a hierarchy that contains embedded totals, has a grouping ID, and is stored in a level-based dimension table `SOLVED VALUE-BASED,` for a hierarchy that contains embedded totals and is stored in a parent-child dimension table

DROP_HIERARCHY Procedure

This procedure drops a hierarchy from the OLAP Catalog.

Syntax

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

Parameters

Table 14-3 DROP_HIERARCHY Procedure Parameters

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

LOCK_HIERARCHY Procedure

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

Syntax

LOCK_HIERARCHY (
           dimension_owner     IN   VARCHAR2,
           dimension_name      IN   VARCHAR2,
           hierarchy_name      IN   VARCHAR2,
           wait_for_lock       IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 14-4 LOCK_HIERARCHY Procedure Parameters

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

Syntax

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

Parameters

Table 14-5 SET_DESCRIPTION Procedure Parameters

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

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,
          hierarchy_name      IN   VARCHAR2,
          display_name        IN   VARCHAR2);

Parameters

Table 14-6 SET_DISPLAY_NAME Procedure Parameters

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

SET_HIERARCHY_NAME Procedure

This procedure sets the name for a hierarchy.

Syntax

SET_HIERARCHY_NAME (
          dimension_owner        IN   VARCHAR2,
          dimension_name         IN   VARCHAR2,
          hierarchy_name         IN   VARCHAR2,
          set_hierarchy_name     IN   VARCHAR2);

Parameters

Table 14-7 SET_HIERARCHY_NAME Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Original name for the hierarchy.
`set_hierarchy_name`	New name for the hierarchy.

SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a hierarchy.

Syntax

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

Parameters

Table 14-8 SET_SHORT_DESCRIPTION Procedure Parameters

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

SET_SOLVED_CODE Procedure

This procedure sets the solved code for a hierarchy. The solved code specifies whether or not the data dimensioned by this hierarchy includes embedded totals and whether it is mapped to a level-based dimension table or a parent-child dimension table. If mapped to a parent-child dimension table, it cannot be accessed by the OLAP API.

For more information on mapping solved and unsolved data, see "Joining Fact Tables with Dimension Tables".

Syntax

SET_SOLVED_CODE (
          dimension_owner     IN   VARCHAR2,
          dimension_name      IN   VARCHAR2,
          hierarchy_name      IN   VARCHAR2,
          solved_code         IN   VARCHAR2);

Parameters

Table 14-9 SET_SOLVED_CODE Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of the hierarchy.
`solved_code`	Specifies whether or not the hierarchy includes embedded totals and whether it is mapped to a level-based dimension table or a parent-child dimension table. For information about mapping hierarchies with different solved codes, see "Joining Fact Tables with Dimension Tables". Values for this parameter are: `UNSOLVED LEVEL-BASED,` for a hierarchy that contains no embedded totals and is stored in a level-based dimension table `SOLVED LEVEL-BASED,` for a hierarchy that contains embedded totals, has a grouping ID, and is stored in a level-based dimension table `SOLVED VALUE-BASED,` for a hierarchy that contains embedded totals and is stored in a parent-child dimension table