Basic Steps

There are three steps to using the OLAP_TABLE function:

1. Define an object type. Equivalent to defining a row.
2. Create a type of these objects. Equivalent to defining a table.
3. Embed a call to the OLAP_TABLE function in a SELECT statement.

Defining a Row

When you define a row, you are actually defining an abstract object type. An abstract object type is composed of attributes, which are equivalent to the columns of a table. (These attributes have no relationship to the attributes described in "Attributes".) When you ultimately create a relational view, you will select its columns from these attributes. However, it is generally easier to understand the process in terms of rows and columns instead of object types and attributes.

This is the basic syntax for defining a row. The last column is defined as type RAW, and stores information used by the single-row functions in DBMS_AW. If you are not going to use those functions, then you do not need to define this column.

CREATE TYPE row_name AS OBJECT (
   column_first       datatype,
 column_second      datatype,
 column_last        RAW(32);

Example 12-1 defines a row for a product dimension table. The five VARCHAR2 columns of PRODUCT_ROW (PRODUCT, PRODUCT_LABEL, and so forth) ultimately define the available columns of a product dimension view.

Example 12-1 Creating the PRODUCT_ROW Object Type

CREATE TYPE product_row AS OBJECT (
   product             VARCHAR2(30),
   product_label       VARCHAR2(30),
   product_parent      VARCHAR2(30),
   product_level       VARCHAR2(2),
   subcategory         VARCHAR2(30),
   category            VARCHAR2(15),
   all_products        VARCHAR2(15)
   r2c                 RAW(32));

Creating a Table

An abstract table type is a collection of abstract object types. The table type describes the table that will be populated by OLAP_TABLE. This is the basic syntax for creating a table type:

CREATE TYPE table_name AS TABLE OF row_name;

Example 12-2 creates a table of the PRODUCT_ROW objects that were created in Example 12-1.

Example 12-2 Creating the PRODUCT_TABLE Table Type

CREATE TYPE product_table AS TABLE OF product_row;

Using OLAP_TABLE in a SELECT Statement

A view of an analytic workspace is like any other relational view in being a saved SELECT statement. The difference is that the OLAP_TABLE function takes the place of a relational table.

The following syntax shows how you would use OLAP_TABLE to create a view:

CREATE OR REPLACE VIEW view_name AS
SELECT columns
   FROM TABLE(OLAP_TABLE(parameters))
WHERE conditions;

Where:

columns are the names of attribute columns in the logical table object that you defined. You do not need to reference all of the columns, only those that you will use as targets in the limit map of OLAP_TABLE.

conditions modify the result set from OLAP_TABLE. These operators are processed in the analytic workspace: =, !=, IN, NOT IN. Conditions that are not supported in the analytic workspace are executed in SQL on the returned result set.

Applications can also generate SELECT statements on the fly that use calls to OLAP_TABLE instead of, or in addition to, the names of relational tables. This type of application can generate calls to OLAP_TABLE with parameters defined by the user.