Syntax: OLAP_TABLE Function

The syntax for the OLAP_TABLE function is shown below.

OLAP_TABLE (
   aw-attach             IN VARCHAR2,
   table-name            IN VARCHAR2,
   olap-command          IN VARCHAR2,
   limit-map             IN VARCHAR2);

The OLAP_TABLE function returns a table of objects.

Parameters

aw-attach

An optional text expression that specifies the name of the analytic workspace that contains the data you want to query and whether the analytic workspace is detached after this function executes. The format of aw-attach is shown below:

[aw-name DURATION QUERY|SESSION]

where:

aw-name is the name of the analytic workspace that you want attached as the active analytic workspace for Oracle OLAP. When there is one or more analytic workspaces attached, you do not have to specify a value for the aw_attach parameter. In this case, Oracle OLAP searches these analytic workspaces for the analytic workspace objects referenced in the olap-command and limit-map parameters. When you do not specify a value for the aw_attach parameter and an analytic workspace is not attached, an error is returned.

The DURATION phrase specifies when the analytic workspace specified by aw-name is detached. When the analytic workspace specified by aw-name is already attached, the DURATION phrase is ignored. You can specify either of the following keywords:

• QUERY specifies that the analytic workspace is detached after this function executes.
• SESSION specifies that the analytic workspace is detached when the connection to the database ends.

table-name

The name of the table of objects that this function returns.

olap-command

A text expression that specifies one or more OLAP DML commands, including an OLAP DML program. When you specify a value for this parameter, Oracle OLAP executes these OLAP commands before it selects the data using the mapping provided by the limit-map parameter. When using quotation marks in an OLAP command, follow these guidelines:

• Wherever you would normally use single quote (') in an OLAP command, use two single quotes (''). The SQL processor strips one of the single quotes before it sends the OLAP command to Oracle OLAP.
• Use a double quote (") to indicate the beginning of a comment.

The main reasons why you specify a value for the olap-command parameter is if you do not want to support the use of WHERE clauses. In this case, you specify a FETCH command (or an OLAP DML program that includes a FETCH command) for the value of the olap-command parameter instead of specifying a value for the limit-map parameter. For the syntax of the OLAP FETCH command, see the topic for that command in Oracle9i OLAP DML Reference help.

limit-map

A text expression that specifies how Oracle OLAP accesses analytic workspace data. The format of the limit-map parameter is shown below:

[MEASURE object-attribute FROM aw-measure] ...
DIMENSION [object-attribute FROM] aw-dim [WITH
   [HIERARCHY [object-attribute FROM] aw-parent-rel 
        [(aw-hierdim aw-hierdim-value  [[, aw-hierdim aw-hierdim-value] ...])] 
      [INHIERARCHY aw-inhier-object]
      [GID object-attribute FROM aw-gid-object] 
      [PARENTGID object-attribute FROM aw-gid-object]
      [LEVELREL object-attribute-list FROM aw-level-rel USING aw-level-dim]] 
   [[ATTRIBUTE object-attribute FROM aw-attribute] ...]
   [LOOP sparse-dimension]...
[PREDMLCMD olap-command]
[POSTDMLCMD olap-command]

MEASURE block -- Each MEASURE block maps the values of one analytic workspace variable (or a function that returns an analytic workspace variable) specified by aw-measure to an object attribute (table column) specified by object-attribute. All of the analytic workspace variables mapped using a single MEASURE block must have exactly the same analytic workspace dimensions.

DIMENSION block -- Each DIMENSION block maps the values of an analytic workspace dimension to one or more object attributes (table columns). When yo are mapping an analytic dimension, you specify that dimension using a single DIMENSION block. When you are mapping one or more analytic workspace variables using a MEASURE block, you also include one DIMENSION block for each dimension of the analytic workspace variables.

The syntax varies depending on whether or not the dimension is a hierarchical dimension.

When mapping a nonhierarchical dimension, use the object-attribute FROM clause to map the dimension values to a single object attribute (table column) specified by object-attribute.

When mapping a hierarchical dimension, use the WITH HIERARCHY clause to map the dimension values to several object attributes (table columns).

The [object-attribute FROM] aw-parent-rel [(aw-hierdim aw-hierdim-value [[, aw-hierdim aw-hierdim-value]...]) subclause maps the values of an hierarchical analytic workspace dimension to columns in the relational object.

object-attribute is the name of the object attribute (relational table column) to which you want the analytic workspace value mapped.

aw-parent-rel is a relation that is dimensioned by aw-dim and all of the aw-hierdim dimensions. The values of aw-parent-rel are the values of the aw-dim dimension. For each unique combination of dimension values, aw-parent-rel has the value of aw-dim that is the parent. When aw-parent-rel represents more than one hierarchy, you use the aw-hierdim parameter to qualify it to a single hierarchy.

aw-hierdim is the name of a dimension whose values are the names of hierarchies and aw-hierdim-value is the name of a particular hierarchy.

aw-inhier-object is the name of an analytic workspace variable or relation whose non-NA values indicate membership in the hierarchy being mapped. aw-inhier-object must have the same dimensions as aw-parent-rel and aw-gid-object (that is, it must be dimensioned by aw-dim and all of the aw-hierdim dimensions).

• The GID object-attribute FROM aw-gid-object subclause maps the grouping ids of the children of an hierarchical analytic workspace dimension to a column in the relational object.

  object-attribute is the name of the object attribute (relational table column) to which you want the analytic workspace value mapped.

  aw-gid-object is the name of an analytic workspace variable whose values are the grouping ids for the hierarchy or the name of an analytic workspace relation that returns a Number dimension whose values are the grouping ids for the hierarchy. aw-gid-object must have the same dimensions as aw-parent-rel and aw-inhier-object (that is, it must be dimensioned by aw-dim and all of the aw-hierdim dimensions). When the aw-gid-object does not exist in the analytic workspace, you can create it using the OLAP GROUPINGID command. (For the syntax of GROUPINGID, see the topic for the command in the Oracle9i OLAP DML Reference help.)

• The PARENTGID object-attribute FROM aw-gid-object subclause maps the grouping ids of the parent values of an hierarchical analytic workspace dimension to a column in the relational object.

  object-attribute is the name of the object attribute (relational table column) to which you want the analytic workspace value mapped.

  aw-gid-object is the name of an analytic workspace variable whose values are the grouping ids for the hierarchy or the name of an analytic workspace relation that returns a Number dimension whose values are the grouping ids for the hierarchy. aw-gid-object must have the same dimensions as aw-parent-rel and aw-inhier-object (that is, it must be dimensioned by aw-dim and all of the aw-hierdim dimensions). When the aw-gid-object does not exist in the analytic workspace, you can create it using the OLAP GROUPINGID command. (For the syntax of GROUPINGID, see the topic for the command in the Oracle9i OLAP DML Reference help.)

• The LEVELREL object-attribute-list FROM aw-level-rel USING aw-level-dim subclause specifies how to map the values of a single hierarchical dimension into several columns of the relational table. There is one column in the table for each level of the analytic workspace hierarchy.

  • object-attribute-list is a list of the names of object attributes that represent columns of the relational table to which you want the values mapped. Specify one attribute (column) name for each level in the hierarchy. Separate the names of the attributes (columns) using commas. The order must be the same as the order specified in aw-level-relation.

    aw-level-rel is the name of an analytic workspace relation that is dimensioned by aw-dim, aw-level-dim, and all aw-hierdim dimensions. The values of aw-level-rel are the numerical values that represent the levels of the dimension. These are the values that will be in the table columns specified by object-attribute-list. When aw-level-rel does not exist in the analytic workspace, you can create it using the OLAP HEIRHEIGHT command. In this case, you can specify if you want the attributes in descending (the default) or ascending order. (For the syntax of HIERHEIGHT, see the topic for the command in the Oracle9i OLAP DML Reference help.)

    aw-level-dim is the name of an integer dimension whose values are 1 through the highest-level of hierarchy in the analytic workspace.

  • The ATTRIBUTE object-attribute FROM aw-attribute subclause specifies how dimension table attributes are mapped.

    object-attribute is the name of the object attribute (relational table column) to which you want the analytic workspace value mapped.

    aw-attribute is the name of an analytic workspace relation, variable, or formula that is dimensioned only by the analytic workspace dimension specified by aw-dim.

  • The LOOP sparse-dimension subclause specifies how the OLAP_TABLE function loops over the values of aw-measure to retrieve its values. You can specify this subclause for only one dimension of a measure. Using this subclause causes the function to loop sparsely over aw-measure using the analytic workspace composite, conjoint or multidimensional dimension specified by sparse-dimension rather than looping over aw-measure densely using the object specified by aw-dim. Typically, the composite specified for sparse-dimension is one by which measure is dimensioned.

  PREDMLCMD and POSTMLCMD blocks --PREDMLCMD is an optional block that specifies an OLAP command to be executed before the data is fetched. POSTDMLCMD is an optional block that specifies an OLAP command to be executed after the data is fetched. In both blocks, the olap-command parameter specifies a text expression that specifies one or more OLAP commands, including an OLAP DML program. When using quotation marks in an OLAP command, follow these guidelines:

  • Wherever you would normally use single quote (') in an OLAP command, use two single quotes (''). The SQL processor strips one of the single quotes before it sends the OLAP command to Oracle OLAP.
  • Use a double quote (") to indicate the beginning of a comment.

  Keep the following points in mind when creating a limit map:

  • Many of the clauses in a limit map are optional. However, only those object attributes (relational table columns) that are mapped to analytic workspace objects can effectively be used in SQL SELECT statements and WHERE clauses. When an object attribute (column) is referenced in a SELECT statement or a WHERE clause but not in the limit map, that portion of the WHERE clause is ignored when retrieving the data.
  • A given analytic workspace object or object attribute (table column) can only be referenced once within the limit map.
  • Any multidimensional analytic workspace relation must be fully qualified down to a single hierarchy.

    Tip: To retrieve the Oracle OLAP session log from the last execution of the OLAP_TABLE function, use the DBMS_AW.GETLOG function. You can print the session log returned by this function by using the DBMS_AW.PRINTLOG procedure.