Example: Creating a Cube

The following statements drop the cube SALES_CUBE, re-create it, and add the dimensions TIME_DIM, GEOG_DIM, and PRODUCT_DIM.

Dropping the cube removes the cube entity, along with its measures, from the OLAP Catalog. However, dropping the cube does not cause the cube's dimensions to be dropped.

execute cwm2_olap_cube.drop_cube('JSMITH', 'SALES_CUBE');
execute cwm2_olap_cube.create_cube
     ('JSMITH', 'SALES_CUBE', 'Sales', 'Sales Cube', 
      'Sales dimensioned over geography, product, and time' );
execute cwm2_olap_cube.add_dimension_to_cube
     ('JSMITH', 'SALES_CUBE', 'JSMITH', 'TIME_DIM');
execute cwm2_olap_cube.add_dimension_to_cube
     ('JSMITH', 'SALES_CUBE', 'JSMITH', 'GEOG_DIM');
execute cwm2_olap_cube.add_dimension_to_cube
     ('JSMITH', 'SALES_CUBE', 'JSMITH', 'PRODUCT_DIM');

Summary of CWM2_OLAP_CUBE Subprograms

Table 9-1 CWM2_OLAP_CUBE Subprograms

Subprogram	Description
ADD_DIMENSION_TO_CUBE Procedure	Adds a dimension to a cube.
CREATE_CUBE Procedure	Creates a cube.
DROP_CUBE Procedure	Drops a cube.
LOCK_CUBE Procedure	Locks a cube's metadata for update.
REMOVE_DIMENSION_FROM_CUBE Procedure	Removes a dimension from a cube.
SET_AGGREGATION_OPERATOR Procedure	Sets the aggregation operators for rolling up the cube's data.
SET_CUBE_NAME Procedure	Sets the name of a cube.
SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure	Sets the default calculation hierarchy for a dimension of the cube.
SET_DESCRIPTION Procedure	Sets the description for a cube.
SET_DISPLAY_NAME Procedure	Sets the display name for a cube.
SET_MV_SUMMARY_CODE Procedure	Sets the format for materialized views associated with a cube.
SET_SHORT_DESCRIPTION Procedure	Sets the short description for a cube.

ADD_DIMENSION_TO_CUBE Procedure

This procedure adds a dimension to a cube.

Syntax

ADD_DIMENSION_TO_CUBE  (
          cube_owner         IN   VARCHAR2,
          cube_name          IN   VARCHAR2,
          dimension_owner    IN   VARCHAR2,
          dimension_name     IN   VARCHAR2);

Parameters

Table 9-2 ADD_DIMENSION_TO_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`dimension_owner`	Owner of the dimension to be added to the cube.
`dimension_name`	Name of the dimension to be added to the cube.

CREATE_CUBE Procedure

This procedure creates a new cube in the OLAP Catalog.

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

Syntax

CREATE_CUBE (
          cube_owner            IN   VARCHAR2,
          cube_name             IN   VARCHAR2,
          display_name          IN   VARCHAR2,
          short_description     IN   VARCHAR2,
          description           IN   VARCHAR2);

Parameters

Table 9-3 CREATE_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`display_name`	Display name for the cube.
`short_description`	Short description of the cube.
`description`	Description of the cube.

DROP_CUBE Procedure

This procedure drops a cube from the OLAP Catalog.

Note:

When a cube is dropped, its associated measures are also dropped. However, the cube's dimensions are not dropped. They might be mapped within the context of a different cube.

Syntax

DROP_CUBE (
         cube_owner     IN   VARCHAR2,
         cube_name      IN   VARCHAR2);

Parameters

Table 9-4 DROP_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.

LOCK_CUBE Procedure

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

Syntax

LOCK_CUBE (
           cube_owner        IN   VARCHAR2,
           cube_name         IN   VARCHAR2.
           wait_for_lock     IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 9-5 LOCK_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`wait_for_lock`	(Optional) Whether or not to wait for the cube 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.

REMOVE_DIMENSION_FROM_CUBE Procedure

This procedure removes a dimension from a cube.

Syntax

REMOVE_DIMENSION_FROM_CUBE  (
          cube_owner         IN   VARCHAR2,
          cube_name          IN   VARCHAR2,
          dimension_owner    IN   VARCHAR2,
          dimension_name     IN   VARCHAR2);

Parameters

Table 9-6 REMOVE_DIMENSION_FROM_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`dimension_owner`	Owner of the dimension to be removed from the cube.
`dimension_name`	Name of the dimension to be removed from the cube.

SET_AGGREGATION_OPERATOR Procedure

This procedure sets the aggregation operator for rolling up a cube's data over its dimensions. The cube must be mapped to a star schema, with a storage type indicator of 'LOWESTLEVEL'. (See "Joining Fact Tables with Dimension Tables".)

The aggregation operators supported by the OLAP Catalog are listed in Table 1-10, "Aggregation Operators".

When no aggregation operator is specified, the operator is addition. The view ALL_OLAP2_AGGREGATION_USES lists the nondefault aggregation operators that have been specified for cubes. See "ALL_OLAP2_AGGREGATION_USES".

Syntax

SET_AGGREGATION_OPERATOR (
          cube_owner        IN   VARCHAR2,
          cube_name         IN   VARCHAR2,
          aggop_spec        IN   VARCHAR2);

Parameters

Table 9-7 SET_AGGREGATION_OPERATOR Procedure Parameters

Parameter Description

cube_owner

Owner of the cube.

cube_name

Name of the cube.

aggop_spec

A string that specifies the aggregation operators for the cube.

Each aggregation operator that you specify applies to all of the cube's measures over a given hierarchy of a given dimension of the cube. If you do not specify a hierarchy, the operator applies to all hierarchies of the dimension. By default, the aggregation operator is addition.

Enclose the string in single quotes, and separate each dimension/operator clause with a semicolon as follows:

'DIM:dim1_owner.dim1_name/AGGOP:operator;DIM:dim2_owner.dim2_name/AGGOP:operator;...........'

If the operator should apply to a specific hierarchy of a dimension, use the optional 'HIER' clause after the DIM clause:

/HIER:hiername1

For weighted operators, the 'AGGOP' clause may optionally be followed with a WEIGHTBY clause:

/WEIGHTBY:TblOwner.TblName.ColName;

NOTE: The cube's data will be aggregated in the order of the dimension clauses in the aggregation specification.

Example

The following example specifies that data in the ANALYTIC_CUBE should be aggregated using addition over the Standard hierarchies of the Product and Channel dimensions, using the MAX operator over the Standard hierarchy of Geography, and using AVERAGE over the Year to Date hierarchy of the Time dimension. Any unspecified hierarchies will use addition.

execute cwm2_olap_cube.set_aggregation_operator
            ('XADEMO', 'ANALYTIC_CUBE',
             'DIM:XADEMO.PRODUCT/HIER:STANDARD/AGGOP:SUM; 
              DIM:XADEMO.GEOGRAPHY/HIER:STANDARD/AGGOP:MAX; 
              DIM:XADEMO.TIME/HIER:YTD/AGGOP:AVERAGE; 
              DIM:XADEMO.CHANNEL/HIER:STANDARD/AGGOP:SUM;');

The following example shows the same specification including a weighted operator for Product.

execute cwm2_olap_cube.set_aggregation_operator
            ('XADEMO', 'ANALYTIC_CUBE',
             'DIM:XADEMO.PRODUCT/HIER:STANDARD/AGGOP:SUM/ 
                                WEIGHTBY:XADEMO.XADEMO_SALES_VIEW.COSTS;
              DIM:XADEMO.GEOGRAPHY/HIER:STANDARD/AGGOP:MAX; 
              DIM:XADEMO.TIME/HIER:YTD/AGGOP:AVERAGE; 
              DIM:XADEMO.CHANNEL/HIER:STANDARD/AGGOP:SUM;');

In the following example, aggregation operators are specified for all hierarchies of each dimension.

execute cwm2_olap_cube.set_aggregation_operator
            ('XADEMO', 'ANALYTIC_CUBE',
              DIM:XADEMO.PRODUCT/AGGOP:SUM; 
              DIM:XADEMO.GEOGRAPHY/AGGOP:MAX; 
              DIM:XADEMO.TIME/AGGOP:AVERAGE; 
              DIM:XADEMO.CHANNEL/AGGOP:SUM;');

SET_CUBE_NAME Procedure

This procedure sets the name for a cube.

Syntax

SET_CUBE_NAME (
          cube_owner        IN   VARCHAR2,
          cube_name         IN   VARCHAR2,
          set_cube_name     IN   VARCHAR2);

Parameters

Table 9-8 SET_CUBE_NAME Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Original name of the cube.
`set_cube_name`	New name for the cube.

SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure

This procedure sets the default calculation hierarchy for a dimension of this cube.

Syntax

SET_DEFAULT_CUBE_DIM_CALC_HIER (
          cube_owner       IN   VARCHAR2,
          cube_name        IN   VARCHAR2,
          dimension_owner  IN   VARCHAR2,
          dimension_name   IN   VARCHAR2,
          hierarchy_name   IN   VARCHAR2);

Parameters

Table 9-9 SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_owner`	Name of the cube.
`dimension_owner`	Owner of the dimension.
`dimension_name`	Name of the dimension.
`hierarchy_name`	Name of the hierarchy to be used by default for this dimension.

SET_DESCRIPTION Procedure

This procedure sets the description for a cube.

Syntax

SET_DESCRIPTION (
          cube_owner     IN   VARCHAR2,
          cube_name      IN   VARCHAR2,
          description    IN   VARCHAR2);

Parameters

Table 9-10 SET_DESCRIPTION Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`description`	Description of the cube.

SET_DISPLAY_NAME Procedure

This procedure sets the display name for a cube.

Syntax

SET_DISPLAY_NAME (
          cube_owner     IN   VARCHAR2,
          cube_name      IN   VARCHAR2,
          display_name   IN   VARCHAR2);

Parameters

Table 9-11 SET_DISPLAY_NAME Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`display_name`	Display name for the cube.

SET_MV_SUMMARY_CODE Procedure

This procedure specifies the form of materialized views for this cube. Materialized views may be in Grouping Set (groupingset) or Rolled Up (rollup) form.

In a materialized view in Rolled Up form, all the dimension key columns are populated, and data may only be accessed when its full lineage is specified.

In a materialized view in Grouping Set form, dimension key columns may contain null values, and data may be accessed simply by specifying one or more levels.

Syntax

SET_MV_SUMMARY_CODE (
          cube_owner            IN   VARCHAR2,
          cube_name             IN   VARCHAR2,
          summary_code          IN   VARCHAR2);

Parameters

Table 9-12 SET_MV_SUMMARY_CODE Procedure Parameters

Parameter Description

cube_owner

Owner of the cube.

cube_name

Name of the cube.

summary_code

One of the following case-insensitive values:

rollup, for Rolled Up form.
groupingset, for Grouping Set form.

SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a cube.

Syntax

SET_SHORT_DESCRIPTION (
          cube_owner            IN   VARCHAR2,
          cube_name             IN   VARCHAR2,
          short_description     IN   VARCHAR2);

Parameters

Table 9-13 SET_SHORT_DESCRIPTION Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`short_description`	Short description of the cube.