Oracle® OLAP Reference 10g Release 2 (10.2.0.3) Part Number B14350-02 |
|
|
View PDF |
OLAP_TABLE
is the fundamental mechanism in the database for querying an analytic workspace. Within a SQL statement, you can specify an OLAP_TABLE
function call wherever you would provide the name of a table or view.
OLAP_TABLE
returns a table of objects that can be joined to relational tables and views, and to other tables of objects populated by OLAP_TABLE
.
OLAP_TABLE
is used internally by the tools and APIs that access analytic workspaces. For example, Analytic Workspace Manager, the Active Catalog views, the OLAP Java APIs, and the DBMS_AW
package all use OLAP_TABLE
to obtain data and other information from analytic workspaces.
Note:
The OLAP tools and APIs that useOLAP_TABLE
require database standard form, but OLAP_TABLE
itself does not use standard form metadata.OLAP_TABLE
uses a limit map to map dimensions and measures defined in an analytic workspace to columns in a logical table. The limit map combines with the WHERE
clause of a SQL SELECT
statement to generate a series of OLAP DML LIMIT
commands that are executed in the analytic workspace.
OLAP_TABLE
can use a limit map in conjunction with a predefined logical table, or it can use the information in a limit map to dynamically generate a logical table at runtime.
See Also:
"Limit Map Parameter".The logical table populated by OLAP_TABLE
is actually a table type whose rows are user-defined object types, also known as Abstract Data Types or ADTs.
A user-defined object type is composed of attributes, which are equivalent to the columns of a table. The basic syntax for defining a row is as follows.
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. The basic syntax for creating a table type is as follows.
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
"Create Type" in the Oracle Database SQL Reference
You can predefine the table of objects or generate it dynamically. When you create the table type in advance, it is available in the database for use by any invocation of OLAP_TABLE
. Queries that use predefined objects typically perform better than queries that dynamically generate the objects.
Example 11-1 shows how to create a view of an analytic workspace using predefined ADTs.
Example 11-1 Template for Creating a View Using Predefined ADTs
SET ECHO ON SET SERVEROUT ON DROP TYPE table_obj; DROP TYPE row_obj; CREATE TYPE row_obj AS OBJECT ( column_first datatype, column_next datatype, column_n datatype); / CREATE TYPE table_obj AS TABLE OF row_obj; / CREATE OR REPLACE VIEW view_name AS SELECT column_first, column_next, column_n FROM TABLE(OLAP_TABLE( 'analytic_workspace', 'table_obj', 'olap_command', 'limit_map')); / COMMIT; / GRANT SELECT ON view_name TO PUBLIC;
Example 11-2 uses OLAP_TABLE
with a predefined table type to create a relational view of the TIME
dimension in the GLOBAL
analytic workspace of the GLOBAL_AW
schema. The first parameter in the OLAP_TABLE
call is the name of the analytic workspace. The second is the name of the predefined table type. The forth is the limit map that specifies how to map the workspace dimension to the columns of the predefined table type. The third parameter is not specified.
Example 11-2 Sample View of the TIME Dimension Using Predefined ADTs
CREATE TYPE time_cal_row AS OBJECT ( time_id varchar2(32), cal_short_label varchar2(32), cal_end_date date, cal_timespan number(6)); CREATE TYPE time_cal_table AS TABLE OF time_cal_row; CREATE OR REPLACE VIEW time_cal_view AS SELECT time_id, cal_short_label, cal_end_date, cal_timespan FROM TABLE(OLAP_TABLE( 'global_aw.global duration session', 'time_cal_table', '', 'DIMENSION time_id from time with HIERARCHY time_parentrel INHIERARCHY time_inhier ATTRIBUTE cal_short_label from time_short_description ATTRIBUTE cal_end_date from time_end_date ATTRIBUTE cal_timespan from time_time_span'));
If you do not supply the name of a table type as an argument, OLAP_TABLE
uses information in the limit map to generate the logical table automatically. In this case, the table type is only available at runtime within the context of the calling SQL SELECT
statement.
Example 11-3 shows how to create a view of an analytic workspace using automatic ADTs.
Example 11-3 Template for Creating a View Using Automatic ADTs
SET ECHO ON SET SERVEROUT ON CREATE OR REPLACE VIEW view_name AS SELECT column_first, column_next, column_n FROM TABLE(OLAP_TABLE( 'analytic_workspace', '', 'olap_command', 'limit_map')); / COMMIT; / GRANT SELECT ON view_name TO PUBLIC;
Example 11-4 creates the same view produced by Example 11-2, but it automatically generates the ADTs instead of using a predefined table type. It uses AS
clauses in the limit map to specify the data types of the target columns.
Example 11-4 View of the TIME Dimension Using Automatic ADTs
CREATE OR REPLACE VIEW time_cal_view AS SELECT time_id, cal_short_label, cal_end_date, cal_timespan FROM TABLE(OLAP_TABLE( 'global_aw.global duration session', null, null, 'DIMENSION time_id AS varchar2(32) FROM time WITH HIERARCHY time_parentrel INHIERARCHY time_inhier ATTRIBUTE cal_short_label AS VARCHAR2(32) from time_short_description ATTRIBUTE cal_end_date AS DATE from time_end_date ATTRIBUTE cal_timespan AS NUMBER(6) from time_time_span'));
When automatically generating ADTs, OLAP_TABLE
uses default relational data types for the target columns unless you override them with AS
clauses in the limit map. The default data type conversions used by OLAP_TABLE
are described in Table 11-1.
Table 11-1 Default Data Type Conversions
Analytic Workspace Data Type | SQL Data Type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other |
|
You can specify a MODEL
clause in a SELECT FROM OLAP_TABLE
statement to significantly improve query performance. The MODEL
clause causes OLAP_TABLE
to use an internal optimization.
You can use the following syntax to maximize the performance advantage of the MODEL
clause with OLAP_TABLE
. This is the recommended syntax for views of analytic workspaces.
SELECT column_first, column_next, column_n FROM TABLE(OLAP_TABLE( 'analytic_workspace', 'table_obj', 'olap_command', 'limit_map')) MODEL DIMENSION BY(dimensions, gids) MEASURES(measures, attributes, rowtocell) RULES UPDATE SEQUENTIAL ORDER();
The MODEL
clause must include DIMENSION BY
and MEASURES
subclauses that specify the columns in the table object. DIMENSION BY
should list all the dimensions, as defined in the limit map. The list should include the GID
columns for applications that use the OLAP API or BI Beans. MEASURES
should list all the measures, attributes, ROW2CELL
columns, and any other columns excluded from the DIMENSION BY
list.
A MODEL
clause lets you view the results of a query as a multidimensional array and specify calculations (rules) to perform on individual cells and ranges of cells within the array. You can specify calculation rules in the MODEL
clause with OLAP_TABLE
, but they will affect response time. If you wish to obtain the full benefit of the performance optimization, you should specify UPDATE
and SEQUENTIAL ORDER
in the RULES
clause.
The UPDATE
keyword indicates that you are not adding any custom members in the DIMENSION BY
clause. If you do not include this keyword, the SQL WHERE
clauses for measures will be discarded, which can significantly degrade performance.
The SEQUENTIAL ORDER
keyword prevents Oracle from evaluating the rules to ascertain their dependencies.
See Also:
Oracle Database SQL Reference and Oracle Database Data Warehousing Guide for more information on SQL models.