11 OLAP_TABLE

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

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.

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.

'[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]'

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.

[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.