8 CWM2_OLAP_CLASSIFY

The CWM2_OLAP_CLASSIFY package provides procedures for managing metadata extensions for the OLAP API.

This chapter discusses the following topics:

OLAP Catalog Metadata Descriptors

Example: Creating Descriptors
Summary of CWM2_OLAP_CLASSIFY Subprograms

8.1 OLAP Catalog Metadata Descriptors

The OLAP Catalog metadata descriptors provide additional information about your data. These descriptors can be used by the OLAP API.

The OLAP Catalog metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".

You can view the descriptors that have been set for your OLAP Catalog metadata in the views ALL_OLAP2_ENTITY_EXT_PARMS (described) and ALL_OLAP2_ENTITY_PARAMETERS (described ).

Table 8-1 OLAP Catalog Metadata Descriptors

Descriptor	Applies To	Description
`Level Standard`	level	The level is not in a time dimension.
`Level Year`	level	The year level in a time dimension.
`Level HalfYear`	level	The half year level in a time dimension.
`Level Quarter`	level	The quarter level in a time dimension.
`Level Month`	level	The month level in a time dimension.
`Level Week`	level	The week level in a time dimension.
`Level Day`	level	The day level in a time dimension.
`Level Hour`	level	The hour level in a time dimension.
`Level Minute`	level	The minutes level in a time dimension.
`Level Second`	level	The seconds level in a time dimension.
`Value Separator`	dimension	The separator character used by the OLAP API to construct the names of dimension members. The default separator is "::".
`Skip Level`	hierarchy	Whether or not the hierarchy supports skip levels. An example of a skip level hierarchy is City-State-Country, where Washington D.C. is a City whose parent is a Country.
`Measure Format`	measure	The display format for a measure.
`Measure Unit`	measure	The unit of measurement of a measure.
`Fact Table Join`	hierarchy	The key columns in a dimension table that satisfy the join to a fact table. This descriptor applies to CWM2 metadata only.
`Default Member`	hierarchy	The default dimension member in a hierarchy.
`Dense Indicator`	dimension	Whether or not the data over a given dimension of a cube is dense or sparse.
`Estimated Cardinality`	level	Estimated number of dimension members in a given level.

8.2 Example: Creating Descriptors

The following examples show how to set some of the metadata descriptors.

Note:

If you have used Enterprise Manager to create your OLAP metadata, be sure to respect the case of metadata names.

The following statements specify the quarter, month, and year levels in the time dimension XADEMO.TIME.

execute cwm2_olap_classify.add_entity_descriptor_use
     ('Level Year', 'LEVEL', 'XADEMO', 'TIME', 'L1');
execute cwm2_olap_classify.add_entity_descriptor_use
     ('Level Quarter', 'LEVEL', 'XADEMO', 'TIME', 'L2');
execute cwm2_olap_classify.add_entity_descriptor_use
     ('Level Month', 'LEVEL', 'XADEMO', 'TIME', 'L3');

The following statement indicates that the value separator used by the OLAP API to contruct dimesion member names for XADEMO.TIME is the default ("::").

execute cwm2_olap_classify.add_entity_descriptor_use
     ('Value Separator', 'DIMENSION', 'XADEMO', 'TIME', NULL, NULL,
      'Value Separator','::');

The following statement indicates that the data in the cube XADEMO.ANALYTIC_CUBE is dense over Time and Geography, but sparse over Channel and Product.

execute cwm2_olap_classify.add_entity_denseindicator_use
     ('XADEMO', 'ANALYTIC_CUBE', 'XADEMO', 'TIME', 'YES');
execute cwm2_olap_classify.add_entity_denseindicator_use
     ('XADEMO', 'ANALYTIC_CUBE', 'XADEMO', 'GEOGRAPHY', 'YES');
execute cwm2_olap_classify.add_entity_denseindicator_use
     ('XADEMO', 'ANALYTIC_CUBE', 'XADEMO', 'CHANNEL', 'NO');
execute cwm2_olap_classify.add_entity_denseindicator_use
     ('XADEMO', 'ANALYTIC_CUBE', 'XADEMO', 'PRODUCT', 'NO');

The following statement removes the Dense Indicator descriptors from XADEMO.ANALYTIC_CUBE.

execute cwm2_olap_classify.remove_entity_descriptor_use
  ('Dense Indicator', 'DENSE INDICATOR','XADEMO', 'ANALYTIC_CUBE',
   'XADEMO', 'CHANNEL');
execute cwm2_olap_classify.remove_entity_descriptor_use
  ('Dense Indicator', 'DENSE INDICATOR','XADEMO', 'ANALYTIC_CUBE',
   'XADEMO', 'PRODUCT');
execute cwm2_olap_classify.remove_entity_descriptor_use
  ('Dense Indicator', 'DENSE INDICATOR','XADEMO', 'ANALYTIC_CUBE',
   'XADEMO', 'GEOGRAPHY');
execute cwm2_olap_classify.remove_entity_descriptor_use
  ('Dense Indicator', 'DENSE INDICATOR','XADEMO', 'ANALYTIC_CUBE',
   'XADEMO', 'TIME');

Summary of CWM2_OLAP_CLASSIFY Subprograms

Table 8-2 CWM2_OLAP_CLASSIFY Subprograms

Subprogram	Description
ADD_ENTITY_CARDINALITY_USE	Adds the `Estimated Cardinality` descriptor to a level of a hierarchy.
ADD_ENTITY_DEFAULTMEMBER_USE	Adds the `Default Member` descriptor to a hierarchy.
ADD_ENTITY_DENSEINDICATOR_USE	Adds the `Dense Indicator` descriptor to a dimension of a cube.
ADD_ENTITY_DESCRIPTOR_USE	Applies a descriptor to a metadata entity.
ADD_ENTITY_FACTJOIN_USE	Adds the `Fact Table Join` descriptor to a CWM2 hierarchy.
REMOVE_ENTITY_DESCRIPTOR_USE	Removes a descriptor from a metadata entity.

ADD_ENTITY_CARDINALITY_USE

This procedure adds the Estimated Cardinality descriptor to a level of a hierarchy.

The OLAP Catalog metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".

Syntax

ADD_ENTITY_CARDINALITY_USE  (
               dimension_owner         IN   VARCHAR2,
               dimension_name          IN   VARCHAR2,
               hierarchy_name          IN   VARCHAR2,
               level_name              IN   VARCHAR2,
               estimated_cardinality   IN   NUMBER);

Parameters

Table 8-3 ADD_ENTITY_CARDINALITY_USE Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Hierarchy within the dimension. If the dimension has no hierarchy, specify `NULL`.
`level_name`	Level within the hierarchy.
`estimated_cardinality`	Estimated number of dimension members in the level.

Example

The following statement sets the estimated cardinality of a level in the Standard hierarchy of the Geography dimension.

execute cwm2_olap_classify.add_entity_cardinality_use 
          ('XADEMO', 'GEOGRAPHY', 'STANDARD', 'L4', 60);

ADD_ENTITY_DEFAULTMEMBER_USE

This procedure adds the Default Member descriptor to a hierarchy.

The OLAP Catalog metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".

Syntax

ADD_ENTITY_DEFAULTMEMBER_USE  (
               dimension_owner        IN   VARCHAR2,
               dimension_name         IN   VARCHAR2,
               hierarchy_name         IN   VARCHAR2,
               default_member         IN   VARCHAR2,
               default_member_level   IN   VARCHAR2,
               position               IN   NUMBER DEFAULT NULL);

Parameters

Table 8-4 ADD_ENTITY_DEFAULTMEMBER_USE Procedure Parameters

Parameter	Description
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of the hierarchy.
`default_member`	Name of a dimension member in the hierarchy.
`default_member_level`	Level of the default dimension member.
`position`	Position of the default member within a multi-column key. If position is not meaningful, this parameter is NULL (default).

Example

The following statement sets the default member of the Standard hierarchy in the Geography dimension to Paris.

execute cwm2_olap_classify.add_entity_defaultmember_use 
          ('XADEMO', 'GEOGRAPHY', 'STANDARD', 'Paris', 'L4');

ADD_ENTITY_DENSEINDICATOR_USE

This procedure adds the Dense Indicator descriptor to a dimension of a cube.

The OLAP Catalog metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".

Syntax

ADD_ENTITY_DENSEINDICATOR_USE  (
               cube_owner        IN   VARCHAR2,
               cube_name         IN   VARCHAR2,
               dimension_owner   IN   VARCHAR2,
               dimension_name    IN   VARCHAR2,
               dense_indicator   IN   VARCHAR2 );

Parameters

Table 8-5 ADD_ENTITY_DENSEINDICATOR_USE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`dense_indicator`	`YES` indicates that the data over this dimension is dense. This means that data exists for most dimension members. `NO` indicates that the data over this dimension is sparse. This means that there is no data for many of the dimension members.

Example

See "Example: Creating Descriptors".

ADD_ENTITY_DESCRIPTOR_USE

This procedure adds a descriptor to a metadata entity.

The OLAP Catalog metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".

Syntax

ADD_ENTITY_DESCRIPTOR_USE  (
               descriptor_name               IN   VARCHAR2,
               entity_type                   IN   VARCHAR2,
               entity_owner                  IN   VARCHAR2,
               entity_name                   IN   VARCHAR2,
               entity_child_name             IN   VARCHAR2 DEFAULT NULL,
               entity_secondary_child_name   IN   VARCHAR2 DEFAULT NULL,
               parameter_name                IN   VARCHAR2 DEFAULT NULL,
               parameter_value               IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 8-6 ADD_ENTITY_DESCRIPTOR_USE Procedure Parameters

Parameter	Description
`descriptor_name`	Name of the descriptor.
`entity_type`	Type of metadata entity to which the descriptor applies. Types are: `DIMENSION` `HIERARCHY` `LEVEL` `LEVEL ATTRIBUTE` `DIMENSION ATTRIBUTE` `CUBE` `MEASURE`
`entity_owner`	Schema of the cube or dimension.
`entity_name`	Name of the cube or dimension.
`entity_child_name`	Name of a child of the cube or dimension. For example, a dimension attribute is a child of a dimension, and a measure is a child of a cube. If the descriptor applies to a cube or dimension, this parameter is NULL.
`entity_secondary_child_name`	Name of a child of the child entity. For example, a level attribute is a child of a level, which is a child of a dimension. If the descriptor applies to a cube or dimension, or a child of a cube or dimension, this parameter is NULL.
`parameter_name`	Label for the descriptor. You can specify any label that you choose.
`parameter_value`	Value of the descriptor.

Example

See "Example: Creating Descriptors".

ADD_ENTITY_FACTJOIN_USE

This procedure adds the Fact Table Join descriptor to a cube. The Fact Table Join descriptor applies to CWM2 metadata only.

The OLAP Catalog metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".

Syntax

ADD_ENTITY_FACTJOIN_USE  (
               cube_owner              IN   VARCHAR2,
               cube_name               IN   VARCHAR2,
               dimension_owner         IN   VARCHAR2,
               dimension_name          IN   VARCHAR2,
               hierarchy_name          IN   VARCHAR2,
               dim_table_owner         IN   VARCHAR2,
               dim_table_name          IN   VARCHAR2,
               dim_table_column_name   IN   VARCHAR2,
               position                IN   NUMBER DEFAULT NULL);

Parameters

Table 8-7 ADD_ENTITY_FACTJOIN_USE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`dimension_owner`	Owner of a dimension of the cube.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of a hierarchy of the dimension.
`dim_table_owner`	Owner of the dimension table.
`dim_table_name`	Name of the dimension table.
`dim_table_column_name`	Key column in the dimension table that maps to a foreign key column in the fact table.
`position`	Position of the key column in a multi-column key. If the key is in a single column, this parameter is NULL (Default).

Example

The following statement adds Fact Table Join descriptor to the Standard hierarchy of the Geography dimension of the ANALYTIC_CUBE.

execute cwm2_olap_classify.add_entity_factjoin_use 
          ('XADEMO', 'ANALYTIC_CUBE', 'XADEMO','GEOGRAPHY, 'STANDARD',
           'XADEMO', 'XADEMO_GEOGRAPHY', 'GEOG_STD_CITY');

REMOVE_ENTITY_DESCRIPTOR_USE

This procedure removes a descriptor from an entity.

The OLAP Catalog metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".

Syntax

REMOVE_ENTITY_DESCRIPTOR_USE  (
               descriptor_name               IN   VARCHAR2,
               entity_type                   IN   VARCHAR2,
               entity_owner                  IN   VARCHAR2,
               entity_name                   IN   VARCHAR2,
               entity_child_name             IN   VARCHAR2 DEFAULT NULL,
               entity_secondary_child_name   IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 8-8 REMOVE_ENTITY_DESCRIPTOR_USE Procedure Parameters

Parameter	Description
`descriptor_name`	Name of the descriptor to remove.
`entity_type`	Type of metadata entity to which the descriptor applies. Types are: `DIMENSION` `HIERARCHY` `LEVEL` `LEVEL ATTRIBUTE` `DIMENSION ATTRIBUTE` `CUBE` `MEASURE` `ESTIMATED CARDINALITY` `DEFAULT MEMBER` `DENSE INDICATOR` `FACT TABLE JOIN`
`entity_owner`	Schema of the cube or dimension.
`entity_name`	Name of the cube or dimension.
`entity_child_name`	Name of a child of the cube or dimension. For example, a dimension attribute is a child of a dimension, and a measure is a child of a cube. If the descriptor applies to a cube or dimension, this parameter is NULL.
`entity_secondary_child_name`	Name of a child of the child entity. For example, a level attribute is a child of a level, which is a child of a dimension. If the descriptor applies to a cube or dimension, or a child of a cube or dimension, this parameter is NULL.

Example

See "Example: Creating Descriptors".