ADVISE_CUBE Procedure

The ADVISE_CUBE procedure helps you determine how to preaggregate a cube in an analytic workspace. When you specify a percentage of the cube's data to preaggregate, ADVISE_CUBE recommends a set of members to preaggregate from each of the cube's dimensions.

The ADVISE_CUBE procedure takes an aggmap and a precompute percentage as input. The aggmap must have a precompute clause in each of its RELATION statements. The precompute clause must consist of a valueset. Based on the precompute percentage that you specify, ADVISE_CUBE returns a set of dimension members in each valueset.

Note:

You cannot execute this procedure from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL*Plus.

See also:

"Using the Aggregate Advisor"

Syntax

ADVISE_CUBE ( 
          aggmap_name             IN   VARCHAR2,
          precompute_percentage   IN   INTEGER DEFAULT 20,
          compressed              IN   BOOLEAN DEFAULT FALSE);

Parameters

Table B-3 ADVISE_CUBE Procedure Parameters

Parameter Description

Parameter	Description
`aggmap_name`	The name of an aggmap associated with the cube. Each `RELATION` statement in the aggmap must have a precompute clause containing a valueset. `ADVISE_CUBE` returns a list of dimension members in each valueset. If the valueset is not empty, `ADVISE_CUBE` deletes its contents before adding new values.
`precompute_percentage`	A percentage of the cube's data to preaggregate. The default is 20%.
`compressed`	Controls whether the advice is for a regular composite (`FALSE`) or a compressed composite (`TRUE`).

aggmap_name

The name of an aggmap associated with the cube.

Each RELATION statement in the aggmap must have a precompute clause containing a valueset. ADVISE_CUBE returns a list of dimension members in each valueset. If the valueset is not empty, ADVISE_CUBE deletes its contents before adding new values.

precompute_percentage

A percentage of the cube's data to preaggregate. The default is 20%.

compressed

Controls whether the advice is for a regular composite (FALSE) or a compressed composite (TRUE).

Example

This example illustrates the ADVISE_CUBE procedure with a cube called UNITS dimensioned by PRODUCT and TIME. ADVISE_CUBE returns the dimension combinations to include if you want to preaggregate 40% of the cube's data.

set serveroutput on
--- View valuesets
SQL>execute dbms_aw.execute('describe prodvals');
     DEFINE PRODVALS VALUESET PRODUCT
SQL>execute dbms_aw.execute('describe timevals');
     DEFINE TIMEVALS VALUESET TIME
--- View aggmap
SQL>execute dbms_aw.execute ('describe units_agg');
     DEFINE UNITS_AGG AGGMAP
          RELATION product_parentrel PRECOMPUTE (prodvals)
          RELATION time_parentrel PRECOMPUTE (timevals)
SQL>EXECUTE dbms_aw.advise_cube ('units_agg', 40);
----
---- The results are returned in the prodvals and timevals valuesets