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

Part Number E17122-07
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

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.

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 are 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 becomes 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 multiple dimensions.

  • 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;
/