ADVISE_SPARSITY Procedure

The ADVISE_SPARSITY procedure analyzes a fact table for sparsity using information about its dimensions provided by the ADD_DIMENSION_SOURCE procedure. It populates a table created by the SPARSITY_ADVICE_TABLE procedure with the results of its analysis.

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"

Output Description

Table B-9 describes the information generated by ADVISE_SPARSITY.

Table B-9 Output Column Descriptions

Column	Data Type	NULL	Description
`CUBENAME`	`VARCHAR2(100)`	`NOT NULL`	The values of cubename in calls to `ADVISE_SPARSITY`, typically the name of the logical cube.
`FACT`	`VARCHAR2(4000)`	`NOT NULL`	The values of fact in calls to `ADVISE_SPARSITY`; the name of the fact table that provides the source data for one or more analytic workspace variables.
`DIMENSION`	`VARCHAR2(100)`	`NOT NULL`	The logical names of the cube's dimensions; the dimensions described in calls to `ADVISE_DIMENSIONALITY`.
`DIMCOLUMN`	`VARCHAR2(100)`		The names of dimension columns in fact (the source fact table), which relate to a dimension table.
`DIMSOURCE`	`VARCHAR2(4000)`		The names of the dimension tables.
`MEMBERCOUNT`	`NUMBER(12,0)`		The total number of dimension members at all levels.
`LEAFCOUNT`	`NUMBER(12,0)`		The number of dimension members at the leaf (or least aggregate) level.
`ADVICE`	`VARCHAR2(10)`	`NOT NULL`	The sparsity evaluation of the dimension: `DENSE`, `SPARSE`, or `COMPRESSED`.
`POSITION`	`NUMBER(4,0)`	`NOT NULL`	The recommended order of the dimensions.
`DENSITY`	`NUMBER(11,8)`		A number that provides an indication of sparsity relative to the other dimensions. The larger the number, the more sparse the dimension.
`PARTNUM`	`NUMBER(6,0)`	`NOT NULL`	The number of the partition described in the `PARTBY` and `PARTTOPS` columns. If partitioning is not recommended, then `1` is the maximum number of partitions.
`PARTBY`	`CLOB`		A list of all dimension members that should be stored in this partition. This list is truncated in SQL*Plus unless you significantly increase the size of the `LONG` setting.
`PARTTOPS`	`CLOB`		A list of top-level dimension members for this partition.

Syntax

ADVISE_SPARSITY ( 
          fact       IN      VARCHAR2,
          cubename   IN      VARCHAR2,
          dimsources IN      dbms_aw$_dimension_sources_t,
          advmode    IN      BINARY_INTEGER DEFAULT ADVICE_DEFAULT,
          partby     IN      BINARY_INTEGER DEFAULT PARTBY_DEFAULT,
          advtable   IN      VARCHAR2 DEFAULT NULL);

Parameters

Table B-10 ADVISE_SPARSITY Procedure Parameters

Parameter	Description
`fact`	The name of the source fact table.
`cubename`	A name for the facts being analyzed, such as the name of the logical cube in the analytic workspace.
`dimsources`	The name of the object type where the `ADD_DIMENSION_SOURCE` procedure has stored information about the cube's dimensions.
`advmode`	The level of advise you want to see. Select one of the following values: `DBMS_AW.ADVICE_DEFAULT` `DBMS_AW.ADVICE_FAST` `DBMS_AW.ADVICE_FULL`
`partby`	A keyword that controls partitioning. Use one of the following values: `DBMS_AW.PARTBY_DEFAULT` Allow the Sparsity Advisor to determine whether or not partitioning is appropriate. `DBMS_AW.PARTBY_NONE` Do not allow partitioning. `DBMS_AW.PARTBY_FORCE` Force partitioning.
`advtable`	The name of a table created by the procedure for storing the results of analysis.

Example

The following PL/SQL program fragment analyzes the sparsity characteristics of the UNITS_HISTORY_FACT table.

DECLARE
     dimsources dbms_aw$_dimension_sources_t;
BEGIN
-- Calls to ADD_DIMENSION_SOURCE for each dimension in the cube
                .
                .
                .
     dbms_aw.advise_sparsity('units_history_fact', 'units_cube', dimsources, 
          dbms_aw.advice_default);
 
END;
/

The following SELECT command displays the results of the analysis, which indicate that there is one denser dimension (CHANNEL) and three comparatively sparse dimensions (PRODUCT, CUSTOMER, and TIME).

SELECT fact, dimension, dimcolumn, membercount nmem, leafcount nleaf, advice, density
     FROM aw_sparsity_advice
     WHERE cubename='units_cube';
 
FACT                 DIMENSION    DIMCOLUMN     NMEM  NLEAF ADVICE          DENSITY
-------------------- ------------ ------------ ----- ------ ------------ ----------
units_history_fact   channel      channel_id       3      3 DENSE         .86545382
units_history_fact   product      item_id         36     36 SPARSE        .98706809
units_history_fact   customer     ship_to_id      61     62 SPARSE        .99257713
units_history_fact   time         month_id        96     80 SPARSE        .99415964