DML Reference
An option is a special type of analytic workspace object that specifies the
characteristic of some aspect of how Oracle OLAP calculates or formats data or
what Oracle OLAP operations are activated. You cannot define an option as part of
a workspace. However, you can use any of the options that are defined as part of
the Oracle OLAP DML.
Oracle® OLAP
Reference
10g Release 1 (10.1)
Part No. B10334-02
December 2003
Oracle OLAP Reference, 10g Release 1 (10.1)
Part No. B10334-02
Copyright © 2003 Oracle Corporation. All rights reserved.
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent and other intellectual and industrial property
laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required
to obtain interoperability with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error-free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Express, Oracle9i, PL/SQL, and SQL*Plus are trademarks or
registered trademarks of Oracle Corporation. Other names may be trademarks of their respective
owners.
Contents
Send Us Your Comments ............................................................................................................... xvii
Preface.......................................................................................................................................................... xix
Intended Audience .............................................................................................................................. xix
Documentation Accessibility ............................................................................................................. xix
Structure ................................................................................................................................................. xx
Related Documents............................................................................................................................. xxii
Conventions......................................................................................................................................... xxiii
1 Creating Analytic Workspaces with DBMS_AWM
Overview .............................................................................................................................................. 1-2
Creating OLAP Catalog Metadata for the Source Cube ........................................................ 1-3
Creating and Populating Workspace Dimensions .................................................................. 1-4
Creating and Populating Workspace Cubes............................................................................. 1-4
Aggregating the Cube's Data in the Analytic Workspace ...................................................... 1-5
Enabling Relational Access to the Workspace Cube ............................................................... 1-5
Viewing Metadata Created by DBMS_AWM........................................................................... 1-5
Active Catalog Views............................................................................................................ 1-6
Analytic Workspace Maintenance Views .......................................................................... 1-6
Understanding the DBMS_AWM Procedures............................................................................... 1-6
Methods on Dimensions.............................................................................................................. 1-7
Methods on Cubes........................................................................................................................ 1-7
Methods on Dimension Load Specifications ............................................................................ 1-8
Methods on Cube Load Specifications ...................................................................................... 1-8
iii
Methods on Aggregation Specifications.................................................................................... 1-9
Methods on Composite Specifications..................................................................................... 1-10
Creating and Refreshing a Workspace Dimension .................................................................... 1-10
Refreshing the Dimension's Metadata ..................................................................................... 1-12
When To Refresh a Dimension ................................................................................................. 1-12
What To Do After a Dimension Refresh.................................................................................. 1-13
Creating and Refreshing a Workspace Cube .............................................................................. 1-13
Data Type Conversion ............................................................................................................... 1-15
Refreshing the Cube's Metadata ............................................................................................... 1-15
When To Refresh a Cube ........................................................................................................... 1-16
What To Do After a Cube Refresh............................................................................................ 1-16
Managing Sparse Data and Optimizing the Workspace Cube ................................................ 1-16
Dimension Order ........................................................................................................................ 1-17
Creating and Modifying a Composite Specification ............................................................. 1-18
Aggregating the Data in an Analytic Workspace........................................................................ 1-18
Creating an Aggregation Specification.................................................................................... 1-19
Choosing an Aggregation Method........................................................................................... 1-21
Creating Relational Access to the Workspace Cube................................................................... 1-23
Procedure: Generate and Run the Enablement Scripts ......................................................... 1-23
Procedure: Run the Enablement Scripts Automatically........................................................ 1-24
The OLAP API Enabler Procedures ......................................................................................... 1-25
Enablement Metadata in the Analytic Workspace ................................................................ 1-26
Disabling Relational Access ...................................................................................................... 1-26
Default Dimension View Names.............................................................................................. 1-27
Default Fact View Names .......................................................................................................... 1-27
Column Structure of Dimension Enablement Views ............................................................ 1-28
Sample Dimension View .................................................................................................... 1-29
Grouping ID Column.......................................................................................................... 1-30
Column Structure of Enablement Fact Views ........................................................................ 1-30
Example: Enable a Workspace Cube for Access by the OLAP API .................................... 1-31
2 Creating OLAP Catalog Metadata with CWM2
OLAP Metadata Entities .................................................................................................................... 2-1
Creating a Dimension ........................................................................................................................ 2-2
Procedure: Create an OLAP Dimension.................................................................................... 2-3
iv
Example: Create a Product Dimension ..................................................................................... 2-3
Procedure: Create a Time Dimension........................................................................................ 2-6
Example: Create a Time Dimension........................................................................................... 2-7
Creating a Cube................................................................................................................................... 2-9
Procedure: Create a Cube ............................................................................................................ 2-9
Example: Create a Costs Cube .................................................................................................. 2-10
Mapping OLAP Metadata ............................................................................................................... 2-11
Mapping to Columns ................................................................................................................. 2-11
Mapping Dimensions ......................................................................................................... 2-11
Mapping Measures.............................................................................................................. 2-11
Joining Fact Tables with Dimension Tables............................................................................ 2-12
Validating and Committing OLAP Metadata.............................................................................. 2-13
Validating OLAP Metadata....................................................................................................... 2-13
Viewing Validity Status ............................................................................................................. 2-15
Refreshing Metadata Tables for the OLAP API ..................................................................... 2-16
Invoking the Procedures ................................................................................................................. 2-16
Security Checks and Error Conditions .................................................................................... 2-16
Size Requirements for Parameters ........................................................................................... 2-17
Case Requirements for Parameters .......................................................................................... 2-17
Directing Output............................................................................................................................... 2-18
Viewing OLAP Metadata ................................................................................................................ 2-19
3 Active Catalog Views
Standard Form Active Catalog ......................................................................................................... 3-1
Standard Form Classes ................................................................................................................ 3-2
Active Catalog and Standard Form Classes ............................................................................. 3-2
Example: Query an Analytic Workspace Cube ............................................................................. 3-3
Summary of Active Catalog Views.................................................................................................. 3-4
ALL_OLAP2_AWS.............................................................................................................................. 3-5
ALL_OLAP2_AW_ATTRIBUTES .................................................................................................... 3-5
ALL_OLAP2_AW_CUBES................................................................................................................. 3-6
ALL_OLAP2_AW_CUBE_AGG_LVL.............................................................................................. 3-6
ALL_OLAP2_AW_CUBE_AGG_MEAS ......................................................................................... 3-7
ALL_OLAP2_AW_CUBE_AGG_OP................................................................................................ 3-7
ALL_OLAP2_AW_CUBE_AGG_SPECS......................................................................................... 3-8
v
ALL_OLAP2_AW_CUBE_DIM_USES ............................................................................................ 3-8
ALL_OLAP2_AW_CUBE_MEASURES .......................................................................................... 3-9
ALL_OLAP2_AW_DIMENSIONS................................................................................................. 3-10
ALL_OLAP2_AW_DIM_HIER_LVL_ORD .................................................................................. 3-10
ALL_OLAP2_AW_DIM_LEVELS .................................................................................................. 3-11
ALL_OLAP2_AW_PHYS_OBJ ........................................................................................................ 3-11
ALL_OLAP2_AW_PHYS_OBJ_PROP........................................................................................... 3-12
4 Analytic Workspace Maintenance Views
Building and Maintaining Analytic Workspaces ......................................................................... 4-1
Example: Query Load and Enablement Parameters for Workspace Dimensions .................. 4-2
Summary of Analytic Workspace Maintenance Views ............................................................... 4-3
ALL_AW_CUBE_AGG_LEVELS...................................................................................................... 4-4
ALL_AW_CUBE_AGG_MEASURES .............................................................................................. 4-4
ALL_AW_CUBE_AGG_PLANS ....................................................................................................... 4-5
ALL_AW_CUBE_ENABLED_HIERCOMBO ................................................................................ 4-5
ALL_AW_CUBE_ENABLED_VIEWS ............................................................................................. 4-6
ALL_AW_DIM_ENABLED_VIEWS................................................................................................ 4-7
ALL_AW_LOAD_CUBES .................................................................................................................. 4-7
ALL_AW_LOAD_CUBE_DIMS ....................................................................................................... 4-8
ALL_AW_LOAD_CUBE_FILTERS .................................................................................................. 4-9
ALL_AW_LOAD_CUBE_MEASURES............................................................................................ 4-9
ALL_AW_LOAD_CUBE_PARMS.................................................................................................. 4-10
ALL_AW_LOAD_DIMENSIONS.................................................................................................. 4-11
ALL_AW_LOAD_DIM_FILTERS .................................................................................................. 4-11
ALL_AW_LOAD_DIM_PARMS .................................................................................................... 4-12
ALL_AW_OBJ .................................................................................................................................... 4-13
ALL_AW_PROP................................................................................................................................. 4-13
5 OLAP Catalog Metadata Views
Access to OLAP Catalog Views ........................................................................................................ 5-1
Views of the Dimensional Model.................................................................................................... 5-2
Views of Mapping Information ....................................................................................................... 5-3
ALL_OLAP2_AGGREGATION_USES........................................................................................... 5-3
ALL_OLAP2_CATALOGS................................................................................................................. 5-4
vi
ALL_OLAP2_CATALOG_ENTITY_USES ..................................................................................... 5-5
ALL_OLAP2_CUBES ......................................................................................................................... 5-5
ALL_OLAP2_CUBE_DIM_USES..................................................................................................... 5-5
ALL_OLAP2_CUBE_MEASURES ................................................................................................... 5-6
ALL_OLAP2_CUBE_MEASURE_MAPS ....................................................................................... 5-6
ALL_OLAP2_CUBE_MEAS_DIM_USES....................................................................................... 5-7
ALL_OLAP2_DIMENSIONS ........................................................................................................... 5-7
ALL_OLAP2_DIM_ATTRIBUTES .................................................................................................. 5-8
ALL_OLAP2_DIM_ATTR_USES..................................................................................................... 5-8
ALL_OLAP2_DIM_HIERARCHIES ............................................................................................... 5-9
ALL_OLAP2_DIM_HIER_LEVEL_USES..................................................................................... 5-10
ALL_OLAP2_DIM_LEVELS........................................................................................................... 5-10
ALL_OLAP2_DIM_LEVEL_ATTRIBUTES ................................................................................. 5-10
ALL_OLAP2_DIM_LEVEL_ATTR_MAPS .................................................................................. 5-11
ALL_OLAP2_ENTITY_DESC_USES ............................................................................................ 5-12
ALL_OLAP2_ENTITY_EXT_PARMS ........................................................................................... 5-12
ALL_OLAP2_ENTITY_PARAMETERS ....................................................................................... 5-14
ALL_OLAP2_FACT_LEVEL_USES ............................................................................................... 5-14
ALL_OLAP2_FACT_TABLE_GID ................................................................................................. 5-15
ALL_OLAP2_HIER_CUSTOM_SORT ......................................................................................... 5-16
ALL_OLAP2_JOIN_KEY_COLUMN_USES ............................................................................... 5-17
ALL_OLAP2_LEVEL_KEY_COL_USES....................................................................................... 5-18
6 OLAP Fixed Views
System Tables Referenced by OLAP Fixed Views ....................................................................... 6-1
Summary of OLAP Fixed Views ...................................................................................................... 6-2
V$AW_AGGREGATE_OP ................................................................................................................ 6-3
V$AW_ALLOCATE_OP..................................................................................................................... 6-3
V$AW_CALC ....................................................................................................................................... 6-3
V$AW_LONGOPS.............................................................................................................................. 6-5
V$AW_OLAP ....................................................................................................................................... 6-6
V$AW_SESSION_INFO .................................................................................................................... 6-7
7 CWM2_OLAP_CATALOG
Understanding Measure Folders ..................................................................................................... 7-1
vii
Example: Creating a Measure Folder .............................................................................................. 7-2
Summary of CWM2_OLAP_CATALOG Subprograms ............................................................... 7-3
ADD_CATALOG_ENTITY Procedure............................................................................... 7-3
CREATE_CATALOG Procedure......................................................................................... 7-4
DROP_CATALOG Procedure ............................................................................................. 7-4
LOCK_CATALOG Procedure ............................................................................................. 7-5
REMOVE_CATALOG_ENTITY Procedure....................................................................... 7-5
SET_CATALOG_NAME Procedure ................................................................................... 7-6
SET_DESCRIPTION Procedure........................................................................................... 7-6
SET_PARENT_CATALOG Procedure ............................................................................... 7-7
8 CWM2_OLAP_CLASSIFY
OLAP Catalog Metadata Descriptors .............................................................................................. 8-1
Example: Creating Descriptors......................................................................................................... 8-2
Summary of CWM2_OLAP_CLASSIFY Subprograms ............................................................... 8-4
ADD_ENTITY_CARDINALITY_USE ................................................................................ 8-4
ADD_ENTITY_DEFAULTMEMBER_USE ........................................................................ 8-5
ADD_ENTITY_DENSEINDICATOR_USE........................................................................ 8-6
ADD_ENTITY_DESCRIPTOR_USE ................................................................................... 8-7
ADD_ENTITY_FACTJOIN_USE......................................................................................... 8-8
REMOVE_ENTITY_DESCRIPTOR_USE ......................................................................... 8-10
9 CWM2_OLAP_CUBE
Understanding Cubes ........................................................................................................................ 9-1
Example: Creating a Cube ................................................................................................................. 9-2
Summary of CWM2_OLAP_CUBE Subprograms ........................................................................ 9-3
ADD_DIMENSION_TO_CUBE Procedure ....................................................................... 9-3
CREATE_CUBE Procedure .................................................................................................. 9-4
DROP_CUBE Procedure....................................................................................................... 9-5
LOCK_CUBE Procedure....................................................................................................... 9-5
REMOVE_DIMENSION_FROM_CUBE Procedure......................................................... 9-6
SET_AGGREGATION_OPERATOR Procedure ............................................................... 9-6
SET_CUBE_NAME Procedure ............................................................................................ 9-8
SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure................................................... 9-9
SET_DESCRIPTION Procedure........................................................................................... 9-9
viii
SET_DISPLAY_NAME Procedure.................................................................................... 9-10
SET_MV_SUMMARY_CODE Procedure ........................................................................ 9-10
SET_SHORT_DESCRIPTION Procedure......................................................................... 9-11
10 CWM2_OLAP_DIMENSION
Understanding Dimensions............................................................................................................ 10-1
Example: Creating a CWM2 Dimension ...................................................................................... 10-2
Summary of CWM2_OLAP_DIMENSION Subprograms........................................................ 10-3
CREATE_DIMENSION Procedure................................................................................... 10-3
DROP_DIMENSION Procedure ....................................................................................... 10-4
LOCK_DIMENSION Procedure ....................................................................................... 10-5
SET_DEFAULT_DISPLAY_HIERARCHY Procedure ................................................... 10-5
SET_DESCRIPTION Procedure ........................................................................................ 10-6
SET_DIMENSION_NAME Procedure ............................................................................. 10-6
SET_DISPLAY_NAME Procedure.................................................................................... 10-7
SET_PLURAL_NAME Procedure..................................................................................... 10-7
SET_SHORT_DESCRIPTION Procedure......................................................................... 10-8
11 CWM2_OLAP_DIMENSION_ATTRIBUTE
Understanding Dimension Attributes.......................................................................................... 11-1
Example: Creating a Dimension Attribute .................................................................................. 11-2
Summary of CWM2_OLAP_DIMENSION_ATTRIBUTE Subprograms .............................. 11-4
CREATE_DIMENSION_ATTRIBUTE Procedure .......................................................... 11-4
DROP_DIMENSION_ATTRIBUTE Procedure ............................................................... 11-5
LOCK_DIMENSION_ATTRIBUTE Procedure ............................................................... 11-6
SET_DESCRIPTION Procedure ........................................................................................ 11-7
SET_DIMENSION_ATTRIBUTE_NAME Procedure..................................................... 11-7
SET_DISPLAY_NAME Procedure.................................................................................... 11-8
SET_SHORT_DESCRIPTION Procedure......................................................................... 11-9
12 CWM2_OLAP_HIERARCHY
Understanding Hierarchies............................................................................................................. 12-1
Example: Creating a Hierarchy....................................................................................................... 12-2
Summary of CWM2_OLAP_HIERARCHY Subprograms........................................................ 12-3
ix
CREATE_HIERARCHY Procedure .................................................................................. 12-3
DROP_HIERARCHY Procedure ....................................................................................... 12-4
LOCK_HIERARCHY Procedure ....................................................................................... 12-5
SET_DESCRIPTION Procedure......................................................................................... 12-6
SET_DISPLAY_NAME Procedure .................................................................................... 12-6
SET_HIERARCHY_NAME Procedure............................................................................. 12-7
SET_SHORT_DESCRIPTION Procedure ......................................................................... 12-7
SET_SOLVED_CODE Procedure ...................................................................................... 12-8
13 CWM2_OLAP_LEVEL
Understanding Levels ...................................................................................................................... 13-1
Example: Creating a Level ............................................................................................................... 13-2
Summary of CWM2_OLAP_LEVEL Subprograms .................................................................... 13-3
ADD_LEVEL_TO_HIERARCHY Procedure................................................................... 13-3
CREATE_LEVEL Procedure .............................................................................................. 13-4
DROP_LEVEL Procedure................................................................................................... 13-5
LOCK_LEVEL Procedure................................................................................................... 13-5
REMOVE_LEVEL_FROM_HIERARCHY Procedure..................................................... 13-6
SET_DESCRIPTION Procedure......................................................................................... 13-6
SET_DISPLAY_NAME Procedure .................................................................................... 13-7
SET_LEVEL_NAME Procedure......................................................................................... 13-8
SET_PLURAL_NAME Procedure ..................................................................................... 13-8
SET_SHORT_DESCRIPTION Procedure ......................................................................... 13-9
14 CWM2_OLAP_LEVEL_ATTRIBUTE
Understanding Level Attributes .................................................................................................... 14-1
Example: Creating Level Attributes .............................................................................................. 14-3
Summary of CWM2_OLAP_LEVEL_ATTRIBUTE Subprograms........................................... 14-4
CREATE_LEVEL_ATTRIBUTE Procedure...................................................................... 14-4
DROP_LEVEL_ATTRIBUTE Procedure .......................................................................... 14-6
LOCK_LEVEL_ATTRIBUTE Procedure .......................................................................... 14-7
SET_DESCRIPTION Procedure......................................................................................... 14-7
SET_DISPLAY_NAME Procedure .................................................................................... 14-8
SET_LEVEL_ATTRIBUTE_NAME Procedure ................................................................ 14-9
SET_SHORT_DESCRIPTION Procedure ....................................................................... 14-10
x
15 CWM2_OLAP_MEASURE
Understanding Measures ................................................................................................................ 15-1
Example: Creating a Measure ......................................................................................................... 15-2
Summary of CWM2_OLAP_MEASURE Subprograms ............................................................ 15-3
CREATE_MEASURE Procedure ....................................................................................... 15-3
DROP_MEASURE Procedure............................................................................................ 15-4
LOCK_MEASURE Procedure............................................................................................ 15-4
SET_DESCRIPTION Procedure ........................................................................................ 15-5
SET_DISPLAY_NAME Procedure.................................................................................... 15-6
SET_MEASURE_NAME Procedure ................................................................................. 15-6
SET_SHORT_DESCRIPTION Procedure......................................................................... 15-7
16 CWM2_OLAP_METADATA_REFRESH
Views of Cached OLAP Catalog Metadata .................................................................................. 16-1
Views of Cached Active Catalog Metadata.................................................................................. 16-2
Summary of CWM2_OLAP_METADATA_REFRESH Subprograms..................................... 16-3
MR_REFRESH Procedure .................................................................................................. 16-3
MR_AC_REFRESH Procedure .......................................................................................... 16-3
17 CWM2_OLAP_PC_TRANSFORM
Prerequisites ...................................................................................................................................... 17-1
Parent-Child Dimensions................................................................................................................ 17-2
Solved, Level-Based Dimensions .................................................................................................. 17-3
Example: Creating a Solved, Level-Based Dimension Table ................................................... 17-3
Grouping ID Column................................................................................................................. 17-4
Embedded Total Key Column .................................................................................................. 17-5
Summary of CWM2_OLAP_PC_TRANSFORM Subprograms............................................... 17-6
CREATE_SCRIPT Procedure............................................................................................. 17-6
18 CWM2_OLAP_TABLE_MAP
Understanding OLAP Metadata Mapping .................................................................................. 18-1
Example: Mapping a Dimension ................................................................................................... 18-2
Example: Mapping a Cube.............................................................................................................. 18-2
Summary of CWM2_OLAP_TABLE_MAP Subprograms ........................................................ 18-4
xi
MAP_DIMTBL_HIERLEVELATTR Procedure............................................................... 18-5
MAP_DIMTBL_HIERLEVEL Procedure.......................................................................... 18-5
MAP_DIMTBL_HIERSORTKEY Procedure.................................................................... 18-6
MAP_DIMTBL_LEVELATTR Procedure......................................................................... 18-7
MAP_DIMTBL_LEVEL Procedure ................................................................................... 18-8
MAP_FACTTBL_LEVELKEY Procedure ......................................................................... 18-9
MAP_FACTTBL_MEASURE Procedure ........................................................................ 18-11
REMOVEMAP_DIMTBL_HIERLEVELATTR Procedure ........................................... 18-12
REMOVEMAP_DIMTBL_HIERLEVEL Procedure ...................................................... 18-13
REMOVEMAP_DIMTBL_HIERSORTKEY Procedure ................................................ 18-14
REMOVEMAP_DIMTBL_LEVELATTR Procedure ..................................................... 18-14
REMOVEMAP_DIMTBL_LEVEL Procedure ................................................................ 18-15
REMOVEMAP_FACTTBL_LEVELKEY Procedure...................................................... 18-16
REMOVEMAP_FACTTBL_MEASURE Procedure....................................................... 18-16
19 CWM2_OLAP_VALIDATE
About OLAP Catalog Metadata Validation ................................................................................. 19-1
Structural Validation .................................................................................................................. 19-1
Cubes ..................................................................................................................................... 19-2
Dimensions ........................................................................................................................... 19-2
Mapping Validation ................................................................................................................... 19-2
Cubes ..................................................................................................................................... 19-2
Dimensions ........................................................................................................................... 19-2
Validation Type........................................................................................................................... 19-3
Summary of CWM2_OLAP_VALIDATE Subprograms ............................................................ 19-4
VALIDATE_ALL_CUBES Procedure ............................................................................... 19-4
VALIDATE_ALL_DIMENSIONS Procedure .................................................................. 19-5
VALIDATE_CUBE Procedure ........................................................................................... 19-5
VALIDATE_DIMENSION Procedure .............................................................................. 19-6
VALIDATE_OLAP_CATALOG Procedure..................................................................... 19-7
20 CWM2_OLAP_VERIFY_ACCESS
Validating the Accessibility of an OLAP Cube........................................................................... 20-1
Summary of CWM2_OLAP_VERIFY_ACCESS Subprograms ................................................ 20-3
VERIFY_CUBE_ACCESS Procedure ................................................................................ 20-3
xii
21 DBMS_AW
Embedding OLAP DML in SQL Statements............................................................................... 21-2
Methods for Executing OLAP DML Commands................................................................... 21-2
Guidelines for Using Quotation Marks in OLAP DML Commands .................................. 21-2
Embedding Custom Measures in SELECT Statements............................................................. 21-3
Using the Aggregate Advisor ......................................................................................................... 21-6
Aggregation Facilities within the Workspace ........................................................................ 21-6
Example: Using the ADVISE_REL Procedure........................................................................ 21-6
Summary of DBMS_AW Subprograms...................................................................................... 21-11
ADVISE_CUBE Procedure............................................................................................... 21-12
ADVISE_REL Procedure .................................................................................................. 21-13
AW_ATTACH Procedure ................................................................................................ 21-14
AW_COPY Procedure ...................................................................................................... 21-15
AW_CREATE Procedure.................................................................................................. 21-16
AW_DELETE ..................................................................................................................... 21-17
AW_DETACH Procedure ................................................................................................ 21-18
AW_RENAME Procedure................................................................................................ 21-19
AW_UPDATE Procedure ................................................................................................. 21-19
EXECUTE Procedure ........................................................................................................ 21-20
GETLOG Function............................................................................................................. 21-21
INTERP Function .............................................................................................................. 21-22
INTERPCLOB Function.................................................................................................... 21-23
INTERP_SILENT Procedure............................................................................................ 21-25
OLAP_EXPRESSION Function ....................................................................................... 21-26
OLAP_EXPRESSION_BOOL Function .......................................................................... 21-27
OLAP_EXPRESSION_DATE Function .......................................................................... 21-28
OLAP_EXPRESSION_TEXT Function ........................................................................... 21-29
PRINTLOG Procedure...................................................................................................... 21-30
22 DBMS_AW_UTILITIES
About Custom Measures ................................................................................................................. 22-1
Querying Custom Measures ........................................................................................................... 22-2
CWM2$_AW_PERM_CUST_MEAS_MAP............................................................................. 22-3
CWM2$_AW_TEMP_CUST_MEAS_MAP ............................................................................. 22-3
Example: Creating a Custom Measure.......................................................................................... 22-4
xiii
Summary of DBMS_AW_UTILITIES Subprograms.................................................................. 22-6
CREATE_CUSTOM_MEASURE Procedure.................................................................... 22-6
DELETE_CUSTOM_MEASURE Procedure .................................................................... 22-8
UPDATE_CUSTOM_MEASURE Procedure ................................................................... 22-8
23 DBMS_AWM
Parameters of DBMS_AWM Subprograms.................................................................................. 23-1
Summary of DBMS_AWM Subprograms .................................................................................... 23-3
ADD_AWCOMP_SPEC_COMP_MEMBER Procedure................................................. 23-6
ADD_AWCOMP_SPEC_MEMBER Procedure............................................................... 23-8
ADD_AWCUBEAGG_SPEC_LEVEL Procedure ............................................................ 23-9
ADD_AWCUBEAGG_SPEC_MEASURE Procedure ................................................... 23-10
ADD_AWCUBELOAD_SPEC_COMP Procedure........................................................ 23-11
ADD_AWCUBELOAD_SPEC_FILTER Procedure ...................................................... 23-12
ADD_AWCUBELOAD_SPEC_MEASURE Procedure ................................................ 23-13
ADD_AWDIMLOAD_SPEC_FILTER Procedure ......................................................... 23-15
AGGREGATE_AWCUBE Procedure.............................................................................. 23-16
CREATE_AWCOMP_SPEC Procedure.......................................................................... 23-18
CREATE_AWCUBE Procedure ....................................................................................... 23-19
CREATE_AWCUBE_ACCESS Procedure...................................................................... 23-22
CREATE_AWCUBE_ACCESS_FULL Procedure ......................................................... 23-23
CREATE_AWCUBEAGG_SPEC Procedure .................................................................. 23-25
CREATE_AWCUBELOAD_SPEC Procedure ............................................................... 23-26
CREATE_AWDIMENSION Procedure.......................................................................... 23-28
CREATE_AWDIMENSION_ACCESS Procedure ........................................................ 23-31
CREATE_AWDIMENSION_ACCESS_FULL Procedure ............................................ 23-32
CREATE_AWDIMLOAD_SPEC Procedure .................................................................. 23-33
DELETE_AWCOMP_SPEC Procedure .......................................................................... 23-35
DELETE_AWCOMP_SPEC_MEMBER Procedure....................................................... 23-36
DELETE_AWCUBE_ACCESS Procedure ...................................................................... 23-36
DELETE_AWCUBE_ACCESS_ALL Procedure ............................................................ 23-38
DELETE_AWCUBEAGG_SPEC Procedure................................................................... 23-38
DELETE_AWCUBEAGG_SPEC_LEVEL Procedure .................................................... 23-39
DELETE_AWCUBEAGG_SPEC_MEASURE Procedure ............................................. 23-40
DELETE_AWCUBELOAD_SPEC Procedure ................................................................ 23-41
xiv
DELETE_AWCUBELOAD_SPEC_COMP Procedure.................................................. 23-41
DELETE_AWCUBELOAD_SPEC_FILTER Procedure ................................................ 23-42
DELETE_AWCUBELOAD_SPEC_MEASURE Procedure .......................................... 23-43
DELETE_AWDIMENSION_ACCESS Procedure......................................................... 23-44
DELETE_AWDIMENSION_ACCESS_ALL Procedure............................................... 23-45
DELETE_AWDIMLOAD_SPEC Procedure .................................................................. 23-46
DELETE_AWDIMLOAD_SPEC_FILTER Procedure................................................... 23-46
REFRESH_AWCUBE Procedure..................................................................................... 23-47
REFRESH_AWCUBE_VIEW_NAME Procedure ......................................................... 23-49
REFRESH_AWDIMENSION Procedure........................................................................ 23-50
REFRESH_AWDIMENSION_VIEW_NAME Procedure ............................................ 23-52
SET_AWCOMP_SPEC_CUBE Procedure...................................................................... 23-53
SET_AWCOMP_SPEC_MEMBER_NAME Procedure ................................................ 23-54
SET_AWCOMP_SPEC_MEMBER_POS Procedure ..................................................... 23-55
SET_AWCOMP_SPEC_MEMBER_SEG Procedure ..................................................... 23-56
SET_AWCOMP_SPEC_NAME Procedure.................................................................... 23-58
SET_AWCUBE_VIEW_NAME Procedure .................................................................... 23-59
SET_AWCUBEAGG_SPEC_AGGOP Procedure .......................................................... 23-60
SET_AWCUBELOAD_SPEC_CUBE Procedure ........................................................... 23-61
SET_AWCUBELOAD_SPEC_LOADTYPE Procedure ................................................ 23-62
SET_AWCUBELOAD_SPEC_NAME Procedure ......................................................... 23-63
SET_AWCUBELOAD_SPEC_PARAMETER Procedure ............................................. 23-64
SET_AWDIMENSION_VIEW_NAME Procedure ....................................................... 23-65
SET_AWDIMLOAD_SPEC_DIMENSION Procedure................................................. 23-66
SET_AWDIMLOAD_SPEC_LOADTYPE Procedure ................................................... 23-66
SET_AWDIMLOAD_SPEC_NAME Procedure ............................................................ 23-67
SET_AWDIMLOAD_SPEC_PARAMETER Procedure................................................ 23-68
24 DBMS_ODM
Summary Management with Materialized Views ..................................................................... 24-1
Grouping Sets.............................................................................................................................. 24-2
Summarizing the Fact Table ........................................................................................................... 24-2
Procedure: Automatically Generate the Materialized Views............................................... 24-3
Procedure: Manually Generate the Materialized Views....................................................... 24-4
Example: Create Materialized Views for a Sales Cube ............................................................. 24-5
xv
Summary of DBMS_ODM Subprograms .................................................................................... 24-8
CREATECUBELEVELTUPLE Procedure......................................................................... 24-8
CREATEDIMLEVTUPLE Procedure ................................................................................ 24-9
CREATEDIMMV_GS Procedure..................................................................................... 24-10
CREATEFACTMV_GS Procedure .................................................................................. 24-11
CREATESTDFACTMV Procedure .................................................................................. 24-12
25 OLAP_API_SESSION_INIT
Initialization Parameters for the OLAP API................................................................................ 25-1
Viewing the Configuration Table .................................................................................................. 25-2
ALL_OLAP_ALTER_SESSION View ...................................................................................... 25-2
Summary of OLAP_API_SESSION_INIT Subprograms.......................................................... 25-3
ADD_ALTER_SESSION Procedure.................................................................................. 25-3
CLEAN_ALTER_SESSION Procedure ............................................................................. 25-4
DELETE_ALTER_SESSION Procedure............................................................................ 25-4
26 OLAP_TABLE
OLAP_TABLE Syntax....................................................................................................................... 26-2
Syntax .................................................................................................................................... 26-2
Parameters ............................................................................................................................ 26-2
Order of Processing in OLAP_TABLE .......................................................................... 26-12
OLAP_TABLE Examples................................................................................................................ 26-13
Creating Views for the BI Beans and OLAP API ................................................................. 26-14
Creating a Dimension View ............................................................................................. 26-14
Creating a Measure View ................................................................................................. 26-15
Using OLAP_TABLE with the FETCH Command .............................................................. 26-18
Index
xvi
Send Us Your Comments
Oracle OLAP Reference, 10g Release 1 (10.1)
Part No. B10334-02
Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this
publication. Your input is an important part of the information used for revision.
■ Did you find any errors?
■ Is the information clearly presented?
■ Do you need more information? If so, where?
■ Are the examples correct? Do you need more examples?
■ What features did you like most about this manual?
If you find any errors or have any other suggestions for improvement, please indicate the title and
part number of the documentation and the chapter, section, and page number (if available). You can
send comments to us in the following ways:
■ Electronic mail: [email protected]
■ FAX: 781-238-9850. Attn: Oracle OLAP
■ Postal service:
Oracle Corporation
Oracle OLAP Documentation
10 Van de Graaff Drive
Burlington, MA 01803
U.S.A.
If you would like a reply, please give your name, address, telephone number, and electronic mail
address (optional).
If you have problems with the software, please contact your local Oracle Support Services.
xvii
xviii
Preface
This reference manual describes the Oracle PL/SQL packages shipped with the
OLAP option of the Oracle Database.
Intended Audience
This reference manual is intended for database administrators and application
developers who perform the following tasks:
■ Administer a database
■ Administer analytic workspaces
■ Build and maintain data warehouses or data marts
■ Define metadata
■ Develop analytical applications
To use this document, you need no prior knowledge of Oracle OLAP.
Documentation Accessibility
Our goal is to make Oracle products, services, and supporting documentation
accessible, with good usability, to the disabled community. To that end, our
documentation includes features that make information available to users of
assistive technology. This documentation is available in HTML format, and contains
markup to facilitate access by the disabled community. Standards will continue to
evolve over time, and Oracle is actively engaged with other market-leading
technology vendors to address technical obstacles so that our documentation can be
accessible to all of our customers. For additional information, visit the Oracle
Accessibility Program Web site at
xix
http://www.oracle.com/accessibility/
Accessibility of Code Examples in Documentation JAWS, a Windows screen
reader, may not always correctly read the code examples in this document. The
conventions for writing code require that closing braces should appear on an
otherwise empty line; however, JAWS may not always read a line of text that
consists solely of a bracket or brace.
Structure
This document contains the following chapters.
Chapter 1, "Creating Analytic Workspaces with DBMS_AWM"
This chapter explains how to use the DBMS_AWM package.
Chapter 2, "Creating OLAP Catalog Metadata with CWM2"
This chapter explains how to use the CWM2 packages.
Chapter 3, "Active Catalog Views"
This chapter describes the views in the Active Catalog.
Chapter 4, "Analytic Workspace Maintenance Views"
This chapter describes the views of analytic workspace maintenance information.
Chapter 5, "OLAP Catalog Metadata Views"
This chapter describes the views of OLAP Catalog metadata.
Chapter 6, "OLAP Fixed Views"
This chapter describes the dynamic performance views for Oracle OLAP.
Chapter 7, "CWM2_OLAP_CATALOG"
This chapter describes the syntax of the procedures in the CWM2_OLAP_CATALOG
package.
Chapter 8, "CWM2_OLAP_CLASSIFY"
This chapter describes the metadata descriptors required by Oracle OLE DB for
OLAP.
xx
