Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
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"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;
Table B-4 ADVISE_DIMENSIONALITY Function Parameters
Parameter | Description |
---|---|
|
The same cubename value provided in the call to |
|
The name of an object (such as a PL/SQL variable) in which the definition of the composite dimension is stored. |
|
An object name for the composite. The default value is |
|
The number of a partition. By default, you see only the definition of the first partition. |
|
The name of a table created by the |
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>