Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

1 Creating Analytic Workspaces with DBMS_AWM

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.6 V$AW_LONGOPS

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.2 Dimensions

19.1.2 Mapping Validation

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_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

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