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 in conjunction with a predefined logical table, or it can use the information in a limit map to dynamically generate a logical table at runtime.

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 in conjunction with a predefined logical table, or it can use the information in a limit map to dynamically generate a logical table at runtime.

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 runtime 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 one of the related Boolean, text, or date functions 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.