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_MEMBER

The ADD_DIMENSION_MEMBER program adds a member to an OLAP cube dimension. An OLAP cube dimension (sometimes also called an "OLAP logical dimension") is an OLAP dimension that is defined as a first-class data object in the Oracle data dictionary.

Note:

You cannot use this program to modify a cube dimension if a materialized view exists for that cube dimension or any cube in which it participates.

Syntax

CALL ADD_DIMENSION_MEMBER(member_id, logical_dim, hier_list, level_name, -

parent-member_id, [ auto_compile, [ merge ]])

Parameters

CALL

Since ADD_DIMENSION_MEMBER is an OLAP DML program with arguments, you invoke it using the OLAP DML CALL statement.

member_id

A text expression that is the value of the member that you want to add to the cube dimension.

logical_dim

A text expression that is the Oracle data dictionary name of the cube dimension being modified.

hier_list

A multi-line text expression consisting of the Oracle data dictionary names of all of the hierarchies that you want the dimension member added to. Specify one hierarchy name per line.

When you want the member to be added to all of the cube dimension hierarchies, specify NA.

level_name

For level hierarchies, a text value that specifies the hierarchy level at which the program will add the member to the cube dimension. For level hierarchies, the value you specify for level_name must be:

  • Compatible with the value you specify for parent_member_id

  • At the same hierarchy level as the existing cube dimension member since a cube dimension member cannot be in two different levels across hierarchies.

When the member participates in a value hierarchy (that is, when there are no levels), specify NA.

parent_member_id

A text expression that specifies the value of the member which is the parent of the member that you want to add to the cube dimension. When you want to add the member as the top member of a hierarchy, specify NA.

auto_compile

A Boolean expression that specifies whether or not you want related analytic workspace objects (for example, he parent relation) to be updated immediately. The default value is TRUE in which case all of the changes to the analytic workspace that are needed to add the cube dimension member happen now. Specify FALSE only when, for performance reasons, you want to make a bulk set of changes before issuing a compile. In this case, you need to explicitly compile the cube dimension before the values of the analytic workspace objects take effect as described in "Explicitly Compiling a Cube Dimension".

Note:

Regardless of the value that you specify for this argument, the new member is always immediately added to the dimension -- even when an error is signaled during compilation.
merge

A Boolean expression that specifies whether or not the program updates the dimension member if it exists or creates it if it does not. The default value is FALSE.

Usage Notes

Explicitly Compiling a Cube Dimension

When you specify FALSE for auto_compile, you need to explicitly compile the cube dimension before the values of the analytic workspace objects take effect. You perform the compilation with a DBMS_CUBE.BUILD package call. You can make this call within Analytic Workspace Manager by issuing the following statement where cube_dimension_name is the fully-qualified name of the cube dimension as defined in the Oracle data dictionary.

SQL PROCEDURE DBMS_CUBE.BUILD('cube_dimension_name USING (COMPILE)');

By default, issuing the above statement performs an UPDATE and COMMIT to the database. To prevent an UPDATE and COMMIT from occurring, add NO COMMIT to the statement as shown below.

SQL PROCEDURE DBMS_CUBE.BUILD'NO COMMIT cube_dimension_name USING (COMPILE)');

Guidelines for Specifying Values for the Names of Logical OLAP Objects

In an OLAP DML statement the text expression that you specify for an OLAP logical object (that is, a first-class OLAP object that is defined in the Oracle data dictionary such as a cube or cube dimension) must resolve to a value with the following form where LOGICAL_OBJECT_NAME is the Oracle data dictionary name of the OLAP object:

[SCHEMA_NAME.] LOGICAL_OBJECT_NAME

For example, valid expressions for referencing the XADEMO cube dimension in the XADEMO schema include:

'product'
'xademo.product'
'PRODUCT'
'XADEMO.product'
'\"XADEMO\".\"PRODUCT\"'

Note:

OLAP DML cube-aware programs interpret a text value that you specify for an OLAP logical object as upper case text unless you enclose the value in double quotes.

Transaction Scope of Cube-Aware OLAP DML Statements

Unless otherwise noted, the scope of a cube-aware OLAP DML statement, just like other OLAP DML statement, is a single session. To persist any changes, you must have attached the analytic workspace in Read/Write mode before you issue the statement and issue OLAP DML UPDATE and COMMIT statements after you execute the statement. If the analytic workspace is attached Read Only or if you do not issue UPDATE and COMMIT statements, then the changes exist only in the session while the analytic workspace is attached.

Invalid Level Names in Cube-Aware OLAP DML Statements

If you specify an invalid value for level_name or parent_member_id, a compile-time error is thrown. Also, an error will occur if you specify a hierarchy level for the member that is different from the level it participates in another hierarchy. In this case, a call error is thrown if auto_compile is FALSE.

Examples

Example 9-5 Adding Members to an OLAP Cube Dimension

This example adds members to an OLAP cube dimension named my_time.

  1. Execute the following PL/SQL statement to report on the values and hierarchy of the my_time cube dimension before any changes are made.

    select dim_key||'  '||level_name||'  '||parent
             from my_time_lvl_hier_view
             order by dim_key asc;
    
    DIM_KEY||''||LEVEL_NAME||''||PARENT
    -------------------------------------------------------------------------------
    L1_0  L1
    L1_1  L1
    L2_1  L2  L1_1
    L2_2  L2  L1_1
    L3_1  L3  L2_1
    L3_2  L3  L2_1
    L3_3  L3  L2_2
    L3_4  L3  L2_2
    L3_5  L3  L2_2
     
    9 rows selected.
    
  2. Execute the following PL/SQL statement to execute the user-written OLAP DML program named ADD_LQ_2.

    exec dbms_aw.execute('call my_util_aw!add_l1_2');
    

    The definition of the user-written OLAP DML program named ADD_LQ_2 is shown below. Note that it calls the ADD_DIMENSION_MEMBER program provided with the OLAP DML to add new members to the my_time cube dimension.

    DEFINE ADD_L1_2 PROGRAM
    PROGRAM
      VARIABLE _aw_dim       text
      VARIABLE _aw_sales     text
      VARIABLE _members      text
      VARIABLE _member       text
      VARIABLE _i            integer
    
      _aw_dim = OBJORG(DIM 'my_time')
      _aw_sales = OBJORG(MEASURE 'my_cube' 'sales')
    
      " Adds L1_2, L2_3, L3_6  CALL ADD_DIMENSION_MEMBER('L1_2', 'my_time', NA, 'L1', NA, NO)
      CALL ADD_DIMENSION_MEMBER('L2_3', 'my_time', NA, 'L2', 'L1_2', NO)
      CALL ADD_DIMENSION_MEMBER('L3_6', 'my_time', NA, 'L3', 'L2_3', NO)
    
      " Set my_time attribute (to meaningless values) so dimension can compile
      LIMIT &_aw_dim TO 'L1_2', 'L2_3', 'L3_6'
      _members = VALUES(&_aw_dim)
      _i = 1
      WHILE _i LE NUMLINES(_members)
      DO
        _member = EXTLINES(_members, _i, 1)
        _i = _i + 1
        CALL UPDATE_ATTRIBUTE_VALUE(_member, 'my_time', 'start_date', -
                                    to_date('01/01/08', 'MM/DD/YY'), NO)
        CALL UPDATE_ATTRIBUTE_VALUE(_member, 'my_time', 'timespan', 1, NO)
      DOEND
    
      &_aw_sales(&_aw_dim 'L3_6') = 3
    
      UPDATE
      COMMIT
    END
    
  3. Issue the following PL/SQL statement to compile the my_time cube dimension.

    exec dbms_cube.build('MY_TIME USING (COMPILE)');
    
  4. Report the values and hierarchy of the my_time cube dimension after compilation

    select dim_key||'  '||level_name||'  '||parent
        from my_time_lvl_hier_view
        order by dim_key asc;
    
    DIM_KEY||''||LEVEL_NAME||''||PARENT
    -------------------------------------------------------------------------------
    L1_0  L1
    L1_1  L1
    L1_2  L1
    L2_1  L2  L1_1
    L2_2  L2  L1_1
    L2_3  L2  L1_2
    L3_1  L3  L2_1
    L3_2  L3  L2_1
    L3_3  L3  L2_2
    L3_4  L3  L2_2
    L3_5  L3  L2_2
    L3_6  L3  L2_3
    
    12 rows selected.
    
  5. Execute the following PL/SQL statement to solve my_cube with the new hierarchy.

    exec dbms_cube.build(script => 'MY_CUBE USING (SOLVE)', add_dimensions => false);
    
  6. Issue the following PL/SQL statement to report on the values of the sales and moving_sales measure in my-cube. Note that the new my_time cube dimension values are shown.

    select my_time||'  '||lpad(sales, 2)||'  '||lpad(moving_sales, 2)
    from my_cube_view
    order by my_time asc;
    
    MY_TIME||''||LPAD(SALES,2)||''||LPAD(MOVING_SALES,2)
    --------------------------------------------------------------------
    L1_0  24  27
    L1_1  14  38
    L1_2   3   3
    L2_1   2   5
    L2_2  12  14
    L2_3   3   3
    L3_1   1   4
    L3_2   1   2
    L3_3  10  11
    L3_4   1  11
    L3_5   1   2
    L3_6   3   3
     
    12 rows selected.