Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
The CWM2_OLAP_CLASSIFY
package provides procedures for managing metadata extensions for the OLAP API.
This chapter discusses the following topics:
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. |
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');
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. |
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);
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');
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.
|
Example
See "Example: Creating Descriptors".
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:
|
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".
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');
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:
|
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