Skip Headers
Oracle® OLAP Reference
10g Release 2 (10.2.0.3)

Part Number B14350-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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'));
/
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;

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')); 

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')); 

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;
/
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:

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.

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.

Instructions for creating these workspace objects are provided in the Oracle OLAP Application Developer's Guide.

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:

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.