7 OLAP_EXPRESSION

OLAP_EXPRESSION is a SQL function that dynamically executes a single-row numeric function in an analytic workspace and returns the results.

OLAP_EXPRESSION Overview

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

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_EXPRESSION and OLAP_TABLE

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.

OLAP_EXPRESSION Examples

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

OLAP_EXPRESSION Syntax

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.

Syntax

OLAP_EXPRESSION(
     r2c                 IN      RAW(32),
     numeric_expression  IN      VARCHAR2)
RETURN NUMBER;

Parameters

Table 7-1 OLAP_EXPRESSION Function Parameters

Parameter	Description
`r2c`	The name of a column specified by a `ROW2CELL` clause in the limit map. `OLAP_TABLE` populates this column with information used by the OLAP single-row functions, including `OLAP_EXPRESSION`. See "Limit Map Parameter".
`numeric_expression`	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".

Return Values

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.

Example

See "OLAP_EXPRESSION Examples".