Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
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.
See also:
"Cube-Aware OLAP DML Statements"CALL SET_INCLUDED_MODEL(logical_dim, model_name, custom_model)
Since SET_INCLUDED_MODEL is an OLAP DML program with arguments, you invoke it using the OLAP DML CALL statement.
A text expression that is the Oracle data dictionary name of the cube dimension being modified.
A text expression that is the name of the logical model that is associated with the cube dimension.
A text expression that is the OLAP DML-created model for which you want to add an INCLUDE statement.
See:
CREATE_LOGICAL_MODELTo remove an INCLUDE statement, specify NA
.
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
.
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
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');
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.
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
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.