11 OLAP_TABLE

OLAP_TABLE is a SQL function that extracts multidimensional data from an analytic workspace and presents it in the two-dimensional format of a relational table.

This chapter contains the following topics:

OLAP_TABLE Overview

OLAP_TABLE is the fundamental mechanism in the database for querying an analytic workspace. 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 returns a table of objects that can be joined to relational tables and views, and to other tables of objects populated by OLAP_TABLE.

OLAP_TABLE is used internally by the tools and APIs that access analytic workspaces. For example, Analytic Workspace Manager, the Active Catalog views, the OLAP Java APIs, and the DBMS_AW package all use OLAP_TABLE to obtain data and other information from analytic workspaces.

Note:

The OLAP tools and APIs that use OLAP_TABLE require database standard form, but OLAP_TABLE itself does not use standard form metadata.

Limit Maps

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.

Logical Tables

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;

See Also:

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 11-1 shows how to create a view of an analytic workspace using predefined ADTs.

Example 11-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 11-2 uses OLAP_TABLE with a predefined table type to create a relational view of the TIME dimension in the GLOBAL analytic workspace of the GLOBAL_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 forth 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 11-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(
         'global_aw.global 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 11-3 shows how to create a view of an analytic workspace using automatic ADTs.

Example 11-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 11-4 creates the same view produced by Example 11-2, 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 11-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(
        'global_aw.global 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 11-1.

Table 11-1 Default Data Type Conversions

Analytic Workspace Data Type SQL Data Type

ID

CHAR(8)

TEXT

VARCHAR2(4000)

TEXT(n)

VARCHAR2(n)

NTEXT

NVARCHAR2(4000)

NTEXT(n)

NVARCHAR2(n)

NUMBER

NUMBER

NUMBER(p,s)

NUMBER(p,s)

LONGINTEGER

NUMBER(19)

INTEGER

NUMBER(10)

SHORTINTEGER

NUMBER(5)

INTEGER WIDTH 1

NUMBER(3)

BOOLEAN

NUMBER(1)

DECIMAL

BINARY_DOUBLE

SHORTDECIMAL

BINARY_FLOAT

DATE

DATE

DAY, WEEK, MONTH, QUARTER, YEAR

DATE

DATETIME

TIMESTAMP

COMPOSITE

VARCHAR2(4000)

Other

VARCHAR2(4000)


Using a MODEL Clause

You can specify a MODEL clause in a SELECT FROM OLAP_TABLE statement to significantly improve query performance. The MODEL clause causes OLAP_TABLE to use an internal optimization.

You can use the following syntax to maximize the performance advantage of the MODEL clause with OLAP_TABLE. This is the recommended syntax for views of analytic workspaces.

SELECT column_first, column_next, column_n
     FROM TABLE(OLAP_TABLE(
          'analytic_workspace', 
          'table_obj', 
          'olap_command',
          'limit_map'))
     MODEL 
          DIMENSION BY(dimensions, gids)
          MEASURES(measures, attributes, rowtocell)
          RULES UPDATE SEQUENTIAL ORDER();

The MODEL clause must include DIMENSION BY and MEASURES subclauses that specify the columns in the table object. DIMENSION BY should list all the dimensions, as defined in the limit map. The list should include the GID columns for applications that use the OLAP API or BI Beans. MEASURES should list all the measures, attributes, ROW2CELL columns, and any other columns excluded from the DIMENSION BY list.

A MODEL clause lets you view the results of a query as a multidimensional array and specify calculations (rules) to perform on individual cells and ranges of cells within the array. You can specify calculation rules in the MODEL clause with OLAP_TABLE, but they will affect response time. If you wish to obtain the full benefit of the performance optimization, you should specify UPDATE and SEQUENTIAL ORDER in the RULES clause.

The UPDATE keyword indicates that you are not adding any custom members in the DIMENSION BY clause. If you do not include this keyword, the SQL WHERE clauses for measures will be discarded, which can significantly degrade performance.

The SEQUENTIAL ORDER keyword prevents Oracle from evaluating the rules to ascertain their dependencies.

See Also:

Oracle Database SQL Reference and Oracle Database Data Warehousing Guide for more information on SQL models.

OLAP_TABLE Examples

Because different applications have different requirements, several different formats are commonly used for fetching data into SQL from an analytic workspace. The examples in this chapter show how to create views using a variety of different formats.

See Also:

"OLAP_TABLE Syntax" for complete descriptions of the syntax used in these examples.

Although these examples are shown as views, the SELECT statements can be extracted from them and used directly to fetch data from an analytic workspace into an application.

Note:

The examples in this section use predefined ADTs. You could modify them to use automatic ADTs. See "Using OLAP_TABLE With Automatic ADTs".

The examples in this section do not include a MODEL clause. In general, you should specify a MODEL clause for performance reasons, as described in "Using a MODEL Clause".

Example: Creating Views of Embedded Total Dimensions

Example 11-5 shows the PL/SQL script used to create an embedded total view of the TIME dimension in the GLOBAL analytic workspace. This view is similar to the view in Example 11-2, but it specifies both a Calendar and a Fiscal hierarchy, and it includes HATTRIBUTE subclauses for hierarchy-specific End Date attributes.

The INHIERARCHY subclause identifies a valueset in the analytic workspace that lists all the dimension members in each hierarchy of a dimension. OLAP_TABLE saves the status of all dimensions in the limit map that have INHIERARCHY subclauses during the processing of the limit map. See "Order of Processing in OLAP_TABLE".

Example 11-5 Script for an Embedded Total Dimension View Using OLAP_TABLE

CREATE TYPE awtime_row AS OBJECT (
             awtime_id               VARCHAR2(12),
             awtime_short_label      VARCHAR2(12),
             awtime_cal_end_date     DATE,
             awtime_fis_end_date     DATE);
/
CREATE TYPE awtime_table AS TABLE OF awtime_row;
/
CREATE OR REPLACE VIEW awtime_view AS
   SELECT awtime_id, awtime_short_label, 
          awtime_cal_end_date, awtime_fis_end_date
      FROM TABLE(OLAP_TABLE(
         'global DURATION SESSION',
         'awtime_table', 
         '',
         'DIMENSION awtime_id FROM time WITH 
             HIERARCHY time_parentrel 
                (time_hierlist ''CALENDAR'')
                INHIERARCHY time_inhier
                HATTRIBUTE awtime_cal_end_date FROM time_cal_end_date
             HIERARCHY time_parentrel 
                (time_hierlist ''FISCAL'')
                INHIERARCHY time_inhier
                HATTRIBUTE awtime_fis_end_date FROM time_fis_end_date
          ATTRIBUTE awtime_short_label FROM time_short_description'));
/

The following SELECT statement queries the view created by the script:

SQL> SELECT * FROM awtime_view;

AWTIME_ID  AWTIME_SHORT_LABEL  AWTIME_CAL_END_DATE  AWTIME_FIS_END_DATE
---------  ------------------  -------------------  -------------------  
19         Jan-98              31-JAN-98            31-JAN-98
20         Feb-98              28-FEB-98            28-FEB-98
21         Mar-98              31-MAR-98            31-MAR-98
22         Apr-98              30-APR-98            30-APR-98
23         May-98              31-MAY-98            31-MAY-98
24         Jun-98              30-JUN-98            30-JUN-98
.                                                            
.                                                            
.                                                            
.                                                            
98         Q1-03               31-MAR-03            30-SEP-03
99         Q2-03               30-JUN-03            31-DEC-03
1          1998                31-DEC-98            30-JUN-99
102        2003                31-DEC-03            30-JUN-04
119        2004                31-DEC-04            30-JUN-05
2          1999                31-DEC-99            30-JUN-00
3          2000                31-DEC-00            30-JUN-01
4          2001                31-DEC-01            30-JUN-02
85         2002                31-DEC-02            30-JUN-03

Note:

Be sure to verify that you have created the views correctly by issuing SELECT statements against them. Only at that time will any errors in the call to OLAP_TABLE show up.

Example: Creating Views of Embedded Total Measures

In a star schema, a separate measure view is needed with columns that can be joined to each of the dimension views. Example 11-6 shows the PL/SQL script used to create a measure view with a column populated by a ROW2CELL clause to support custom measures.

See Also:

"Limit Map: ROW2CELL Clause" for information on ROW2CELL.

Example 11-6 Script for a Measure View Using OLAP_TABLE

CREATE TYPE awunits_row AS OBJECT (
             awtime                  VARCHAR2(12),
             awcustomer              VARCHAR2(30),
             awproduct               VARCHAR2(30),
             awchannel               VARCHAR2(30),
             awunits                 NUMBER(16),
             r2c                     RAW(32));
/
CREATE TYPE awunits_table AS TABLE OF awunits_row;
/
CREATE OR REPLACE VIEW awunits_view AS
   SELECT awunits,
          awtime, awcustomer, awproduct, awchannel, r2c 
      FROM TABLE(OLAP_TABLE(
         'global DURATION SESSION', 
         'awunits_table', 
         '',
         'MEASURE awunits FROM units_cube_units
          DIMENSION awtime FROM time WITH
             HIERARCHY time_parentrel
          DIMENSION awcustomer FROM customer WITH 
             HIERARCHY customer_parentrel
                       (customer_hierlist ''MARKET_ROLLUP'')
                INHIERARCHY customer_inhier
          DIMENSION awproduct FROM product WITH
             HIERARCHY product_parentrel
          DIMENSION channel WITH
             HIERARCHY channel_parentrel
             ATTRIBUTE  awchannel FROM channel_short_description
          ROW2CELL r2c'))
      WHERE awunits IS NOT NULL;

The following SELECT statement queries the view created by the script:

SQL> SELECT awchannel, awunits FROM awunits_view 
     WHERE  awproduct = '1'
     AND    awcustomer = '7'
     AND    awtime = '4';

AWCHANNEL            AWUNITS
---------            -------
All Channels         415392
Direct Sales         43783 
Catalog              315737
Internet             55872 

Example: Creating Views in Rollup Form

Rollup form uses a column for each hierarchy level to show the full parentage of each dimension member. The only difference between the syntax for rollup form and the syntax for embedded total form is the addition of a FAMILYREL clause in the definition of each dimension in the limit map.

See Also:

"Limit Map: DIMENSION Clause: WITH HIERARCHY Subclause" for information on FAMILYREL.

Example 11-7 shows the PL/SQL script used to create a rollup view of the PRODUCT dimension. It shows a dimension view to highlight the differences in the syntax of the limit map from the one used for the embedded total form, as shown in Example 11-5, "Script for an Embedded Total Dimension View Using OLAP_TABLE". Note that the target columns for these levels are listed in the FAMILYREL clause from most aggregate (CLASS) to least aggregate (ITEM), which is the order they are listed in the level list dimension. The family relation returns four columns. The most aggregate level (all products) is omitted from the view by mapping it to null.

Example 11-8 shows the alternate syntax for the FAMILYREL clause, which uses QDRs to identify exactly which columns will be mapped from the family relation.

The limit maps in Example 11-7 and Example 11-8 generate identical views.

Example 11-7 Script for a Rollup View of Products Using OLAP_TABLE

CREATE TYPE awproduct_row AS OBJECT (  
            class       VARCHAR2(50),
            family      VARCHAR2(50),
            item        VARCHAR2(50));
/
CREATE TYPE awproduct_table AS TABLE OF awproduct_row;
/
CREATE OR REPLACE VIEW awproduct_view AS
   SELECT class, family, item
      FROM TABLE(OLAP_TABLE(
         'global DURATION QUERY',
         'awproduct_table', 
         '',
         'DIMENSION product WITH 
            HIERARCHY product_parentrel
               FAMILYREL null, class, family, item
                  FROM product_familyrel USING product_levellist
                  LABEL product_short_description')); 

The following SELECT statement queries the view created by the script:

SQL> SELECT * FROM awproduct_view 
     ORDER BY class, family, item;

CLASS            FAMILY             ITEM
--------------   ----------------   ------------------------
Hardware          CD-ROM             Envoy External 6X CD-ROM  
Hardware          CD-ROM             Envoy External 8X CD-ROM  
Hardware          CD-ROM             External 6X CD-ROM  
Hardware          CD-ROM             External 8X CD-ROM  
Hardware          CD-ROM             Internal 6X CD-ROM  
Hardware          CD-ROM             Internal 8X CD-ROM  
Hardware          CD-ROM             
Hardware          Desktop PCs        Sentinel Financial  
Hardware          Desktop PCs        Sentinel Multimedia  
.
.
.
Software/Other   Operating Systems  Unix/Windows 1-user pack  
Software/Other   Operating Systems  Unix/Windows 5-user pack  
Software/Other   Operating Systems     
Software/Other

Example 11-8 Script Using QDRs in the FAMILYREL Clause of OLAP_TABLE

CREATE OR REPLACE TYPE awproduct_row AS OBJECT (
             class       VARCHAR2(50),
             family      VARCHAR2(50),
             item        VARCHAR2(50));
/
CREATE TYPE awproduct_table AS TABLE OF awproduct_row;
/
CREATE OR REPLACE VIEW awproduct_view AS
   SELECT class, family, item
      FROM TABLE(OLAP_TABLE(
         'global DURATION QUERY',
         'awproduct_table', 
         '',
         'DIMENSION product WITH 
            HIERARCHY product_parentrel
               FAMILYREL class, family, item FROM
                  product_familyrel(product_levellist ''CLASS''),
                  product_familyrel(product_levellist ''FAMILY''),
                  product_familyrel(product_levellist ''ITEM'')
                  LABEL product_short_description')); 

The following SELECT statement queries the view created by the script:

SQL> SELECT * FROM awproduct_view 
     ORDER BY by class, family, item;

CLASS            FAMILY             ITEM
--------------   ----------------   ------------------------  
Hardware          CD-ROM             Envoy External 6X CD-ROM  
Hardware          CD-ROM             Envoy External 8X CD-ROM  
Hardware          CD-ROM             External 6X CD-ROM  
Hardware          CD-ROM             External 8X CD-ROM  
Hardware          CD-ROM             Internal 6X CD-ROM  
Hardware          CD-ROM             Internal 8X CD-ROM  
Hardware          CD-ROM             
Hardware          Desktop PCs        Sentinel Financial  
Hardware          Desktop PCs        Sentinel Multimedia  
.                                                 
.                                                 
.                                                 
Software/Other   Operating Systems  Unix/Windows 1-user pack  
Software/Other   Operating Systems  Unix/Windows 5-user pack  
Software/Other   Operating Systems     
Software/Other

Using OLAP_TABLE with the FETCH Command

Oracle Express Server applications that are being revised for use with Oracle Database can use an OLAP DML FETCH command instead of a limit map to map workspace objects to relational columns.

The FETCH command is supplied in the third parameter of OLAP_TABLE, which specifies a single OLAP DML command. See "OLAP Command Parameter".

The script shown in Example 11-9 fetches data from two variables (SALES and COST) in the GLOBAL analytic workspace, and calculates two custom measures (COST_PRIOR_PERIOD and PROFIT). This example also shows the use of OLAP_TABLE directly by an application, without creating a view.

Important:

The FETCH statement in Example 11-9 is formatted with indentation for readability. In reality, the entire FETCH statement must be entered on one line, without line breaks or continuation characters.

Example 11-9 Script Using FETCH with OLAP_TABLE

CREATE TYPE measure_row AS OBJECT (
             time                       VARCHAR2(20),
             geography                  VARCHAR2(30),
             product                    VARCHAR2(30),
             channel                    VARCHAR2(30),
             sales                      NUMBER(16),
             cost                       NUMBER(16),
             cost_prior_period          NUMBER(16),
             profit                     NUMBER(16));
/   
CREATE TYPE measure_table AS TABLE OF measure_row;
/

The following SELECT statement queries the view created by the script:

SQL> SELECT time, geography, product, channel, 
     sales, cost, cost_prior_period, profit
     FROM TABLE(OLAP_TABLE(
          'xademo DURATION SESSION', 
          'measure_table', 
          'FETCH time, geography, product, channel, analytic_cube_f.sales, 
               analytic_cube_f.costs, 
               LAG(analytic_cube_f.costs, 1, time, LEVELREL time_member_levelrel),
               analytic_cube_f.sales - analytic_cube_f.costs',
             ''))
     WHERE channel =    'STANDARD_2.TOTALCHANNEL' AND
           product =    'L1.TOTALPROD' AND
           geography =  'L1.WORLD'
     ORDER BY time;

This SQL SELECT statement returns the following result set:

TIME      GEOGRAPHY PRODUCT      CHANNEL                 SALES       COST    COST_PRIOR_PERIOD    PROFIT
--------- --------- ------------ ----------------------- --------- --------- ------------------ --------
L1.1996   L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL 118247112   2490243                   115756869
L1.1997   L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL  46412113   1078031          2490243   45334082
L2.Q1.96  L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL  26084848    560379                    25524469
L2.Q1.97  L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL  26501765    615399           560379   25886367
L2.Q2.96  L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL  30468054    649004           615399   29819049
L2.Q2.97  L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL  19910347    462632           649004   19447715
L2.Q3.96  L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL  27781702    582693           462632   27199009
L2.Q4.96  L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL  33912508    698166           582693   33214342
L3.APR96  L1.WORLD  L1.TOTALPROD STANDARD_2.TOTALCHANNEL   8859808    188851                     8670957
                                              .
                                              .
                                              .
27 rows selected.

OLAP_TABLE Syntax

The OLAP_TABLE function returns multidimensional data in an analytic workspace as a logical table.

The order in which OLAP_TABLE processes information specified in its input parameters is described in "Order of Processing in OLAP_TABLE".

Syntax

OLAP_TABLE(
        analytic_workspace    IN   VARCHAR2,
        table_object          IN   VARCHAR2,
        olap_command          IN   VARCHAR2,
        limit_map1            IN   VARCHAR2,
        limit_map2            IN   VARCHAR2,
              .
              .
              .
        limit_map8            IN   VARCHAR2)
     RETURN TYPE;

Parameters

Table 11-2 OLAP_TABLE Function Parameters

Parameter Description

analytic_workspace

The name of the analytic workspace with the source data. This parameter also specifies how to attach the workspace to your session. See "Analytic Workspace Parameter".

table_object

The name of a table of objects that has been defined to structure the multidimensional data in tabular form. See "Table Object Parameter".

olap_command

An optional OLAP DML command. See "OLAP Command Parameter".

limit_map1...8

A keyword-based map that identifies the source objects in the analytic workspace and the target columns in a table of objects. You can define up to eight limit maps in order to circumvent the 4000 byte VARCHAR2 limit. The limit maps are concatenated. Be sure to include a space character if needed between the strings. See "Limit Map Parameter".


Returns

A table type whose rows are objects (ADTs) that identify the selected workspace data. See "Logical Tables".


Analytic Workspace Parameter

The first parameter of the OLAP_TABLE function provides the name of the analytic workspace where the source data is stored. It also specifies how long the analytic workspace will be attached to your OLAP session, which opens on your first call to OLAP_TABLE.

This parameter is always required by OLAP_TABLE.

The syntax of this parameter is:

'[owner.]aw_name DURATION QUERY | SESSION'

For example:

'olapuser.xademo DURATION SESSION'

owner

Specify owner whenever you are creating views that will be accessed by other users. Otherwise, you can omit the owner if you own the analytic workspace. It is required only when you are logged in under a different user name than the owner.

QUERY

Attaches an analytic workspace for the duration of a single query. Use QUERY only when you need to see updates to the analytic workspace made in other sessions.

SESSION

SESSION attaches an analytic workspace and keeps it attached at the end of the query. It provides better performance than QUERY because it keeps the OLAP session open. This performance difference is significant when the function is called without either a table_object parameter or AS clauses in the limit map; in this case, the OLAP_TABLE function must determine the appropriate table definition. See "Using OLAP_TABLE With Automatic ADTs".


Table Object Parameter

The second parameter identifies the name of a predefined table of objects, as described in "Using OLAP_TABLE With Predefined ADTs".

This parameter is optional. Omit this parameter if you are using automatic ADTs.

The syntax of this parameter is:

'table_name'

For example:

'product_dim_tbl'

When you specify the table_name parameter, the column data types for the returned data are predefined. In this case you cannot use AS clauses in the limit map.

When you omit the table_name parameter, the column data types for the returned data are generated at runtime. You can either provide the target data types with AS clauses in the limit map, or you can use the default data types described in Table 11-1, "Default Data Type Conversions". See "Using OLAP_TABLE With Automatic ADTs".


OLAP Command Parameter

The third parameter of the OLAP_TABLE function is a single OLAP DML command. If you want to execute more than one command, then you must create a program in your analytic workspace and call the program in this parameter. The power and flexibility of this parameter comes from its ability to process virtually any data manipulation commands available in the OLAP DML.

The order in which OLAP_TABLE processes the olap_command parameter is specified in "Order of Processing in OLAP_TABLE".

The syntax of this parameter is:

'olap_command'

There are two distinct ways of using the olap_command parameter:

  • To make changes in the workspace session immediately before the data is fetched (after all the limits have been applied)

  • To specify the source data directly instead of using a limit map

Both methods are described in the following sections.

Using olap_command with a Limit Map

You may want your application to modify the analytic workspace on the fly during the execution of OLAP_TABLE.

A common use of the olap_command parameter is to limit one or more dimensions. If you limit any of the dimensions that have INHIERARCHY clauses in the limit map, then the status of those dimensions is changed only during execution of this call to OLAP_TABLE; the limits do not affect the rest of your OLAP session. However, other commands (for example, commands that limit dimensions not referenced with INHIERARCHY clauses) can affect your session.

If you want a limit on a dimension in the limit map to stay in effect for the rest of your session, and not just during the command, specify it in the PREDMLCMD clause of the limit map or specify an OLAP_CONDITION function in the SQL SELECT statement.

The following is an example of a LIMIT command in the olap_command parameter.

'LIMIT product TO product_member_levelrel ''L2'''

Using FETCH in the olap_command Parameter

If you specify an OLAP DML FETCH command in the olap_command parameter, OLAP_TABLE uses it, instead of the instructions in the limit map, to fetch the source data for the table object. Because of this usage, the olap_command parameter is sometimes referred to as the data map. In general, you should not specify a limit map if you specify a FETCH command.

Note:

Normally, you should only use the FETCH command with OLAP_TABLE if you are upgrading an Express application that used the FETCH command for SNAPI. If you are upgrading, note that the full syntax is the same in Oracle as in Express 6.3. You can use the same FETCH commands in OLAP_TABLE that you used previously in SNAPI. The syntax of the FETCH command is documented in the Oracle OLAP DML Reference

FETCH specifies explicitly how analytic workspace data is mapped to a table object. The basic syntax is:

FETCH expression...

Enter one expression for each target column, listing the expressions in the same order they appear in the row definition. Separate expressions with spaces or commas.You must enter the entire statement on one line, without line breaks or continuation marks of any type.


Limit Map Parameter

The fourth (and last) parameter of the OLAP_TABLE function maps workspace objects to relational columns and identifies the role of each one. See "Limit Maps".

The limit map can also specify special instructions to be executed by OLAP_TABLE. For example: It can cause an OLAP DML command to execute before or after the limit map is processed; it can specify a ROW2CELL column for the OLAP_CONDITION and OLAP_EXPRESSION functions. (See Chapter 6 and Chapter 7.)

The order in which OLAP_TABLE processes information in the limit map is specified in "Order of Processing in OLAP_TABLE".

The limit map parameter is generally a required parameter. It can only be omitted when you specify a FETCH command in the olap_command parameter. See "OLAP Command Parameter".

You can supply the entire text of the limit map as a parameter to OLAP_TABLE, or you can store all or part of the limit map in a text variable in the analytic workspace and reference it using ampersand substitution. For example, the following OLAP_TABLE query uses a limit map stored in a variable called limitmapvar in the GLOBAL analytic workspace of the GLOBAL_AW schema.

SQL> SELECT * FROM TABLE(OLAP_TABLE(
     'global_aw.global DURATION SESSION',
     '',
     '',
     '&(global_aw.global!limitmapvar)');

If you supply the limit map as text within the call to OLAP_TABLE, then it has a maximum length of 4000 characters, which is imposed by PL/SQL. If you store the limit map in the analytic workspace, then the limit map has no maximum length.

The syntax of the limit map has numerous clauses, primarily for defining dimension hierarchies. Pay close attention to the presence or absence of commas, since syntax errors will prevent your limit map from being parsed. The syntax of the limit map is summarized in Example 11-10. Individual syntax components are described in the following sections.

Note:

Several objects must be predefined within the workspace to support the mapping of dimension hierarchies in the limit map. These objects are already defined in standard form workspaces. If the workspace does not conform to standard form, you may need to prepare the workspace by defining objects such as:
  • A parent relation, which identifies the parent of each dimension member within a hierarchy.

  • A hierarchy dimension, which lists the hierarchies of a dimension.

  • An inhierarchy variable or valueset, which specifies which dimension members belong to each level of a hierarchy.

  • A grouping ID variable, which identifies the depth within a hierarchy of each dimension member.

  • A family relation, which provides the full parentage of each dimension member in a hierarchy.

  • A level dimension, which lists the levels of a dimension.

Example 11-10 Syntax of an OLAP_TABLE Limit Map

'[MEASURE column [AS datatype] FROM {measure | AW_EXPR expression}]
           .
           .
 DIMENSION [column [AS datatype] FROM] dimension 
    [WITH
       [HIERARCHY [column [AS datatype] FROM] parent_relation
          [(hierarchy_dimension ''hierarchy_name'')]
          [INHIERARCHY inhierarchy_obj]
          [GID column [AS datatype] FROM gid_variable]
          [PARENTGID column [AS datatype] FROM gid_variable]
          [FAMILYREL column1 [AS datatype],  
                     column2 [AS datatype],
                      ... columnn [AS datatype]
                     FROM {expression1, expression2, ... expressionn | 
                           family_relation USING level_dimension }
                     [LABEL label_variable]]
          [HATTRIBUTE column [AS datatype] FROM hier_attribute_variable] 
           .
           .
       ]
       [ATTRIBUTE column [AS datatype] FROM attribute_variable]
           .
           .
    ]
 [ROW2CELL column] 
 [LOOP composite_dimension]
 [PREDMLCMD olap_command]
 [POSTDMLCMD olap_command]'

Where:

column is the name of a column in the target table.

datatype is the data type of column.

measure is a measure in the analytic workspace.

expression is a formula or qualified data reference for objects in the analytic workspace.

dimension is a dimension in the analytic workspace.

parent_relation is a self-relation in the analytic workspace that defines the hierarchies for dimension.

hierarchy_dimension is a dimension in the analytic workspace that contains the names of the hierarchies for dimension.

hierarchy_name is a member of hierarchy_dimension.

inhierarchy_obj is a variable or valueset in the analytic workspace that identifies which dimension members are in each level of the hierarchy.

gid_variable is a variable in the analytic workspace that contains the grouping ID of each dimension member in the hierarchy.

family_relation is a self-relation that provides the full parentage of each dimension member in the hierarchy.

level_dimension is a dimension in the analytic workspace that contains the names of the levels for the hierarchy.

label_variable is a variable in the analytic workspace that contains descriptive text values for dimension.

hier_attribute_variable is a variable in the analytic workspace that contains attribute values for hierarchy_name.

attribute_variable is a variable in the analytic workspace that contains attribute values for dimension.

composite_dimension is a composite dimension used in the definition of measure.

olap_command is an OLAP DML command.

Limit Map: MEASURE Clause

The MEASURE clause maps a variable, formula, or relation in the analytic workspace to a column in the target table.

MEASURE column [AS datatype] FROM {measure | AW_EXPR expression}

The AS subclause specifies the data type of the target column. You can specify an AS subclause when the table of objects has not been predefined. See "Using OLAP_TABLE With Automatic ADTs".

In the FROM subclause, you can either specify the name of a workspace measure or an OLAP expression that evaluates to a measure. For example:

AW_EXPR analytic_cube_sales - analytic_cube_cost
or
AW_EXPR LOGDIF(analytic_cube_sales, 1, time, LEVELREL time.lvlrel)

You can list any number of MEASURE clauses. This clause is optional when, for example, you wish to create a dimension view.

Limit Map: DIMENSION Clause

The DIMENSION clause identifies a dimension or conjoint in the analytic workspace that dimensions one or more measures or attributes, or provides the dimension members for one or more hierarchies in the limit map.

DIMENSION [column [AS datatype] FROM] dimension ....

The column subclause is optional when you do not want the dimension members themselves to be represented in the table. In this case, you should include a dimension attribute that can be used for data selection.

For a description of the AS subclause, see "Limit Map: MEASURE Clause".

Every limit map should have at least one DIMENSION clause. If the limit map contains MEASURE clauses, then it should also contain a single DIMENSION clause for each dimension of the measures, unless a dimension is being limited to a single value. If the measures are dimensioned by a composite, then you must identify each dimension in the composite with a DIMENSION clause. For the best performance when fetching a large result set, identify the composite in a LOOP clause. See "Limit Map: LOOP Clause".

A dimension can be named in only one DIMENSION clause. Subclauses of the DIMENSION clause identify the dimension hierarchies and attributes.

Limit Map: WITH Subclause for Dimension Hierarchies and Attributes

The WITH subclause introduces a HIERARCHY or ATTRIBUTE subclause. If you do not specify hierarchies or attributes, then omit the WITH keyword. If you specify both hierarchies and attributes, then precede them with a single WITH keyword. The syntax of the WITH clause is included in Example 11-10, "Syntax of an OLAP_TABLE Limit Map". It is shown without the rest of the limit map syntax in Example 11-11.

Example 11-11 WITH Subclause of Limit Map DIMENSION Clause

[WITH
   [HIERARCHY [column [AS datatype] FROM] parent_relation
       [(hierarchy_dimension ''hierarchy_name'')]
       [INHIERARCHY inhierarchy_obj]
       [GID column [AS datatype] FROM gid_variable]
       [PARENTGID column [AS datatype] FROM gid_variable]
       [FAMILYREL column1 [AS datatype], 
                  column2 [AS datatype],
                  ... columnn [AS datatype] 
                  FROM {expression1, expression2,... expressionn |
                        family_relation USING level_dimension}
                  [LABEL label_variable]]
          [HATTRIBUTE column [AS datatype] FROM hier_attribute_variable] 
   ...
   ]
   [ATTRIBUTE column [AS datatype] FROM attribute_variable]
   ...

Limit Map: DIMENSION Clause: WITH HIERARCHY Subclause

The HIERARCHY subclause identifies the parent self-relation in the analytic workspace that defines the hierarchies for the dimension.

HIERARCHY [column [AS datatype] FROM] parent_relation 
          [(hierarchy_dimension ''hierarchy_name'')]...

For a description of the column subclause, see "Limit Map: DIMENSION Clause".

If the dimension has more than one hierarchy, specify a hierarchy_dimension phrase. hierarchy_dimension identifies a dimension in the analytic workspace which holds the names of the hierarchies for this dimension. hierarchy_name is a member of hierarchy_dimension. The hierarchy dimension is limited to hierarchy_name for all workspace objects that are referenced in subsequent subclauses for this hierarchy (that is, INHIERARCHY, GID, PARENTGID, FAMILYREL, and HATTRIBUTE).

To include multiple hierarchies for the dimension, specify a HIERARCHY subclause for each one.

The HIERARCHY subclause is optional when the dimension does not have a hierarchy, or when the status of the dimension has been limited to a single level of the hierarchy.

The keywords in the HIERARCHY subclause are described as follows:

  • INHIERARCHY inhierarchy_obj

    The INHIERARCHY subclause identifies a boolean variable or a valueset in the analytic workspace that identifies the dimension members in each level of the hierarchy. It is required when there are members of the dimension that are omitted from the hierarchy. It is good practice to include an INHIERARCHY subclause, because OLAP_TABLE saves the status of all dimensions with INHIERARCHY subclauses during the processing of the limit map.

  • GID column [AS datatype] FROM gid_variable

    The GID subclause maps an integer variable in the analytic workspace, which contains the grouping ID for each dimension member, to a column in the target table. The grouping ID variable is populated by the OLAP DML GROUPINGID command.

    For a description of the AS subclause, see "Limit Map: MEASURE Clause".

    The GID subclause is required for Java applications that use the OLAP API.

  • PARENTGID column [AS datatype] FROM gid_variable

    The PARENTGID subclause calculates the grouping IDs for the parent relation using the GID variable in the analytic workspace. The parent GIDs are not stored in a workspace object. Instead, you specify the same GID variable for the PARENTGID clause that you used in the GID clause.

    For a description of the AS subclause, see "Limit Map: MEASURE Clause".

    The PARENTGID clause is recommended for Java applications that use the OLAP API.

  • FAMILYREL column1 [AS datatype], column2 [AS datatype], ... columnn [AS datatype] FROM {expression1, expression2, ... expressionn| family_relation USING level_dimension } [LABEL label_variable]

    The FAMILYREL subclause is used primarily to map a family relation in the analytic workspace to multiple columns in the target table. List the columns in the order of level_dimension (a dimension in the analytic workspace that holds the names of all the levels for the dimension). If you do not want a particular level included, then specify null for the target column. For a description of the AS subclause, see "Limit Map: MEASURE Clause".

    The tabular data resulting from a FAMILYREL clause is in rollup form, in which each level of the hierarchy is represented in a separate column, and the full parentage of each dimension member is identified within the row. See "Example: Creating Views in Rollup Form".

    The LABEL keyword identifies a text attribute that provides more meaningful names for the dimension members.

    You can use multiple FAMILYREL clauses for each hierarchy.

  • HATTRIBUTE column [AS datatype] FROM hier_attribute_variable

    The HATTRIBUTE subclause maps a hierarchy-specific attribute variable, dimensioned by hierarchy_dimension in the analytic workspace, to a column in the target table.

Limit Map: DIMENSION Clause: WITH ATTRIBUTE Subclause

The ATTRIBUTE subclause maps an attribute variable in the analytic workspace to a column in the target table.

ATTRIBUTE column [AS datatype] FROM attribute_variable

If attribute_variable has multiple dimensions, then values are mapped for all members of dimension, but only for the first member in the current status of additional dimensions. For example, if your attributes have a language dimension, then you must set the status of that dimension to a particular language. You can set the status of dimensions in a PREDMLCMD clause. See "Limit Map: PREDMLCMD Clause".

Limit Map: ROW2CELL Clause

The ROW2CELL clause creates a RAW column, between 16 and 32 characters wide, in the target table and populates it with information that is used by the OLAP_EXPRESSION functions. The OLAP_CONDITION function also uses the ROW2CELL column. Specify a ROW2CELL column when creating a view that will be used by these functions. See Chapter 6 and Chapter 7.

ROW2CELL column

Limit Map: LOOP Clause

The LOOP clause identifies a single named composite that dimensions one or more measures specified in the limit map. It improves performance when fetching a large result set; however, it can slow the retrieval of a small number of values.

LOOP sparse_dimension

Limit Map: PREDMLCMD Clause

The PREDMLCMD clause specifies an OLAP DML command that is executed before the data is fetched from the analytic workspace into the target table. It can be used, for example, to execute an OLAP model or forecast whose results will be fetched into the table. The results of the command are in effect during execution of the limit map, and continue into your session after execution of OLAP_TABLE is complete. See "Order of Processing in OLAP_TABLE".

PREDMLCMD olap_command

Limit Map: POSTDMLCMD Clause

The POSTDMLCMD clauses specifies an OLAP DML command that is executed after the data is fetched from the analytic workspace into the target table. It can be used, for example, to delete objects or data that were created by commands in the PREDMLCMD clause, or to restore the dimension status that was changed in a PREDMLCMD clause. See "Order of Processing in OLAP_TABLE".

POSTDMLCMD olap_command

Order of Processing in OLAP_TABLE

The following list identifies the order in which the OLAP_TABLE function processes instructions in the limit map that can change the status of dimensions in the analytic workspace.

  1. Execute any OLAP DML command specified in the PREDMLCMD parameter of the limit map.

  2. Save the current status of all dimensions in the limit map so that it can be restored later (PUSH status).

  3. Keep in status only those dimension members specified by INHIERARCHY subclauses in the limit map (LIMIT KEEP).

  4. Within the status set during step 3, keep only those dimension members that satisfy the WHERE clause of the SQL SELECT statement containing the OLAP_TABLE function (LIMIT KEEP).

  5. Execute any OLAP DML command specified in the olap_command parameter of the OLAP_TABLE function. (If olap_command includes a FETCH, fetch the data.)

  6. Fetch the data (unless a FETCH command was specified in the olap_command parameter).

  7. Restore the status of all dimensions in the limit map (POP status).

  8. Execute any OLAP DML command specified in the POSTDMLCMD parameter of the limit map.