ADVISE_DIMENSIONALITY Function

The ADVISE_DIMENSIONALITY function returns an OLAP DML definition of a composite dimension and the dimension order for variables in the cube, based on the sparsity recommendations generated by the ADVISE_SPARSITY procedure for a particular partition.

Note:

You cannot execute this function 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 ( 
          cubename   IN     VARCHAR2,
          sparsedfn  OUT    VARCHAR2
          sparsename IN     VARCHAR2 DEFAULT NULL,
          partnum    IN     NUMBER DEFAULT 1,
          advtable   IN     VARCHAR2 DEFAULT NULL)
     RETURN VARCHAR2;

Parameters

Table B-4 ADVISE_DIMENSIONALITY Function Parameters

Parameter	Description
`cubename`	The same cubename value provided in the call to `ADVISE_SPARSITY`.
`sparsedfn`	The name of an object (such as a PL/SQL variable) in which the definition of the composite dimension is stored.
`sparsename`	An object name for the composite. The default value is `cubename.cp`.
`partnum`	The number of a partition. By default, you see only the definition of the first partition.
`advtable`	The name of a table created by the `SPARSITY_ADVICE_TABLE` procedure for storing the results of analysis.

Example

The following PL/SQL program fragment defines two variables to store the recommendations returned by the ADVISE_DIMENSIONALITY function. SPARSEDIM stores the definition of the recommended composite, and DIMLIST stores the recommended dimension order of the cube.

DECLARE
     sparsedim VARCHAR2(500);
     dimlist VARCHAR2(500);
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
          .
          .
          .
dimlist := dbms_aw.advise_dimensionality('units_cube', sparsedim);
dbms_output.put_line('Sparse dimension:  ' || sparsedim);
dbms_output.put_line('Dimension list:  ' || dimlist);
END;
/

The program uses DBMS_OUTPUT.PUT_LINE to display the results of the analysis. The Sparsity Advisor recommends a composite dimension for the sparse dimensions, which are PRODUCT, CUSTOMER, and TIME. The recommended dimension order for UNITS_CUBE is CHANNEL followed by this composite.

Sparse dimension:  DEFINE units_cube.cp COMPOSITE <product customer time>
Dimension list:  channel units_cube.cp<product customer time>

The next example uses the Sparsity Advisor to evaluate the SALES table in the Sales History sample schema. A WHILE loop displays the recommendations for all partitions.

DECLARE
     dimlist VARCHAR2(500);
     sparsedim VARCHAR2(500);
     counter NUMBER(2) := 1;
     maxpart NUMBER(2);
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
          .
          .
          .
 
SELECT MAX(partnum) INTO maxpart FROM sh_sparsity_advice;
WHILE counter <= maxpart LOOP
dimlist := dbms_aw.advise_dimensionality('sales_cube', sparsedim, 
   'sales_cube_composite', counter, 'sh_sparsity_advice');
dbms_output.put_line('Dimension list:  ' || dimlist);
dbms_output.put_line('Sparse dimension:  ' || sparsedim);
counter := counter+1;
END LOOP;
dbms_aw.advise_dimensionality(defs,'sales_cube', 'sales_cube_composite',
   'DECIMAL', 'sh_sparsity_advice');
dbms_output.put_line('Definitions:  ');
dbms_aw.printlog(defs);
END;
/

The Sparsity Advisor recommends 11 partitions; the first ten use the same composite. The last partition uses a different composite. (The SH_SPARSITY_ADVICE table shows that TIME_ID is dense in the last partition, whereas it is very sparse in the other partitions.)

Dimension list:  sales_cube_composite<time channel product promotion customer>
Sparse dimension:  DEFINE sales_cube_composite COMPOSITE COMPRESSED <time channel product promotion customer>
Dimension list:   sales_cube_composite<time channel product promotion customer>
Sparse dimension:  DEFINE sales_cube_composite COMPOSITE COMPRESSED <time channel product promotion customer>
                   .
                   .
                   .
Dimension list:  time sales_cube_composite<channel product promotion customer>
Sparse dimension:  DEFINE sales_cube_composite COMPOSITE COMPRESSED <channel product promotion customer>