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

REMOVE_DIMENSION_MEMBER

The REMOVE_DIMENSION_MEMBER program removes an OLAP cube dimension member from one or more hierarchies, or entirely removes an OLAP cube dimension member from a cube dimension.

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 REMOVE_DIMENSION_MEMBER(member_id, logical_dim, hier_list, [ auto_compile ])

Parameters

CALL

Since REMOVE_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 member that you want to remove from the cube dimension hierarchies.

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 from which you want to remove the cube dimension member. Specify one hierarchy name per line.

To entirely remove a member from the dimension, specify NA.

auto_compile

A Boolean expression that specifies whether or not you want related analytic workspace objects (for example, changes to the 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 remove 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 removed from the dimension -- even when an error is signaled during compilation.

Examples

Example 10-98 Removing OLAP Cube Dimension Members From a Hierarchy

  1. Execute the following SQL statement that reports on the values of the my_time cube dimension.

    select dim_key||'  '||level_name||'  '||parent 
       from my_time_lvl_hier_view 
       order by dim_key asc;
    

    The values this statement returns are shown below.

    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.
     
    
  2. Execute the following SQL statement that calls a user-written program named remove_12_1 OLAP DML program.

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

    The contents of the user-written remove_12_1 program are shown below.

    Note:

    The program uses:
    • The OBJORG function to specify the OLAP DML objects that physically implement the my_time cube dimension.

    • The REMOVE_DIMENSION_MEMBER program provided with the OLAP DML to remove L2_1 and its descendants from the my_time cube dimension.

    DEFINE REMOVE_L2_1 PROGRAM
    PROGRAM
     
      VARIABLE _aw_dim       text
      VARIABLE _parentrel    text
      VARIABLE _members      text
      VARIABLE _member       text
      VARIABLE _i            integer
     
      " Removes L2_1 and descendants
      _aw_dim = OBJORG(DIM 'my_time')
      _parentrel = OBJORG(PARENTREL 'my_time')
     
      LIMIT &_aw_dim TO 'L2_1'
      LIMIT &_aw_dim ADD DESCENDANTS USING &_parentrel
      _members = VALUES(&_aw_dim)
      _i = 1
      WHILE _i LE NUMLINES(_members)
      DO
        _member = EXTLINES(_members, _i, 1)
        _i = _i + 1
        CALL REMOVE_DIMENSION_MEMBER(_member, 'my_time', NA, NO)
      DOEND
     
      UPDATE
      COMMIT
    END
     
    
  3. Execute the following SQL statement (the same statement as in Step 1) that shows the values of the my_time cube dimension after removal.

    select dim_key||'  '||level_name||'  '||parent 
        from my_time_lvl_hier_view 
        order by dim_key asc;
    

    The values after removal are shown below.

    DIM_KEY||''||LEVEL_NAME||''||PARENT
    --------------------------------------------------------------------------------
    L1_0  L1
    L1_1  L1
    L1_2  L1
    L2_2  L2  L1_1
    L2_3  L2  L1_2
    L3_3  L3  L2_2
    L3_4  L3  L2_2
    L3_5  L3  L2_2
    L3_6  L3  L2_3
     
    9 rows selected.