OLAP_CONDITION is a SQL function that dynamically executes an OLAP DML command during a query of an analytic workspace.
See Also:
Oracle OLAP DML Reference for information on analytic workspace objects and the syntax of individual OLAP DML commands.
This chapter includes the following topics:
OLAP_CONDITION modifies an analytic workspace within the context of a SELECT FROM OLAP_TABLE statement. You can specify OLAP_CONDITION like other Oracle functions, typically in the WHERE clause.
You can use OLAP_CONDITION to set an option, execute a LIMIT command, execute an OLAP model or forecast, or run a program. The changes made to the workspace can be transitory or they can persist in your session upon completion of the query.
Parameters of OLAP_CONDITION identify an invocation of OLAP_TABLE, specify an entry point in the limit map, and provide the OLAP DML command to be executed at that entry point.
The target limit map must include a ROW2CELL column. OLAP_CONDITION uses this column to identify an instance of OLAP_TABLE. Within that instance OLAP_CONDITION executes the OLAP DML command at one of three possible entry points. The entry point that you specify will determine whether the condition affects the data returned by the query and whether the condition remains in effect upon completion of the query.
OLAP_CONDITION can be triggered at any of the following points:
Before the status of the dimensions in the limit map is saved (which occurs before the result set is calculated).
After the result set has been calculated and before it is fetched. (Default)
After the result set has been fetched and the status of the dimensions in the limit map has been restored.
The entry points are described in detail in Table 6-2, "Entry Points for OLAP_CONDITION in the OLAP_TABLE Limit Map".
There are several mechanisms for modifying an analytic workspace on the fly during the execution of OLAP_TABLE. In addition to OLAP_CONDITION, you can use syntax supported by the OLAP_TABLE function itself: The PREDMLCMD and POSTDMLCMD clauses in the limit map, as well as the olap_command parameter. OLAP_CONDITION has the advantage of portability, since it is not embedded within OLAP_TABLE, and versatility, since it can be applied at different entry points.
OLAP_TABLE saves the status of dimensions in the limit map before executing the LIMIT commands that generate the result set for the query. After the data is fetched, OLAP_TABLE restores the status of the dimensions. You can specify a PREDMLCMD clause in the limit map to cause an OLAP DML command to execute before the dimension status is saved. Modifications resulting from the PREDMLCMD clause remain in the workspace after execution of OLAP_TABLE, unless reversed with a POSTDMLCMD clause. For more information, see "Limit Map Parameter".
The olap_command parameter of OLAP_TABLE specifies an OLAP DML command that executes immediately before the result set is fetched. In some circumstances, the olap_command parameter may contain an OLAP DML FETCH command, which itself manages the fetch. Limits set by the olap_command parameter are only in effect during the execution of OLAP_TABLE. For more information, see "OLAP Command Parameter".
Several sample queries using OLAP_CONDITION are shown in Example 6-2. These examples use the PRICE_CUBE in the GLOBAL analytic workspace. The cube has a time dimension, a product dimension, and measures for unit cost and unit price.
See Also:
"OLAP_CONDITION Syntax" for complete descriptions of the syntax used in these examples.The examples are based on a view called unit_cost_price_view. The SQL for creating this view is shown in Example 6-1. For information about creating views of analytic workspaces, see "OLAP_TABLE Overview".
Example 6-1 View of PRICE_CUBE in GLOBAL Analytic Workspace
-- Create the logical row
SQL> CREATE TYPE unit_cost_price_row AS OBJECT (
aw_unit_cost NUMBER,
aw_unit_price NUMBER,
aw_product VARCHAR2(50),
aw_product_gid NUMBER(10),
aw_time VARCHAR2(20),
aw_time_gid NUMBER(10),
r2c RAW(32));
-- Create the logical table
SQL> CREATE TYPE unit_cost_price_table AS TABLE OF unit_cost_price_row;
-- Create the view
SQL> CREATE OR REPLACE VIEW unit_cost_price_view AS
SELECT aw_unit_cost, aw_unit_price, aw_product, aw_product_gid,
aw_time, aw_time_gid, r2c
FROM TABLE(OLAP_TABLE(
'global DURATION SESSION',
'unit_cost_price_table',
'',
'MEASURE aw_unit_cost FROM price_cube_unit_cost
MEASURE aw_unit_price FROM price_cube_unit_price
DIMENSION product WITH
HIERARCHY product_parentrel
INHIERARCHY product_inhier
GID aw_product_gid FROM product_gid
ATTRIBUTE aw_product FROM product_short_description
DIMENSION time WITH
HIERARCHY time_parentrel
INHIERARCHY time_inhier
GID aw_time_gid FROM time_gid
ATTRIBUTE aw_time FROM time_short_description
ROW2CELL r2c'));
-- query the view
SQL> SELECT * FROM unit_cost_price_view
WHERE aw_product = 'Hardware'
AND aw_time in ('2000', '2001', '2002', '2003')
ORDER BY aw_time;
AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
------------ ------------- ---------- -------------- ------- ----------- -----
211680.12 224713.71 Hardware 3 2000 3 00...
195591.60 207513.16 Hardware 3 2001 3 00...
184413.05 194773.78 Hardware 3 2002 3 00...
73457.31 77275.06 Hardware 3 2003 3 00...
Example 6-2 Queries of UNIT_COST_PRICE_VIEW Using OLAP_CONDITION
The queries in this example use OLAP_CONDITION to modify the query of UNIT_COST_PRICE_VIEW in Example 6-1. In each query, OLAP_CONDITION uses a different entry point to limit the TIME dimension to the year 2000.
In the first query, OLAP_CONDIITON uses entry point 0. The limited data is returned by OLAP_TABLE, and the limit remains in effect in the analytic workspace.
SQL> SELECT * FROM unit_cost_price_view
WHERE aw_product = 'Hardware'
AND aw_time IN ('2000', '2001', '2002', '2003')
AND OLAP_CONDITION(R2C,
'LIMIT time TO time_short_description EQ ''2000''', 0)=1
ORDER BY aw_time;
AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
------------ ------------- ---------- -------------- ------- ----------- -----
211680.12 224713.71 Hardware 3 2000 3 00...
-- Check status in the analytic workspace
SQL> EXECUTE dbms_aw.execute('REPORT time_short_description');
TIME TIME_SHORT_DESCRIPTION
---- ----------------------
3 2000
-- Reset status
SQL> EXECUTE dbms_aw.execute('ALLSTAT');
In the next query, OLAP_CONDIITON uses entry point 1. The limited data is returned by OLAP_TABLE, but the limit does not remain in effect in the analytic workspace.
Note that the third parameter is not required in this case, since entry point 1 is the default.
SQL> SELECT * FROM unit_cost_price_view
WHERE aw_product = 'Hardware'
AND aw_time IN ('2000', '2001', '2002', '2003')
AND OLAP_CONDITION(R2C,
'LIMIT time TO time_short_description EQ ''2000''', 1)=1
ORDER BY aw_time;
AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
------------ ------------- ---------- -------------- ------- ----------- -----
211680.12 224713.71 Hardware 3 2000 3 00...
-- Check status in the analytic workspace
SQL> EXECUTE dbms_aw.execute('REPORT time_short_description');
TIME TIME_SHORT_DESCRIPTION
---- ----------------------
19 Jan-98
20 Feb-98
21 Mar-98
22 Apr-98
.
.
.
1 1998
2 1999
3 2000
4 2001
85 2002
102 2003
119 2004
-- Reset status
SQL> EXECUTE dbms_aw.execute('ALLSTAT');
In the final query, OLAP_CONDIITON uses entry point 2. The limit does not affect the data returned by OLAP_TABLE, but the limit remains in effect in the analytic workspace.
SQL> SELECT * FROM unit_cost_price_view
WHERE aw_product = 'Hardware'
AND aw_time IN ('2000', '2001', '2002', '2003')
AND OLAP_CONDITION(R2C,
'LIMIT time TO time_short_description EQ ''2000''', 2)=1
ORDER BY aw_time;
AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C
------------ ------------- ---------- -------------- ------- ----------- -----
211680.12 224713.71 Hardware 3 2000 3 00...
195591.60 207513.16 Hardware 3 2001 3 00...
184413.05 194773.78 Hardware 3 2002 3 00...
73457.31 77275.06 Hardware 3 2003 3 00...
-- Check status in the analytic workspace
SQL> EXECUTE dbms_aw.execute('REPORT time_short_description');
TIME TIME_SHORT_DESCRIPTION
---- ----------------------
3 2000
The OLAP_CONDITION function executes an OLAP DML command at one of three entry points in the limit map used in a call to OLAP_TABLE.
OLAP_CONDITION(
r2c IN RAW(32),
expression IN VARCHAR2,
event IN NUMBER DEFAULT 1);
RETURN NUMBER;
Table 6-1 OLAP_CONDITION Function Parameters
| Parameter | Description |
|---|---|
|
|
The name of a column specified by a The For information on creating a |
|
|
A single OLAP DML command to be executed within the context of the |
|
|
The event during |
The number 1 to indicate a successful invocation of OLAP_CONDITION.
The entry points for OLAP_CONDITION are described in Table 6-2. Refer to "Order of Processing in OLAP_TABLE" to determine where each entry point occurs.
Table 6-2 Entry Points for OLAP_CONDITION in the OLAP_TABLE Limit Map
| Entry Point | Description |
|---|---|
|
0 |
Execute the OLAP DML command after the The entry point is between steps 1 and 2 in "Order of Processing in OLAP_TABLE". If |
|
1 |
Execute the OLAP DML command after the conditions of the The entry point is between steps 4 and 5 in "Order of Processing in OLAP_TABLE". If an OLAP DML command (other than If |
|
2 |
Execute the OLAP DML command after the data is fetched and the status of dimensions in the limit map has been restored. The entry point is after step 8 in "Order of Processing in OLAP_TABLE". If |