|Oracle9i OLAP User's Guide
Release 2 (220.127.116.11)
Part Number A95295-02
There are three steps to using the
OLAP_TABLEfunction in a
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_LABEL, and so forth) ultimately define the available columns of a product dimension view.
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));
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:
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;
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
conditions modify the result set from
OLAP_TABLE. These operators are processed in the analytic workspace:
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.