Skip Headers

Oracle® OLAP Reference
10g Release 1 (10.1)

Part Number B10334-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
Next
View PDF

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience
Documentation Accessibility
Structure
Related Documents
Conventions

1 Creating Analytic Workspaces with DBMS_AWM

1.1 Overview
1.1.1 Creating OLAP Catalog Metadata for the Source Cube
1.1.2 Creating and Populating Workspace Dimensions
1.1.3 Creating and Populating Workspace Cubes
1.1.4 Aggregating the Cube's Data in the Analytic Workspace
1.1.5 Enabling Relational Access to the Workspace Cube
1.1.6 Viewing Metadata Created by DBMS_AWM
1.1.6.1 Active Catalog Views
1.1.6.2 Analytic Workspace Maintenance Views
1.2 Understanding the DBMS_AWM Procedures
1.2.1 Methods on Dimensions
1.2.2 Methods on Cubes
1.2.3 Methods on Dimension Load Specifications
1.2.4 Methods on Cube Load Specifications
1.2.5 Methods on Aggregation Specifications
1.2.6 Methods on Composite Specifications
1.3 Creating and Refreshing a Workspace Dimension
1.3.1 Refreshing the Dimension's Metadata
1.3.2 When To Refresh a Dimension
1.3.3 What To Do After a Dimension Refresh
1.4 Creating and Refreshing a Workspace Cube
1.4.1 Data Type Conversion
1.4.2 Refreshing the Cube's Metadata
1.4.3 When To Refresh a Cube
1.4.4 What To Do After a Cube Refresh
1.5 Managing Sparse Data and Optimizing the Workspace Cube
1.5.1 Dimension Order
1.5.2 Creating and Modifying a Composite Specification
1.6 Aggregating the Data in an Analytic Workspace
1.6.1 Creating an Aggregation Specification
1.6.2 Choosing an Aggregation Method
1.7 Creating Relational Access to the Workspace Cube
1.7.1 Procedure: Generate and Run the Enablement Scripts
1.7.2 Procedure: Run the Enablement Scripts Automatically
1.7.3 The OLAP API Enabler Procedures
1.7.4 Enablement Metadata in the Analytic Workspace
1.7.5 Disabling Relational Access
1.7.6 Default Dimension View Names
1.7.7 Default Fact View Names
1.7.8 Column Structure of Dimension Enablement Views
1.7.8.1 Sample Dimension View
1.7.8.2 Grouping ID Column
1.7.9 Column Structure of Enablement Fact Views
1.7.10 Example: Enable a Workspace Cube for Access by the OLAP API

2 Creating OLAP Catalog Metadata with CWM2

2.1 OLAP Metadata Entities
2.2 Creating a Dimension
2.2.1 Procedure: Create an OLAP Dimension
2.2.2 Example: Create a Product Dimension
2.2.3 Procedure: Create a Time Dimension
2.2.4 Example: Create a Time Dimension
2.3 Creating a Cube
2.3.1 Procedure: Create a Cube
2.3.2 Example: Create a Costs Cube
2.4 Mapping OLAP Metadata
2.4.1 Mapping to Columns
2.4.1.1 Mapping Dimensions
2.4.1.2 Mapping Measures
2.4.2 Joining Fact Tables with Dimension Tables
2.5 Validating and Committing OLAP Metadata
2.5.1 Validating OLAP Metadata
2.5.2 Viewing Validity Status
2.5.3 Refreshing Metadata Tables for the OLAP API
2.6 Invoking the Procedures
2.6.1 Security Checks and Error Conditions
2.6.2 Size Requirements for Parameters
2.6.3 Case Requirements for Parameters
2.7 Directing Output
2.8 Viewing OLAP Metadata

3 Active Catalog Views

3.1 Standard Form Active Catalog
3.1.1 Standard Form Classes
3.1.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.6 ALL_OLAP2_AW_CUBES
3.7 ALL_OLAP2_AW_CUBE_AGG_LVL
3.8 ALL_OLAP2_AW_CUBE_AGG_MEAS
3.9 ALL_OLAP2_AW_CUBE_AGG_OP
3.10 ALL_OLAP2_AW_CUBE_AGG_SPECS
3.11 ALL_OLAP2_AW_CUBE_DIM_USES
3.12 ALL_OLAP2_AW_CUBE_MEASURES
3.13 ALL_OLAP2_AW_DIMENSIONS
3.14 ALL_OLAP2_AW_DIM_HIER_LVL_ORD
3.15 ALL_OLAP2_AW_DIM_LEVELS
3.16 ALL_OLAP2_AW_PHYS_OBJ
3.17 ALL_OLAP2_AW_PHYS_OBJ_PROP

4 Analytic Workspace Maintenance Views

4.1 Building and Maintaining Analytic Workspaces
4.2 Example: Query Load and Enablement Parameters for Workspace Dimensions
4.3 Summary of Analytic Workspace Maintenance Views
4.4 ALL_AW_CUBE_AGG_LEVELS
4.5 ALL_AW_CUBE_AGG_MEASURES
4.6 ALL_AW_CUBE_AGG_PLANS
4.7 ALL_AW_CUBE_ENABLED_HIERCOMBO
4.8 ALL_AW_CUBE_ENABLED_VIEWS
4.9 ALL_AW_DIM_ENABLED_VIEWS
4.10 ALL_AW_LOAD_CUBES
4.11 ALL_AW_LOAD_CUBE_DIMS
4.12 ALL_AW_LOAD_CUBE_FILTERS
4.13 ALL_AW_LOAD_CUBE_MEASURES
4.14 ALL_AW_LOAD_CUBE_PARMS
4.15 ALL_AW_LOAD_DIMENSIONS
4.16 ALL_AW_LOAD_DIM_FILTERS
4.17 ALL_AW_LOAD_DIM_PARMS
4.18 ALL_AW_OBJ
4.19 ALL_AW_PROP

5 OLAP Catalog Metadata Views

5.1 Access to OLAP Catalog Views
5.2 Views of the Dimensional Model
5.3 Views of Mapping Information
5.4 ALL_OLAP2_AGGREGATION_USES
5.5 ALL_OLAP2_CATALOGS
5.6 ALL_OLAP2_CATALOG_ENTITY_USES
5.7 ALL_OLAP2_CUBES
5.8 ALL_OLAP2_CUBE_DIM_USES
5.9 ALL_OLAP2_CUBE_MEASURES
5.10 ALL_OLAP2_CUBE_MEASURE_MAPS
5.11 ALL_OLAP2_CUBE_MEAS_DIM_USES
5.12 ALL_OLAP2_DIMENSIONS
5.13 ALL_OLAP2_DIM_ATTRIBUTES
5.14 ALL_OLAP2_DIM_ATTR_USES
5.15 ALL_OLAP2_DIM_HIERARCHIES
5.16 ALL_OLAP2_DIM_HIER_LEVEL_USES
5.17 ALL_OLAP2_DIM_LEVELS
5.18 ALL_OLAP2_DIM_LEVEL_ATTRIBUTES
5.19 ALL_OLAP2_DIM_LEVEL_ATTR_MAPS
5.20 ALL_OLAP2_ENTITY_DESC_USES
5.21 ALL_OLAP2_ENTITY_EXT_PARMS
5.22 ALL_OLAP2_ENTITY_PARAMETERS
5.23 ALL_OLAP2_FACT_LEVEL_USES
5.24 ALL_OLAP2_FACT_TABLE_GID
5.25 ALL_OLAP2_HIER_CUSTOM_SORT
5.26 ALL_OLAP2_JOIN_KEY_COLUMN_USES
5.27 ALL_OLAP2_LEVEL_KEY_COL_USES

6 OLAP Fixed Views

6.1 System Tables Referenced by OLAP Fixed Views
6.2 Summary of OLAP Fixed Views
6.3 V$AW_AGGREGATE_OP
6.4 V$AW_ALLOCATE_OP
6.5 V$AW_CALC
6.6 V$AW_LONGOPS
6.7 V$AW_OLAP
6.8 V$AW_SESSION_INFO

7 CWM2_OLAP_CATALOG

7.1 Understanding Measure Folders
7.2 Example: Creating a Measure Folder
Summary of CWM2_OLAP_CATALOG Subprograms
ADD_CATALOG_ENTITY Procedure
CREATE_CATALOG Procedure
DROP_CATALOG Procedure
LOCK_CATALOG Procedure
REMOVE_CATALOG_ENTITY Procedure
SET_CATALOG_NAME Procedure
SET_DESCRIPTION Procedure
SET_PARENT_CATALOG Procedure

8 CWM2_OLAP_CLASSIFY

8.1 OLAP Catalog Metadata Descriptors
8.2 Example: Creating Descriptors
Summary of CWM2_OLAP_CLASSIFY Subprograms
ADD_ENTITY_CARDINALITY_USE
ADD_ENTITY_DEFAULTMEMBER_USE
ADD_ENTITY_DENSEINDICATOR_USE
ADD_ENTITY_DESCRIPTOR_USE
ADD_ENTITY_FACTJOIN_USE
REMOVE_ENTITY_DESCRIPTOR_USE

9 CWM2_OLAP_CUBE

9.1 Understanding Cubes
9.2 Example: Creating a Cube
Summary of CWM2_OLAP_CUBE Subprograms
ADD_DIMENSION_TO_CUBE Procedure
CREATE_CUBE Procedure
DROP_CUBE Procedure
LOCK_CUBE Procedure
REMOVE_DIMENSION_FROM_CUBE Procedure
SET_AGGREGATION_OPERATOR Procedure
SET_CUBE_NAME Procedure
SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure
SET_DESCRIPTION Procedure
SET_DISPLAY_NAME Procedure
SET_MV_SUMMARY_CODE Procedure
SET_SHORT_DESCRIPTION Procedure

10 CWM2_OLAP_DIMENSION

10.1 Understanding Dimensions
10.2 Example: Creating a CWM2 Dimension
Summary of CWM2_OLAP_DIMENSION Subprograms
CREATE_DIMENSION Procedure
DROP_DIMENSION Procedure
LOCK_DIMENSION Procedure
SET_DEFAULT_DISPLAY_HIERARCHY Procedure
SET_DESCRIPTION Procedure
SET_DIMENSION_NAME Procedure
SET_DISPLAY_NAME Procedure
SET_PLURAL_NAME Procedure
SET_SHORT_DESCRIPTION Procedure

11 CWM2_OLAP_DIMENSION_ATTRIBUTE

11.1 Understanding Dimension Attributes
11.2 Example: Creating a Dimension Attribute
Summary of CWM2_OLAP_DIMENSION_ATTRIBUTE Subprograms
CREATE_DIMENSION_ATTRIBUTE Procedure
DROP_DIMENSION_ATTRIBUTE Procedure
LOCK_DIMENSION_ATTRIBUTE Procedure
SET_DESCRIPTION Procedure
SET_DIMENSION_ATTRIBUTE_NAME Procedure
SET_DISPLAY_NAME Procedure
SET_SHORT_DESCRIPTION Procedure

12 CWM2_OLAP_HIERARCHY

12.1 Understanding Hierarchies
12.2 Example: Creating a Hierarchy
Summary of CWM2_OLAP_HIERARCHY Subprograms
CREATE_HIERARCHY Procedure
DROP_HIERARCHY Procedure
LOCK_HIERARCHY Procedure
SET_DESCRIPTION Procedure
SET_DISPLAY_NAME Procedure
SET_HIERARCHY_NAME Procedure
SET_SHORT_DESCRIPTION Procedure
SET_SOLVED_CODE Procedure

13 CWM2_OLAP_LEVEL

13.1 Understanding Levels
13.2 Example: Creating a Level
Summary of CWM2_OLAP_LEVEL Subprograms
ADD_LEVEL_TO_HIERARCHY Procedure
CREATE_LEVEL Procedure
DROP_LEVEL Procedure
LOCK_LEVEL Procedure
REMOVE_LEVEL_FROM_HIERARCHY Procedure
SET_DESCRIPTION Procedure
SET_DISPLAY_NAME Procedure
SET_LEVEL_NAME Procedure
SET_PLURAL_NAME Procedure
SET_SHORT_DESCRIPTION Procedure

14 CWM2_OLAP_LEVEL_ATTRIBUTE

14.1 Understanding Level Attributes
14.2 Example: Creating Level Attributes
Summary of CWM2_OLAP_LEVEL_ATTRIBUTE Subprograms
CREATE_LEVEL_ATTRIBUTE Procedure
DROP_LEVEL_ATTRIBUTE Procedure
LOCK_LEVEL_ATTRIBUTE Procedure
SET_DESCRIPTION Procedure
SET_DISPLAY_NAME Procedure
SET_LEVEL_ATTRIBUTE_NAME Procedure
SET_SHORT_DESCRIPTION Procedure

15 CWM2_OLAP_MEASURE

15.1 Understanding Measures
15.2 Example: Creating a Measure
Summary of CWM2_OLAP_MEASURE Subprograms
CREATE_MEASURE Procedure
DROP_MEASURE Procedure
LOCK_MEASURE Procedure
SET_DESCRIPTION Procedure
SET_DISPLAY_NAME Procedure
SET_MEASURE_NAME Procedure
SET_SHORT_DESCRIPTION Procedure

16 CWM2_OLAP_METADATA_REFRESH

16.1 Views of Cached OLAP Catalog Metadata
16.2 Views of Cached Active Catalog Metadata
Summary of CWM2_OLAP_METADATA_REFRESH Subprograms
MR_REFRESH Procedure
MR_AC_REFRESH Procedure

17 CWM2_OLAP_PC_TRANSFORM

17.1 Prerequisites
17.2 Parent-Child Dimensions
17.3 Solved, Level-Based Dimensions
17.4 Example: Creating a Solved, Level-Based Dimension Table
17.4.1 Grouping ID Column
17.4.2 Embedded Total Key Column
Summary of CWM2_OLAP_PC_TRANSFORM Subprograms
CREATE_SCRIPT Procedure

18 CWM2_OLAP_TABLE_MAP

18.1 Understanding OLAP Metadata Mapping
18.2 Example: Mapping a Dimension
18.3 Example: Mapping a Cube
Summary of CWM2_OLAP_TABLE_MAP Subprograms
MAP_DIMTBL_HIERLEVELATTR Procedure
MAP_DIMTBL_HIERLEVEL Procedure
MAP_DIMTBL_HIERSORTKEY Procedure
MAP_DIMTBL_LEVELATTR Procedure
MAP_DIMTBL_LEVEL Procedure
MAP_FACTTBL_LEVELKEY Procedure
MAP_FACTTBL_MEASURE Procedure
REMOVEMAP_DIMTBL_HIERLEVELATTR Procedure
REMOVEMAP_DIMTBL_HIERLEVEL Procedure
REMOVEMAP_DIMTBL_HIERSORTKEY Procedure
REMOVEMAP_DIMTBL_LEVELATTR Procedure
REMOVEMAP_DIMTBL_LEVEL Procedure
REMOVEMAP_FACTTBL_LEVELKEY Procedure
REMOVEMAP_FACTTBL_MEASURE Procedure

19 CWM2_OLAP_VALIDATE

19.1 About OLAP Catalog Metadata Validation
19.1.1 Structural Validation
19.1.1.1 Cubes
19.1.1.2 Dimensions
19.1.2 Mapping Validation
19.1.2.1 Cubes
19.1.2.2 Dimensions
19.1.3 Validation Type
Summary of CWM2_OLAP_VALIDATE Subprograms
VALIDATE_ALL_CUBES Procedure
VALIDATE_ALL_DIMENSIONS Procedure
VALIDATE_CUBE Procedure
VALIDATE_DIMENSION Procedure
VALIDATE_OLAP_CATALOG Procedure

20 CWM2_OLAP_VERIFY_ACCESS

20.1 Validating the Accessibility of an OLAP Cube
Summary of CWM2_OLAP_VERIFY_ACCESS Subprograms
VERIFY_CUBE_ACCESS Procedure

21 DBMS_AW

21.1 Embedding OLAP DML in SQL Statements
21.1.1 Methods for Executing OLAP DML Commands
21.1.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.3.1 Aggregation Facilities within the Workspace
21.3.2 Example: Using the ADVISE_REL Procedure
Summary of DBMS_AW Subprograms
ADVISE_CUBE Procedure
ADVISE_REL Procedure
AW_ATTACH Procedure
AW_COPY Procedure
AW_CREATE Procedure
AW_DELETE
AW_DETACH Procedure
AW_RENAME Procedure
AW_UPDATE Procedure
EXECUTE Procedure
GETLOG Function
INTERP Function
INTERPCLOB Function
INTERP_SILENT Procedure
OLAP_EXPRESSION Function
OLAP_EXPRESSION_BOOL Function
OLAP_EXPRESSION_DATE Function
OLAP_EXPRESSION_TEXT Function
PRINTLOG Procedure

22 DBMS_AW_UTILITIES

22.1 About Custom Measures
22.2 Querying Custom Measures
22.2.1 CWM2$_AW_PERM_CUST_MEAS_MAP
22.2.2 CWM2$_AW_TEMP_CUST_MEAS_MAP
22.3 Example: Creating a Custom Measure
Summary of DBMS_AW_UTILITIES Subprograms
CREATE_CUSTOM_MEASURE Procedure
DELETE_CUSTOM_MEASURE Procedure
UPDATE_CUSTOM_MEASURE Procedure

23 DBMS_AWM

23.1 Parameters of DBMS_AWM Subprograms
Summary of DBMS_AWM Subprograms
ADD_AWCOMP_SPEC_COMP_MEMBER Procedure
ADD_AWCOMP_SPEC_MEMBER Procedure
ADD_AWCUBEAGG_SPEC_LEVEL Procedure
ADD_AWCUBEAGG_SPEC_MEASURE Procedure
ADD_AWCUBELOAD_SPEC_COMP Procedure
ADD_AWCUBELOAD_SPEC_FILTER Procedure
ADD_AWCUBELOAD_SPEC_MEASURE Procedure
ADD_AWDIMLOAD_SPEC_FILTER Procedure
AGGREGATE_AWCUBE Procedure
CREATE_AWCOMP_SPEC Procedure
CREATE_AWCUBE Procedure
CREATE_AWCUBE_ACCESS Procedure
CREATE_AWCUBE_ACCESS_FULL Procedure
CREATE_AWCUBEAGG_SPEC Procedure
CREATE_AWCUBELOAD_SPEC Procedure
CREATE_AWDIMENSION Procedure
CREATE_AWDIMENSION_ACCESS Procedure
CREATE_AWDIMENSION_ACCESS_FULL Procedure
CREATE_AWDIMLOAD_SPEC Procedure
DELETE_AWCOMP_SPEC Procedure
DELETE_AWCOMP_SPEC_MEMBER Procedure
DELETE_AWCUBE_ACCESS Procedure
DELETE_AWCUBE_ACCESS_ALL Procedure
DELETE_AWCUBEAGG_SPEC Procedure
DELETE_AWCUBEAGG_SPEC_LEVEL Procedure
DELETE_AWCUBEAGG_SPEC_MEASURE Procedure
DELETE_AWCUBELOAD_SPEC Procedure
DELETE_AWCUBELOAD_SPEC_COMP Procedure
DELETE_AWCUBELOAD_SPEC_FILTER Procedure
DELETE_AWCUBELOAD_SPEC_MEASURE Procedure
DELETE_AWDIMENSION_ACCESS Procedure
DELETE_AWDIMENSION_ACCESS_ALL Procedure
DELETE_AWDIMLOAD_SPEC Procedure
DELETE_AWDIMLOAD_SPEC_FILTER Procedure
REFRESH_AWCUBE Procedure
REFRESH_AWCUBE_VIEW_NAME Procedure
REFRESH_AWDIMENSION Procedure
REFRESH_AWDIMENSION_VIEW_NAME Procedure
SET_AWCOMP_SPEC_CUBE Procedure
SET_AWCOMP_SPEC_MEMBER_NAME Procedure
SET_AWCOMP_SPEC_MEMBER_POS Procedure
SET_AWCOMP_SPEC_MEMBER_SEG Procedure
SET_AWCOMP_SPEC_NAME Procedure
SET_AWCUBE_VIEW_NAME Procedure
SET_AWCUBEAGG_SPEC_AGGOP Procedure
SET_AWCUBELOAD_SPEC_CUBE Procedure
SET_AWCUBELOAD_SPEC_LOADTYPE Procedure
SET_AWCUBELOAD_SPEC_NAME Procedure
SET_AWCUBELOAD_SPEC_PARAMETER Procedure
SET_AWDIMENSION_VIEW_NAME Procedure
SET_AWDIMLOAD_SPEC_DIMENSION Procedure
SET_AWDIMLOAD_SPEC_LOADTYPE Procedure
SET_AWDIMLOAD_SPEC_NAME Procedure
SET_AWDIMLOAD_SPEC_PARAMETER Procedure

24 DBMS_ODM

24.1 Summary Management with Materialized Views
24.1.1 Grouping Sets
24.2 Summarizing the Fact Table
24.2.1 Procedure: Automatically Generate the Materialized Views
24.2.2 Procedure: Manually Generate the Materialized Views
24.3 Example: Create Materialized Views for a Sales Cube
Summary of DBMS_ODM Subprograms
CREATECUBELEVELTUPLE Procedure
CREATEDIMLEVTUPLE Procedure
CREATEDIMMV_GS Procedure
CREATEFACTMV_GS Procedure
CREATESTDFACTMV Procedure

25 OLAP_API_SESSION_INIT

25.1 Initialization Parameters for the OLAP API
25.2 Viewing the Configuration Table
25.2.1 ALL_OLAP_ALTER_SESSION View
Summary of OLAP_API_SESSION_INIT Subprograms
ADD_ALTER_SESSION Procedure
CLEAN_ALTER_SESSION Procedure
DELETE_ALTER_SESSION Procedure

26 OLAP_TABLE

OLAP_TABLE Syntax
Syntax
Parameters
Order of Processing in OLAP_TABLE
OLAP_TABLE Examples
Creating Views for the BI Beans and OLAP API
Creating a Dimension View
Creating a Measure View
Using OLAP_TABLE with the FETCH Command

Index