A OLAP_TABLE SQL Functions

This appendix contains reference documentation for the following SQL functions that you can use to extract multidimensional data from an analytic workspace and present it in the two-dimensional format of a relational table:

Creating Relational Views Using OLAP_TABLE

OLAP_TABLE is a SQL function that returns a table of objects that can be joined to relational tables and views, and to other tables of objects populated by OLAP_TABLE. Within a SQL statement, you can specify an OLAP_TABLE function call wherever you would provide the name of a table or view

OLAP_TABLE uses a limit map to map dimensions and measures defined in an analytic workspace to columns in a logical table. The limit map combines with the WHERE clause of a SQL SELECT statement to generate a series of OLAP DML LIMIT commands that are executed in the analytic workspace.

OLAP_TABLE can use a limit map with a predefined logical table, or it can use the information in a limit map to dynamically generate a logical table at run time.

Required OLAP DML Objects

Several objects must be predefined within the analytic workspace to support the mapping of dimension hierarchies in the limit map:

a parent relation, which identifies the parent of each dimension member within a hierarchy. See "Parentrel Relation" for more information.
a hierarchy dimension, which lists the hierarchies of a dimension. See "Hierlist Dimension" for more information.
an inhierarchy variable or valueset, which specifies which dimension members belong to each level of a hierarchy. See "Inhier Valueset or Variable" for more information.
a grouping ID variable, which identifies the depth within a hierarchy of each dimension member. See "Gidrel Relation" for more information.
a family relation, which provides the full parentage of each dimension member in a hierarchy. See "Familyrel Relation" for more information.
a level dimension, which lists the levels of a dimension. See "Levellist Dimension" for more information.

Creating Logical Tables for Use by OLAP_TABLE

The logical table populated by OLAP_TABLE is actually a table type whose rows are user-defined object types, also known as Abstract Data Types or ADTs.

A user-defined object type is composed of attributes, which are equivalent to the columns of a table. The basic syntax for defining a row is as follows.

CREATE TYPE object_name AS OBJECT (
   attribute1      datatype,
   attribute2      datatype,
   attributen      datatype);

A table type is a collection of object types; this collection is equivalent to the rows of a table. The basic syntax for creating a table type is as follows.

CREATE TYPE table_name AS TABLE OF object_name;

OLAP_TABLE can use a limit map with a predefined logical table, or it can use the information in a limit map to dynamically generate a logical table at run time.

Using OLAP_TABLE With Predefined ADTs

You can predefine the table of objects or generate it dynamically. When you create the table type in advance, it is available in the database for use by any invocation of OLAP_TABLE. Queries that use predefined objects typically perform better than queries that dynamically generate the objects.

Example A-1, "Template for Creating a View Using Predefined ADTs" shows how to create a view of an analytic workspace using predefined ADTs.

Example A-1 Template for Creating a View Using Predefined ADTs

SET ECHO ON
SET SERVEROUT ON

DROP TYPE table_obj;
DROP TYPE row_obj;

CREATE TYPE row_obj AS OBJECT (
            column_first     datatype,
            column_next      datatype,
            column_n         datatype);
/   
CREATE TYPE table_obj AS TABLE OF row_obj;
/
CREATE OR REPLACE VIEW view_name AS
   SELECT column_first, column_next, column_n
      FROM TABLE(OLAP_TABLE(
         'analytic_workspace', 
         'table_obj', 
         'olap_command',
         'limit_map'));
/
COMMIT;
/ 
GRANT SELECT ON view_name TO PUBLIC;

Example A-2, "Sample View of the TIME Dimension Using Predefined ADTs" uses OLAP_TABLE with a predefined table type to create a relational view of the TIME dimension in an analytic workspace named MYAW in the MYAW_AW schema.

The first parameter in the OLAP_TABLE call is the name of the analytic workspace. The second is the name of the predefined table type. The fourth is the limit map that specifies how to map the workspace dimension to the columns of the predefined table type. The third parameter is not specified

Example A-2 Sample View of the TIME Dimension Using Predefined ADTs

CREATE TYPE time_cal_row AS OBJECT (
            time_id           varchar2(32),
            cal_short_label   varchar2(32),
            cal_end_date      date,
            cal_timespan      number(6));

CREATE TYPE time_cal_table AS TABLE OF time_cal_row;

CREATE OR REPLACE VIEW time_cal_view AS
   SELECT time_id, cal_short_label, cal_end_date, cal_timespan
      FROM TABLE(OLAP_TABLE(
         'myaw_aw.myaw duration session',
         'time_cal_table',
          '',
         'DIMENSION time_id from time with
            HIERARCHY time_parentrel
               INHIERARCHY time_inhier
            ATTRIBUTE cal_short_label from time_short_description
            ATTRIBUTE cal_end_date    from time_end_date
            ATTRIBUTE cal_timespan    from time_time_span'));

Using OLAP_TABLE With Automatic ADTs

If you do not supply the name of a table type as an argument, OLAP_TABLE uses information in the limit map to generate the logical table automatically. In this case, the table type is only available at run time within the context of the calling SQL SELECT statement.

Example A-3, "Template for Creating a View Using Automatic ADTs" shows how to create a view of an analytic workspace using automatic ADTs.

Example A-3 Template for Creating a View Using Automatic ADTs

SET ECHO ON
SET SERVEROUT ON

CREATE OR REPLACE VIEW view_name AS
   SELECT column_first, column_next, column_n
      FROM TABLE(OLAP_TABLE(
         'analytic_workspace',
         '', 
         'olap_command',
         'limit_map'));
/
COMMIT;
/ 
GRANT SELECT ON view_name TO PUBLIC;

Example A-4, "View of the TIME Dimension Using Automatic ADTs" creates the same view produced by Example A-2, "Sample View of the TIME Dimension Using Predefined ADTs", but it automatically generates the ADTs instead of using a predefined table type. It uses AS clauses in the limit map to specify the data types of the target columns.

Example A-4 View of the TIME Dimension Using Automatic ADTs

CREATE OR REPLACE VIEW time_cal_view AS
   SELECT time_id, cal_short_label, cal_end_date, cal_timespan
      FROM TABLE(OLAP_TABLE(
        'myaw_aw.myaw duration session',
        null,
        null,
        'DIMENSION time_id AS varchar2(32) FROM time WITH
           HIERARCHY time_parentrel
              INHIERARCHY time_inhier
           ATTRIBUTE cal_short_label AS VARCHAR2(32) from time_short_description
           ATTRIBUTE cal_end_date AS DATE            from time_end_date
           ATTRIBUTE cal_timespan AS NUMBER(6)       from time_time_span'));

When automatically generating ADTs, OLAP_TABLE uses default relational data types for the target columns unless you override them with AS clauses in the limit map. The default data type conversions used by OLAP_TABLE are described in Table A-1, "Default Data Type Conversions".

Adding Calculated Columns to the Relational View

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. You can add a calculated column to your relational view by specifying the OLAP_EXPRESSION function or a related Boolean, text, or date function in the select list of the query. When you specify one of these functions in the select list, OLAP_TABLE generates additional columns for the results of the function.

Before you use one of these expressions, you must specify a ROW2CELL clause in the limit map used by OLAP_TABLE to identifies the RAW column that OLAP_TABLE populates with information used by the OLAP single-row functions.

Using OLAP DML Expressions in SELECT FROM OLAP_TABLE Statements

You can use OLAP DML commands within your SELECT FROM OLAP_TABLE statements as described in:

"Using OLAP DML Expressions as Single-Row Functions"
"Modifying an Analytic Workspace From Within a SELECT FROM OLAP_TABLE Statement"

Using OLAP DML Expressions as Single-Row Functions

SQL functions are typically single-row functions return a single result row for every row of a queried table or view. Oracle supports several predefined SQL single-row functions, for example COS, LOG, and ROUND which return numeric data, and UPPER and LOWER which return character data.

Within the context of a SELECT FROM OLAP_TABLE statement, there are four SQL functions that you can use to wrap OLAP DML functions in such a way that the OLAP DML function acts as a single-row function. There are four functions that the results of expressions of different OLAP DML data types: OLAP_EXPRESSION for Oracle OLAP numeric expressions, OLAP_EXPRESSION_BOOL for Oracle OLAP Boolean expressions, OLAP_EXPRESSION_DATE for Oracle OLAP datetime expressions, and OLAP_EXPRESSION_TEXT for Oracle OLAP text expressions. One argument of each of these SQL functions is an OLAP DML function.

You can specify the OLAP_EXPRESSION function and its variants in the same way you specify other Oracle single-row functions, notably in the select list, WHERE, and ORDER BY clauses.

Modifying an Analytic Workspace From Within a SELECT FROM OLAP_TABLE Statement

There are several mechanisms for modifying an analytic workspace on the fly during the execution of OLAP_TABLE.

You can use the OLAP_CONDITION SQL function 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. Using the OLAP_CONDITION, you can 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.

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, and the olap_command parameter. OLAP_CONDITION has the advantage of portability, because it is not embedded within OLAP_TABLE, and versatility, because 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.

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.