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

Part Number E17122-05
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
View PDF

UPDATE_DIMENSION_MEMBER

The UPDATE_DIMENSION_MEMBER program sets the level and the parent of an OLAP cube dimension member in one or more hierarchies.

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 UPDATE_DIMENSION_MEMBER(member_id, logical_dim, hier_list, level_name, -

parent-member_id [, auto_compile ])

Parameters

CALL

Since UPDATE_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 for which you want to set the level and parent information.

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 to set the level and parent for the dimension member. Specify one hierarchy name per line.

When you want to specify information for the member in all hierarchies of the cube dimension, specify NA.

level_name

For level hierarchies, a text value that specifies the hierarchy level at which the program will set the member of 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 dimension member that you want to add to the hierarchy of the cube dimension. Specify NA when you want to add the dimension member as the top member.

auto_compile

A Boolean expression that specifies whether or not you want your changes to take effect 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 (for example, before attribute values are spread down or for time dimension valuesets to be updated), as described in "Explicitly Compiling a Cube Dimension".

Examples

Example 10-170 Changing the Hierarchy of an OLAP Cube Dimension

Assume that you have a hierarchical cube dimension named my_time and you want to change its hierarchy by moving the value L3_2 from the top-level to being a child of L2_2.

  1. Execute the following SQL statement to report on the values of the my_time cube dimension before the move.

    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_1
    L3_4  L3  L2_2
    L3_5  L3  L2_2
     
    9 rows selected.
     
    
  2. Issue the following PL/SQL statement that calls the user-written MOVE_L3_3 program to make the move.

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

    As you can see from the following definition of the MOVE_L3_3 program, the actual move is performed by a CALL to the PDATE_DIMENSION_MEMBER program provided with the OLAP DML.

    DEFINE MOVE_L3_3 PROGRAM
    PROGRAM
     
      " Change the parent of L3_2 to L2_2
      CALL UPDATE_DIMENSION_MEMBER('L3_3', 'my_time', NA, 'L3', 'L2_2')
      UPDATE
      C0MMIT
    END
    
  3. Issue the following statement to report the values of the my_time dimension after the move

    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.
     
    
  4. Issue the following statement to solve my_cube with new hierarchy of my_time.

    exec dbms_cube.build(script => 'MY_CUBE USING (SOLVE)', add_dimensions => false);
     
    
  5. Issue the following statement to report on the values of sales and moving_sales (the measures in my_cube) now that my_time has a new hierarchy.

    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  24
    L1_1  14  38
    L2_1   2   2
    L2_2  12  14
    L3_1   1   1
    L3_2   1   2
    L3_3  10  11
    L3_4   1  11
    L3_5   1   2
     
    9 rows selected.