Skip Headers
Oracle® OLAP Reference
10g Release 2 (10.2)

Part Number B14350-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

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