ADD_DIMENSION_SOURCE Procedure

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.

See also:

"Using the Sparsity Advisor"

Note:

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

Syntax

ADD_DIMENSION_SOURCE ( 
          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);

Parameters

Table B-2 ADD_DIMENSION_SOURCE Procedure Parameters

Parameter	Description
`dimname`	A name for the dimension. For clarity, use the logical name of the dimension in the analytic workspace.
`colname`	The name of the column in the fact table that maps to the dimension members for dimname.
`sources`	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.
`srcval`	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.
`dimtype`	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
`hiercols`	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.
`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 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.

Example

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.

DECLARE
     dimsources dbms_aw$_dimension_sources_t;
BEGIN
     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'));
                   .
                   .
                   .
END;
/