Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
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.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);
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 |
|
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 hierarchyDBMS_AW.HIER_PARENTCHILD Parent-child hierarchyDBMS_AW.MEASURE Measure dimensionDBMS_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 becomes dimension members. |
|
A keyword that controls partitioning. Use one of the following values:
|
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; /