Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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.

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