OLAP_TABLE Reference

The OLAP_TABLE function extracts multidimensional data from an analytic workspace and presents it in the two-dimensional format of a relational table. It can be used wherever you would use the name of a table or view. The analytic workspace data can be stored or calculated on the fly from stored data. The result set is a table of objects that can be joined to relational tables and views, or to other tables of objects populated by OLAP_TABLE.

Syntax

OLAP_TABLE(
      aw_attach          IN VARCHAR2,
      table_name         IN VARCHAR2,
      olap_command       IN VARCHAR2,

limit_map IN VARCHAR2);

The OLAP_TABLE function returns the table of objects identified by table_name, which has been populated according to the rules defined in limit_map.

Parameters

Table 12-1 OLAP_TABLE Function Parameters

Parameter	Description
aw_attach	The name of the analytic workspace with the source data
table_name	The name of the table that has been defined to structure the multidimensional data in tabular form
olap_command	An OLAP DML command that will be executed before the data is fetched
limit_map	A keyword-based map that identifies the source objects in aw_attach and the target columns in table_name.

AW_ATTACH Parameter

The first parameter of the OLAP_TABLE function provides the name of the analytic workspace where the source data is stored and specifies how long the analytic workspace will be attached to your OLAP session, which opens for your first call to OLAP_TABLE. You can detach the analytic workspace either at the end of the query or at the end of the session. This is the full syntax of this parameter:

'[owner.]aw_name DURATION QUERY | SESSION'

For example:

'sys.xademo DURATION QUERY'

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.

If you specify SESSION, then you can use an empty string for this parameter in subsequent calls to OLAP_TABLE, because the analytic workspace is already attached. If you repeat the connection string unnecessarily, it is simply ignored.

SESSION provides slightly better performance than QUERY, because the analytic workspace is attached only once instead of multiple times in the session. However, you will not see modifications made by other users in the meantime.

Table_Name Parameter

The second parameter identifies the name of the table of objects that you defined, as shown in "Creating a Table". The syntax of this parameter is:

'table_name'

For example:

'product_table'

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.

A common use of this parameter is to limit one or more dimensions. If you limit one of the dimensions specified in a DIMENSION clause, then the status of that dimension is changed only during execution of this call to OLAP_TABLE; it does not affect the rest of your OLAP session. However, other commands can affect your session.

The syntax of this parameter is:

'olap_command'

For example:

'LIMIT product TO product_member_levelrel ''L2'''

Another use is to execute the OLAP FETCH command in this parameter and omit the limit map.

The power and flexibility of this parameter comes from its ability to process virtually any data manipulation commands available in the OLAP DML.

Limit_Map Parameter

The fourth (and last) parameter of the OLAP_TABLE function maps workspace objects to columns in the table and identifies the role of each one. It is called a limit map because it combines with the WHERE clause of a SQL SELECT statement to issue a series of LIMIT commands to the analytic workspace. The contents of the limit map populate the table specified in the table_name parameter.

All or part of the limit map can be stored in a text variable in the analytic workspace. To insert the variable in the limit map, precede the name of the variable with an ampersand (&). This practice is called ampersand substitution in the OLAP DML.

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.

'[MEASURE column FROM {measure | AW_EXPR expression}]
           .
           .
           .
 DIMENSION [column FROM] dimension 
    [WITH
       [HIERARCHY [column FROM] hierarchy_relation[(hierarchy_dimension 'hierarchy')]
          [INHIERARCHY inhierarchy_variable]
          [GID column FROM gid_variable]
          [PARENTGID column FROM gid_variable]
          [FAMILYREL col1, col2, coln FROM
             {expression1, expression2, expressionn | 
              family_relation USING level_dimension }
             [LABEL label_variable]]
           .
           .
           .
       ]
       [ATTRIBUTE column 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.

measure is a business measure that is stored in the analytic workspace.

dimension is a dimension in the analytic workspace

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

hierarchy_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 is a member of hierarchy_dimension.

inhierarchy_variable is a Boolean variable in the analytic workspace that identifies whether a dimension member is in hierarchy.

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

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

sparse_dimension is the name of a composite dimension used in the definition of measure.

olap_command is an OLAP DML command.

MEASURE column FROM {measure | AW_EXPR expression}

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

Alternatively, the AW_EXPR keyword can map a calculation performed by the OLAP engine on one or more of these objects to a column. For example, you could specify calculations such as these:

analytic_cube_sales - analytic_cube_cost

or

LAGDIF(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.

Refer to "Measures" for additional information about measures in an analytic workspace.

DIMENSION [column FROM] dimension...

The DIMENSION clause identifies a dimension or conjoint in the analytic workspace that dimensions one or more measures, attributes, or hierarchies in the limit map. Refer to "Dimensions" for additional information about dimensions in an analytic workspace.

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.

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.

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

WITH...

The WITH clause introduces a HIERARCHY or ATTRIBUTE subclause. If you omit these subclauses from the limit map, then omit the WITH clause also. However, if you include one or both of these subclauses, then precede them with a single WITH clause.

HIERARCHY [column FROM] hierarchy_relation[(hierarchy_dimension 'hierarchy')]...

The HIERARCHY subclause identifies the parent self-relation in the analytic workspace that defines the hierarchies for dimension. Refer to "Hierarchies" for additional information on dimension hierarchies in an analytic workspace.

If hierarchy_dimension has more than one member, then you can specify the one that you want with a (hierarchy_dimension 'hierarchy') phrase. To include multiple hierarchies, specify a HIERARCHY subclause for each one. The hierarchy_dimension is limited to hierarchy for all workspace objects that are referenced in subsequent subclauses (that is, INHIERARCHY, GID, PARENTGID, and FAMILYREL).

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

INHIERARCHY inhierarchy_variable

The INHIERARCHY subclause identifies a boolean variable in the analytic workspace that identifies whether a dimension member is in hierarchy. It is required only when there are members of the dimension that are omitted from the hierarchy, which is typical when a dimension has multiple hierarchies. Refer to "In-Hierarchy Variables" for additional information about in-hierarchy variables.

GID column 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. It is required for Java applications that use the OLAP API. Refer to "Grouping IDs" for additional information about GIDs.

PARENTGID column 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.

The PARENTGID clause is recommended for Java applications that use the OLAP API. Refer to "Grouping IDs" for additional information about GIDs.

FAMILYREL col1, col2, coln 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. If you do not want a particular level included, then specify null for the target column. The resulting view 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. Refer to "Family Relations" for more information about family relations.

The FAMILYREL subclause can also be used to map a list of qualified data references (QDRs) to multiple columns. In this usage, the first QDR maps to the first column, the second QDR maps to the second column, and so forth.

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.

ATTRIBUTE column FROM attribute_variable

The ATTRIBUTE clause maps a variable in the analytic workspace to a column in the target table. 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.

ROW2CELL column

The ROW2CELL clause populates a RAW(32) column with information needed by the single-row functions in the DBMS_AW package. Use this clause when creating a view that will be used by these functions.

LOOP sparse_dimension

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.

PREDMLCMD olap_command

The PREDMLCMD 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 a model or forecast whose results will be fetched into the table.

POSTDMLCMD olap_command

The POSTDMLCMD 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.