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

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

Since 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.