26 OLAP_TABLE

The OLAP_TABLE function extracts multidimensional data from an analytic workspace and presents it in the two-dimensional format of a relational table. It provides access to analytic workspace data from SQL.

This chapter contains the following topics:

OLAP_TABLE Syntax
OLAP_TABLE Examples

See Also:

OLAP_TABLE Syntax

The OLAP_TABLE function returns the table of objects which has been populated according to the mapping rules defined in limit_map.

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

Syntax

OLAP_TABLE(
      aw_attach          IN   VARCHAR2,
      table_name         IN   VARCHAR2,
      datamap            IN   VARCHAR2,
      limit_map          IN   VARCHAR2   );

Parameters

Table 26-1 OLAP_TABLE Function Parameters

Parameter	Description
aw_attach	The name of the analytic workspace with the source data. See "AW Attach Parameter".
table_name	The name of a table of objects that has been defined to structure the multidimensional data in tabular form. See "Table Name Parameter".
datamap	An optional OLAP DML command that controls data mapping as an alternative to the limit map. See "Datamap Parameter".
limit_map	A keyword-based map that identifies the source objects in aw_attach and the target columns in table_name. See "Limit Map Parameter".

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 on your first call to OLAP_TABLE. This is the full syntax of this parameter:

'[owner.]aw_name DURATION QUERY | SESSION'

For example:

'sys.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_name parameter or AS clauses in the limit map; in this case, the OLAP_TABLE function must determine the appropriate table definition.

Table Name Parameter

The second parameter identifies the name of a table of objects. The syntax of this parameter is:

'table_name'

For example:

'product_dim_tbl'

If you use table_name, then you cannot use AS clauses in the limit map.

If you omit the table_name parameter, then OLAP_TABLE converts the analytic workspace data types to SQL data types, as shown in Table 26-2. You can override these defaults by using AS clauses in the limit map

Table 26-2 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)`

Creating a Table of Objects

A user-defined object type is composed of attributes, which are equivalent to the columns of a table.

This is the basic syntax for defining a row:

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. This is the basic syntax for creating a table type:

CREATE TYPE table_name AS TABLE OF object_name;

See Also:

Oracle Database Application Developer's Guide - Object-Relational Features for information about object types

Datamap Parameter

The third parameter of the OLAP_TABLE function is a single OLAP DML command. It is called a datamap because its primary use is to manually control the mapping of data sources, using the OLAP DML FETCH command. The datamap parameter is also used for selections and calculations that cannot be performed in the limit map. It is an optional parameter and is typically omitted.

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

The syntax of this parameter is:

'olap_command'

Using the 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.

Note:

Use the FETCH keyword in OLAP_TABLE only if you are migrating an Express Server application that used the FETCH command for SNAPI. In that case, 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.

Using a Limit Map With FETCH

When you use FETCH, the limit map is not required; if you do not provide a limit map or omit its DIMENSION clauses, then you must use the table_name parameter. The MEASURE and LOOP clauses of a limit map are irrelevant when used with FETCH.

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.

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

If you are using a FETCH command in the datamap parameter, you typically omit the limit map.

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.

If you supply the limit map as text in the SELECT statement, 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.

Example 26-1 Syntax of the Limit Map Parameter of OLAP_TABLE

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

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_obj is either a valueset or a Boolean variable in the analytic workspace. It identifies whether a dimension member is in hierarchy. A valueset is more efficient than a Boolean variable.

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.

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

datamap is an OLAP DML command.

Table 26-3 Components of the OLAP_TABLE Limit Map

Keyword	Keyword Clause Syntax and Description
`MEASURE`	`MEASURE column [AS datatype] 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. The `AS` clause specifies the data type (such as `NUMBER(12)` or `VARCHAR2(30)`) for the column. You cannot use `AS` if you use the `OLAP_TABLE` table_name clause. If you do not specify `AS` or table_name, then the `OLAP_TABLE` function determines the best data type. 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.
`DIMENSION`	`DIMENSION [column [AS datatype] 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. 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. The `AS` clause specifies the data type (such as `NUMBER(12)` or `VARCHAR2(30)`) for the column. You cannot use `AS` if you use the `OLAP_TABLE` table_name clause. If you do not specify `AS` or table_name, then the `OLAP_TABLE` function determines the best data type. 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. 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. The syntax of the `WITH` clause is summarized as follows. See Table 26-4 for complete descriptions of each component. [WITH [HIERARCHY [column [AS datatype FROM] hierarchy_relation[(hierarchy_dimension 'hierarchy')] [INHIERARCHY inhierarchy_variable] [GID column [AS datatype] FROM gid_variable] [PARENTGID column [AS datatype] FROM gid_variable] [FAMILYREL col1 [AS datatype], col2 [AS datatype], coln [AS datatype] FROM {expression1, expression2, expressionn \| family_relation USING level_dimension } [LABEL label_variable]] . . .] [ATTRIBUTE column [AS datatype] FROM attribute_variable] . . .]
`ROW2CELL`	`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. See "OLAP_EXPRESSION Function".
`LOOP`	`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`	`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. 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 ".
`POSTDMLCMD`	`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. See "Order of Processing in OLAP_TABLE ".

Table 26-4 WITH Subclause of DIMENSION Clause of OLAP_TABLE Limit Map

Keyword Component

Keyword	Component
`HIERARCHY`	`HIERARCHY [column [AS datatype] FROM] hierarchy_relation[(hierarchy_dimension 'hierarchy')]...` The `HIERARCHY` subclause identifies the parent self-relation in the analytic workspace that defines the hierarchies for dimension. The `AS` clause specifies the data type (such as `NUMBER(12)` or `VARCHAR2(30)`) for the column. You cannot use `AS` if you use the `OLAP_TABLE` table_name clause. If you do not specify `AS` or table_name, then the `OLAP_TABLE` function determines the best data type. 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. `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. It 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. The `PARENTGID` clause is recommended for Java applications that use the OLAP API. `FAMILYREL col1 [AS datatype], col2 [AS datatype], coln [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. 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. 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`	`ATTRIBUTE column [AS datatype] 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.

HIERARCHY

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

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

The AS clause specifies the data type (such as NUMBER(12) or VARCHAR2(30)) for the column. You cannot use AS if you use the OLAP_TABLE table_name clause. If you do not specify AS or table_name, then the OLAP_TABLE function determines the best data type.

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

The PARENTGID clause is recommended for Java applications that use the OLAP API.
FAMILYREL col1 [AS datatype], col2 [AS datatype], coln [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. 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.

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

ATTRIBUTE column [AS datatype] 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.

Order of Processing in OLAP_TABLE

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

Execute any OLAP DML command specified in the PREDMLCMD parameter of the limit map.
Save the current status of all dimensions so that it can be restored later (PUSH status).
Keep in status only those dimension values that are in the hierarchy specified by the INHIERARCHY clause (LIMIT KEEP).
Keep in status only those dimension values that satisfy the WHERE clause on the SQL SELECT statement containing the OLAP_TABLE function.
Execute any OLAP DML command specified in the datamap parameter of the OLAP_TABLE function.
Fetch the data.
Restore the status of all dimensions in the limit map (POP status).
Execute any OLAP DML command specified in the POSTDMLCMD parameter of the limit map.

OLAP_TABLE Examples

The examples show the two basic methods of using OLAP_TABLE:

"Creating Views for the BI Beans and OLAP API" uses a limit map. This is the most common use of OLAP_TABLE.
"Using OLAP_TABLE with the FETCH Command" uses the FETCH command. This method is for use only by Oracle Express Server applications that are being revised for use with Oracle Database.

Creating Views for the BI Beans and OLAP API

The examples provided here define a dimension view for the PRODUCT dimension and a measure view for the ANALYTIC_CUBE cube in the XADEMO sample analytic workspace. These are the type of views created by the OLAP API enabler. The data types of the columns are specified in the limit maps in AS clauses.

Note the use of a MODEL clause in the SELECT statements. The MODEL clause, when used with OLAP_TABLE, is an optimization that enables data to be fetched much faster from an analytic workspace. Refer to Oracle OLAP Application Developer's Guide for information about the use of arguments in the MODEL clause.

Creating a Dimension View

Example 26-2 creates a view named XADE_XADEM_XADEM_STAND4VIEW for the PRODUCT embedded total dimension in XADEMO. The third argument to OLAP_TABLE uses ampersand substitution to reference the limit map, which is stored in a variable named OLAP_SYS_LIMITMAP in the analytic workspace.

Example 26-2 Defining a PRODUCT Dimension View

CREATE OR REPLACE VIEW xademo.xade_xadem_xadem_stand4view AS SELECT * FROM
   TABLE(OLAP_TABLE('xademo.xademo DURATION SESSION',
   '',
   '',
   '&(xademo.xademo!olap_sys_limitmap(xademo.xademo!olap_sys_viewdim
       ''xade_xadem_xadem_stand4view''))'))
MODEL
   DIMENSION BY (
     product_et,
     product_gid)
   MEASURES (
     product_parent,
     product_parentgid,
     r2c,
     l4_equipment_parts,
     l3_components,
     l2_divisions,
     l1_total_products,
     aw_member_order,
     color,
     size_attr,
     long_description,
     short_description)
   RULES UPDATE();

Example 26-3 shows the contents of the limit map for the PRODUCT dimension. This limit map specifies the data types of the columns using AS clauses, instead of using the defaults.

Example 26-3 Limit Map for PRODUCT Dimension

DIMENSION PRODUCT_ET AS VARCHAR2(100) FROM XADEMO.XADEMO!PRODUCT WITH 
   HIERARCHY PRODUCT_PARENT AS VARCHAR2(100) FROM
     XADEMO.XADEMO!PRODUCT_PARENTREL(XADEMO.XADEMO!PRODUCT_HIERLIST 1)
     INHIERARCHY XADEMO.XADEMO!PRODUCT_INHIER
     GID PRODUCT_GID AS NUMBER(12) FROM XADEMO.XADEMO!PRODUCT_GID
     PARENTGID PRODUCT_PARENTGID AS NUMBER(12) FROM XADEMO.XADEMO!PRODUCT_GID
     LEVELREL L4_Equipment_Parts AS VARCHAR2(100),
       L3_Components AS VARCHAR2(100),
       L2_Divisions AS VARCHAR2(100),
       L1_Total_Products AS VARCHAR2(100) 
       FROM XADEMO.XADEMO!PRODUCT_FAMILYREL USING
         XADEMO.XADEMO!PRODUCT_LEVELLIST
   ATTRIBUTE AW_MEMBER_ORDER AS NUMBER FROM XADEMO.XADEMO!PRODUCT_ORDER
   ATTRIBUTE COLOR AS VARCHAR2(1000) FROM XADEMO.XADEMO!PRODUCT_COLOR
   ATTRIBUTE SIZE_ATTR AS VARCHAR2(1000) FROM XADEMO.XADEMO!PRODUCT_SIZE
   ATTRIBUTE LONG_DESCRIPTION AS VARCHAR2(1000) FROM
   XADEMO.XADEMO!PRODUCT_LONG_DESCRIPTION
   ATTRIBUTE SHORT_DESCRIPTION AS VARCHAR2(1000) FROM
     XADEMO.XADEMO!PRODUCT_SHORT_DESCRIPTION
   ROW2CELL R2C
   PREDMLCMD 'limit XADEMO.XADEMO!PRODUCT_HIERLIST to 1'

Creating a Measure View

Example 26-4 creates a view named XADEMO.XADE_XADEM_ANALY11VIEW for the measures in ANALYTIC_CUBE in XADEMO. The third argument to OLAP_TABLE uses ampersand substitution to reference the limit map, which is stored in a variable named OLAP_SYS_LIMITMAP in the analytic workspace. The OLAP API enabler stores all limit maps in this variable, which is dimensioned by OLAP_SYS_VIEWDIM so that the limit map for each view can be stored in a separate cell.

Note also how the MODEL clause is used in a measure view.

Example 26-4 Defining a Cube View

CREATE OR REPLACE VIEW xademo.xade_xadem_analy11view AS
   SELECT * FROM TABLE(OLAP_TABLE('xademo.xademo DURATION SESSION',
     '',
     '',
     '&(xademo.xademo!olap_sys_limitmap(xademo.xademo!olap_sys_viewdim
     ''xade_xadem_analy11view''))'))
   MODEL
     DIMENSION BY (
       channel_et,
       channel_gid,
       geography_et,
       geography_gid,
       product_et,
       product_gid,
       time_et,
       time_gid)
     MEASURES (
       analytic_cube_f_sales,
       analytic_cube_f_costs,
       analytic_cube_f_units,
       analytic_cube_f_quota,
       analytic_cube_f_promo,
       r2c)
     RULES UPDATE();

Example 26-5 shows the contents of the limit map for the measures in ANALYTIC_CUBE. This limit map specifies the data types of the columns using AS clauses, instead of using the defaults.

Example 26-5 Limit Map for ANALYTIC_CUBE

MEASURE ANALYTIC_CUBE_F_SALES AS NUMBER FROM
   XADEMO.XADEMO!ANALYTIC_CUBE_F_SALES
MEASURE ANALYTIC_CUBE_F_COSTS AS NUMBER FROM
   XADEMO.XADEMO!ANALYTIC_CUBE_F_COSTS
MEASURE ANALYTIC_CUBE_F_UNITS AS NUMBER FROM
   XADEMO.XADEMO!ANALYTIC_CUBE_F_UNITS
MEASURE ANALYTIC_CUBE_F_QUOTA AS NUMBER FROM
   XADEMO.XADEMO!ANALYTIC_CUBE_F_QUOTA
MEASURE ANALYTIC_CUBE_F_PROMO AS NUMBER FROM
   XADEMO.XADEMO!ANALYTIC_CUBE_F_PROMO
ROW2CELL R2C
DIMENSION CHANNEL_ET AS VARCHAR2(100) FROM XADEMO.XADEMO!CHANNEL
WITH HIERARCHY XADEMO.XADEMO!CHANNEL_PARENTREL(CHANNEL_HIERLIST 1)
INHIERARCHY XADEMO.XADEMO!CHANNEL_INHIER
GID CHANNEL_GID AS NUMBER(12) FROM XADEMO.XADEMO!CHANNEL_GID
DIMENSION GEOGRAPHY_ET AS VARCHAR2(100) FROM XADEMO.XADEMO!GEOGRAPHY
WITH HIERARCHY XADEMO.XADEMO!GEOGRAPHY_PARENTREL(GEOGRAPHY_HIERLIST 1)
INHIERARCHY XADEMO.XADEMO!GEOGRAPHY_INHIER
GID GEOGRAPHY_GID AS NUMBER(12) FROM XADEMO.XADEMO!GEOGRAPHY_GID
DIMENSION PRODUCT_ET AS VARCHAR2(100) FROM XADEMO.XADEMO!PRODUCT
WITH HIERARCHY XADEMO.XADEMO!PRODUCT_PARENTREL(PRODUCT_HIERLIST 1)
INHIERARCHY XADEMO.XADEMO!PRODUCT_INHIER
GID PRODUCT_GID AS NUMBER(12) FROM XADEMO.XADEMO!PRODUCT_GID
DIMENSION TIME_ET AS VARCHAR2(100) FROM XADEMO.XADEMO!TIME
WITH HIERARCHY XADEMO.XADEMO!TIME_PARENTREL(TIME_HIERLIST 2)
INHIERARCHY XADEMO.XADEMO!TIME_INHIER
GID TIME_GID AS NUMBER(12) FROM XADEMO.XADEMO!TIME_GID

Using OLAP_TABLE with the FETCH Command

The following example 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.

The data types of the columns are defined explicitly with CREATE TYPE statements. These user types can be saved permanently and used by multiple calls to OLAP_TABLE.

Example 26-6 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.