OLAP_EXPRESSION is a SQL function that dynamically executes a single-row numeric function in an analytic workspace and returns the results.
See Also:
Oracle OLAP Application Developer's Guide for information about using OLAP_EXPRESSION to create custom measures.
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_EXPRESSION acts as a numeric single-row function within the context of a SELECT FROM OLAP_TABLE statement. You can specify OLAP_EXPRESSION in the same way you specify other Oracle single-row functions, notably in the select list, WHERE , and ORDER BY clauses.
Single-row functions return a single result row for every row of a queried table or view. Oracle supports a number of predefined single-row functions, for example COS, LOG, and ROUND which return numeric data, and UPPER and LOWER which return character data. For more information on single-row functions, refer to the Oracle Database SQL Reference.
The OLAP single-row functions, OLAP_EXPRESSION and its variants for text, date, and boolean data, return the result of an OLAP DML expression that you specify. The OLAP DML supports a rich syntax for specifying computations ranging from simple arithmetic expressions to statistical, financial, and time-series operations. You can use OLAP_EXPRESSION to dynamically perform any valid numeric expression within an analytic workspace and retrieve its results. For more information on OLAP DML expressions, refer to the Oracle OLAP DML Reference.
OLAP_TABLE uses a limit map to present the multidimensional data from an analytic workspace in tabular form. The limit map specifies the columns of the logical table. When an OLAP_EXPRESSION function is specified in the select list of the query, OLAP_TABLE generates additional columns for the results of the function.
To use OLAP_EXPRESSION, you must specify a ROW2CELL clause in the limit map used by OLAP_TABLE. ROW2CELL identifies a RAW column that OLAP_TABLE populates with information used by the OLAP single-row functions.
The following script was used to create the view unit_cost_price_view, which is used in Example 7-1 and Example 7-2 to illustrate the use of OLAP_EXPRESSION. For information about creating views of analytic workspaces, see "OLAP_TABLE Overview".
Sample View: GLOBAL.UNIT_COST_PRICE_VIEW
-- Create the logical row
CREATE TYPE unit_cost_price_row AS OBJECT (
aw_unit_cost NUMBER,
aw_unit_price NUMBER,
aw_product VARCHAR2(50),
aw_time VARCHAR2(20),
r2c RAW(32));
/
-- Create the logical table
CREATE TYPE unit_cost_price_table AS TABLE OF unit_cost_price_row;
/
-- Create the view
CREATE OR REPLACE VIEW unit_cost_price_view AS
SELECT aw_unit_cost, aw_unit_price, aw_product, aw_time, 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
ATTRIBUTE aw_product FROM product_short_description
DIMENSION time WITH
HIERARCHY time_parentrel
INHIERARCHY time_inhier
ATTRIBUTE aw_time FROM time_short_description
ROW2CELL r2c'));
/
The following query shows some of the aggregate data in 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_TIME R2C
------------ ------------- ---------- ------- -----
211680.12 224713.71 Hardware 2000 00...
195591.60 207513.16 Hardware 2001 00...
184413.05 194773.78 Hardware 2002 00...
73457.31 77275.06 Hardware 2003 00...
Example 7-1 OLAP_EXPRESSION: Time Series Function in a WHERE Clause
This example uses the view described in "Sample View: GLOBAL.UNIT_COST_PRICE_VIEW".
The following SELECT statement calculates an expression with an alias of PERIODAGO, and limits the result set to calculated values greater than 50,000. The calculation uses the LAG function to return the value of the previous time period.
SQL> SELECT aw_time time, aw_unit_cost unit_cost,
OLAP_EXPRESSION(r2c,
'LAG(price_cube_unit_cost, 1, time,
LEVELREL time_levelrel)') periodago
FROM unit_cost_price_view
WHERE aw_product = 'Hardware'
AND olap_expression(r2c,
'LAG(price_cube_unit_cost, 1, time,
LEVELREL time_levelrel)') > 50000;
This SELECT statement produces these results.
TIME UNIT_COST PERIODAGO -------------------- ---------- ---------- 2003 73457.31 184413.05 2004 73457.31 1999 231095.4 162526.92 2000 211680.12 231095.4 2001 195591.6 211680.12 2002 184413.05 195591.6 Q2-99 57587.34 57856.76 Q3-99 59464.25 57587.34 Q4-99 56187.05 59464.25 Q1-00 53982.32 56187.05 Q2-00 53629.74 53982.32 Q3-00 53010.65 53629.74 Q4-00 51057.41 53010.65 Q1-01 49691.22 51057.41
Example 7-2 OLAP_EXPRESSION: Numeric Calculation in an ORDER BY CLause
This example uses the view described in "Sample View: GLOBAL.UNIT_COST_PRICE_VIEW".
This example subtracts costs from price, and gives this expression an alias of MARKUP. The rows are ordered by markup from highest to lowest.
SQL> SELECT aw_time time, aw_unit_cost unit_cost, aw_unit_price unit_price,
OLAP_EXPRESSION(r2c,
'PRICE_CUBE_UNIT_PRICE - PRICE_CUBE_UNIT_COST') markup
FROM unit_cost_price_view
WHERE aw_product = 'Hardware'
AND aw_time in ('1998', '1999', '2000', '2001')
ORDER BY OLAP_EXPRESSION(r2c,
'PRICE_CUBE_UNIT_PRICE - PRICE_CUBE_UNIT_COST') DESC;
This SELECT statement produces these results.
TIME UNIT_COST UNIT_PRICE MARKUP -------------------- ---------- ---------- --------- 1999 231095.40 245412.91 14317.51 2000 211680.12 224713.71 13033.59 2001 195591.60 207513.16 11921.56 1998 162526.92 173094.41 10567.49
The OLAP_EXPRESSION function dynamically executes an OLAP DML numeric expression within the context of an OLAP_TABLE function. In addition to returning a custom measure, OLAP_EXPRESSION can be used in the WHERE and ORDER BY clauses to modify the result set of the query of the analytic workspace.
OLAP_EXPRESSION(
r2c IN RAW(32),
numeric_expression IN VARCHAR2)
RETURN NUMBER;
Table 7-1 OLAP_EXPRESSION Function Parameters
| Parameter | Description |
|---|---|
|
|
The name of a column specified by a |
|
|
An OLAP DML expression that returns a numeric result. Search for "expressions" in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands". |
An evaluation of numeric_expression for each row of the table object returned by the OLAP_TABLE function.
OLAP_EXPRESSION returns numeric data. To return text, boolean, or date data, use the OLAP_EXPRESSION_TEXT, OLAP_EXPRESSION_BOOL, or OLAP_EXPRESSION_DATE functions.