../E17122-08.epub /> ../E17122-08.mobi />

SET_INCLUDED_MODEL

The SET_INCLUDED_MODEL program adds an INCLUDE model statement to a previously-defined cube dimension's model, or deletes an INCLUDE model statement from a previously-defined cube dimension's model. The changes made when this program executes are not transactional; an automatic COMMIT is executed as part of the program.

Syntax

CALL SET_INCLUDED_MODEL(logical_dim, model_name, custom_model)

Parameters

CALL

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

logical_dim

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

model_name

A text expression that is the name of the logical model that is associated with the cube dimension.

custom_model

A text expression that is the OLAP DML-created model for which you want to add an INCLUDE statement.

To remove an INCLUDE statement, specify NA.

Examples

Example 10-118 Creating Static and Dynamic Models for an OLAP Cube

Assume that you have created an OLAP cube dimension named my_time that dimensions an OLAP cube named my_cube. Within my_cube there are two measures: sales and moving_sales. Now you want to create static and dynamic models for my_cube.

  1. To create a static model, execute the following PL/SQL statement that executes a user-written OLAP DML program named setup_pre_model.

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

    As you can see from the following definition of the setup_pre_model program, the model is actually defined using the SET_INCLUDED_MODEL program and added using the ADD_CUBE_MODEL program, both of which are provided with the OLAP DML.

    DEFINE SETUP_PRE_MODEL PROGRAM
    PROGRAM
     
      VARIABLE _pre_model    text
      VARIABLE _aw_dim       text
     
      _pre_model = 'my_aw!my_pre_model'
      _aw_dim = OBJORG(DIM 'my_time')
     
      DEFINE &_pre_model model
      CONSIDER &_pre_model
      MODEL JOINLINES(JOINCHARS('dimension ' _aw_dim) -
                      JOINCHARS(_aw_dim '(\'L3_3\')=10') -
                      'end')
     
      CALL SET_INCLUDED_MODEL('my_time', 'pre_model', _pre_model)
      CALL ADD_CUBE_MODEL('my_cube', 'my_time', 'pre_model', YES)
    END
    
  2. Load the my_time dimension and load and solve my_cube by executing the following the PL/SQL statement.

    exec dbms_cube.build('MY_CUBE');
    
  3. You can now report on the initial values of sales and moving sales with the static model by issuing the following statement.

    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_1  14  14
    L2_1  12  12
    L2_2   2  14
    L3_1   1   1
    L3_2   1   2
    L3_3  10  11
    L3_4   1  11
    L3_5   1   2
     
    8 rows selected.
     
    
  4. To create a dynamic model where L1_2 = (L2_2' * 2) execute the following PL/SQL statement that calls a user-written OLAP DML program named SETUP_POST_MODEL.

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

    As you can see from the definition of the user-written setup_post_model program shown below, the model is actually defined using the SET_INCLUDED_MODEL program and added using the ADD_CUBE_MODEL program, both of which are provided with the OLAP DML.

    DEFINE SETUP_POST_MODEL PROGRAM
    PROGRAM
     
      VARIABLE _post_model   text
      VARIABLE _aw_dim       text
      VARIABLE _start_date   text
      VARIABLE _timespan     text
      VARIABLE _member       text
     
      _post_model = 'my_aw!my_post_model'
      _aw_dim = OBJORG(DIM 'my_time')
      _start_date = OBJORG(ATTRIBUTE 'my_time' 'start_date')
      _timespan = OBJORG(ATTRIBUTE 'my_time' 'timespan')
      _member = 'L1_0'
     
      DEFINE &_post_model model
      CONSIDER &_post_model
      MODEL JOINLINES( -
               JOINCHARS('dimension ' _aw_dim) -
               JOINCHARS(_aw_dim '(\'' _member '\')=' _aw_dim '(\'L2_2\')*2') -
               'end')
     
      CALL SET_INCLUDED_MODEL('my_time', 'post_model', _post_model)
      CALL ADD_CUBE_MODEL('my_cube', 'my_time', 'post_model', NO)
     
      " Add _member to the dimension
      CALL ADD_DIMENSION_MEMBER(_member, 'my_time', NA, 'L1', NA, NO)
      CALL UPDATE_ATTRIBUTE_VALEU(_member, 'my_time', 'start_date', -
                                  &_start_date(&_aw_dim 'L1_1')-365, NO)
      CALL UPDATE_ATTRIBUTE_VALUE(_member, 'my_time', 'timespan', -
                                  &_timespan(&_aw_dim 'L1_1'))
      UPDATE
      COMMIT
    END
     
    
  5. Execute the following statement to report on the new values of my_time, sales and moving_sales.

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