The ADD_DIMENSION_SOURCE procedure populates a table type named DBMS_AW$_DIMENSION_SOURCES_T with information about the dimensions of a cube. This information is analyzed by the ADVISE_SPARSITY procedure.


You cannot execute this procedure from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL*Plus.


          dimname  IN      VARCHAR2,
          colname  IN      VARCHAR2,
          sources  IN OUT  dbms_aw$_dimension_sources_t,
          srcval   IN      VARCHAR2     DEFAULT NULL,
          dimtype  IN      NUMBER       DEFAULT NO_HIER,
          hiercols IN      columnlist_t DEFAULT NULL,
          partby   IN      NUMBER       DEFAULT PARTBY_DEFAULT);


Table B-2 ADD_DIMENSION_SOURCE Procedure Parameters

Parameter Description


A name for the dimension. For clarity, use the logical name of the dimension in the analytic workspace.


The name of the column in the fact table that maps to the dimension members for dimname.


The name of an object (such as a PL/SQL variable) defined with a data type of DBMS_AW$_DIMENSION_SOURCES_T, which will be used to store the information provided by the other parameters.


The name of a dimension table, or a SQL statement that returns the columns that define the dimension. If this parameter is omitted, then colname is used.


One of the following hierarchy types:

DBMS_AW.HIER_LEVELS Level-based hierarchy
DBMS_AW.HIER_PARENTCHILD Parent-child hierarchy
DBMS_AW.MEASURE Measure dimension
DBMS_AW.NO_HIER No hierarchy


The names of the columns that define a hierarchy.

For level-based hierarchies, list the base-level column first and the topmost-level column last. If the dimension has multiple hierarchies, choose the one you predict will be used the most frequently; only list the columns that define the levels of this one hierarchy.

For parent-child hierarchies, list the child column first, then the parent column.

For measure dimensions, list the columns in the fact table that will become dimension members.


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 for this dimension.

  • DBMS_AW.PARTBY_NONE Do not allow partitioning on this dimension.

  • DBMS_AW.PARTBY_FORCE Force partitioning on this dimension.

    Important: Do not force partitioning on more than one dimension.

  • An integer value for the number of partitions you want created for this dimension.


The following PL/SQL program fragment provides information about the TIME dimension for use by the Sparsity Advisor. The source data for the dimension is stored in a dimension table named TIME_DIM. Its primary key is named MONTH_ID, and the foreign key column in the fact table is also named MONTH_ID. The dimension hierarchy is level based as defined by the columns MONTH_ID, QUARTER_ID, and YEAR_ID.

The program declares a PL/SQL variable named DIMSOURCES with a table type of DBMS_AW$_DIMENSION_SOURCES_T to store the information.

     dimsources dbms_aw$_dimension_sources_t;
     dbms_aw.add_dimension_source('time', 'month_id', dimsources, 
          'time_dim', dbms_aw.hier_levels,
          dbms_aw$_columnlist_t('month_id', 'quarter_id', 'year_id'));