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 will provide 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).

SQL> 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