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_ATTRIBUTE_VALUE

The UPDATE_ATTRIBUTE_VALUE program modifies the attributes of an OLAP cube dimension member.

This program is especially useful when updating the following kinds of attributes:

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_ATTRIBUTE_MEMBER(dim_member_id, attribute_name, attribute_value -

[, auto_compile ])

Parameters

CALL

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

dim_member_id

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

logical_dim

A text expression that is the Oracle data dictionary name of the cube dimension of which dim_member_id is a member.

attribute_name

A text expression that is the Oracle data dictionary name of the attribute.

attribute_value

A text expression that specifies the value of attribute_name.

auto_compile

A Boolean expression that specifies whether or not you want related analytic workspace objects to be updated immediately.

The default value is TRUE in which case all of the changes to the analytic workspace that are needed to update the attribute of 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 attribute of the dimension member is always immediately updated -- even when an error is signaled during compilation.

Examples

Example 10-169 Updating the Attributes of an OLAP Cube Dimension

This example uses the UPDATE_ATTRIBUTE_VALUE program provided with the OLAP DML to update attributes of the my_time cube dimension for the L1-2 and the descendants of L1-2. The calls to the UPDATE_ATTRIBUTE_VALUE program provided with the OLAP DML are within a user-written OLAP DML program named ADD_L1_2_DATE.

  1. Issue the following PL/SQL statement to execute the user-written OLAP DML program named ADD_L1_2_DATE to set time attribute values for L1_2 and its descendants

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

    The definition of the user-written ADD_L1_2_DATE OLAP DML program is shown below. Notice the calls to the UPDATE_ATTRIBUTE_VALUE program provided with the OLAP DML.

    DEFINE ADD_L1_2_DATES PROGRAM
    PROGRAM
      VARIABLE _aw_dim       text
      VARIABLE _start_date   text
      VARIABLE _timespan     text
    
      _aw_dim = OBJORG(DIM 'my_time')
      _start_date = OBJORG(ATTRIBUTE 'my_time' 'start_date')
      _timespan = OBJORG(ATTRIBUTE 'MY_TIME' 'timespan')
    
      " Updates the time attribute of L1_2, L2_3, L3_6, but does not compile
      CALL UPDATE_ATTRIBUTE_VALUE('L1_2', 'my_time', 'start_date', -
                                  &_start_date(&_aw_dim 'L1_1')+365, NO)
      CALL UPDATE_ATTRIBUTE_VALUE('L1_2', 'my_time', 'timespan', -
                                  &_timespan(&_aw_dim 'L1_1'), NO)
    
      CALL UPDATE_ATTRIBUTE_VALUE('L2_3', 'my_time', 'start_date', -
                                  &_start_date(&_aw_dim 'L1_2'), NO)
      CALL UPDATE_ATTRIBUTE_VALUE('L2_3', 'my_time', 'timespan', -
                                  &_timespan(&_aw_dim 'L2_1'), NO)
    
      CALL UPDATE_ATTRIBUTE_VALUE('L3_6', 'my_time', 'start_date', -
                                  &_start_date(&_aw_dim 'L1_2'), NO)
      CALL UPDATE_ATTRIBUTE_VALUE('L3_6', 'my_time', 'timespan', -
                                  &_timespan(&_aw_dim 'L3_1'), NO)
      UPDATE
      COMMIT
    END
    
  2. Issue the following SQL statement to see what the attributes of the my_time cube dimension are.

    select dim_key||'  '||start_date||'  '||lpad(timespan, 3) 
        from my_time_view 
        order by dim_key asc;
    
    
    DIM_KEY||''||START_DATE||''||LPAD(TIMESPAN,3)
    ----------------------------------------------------------------------------
    L1_0  01-JAN-09  365
    L1_1  01-JAN-10  365
    L1_2  01-JAN-11  365
    L2_1  01-JAN-10   90
    L2_2  01-APR-10   61
    L2_3  01-JAN-11   90
    L3_1  01-JAN-10   31
    L3_2  01-FEB-10   28
    L3_3  01-MAR-10   31
    L3_4  01-APR-10   30
    L3_5  01-MAY-10   31
    L3_6  01-JAN-11   31
    
    12 rows selected.
    
  3. Issue the following SQL statement to report the values of the sales and moving_sales measures before the my_time cube dimension is compiled. Note that the calculation for the measures does not consider the new attributes of the my_time cube dimension.

    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.
    
  4. Issue the following SQL statement to compile the my_time cube dimension.

    exec dbms_cube.build('MY_TIME USING (COMPILE)');
    
  5. Issue the following SQL statement to report the values of the sales and moving_sales measures after the my_time cube dimension is compiled. Note that now the calculation for the measures considers the new attributes of the my_time cube dimension

    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
    L1_2   3  17
    L2_1   2   2
    L2_2  12  14
    L2_3   3  15
    L3_1   1   1
    L3_2   1   2
    L3_3  10  11
    L3_4   1  11
    L3_5   1   2
    L3_6   3   4
    
    12 rows selected.