ADVISE_DIMENSIONALITY Procedure

The ADVISE_DIMENSIONALITY procedure evaluates the information provided by the ADVISE_SPARSITY procedure and generates the OLAP DML commands for defining a composite and a variable in the analytic workspace.

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 Sparsity Advisor"

Syntax

ADVISE_DIMENSIONALITY ( 
          output     OUT    CLOB,
          cubename   IN     VARCHAR2,
          sparsename IN     VARCHAR2 DEFAULT NULL,
          dtype      IN     VARCHAR2 DEFAULT 'NUMBER',
          advtable   IN     VARCHAR2 DEFAULT NULL);

Parameters

Table B-5 ADVISE_DIMENSIONALITY Procedure Parameters

Parameter	Description
`output`	The name of an object (such as a PL/SQL variable) in which the recommendations of the procedure is stored.
`cubename`	The same cubename value provided in the call to `ADVISE_SPARSITY`.
`sparsename`	An object name for the sample composite. The default value is `cubename.cp`.
`dtype`	The OLAP DML data type of the sample variable.
`advtable`	The name of the table created by the `SPARSITY_ADVICE_TABLE` procedure in which the results of the analysis are stored.

Example

The following PL/SQL program fragment defines a variable named DEFS to store the recommended definitions.

DECLARE
     defs CLOB;
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
          .
          .
          .
dbms_aw.advise_dimensionality(defs, 'units_cube_measure_stored', 
     'units_cube_composite', 'DECIMAL');
dbms_output.put_line('Definitions:  ');
dbms_aw.printlog(defs);
END;
/

The program uses the DBMS_OUTPUT.PUT_LINE and DBMS_AW.PRINTLOG procedures to display the recommended object definitions.

Definitions:
DEFINE units_cube.cp COMPOSITE <product customer time>
DEFINE units_cube NUMBER VARIABLE <channel units_cube.cp<product customer time>>

In contrast to the Global schema, which is small and dense, the Sales cube in the Sales History sample schema is large and very sparse, and the Sparsity Advisor recommends 11 partitions. The following excerpt shows some additional OLAP DML definitions for defining a partition template and moving the TIME dimension members to the various partitions.

Definitions:
DEFINE sales_cube_composite_p1 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p2 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p3 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p4 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p5 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p6 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p7 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p8 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p9 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p10 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p11 COMPOSITE <channel product promotion customer>
DEFINE sales_cube_pt PARTITION TEMPLATE <time channel product promotion customer> -
  PARTITION BY LIST (time) -
   (PARTITION p1 VALUES () <sales_cube_composite_p1<>> -
    PARTITION p2 VALUES () <sales_cube_composite_p2<>> -
    PARTITION p3 VALUES () <sales_cube_composite_p3<>> -
    PARTITION p4 VALUES () <sales_cube_composite_p4<>> -
    PARTITION p5 VALUES () <sales_cube_composite_p5<>> -
    PARTITION p6 VALUES () <sales_cube_composite_p6<>> -
    PARTITION p7 VALUES () <sales_cube_composite_p7<>> -
    PARTITION p8 VALUES () <sales_cube_composite_p8<>> -
    PARTITION p9 VALUES () <sales_cube_composite_p9<>> -
    PARTITION p10 VALUES () <sales_cube_composite_p10<>> -
    PARTITION p11 VALUES () <time sales_cube_composite_p11<>>)
MAINTAIN sales_cube_pt MOVE TO PARTITION p1 -
   '06-JAN-98', '07-JAN-98', '14-JAN-98', '21-JAN-98', -
   '24-JAN-98', '28-JAN-98', '06-FEB-98', '07-FEB-98', -
   '08-FEB-98', '16-FEB-98', '21-FEB-98', '08-MAR-98', -
   '20-MAR-98', '03-JAN-98', '26-JAN-98', '27-JAN-98'
MAINTAIN sales_cube_pt MOVE TO PARTITION p1 -
   '31-JAN-98', '11-FEB-98', '12-FEB-98', '13-FEB-98', -
   '15-FEB-98', '17-FEB-98', '14-MAR-98', '18-MAR-98', -
   '26-MAR-98', '30-MAR-98', '05-JAN-98', '08-JAN-98', -
   '10-JAN-98', '16-JAN-98', '23-JAN-98', '01-FEB-98'
MAINTAIN sales_cube_pt MOVE TO PARTITION p1 -
   '14-FEB-98', '28-FEB-98', '05-MAR-98', '07-MAR-98', -
   '15-MAR-98', '19-MAR-98', '17-JAN-98', '18-JAN-98', -
   '22-JAN-98', '25-JAN-98', '03-FEB-98', '10-FEB-98', -
   '19-FEB-98', '22-FEB-98', '23-FEB-98', '26-FEB-98'
          .
          .
          .