Oracle9i OLAP User's Guide -- Contents

Audience

Organization

Related Documentation

Conventions

Documentation Accessibility

Oracle9i Release 2 (9.2) New Features in Oracle OLAP

Why OLAP?

Analytical Processing Answers Business Questions

Types of OLAP Applications

Analytical Reporting

Predictive Analysis

The Oracle9i Integrated Relational-Multidimensional Database

Components of Oracle OLAP

Applications Access to Oracle OLAP

What Is the OLAP DML?

Extensive Analytic Capabilities

Features of the Multidimensional Model

Basic Categories of OLAP DML Commands

File Reading and Writing

Financial Operations

Forecasts and Regressions

Models

Numeric Computations

Statistical Operations

Text Manipulation

Time Series Manipulation

Methods of Executing OLAP DML Commands

OLAP Worksheet: The OLAP DML Development Tool

Embedding OLAP DML Commands in Programs

Building SQL-Based OLAP Applications

Methods of Accessing Multidimensional Data From SQL

Embedding OLAP DML Commands in SQL

Building Analytical Java Applications

About Java

Deploying Java Applications

The Java Solution for OLAP

Oracle Java Development Environment

Introducing the BI Beans

Thick-Client Configuration

Thin-Client Configuration

Understanding the OLAP API

How the OLAP API Accesses Multidimensional Data

Intelligent Caching

Calculation Capabilities

Overview

Preparing a Database for the OLAP API

Types of Data Stored in a Data Warehouse

Historical Data

Derived Data

Metadata

Data Structures in Relational and Multidimensional Data Stores

Relational Table Storage

Multidimensional Table Storage

Temporary and Persistent Analytic Workspaces

About Star, Snowflake, Parent/Child, and Multidimensional Schemas

Choosing a Schema for Your Data

OLAP Metadata Model

Mapping Data Objects to Metadata Objects

Measures

Dimensions

Time Dimensions

Hierarchical Dimensions

Attributes

Choosing the Right Metadata Creation Method

Basic Star or Snowflake Schema

Dimension Tables with Complex Hierarchies

Multidimensional Data and Parent-Child Dimensions

Multidimensional Data Structures in the OLAP Catalog

Accessing the OLAP Catalog

Organization of the OLAP Catalog

CWM and CWM2

Logical Steps for Creating the Metadata

Creating Metadata Using Oracle Enterprise Manager

Procedure: Accessing OLAP Management

Defining Metadata for Dimension Tables

Defining Metadata for Fact Tables

Viewing Cubes

Procedure: Viewing cubes

Creating Metadata Using PL/SQL

CWM2 Packages

Administration Overview

Initialization Parameters for Oracle OLAP

Initialization Parameters for the OLAP API

Creating Tablespaces for Analytic Workspaces

Creating a Tablespace for Rollbacks

Creating a Temporary Tablespace

Creating Tablespaces for Analytic Workspaces

Querying the Size of an Analytic Workspace

Setting Up User Names

Controlling Access to External Files

Creating a Directory Alias

Granting Access Rights to a Directory Alias

Example: Creating and Using a Directory Alias

Understanding Data Storage

User-Owned Tables

System Tables

Monitoring Performance

System Tables Referenced by OLAP Performance Views

Summary of OLAP Performance Views

V$AW_CALC

V$AW_OLAP

V$AW_SESSION_INFO

Overview

Summary of OLAP_API_SESSION_INIT Subprograms

ADD_ALTER_SESSION Procedure

DELETE_ALTER_SESSION Procedure

CLEAN_ALTER_SESSION Procedure

Syntax

Examples

ALL_OLAP_ALTER_SESSION View

Process Overview

Creating an Analytic Workspace Using the CNV_CWM.TO.ECM Program

Manually Creating an Analytic Workspace

CNV_CWM.TO.ECM Program

GENSQLOBJS Program

Analytic Workspace Metadata Catalogs

Level Attribute Catalog

References to OLAP Catalog Metadata

Summary of DBMS_AW Subprograms

Accessing Multidimensional Data Using the OLAP_TABLE Function

Preparing an Analytic Workspace for Access by OLAP_TABLE

Creating Object Type Definitions Used by OLAP_TABLE

Designing the Objects

Creating Type Definitions for Multidimensional Data

Syntax: OLAP_TABLE Function

Example: Using the OLAP_TABLE Function

Access to OLAP Catalog Union Views

Summary of OLAP Catalog Union Views

ALL_OLAP2_CATALOG_ENTITY_USES

ALL_OLAP2_CATALOGS

ALL_OLAP2_CUBE_DIM_USES

ALL_OLAP2_CUBE_MEAS_DIM_USES

ALL_OLAP2_CUBE_MEASURE_MAPS

ALL_OLAP2_CUBE_MEASURES

ALL_OLAP2_CUBES

ALL_OLAP2_DIM_ATTR_USES

ALL_OLAP2_DIM_ATTRIBUTES

ALL_OLAP2_DIM_HIER_LEVEL_USES

ALL_OLAP2_DIM_HIERARCHIES

ALL_OLAP2_DIM_LEVEL_ATTR_MAPS

ALL_OLAP2_DIM_LEVEL_ATTRIBUTES

ALL_OLAP2_DIM_LEVELS

ALL_OLAP2_DIMENSIONS

ALL_OLAP2_ENTITY_DESC_USES

ALL_OLAP2_FACT_LEVEL_USES

ALL_OLAP2_FACT_TABLE_GID

ALL_OLAP2_HIER_CUSTOM_SORT

ALL_OLAP2_JOIN_KEY_COLUMN_USES

ALL_OLAP2_LEVEL_KEY_COLUMN_USES

Access to OLAP Catalog (CWM2) Views

System Tables Referenced by OLAP Catalog (CWM2) Views

Summary of OLAP Catalog (CWM2)Views

ALL$OLAP2_CATALOG_ENTITY_USES

ALL$OLAP2_CATALOGS

ALL$OLAP2_CUBE_DIM_USES

ALL$OLAP2_CUBE_MEAS_DIM_USES

ALL$OLAP2_CUBE_MEASURE_MAPS

ALL$OLAP2_CUBE_MEASURES

ALL$OLAP2_CUBES

ALL$OLAP2_DIM_ATTR_USES

ALL$OLAP2_DIM_ATTRIBUTES

ALL$OLAP2_DIM_HIER_LEVEL_USES

ALL$OLAP2_DIM_HIERARCHIES

ALL$OLAP2_DIM_LEVEL_ATTR_MAPS

ALL$OLAP2_DIM_LEVEL_ATTRIBUTES

ALL$OLAP2_DIM_LEVELS

ALL$OLAP2_DIMENSIONS

ALL$OLAP2_ENTITY_DESC_USES

ALL$OLAP2_FACT_LEVEL_USES

ALL$OLAP2_FACT_TABLE_GID

ALL$OLAP2_HIER_CUSTOM_SORT

ALL$OLAP2_JOIN_KEY_COLUMN_USES

ALL$OLAP2_LEVEL_KEY_COLUMN_USES

Summary of Analytic Workspace Object Views

ALL$OLAP2_AW_PHYS_OBJ

ALL$OLAP2_AW_PHYS_OBJ_EXT

ALL$OLAP2_AW_PHYS_OBJ_REL_OBJ

ALL$OLAP2_AW_PHYS_OBJ_PROP

ALL$OLAP2_AW_MAP_DIM_USE

ALL$OLAP2_AW_MAP_MEAS_USE

ALL$OLAP2_AW_MAP_HIER_USE

ALL$OLAP2_AW_MAP_LVL_USE

ALL$OLAP2_AW_MAP_ATTR_USE

Prerequisites

Process Overview

Preparing the Analytic Workspace

Measures

Dimensions

Hierarchies

Hierarchy Dimensions

Parent-Child Relations

Level Dimensions

In Hierarchy Variables

Specifying the Source and Target Objects

Defining Dimension Views

Defining Fact Views

Example: Creating Views

Example: Input Files for Mapping Variables to Views

Geography Dimension Standard Hierarchy View

Product Dimension View

Channel Dimension View

Time Standard Hierarchy Input File

Sales and Costs Fact Views

Example: Script for the Product View

Example: Product View

Summary of CWM2_OLAP_AW_ACCESS Subprograms

CreateAWAccessStructures_FR Procedure

CreateAWAccessStructures Procedure

Summary of CWM2_OLAP_PC_TRANSFORM Subprograms

CREATE_SCRIPT Procedure

Understanding Dimensions

Dimension Table Requirements

Normal Dimensions and Time Dimensions

Dependencies Between Dimensional Entities

Creating Dimensions

Completing the Dimension's Metadata

Verifying Dimensional Metadata

Common Logic in CWM2_OLAP_DIMENSION Subprograms

Case Requirements for Subprogram Parameters

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

Understanding Dimension Attributes

Creating Dimension Attributes

Completing the Dimension's Metadata

Common Logic in CWM2_OLAP_DIMENSION_ATTRIBUTE Subprograms

Case Requirements for Subprogram Parameters

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

Understanding Hierarchies

Creating Hierarchies

Completing the Dimension's Metadata

Common Logic in CWM2_OLAP_HIERARCHY Subprograms

Case Requirements for Subprogram Parameters

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

Understanding Levels

Creating Levels

Completing the Dimension's Metadata

Common Logic in CWM2_OLAP_LEVEL Subprograms

Case Requirements for Subprogram Parameters

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

Understanding Level Attributes

Creating Level Attributes

Completing the Dimension's Metadata

Common Logic in CWM2_OLAP_LEVEL_ATTRIBUTE Subprograms

Case Requirements for Subprogram Parameters

Summary of CWM2_OLAP_LEVEL_ATTRIBUTE Subprograms

CREATE_LEVEL_ATTRIBUTE

DROP_LEVEL_ATTRIBUTE Procedure

LOCK_LEVEL_ATTRIBUTE Procedure

SET_DESCRIPTION Procedure

SET_DISPLAY_NAME Procedure

SET_LEVEL_ATTRIBUTE_NAME Procedure

SET_SHORT_DESCRIPTION Procedure

Understanding Cubes

Cubes and Measures

Fact Table Requirements

Fact Table Key Columns

Creating Cubes

Creating a Cube's Dimensions

Completing the Cube's Metadata

Verifying the Metadata for a Cube

Common Logic in CWM2_OLAP_CUBE Subprograms

Case Requirements for Subprogram Parameters

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

Understanding Measures

Creating Measures

Creating a Cube for the Measure

Completing the Measure's Metadata

Common Logic in CWM2_OLAP_MEASURE Subprograms

Case Requirements for Subprogram Parameters

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

Understanding OLAP Metadata Mapping

Mapping Logical Metadata Entities

Joining Fact Tables with Dimension Tables

Common Logic in CWM2_OLAP_TABLE_MAP Subprograms

Case Requirements for Subprogram Parameters

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

Understanding AW Object Metadata Entities

Common Logic in CWM2_OLAP_AW_OBJECT Subprograms

Case Requirements for Subprogram Parameters

Summary of CWM2_OLAP_AW_OBJECT Subprograms

CREATE_AW_OBJECT Procedure

CREATE_AW_OBJECT_INFO Procedure

CREATE_AW_OBJECT_RELATED_OBJ Procedure

CREATE_AW_OBJECT_PROPERTY Procedure

DROP_AW_OBJECT Procedure

DROP_AW_OBJECT_INFO Procedure

DROP_AW_OBJECT_RELATED_OBJ Procedure

DROP_AW_OBJECT_PROPERTY Procedure

Understanding AW Object Metadata Mapping

Common Logic in CWM2_OLAP_AW_MAP Subprograms

Case Requirements for Subprogram Parameters

Summary of CWM2_OLAP_AW_MAP Subprograms

MAP_AW_ATTRIBUTE Procedure

MAP_AW_DIMENSION Procedure

MAP_AW_HIERARCHY Procedure

MAP_AW_LEVEL Procedure

MAP_AW_MEASURE Procedure

REMOVEMAP_AW_ATTRIBUTE Procedure

REMOVEMAP_AW_DIMENSION Procedure

REMOVEMAP_AW_HIERARCHY Procedure

REMOVEMAP_AW_LEVEL Procedure

REMOVEMAP_AW_MEASURE Procedure

Understanding the OLAP Classification System

Summary of CWM_CLASSIFY Subprograms

ADD_CATALOG_ENTITY Procedure

ADD_DESCRIPTOR_ENTITY_TYPE Procedure

ADD_ENTITY_DESCRIPTOR_USE Procedure

CREATE_CATALOG Function

CREATE_DESCRIPTOR Function

CREATE_DESCRIPTOR_TYPE Procedure

DROP_CATALOG Procedure

DROP_DESCRIPTOR Procedure

DROP_DESCRIPTOR_TYPE Procedure

LOCK_CATALOG Procedure

REMOVE_CATALOG_ENTITY Procedure

REMOVE_DESCRIPTOR_ENTITY_TYPE Procedure

REMOVE_ENTITY_DESCRIPTOR_USE Procedure

SET_CATALOG_DESCRIPTION Procedure

SET_CATALOG_PARENT Procedure

Optimizing the Database for OLAP

About Materialized Views

Summary Management Options

Grouping Sets

Concatenated Rollup

Materialized Views and OLAP Metadata

CWM Metadata

CWM2 Metadata

Dimension Materialized Views

Creating Dimension Materialized Views

Number of Dimension Materialized Views

Fact Materialized Views

Creating Fact Materialized Views

Number of Fact Materialized Views

Choosing the Right Summary Management Solution

Summary Management for CWM Metadata

Summary Management With a Graphical User Interface

Summary Management for Multiple Hierarchies

Build Times

Partial Materialization

MV Size

Lineage (Key)

Query Performance

Creating Materialized Views for Dimensions

Statistics and Bitmap Indexes

Statistics

Bitmap Indexes

The CREATE Statement for a Dimension Materialized View

Sample Script for the TIMES_DIM Dimension

Table Structure of Sample TIMES_DIM Dimension Materialized View

Using the DBMS_ODM Package

Procedure: Create and Run Scripts to Generate Grouping Set Materialized Views

Partitioning, Statistics, and Indexes

Partitioning

Statistics

Bitmap Indexes

Sample Script for the COST Cube

Summary of DBMS_ODM Subprograms

CREATEDIMLEVTUPLE Procedure

CREATECUBELEVELTUPLE Procedure

CREATEFACTMV_GS Procedure

CREATEDIMMV_GS Procedure

Using the OLAP Summary Advisor Wizard

Procedure: Run the OLAP Summary Advisor

Partitioning, Statistics, and Indexes

Partitioning

Statistics

Bitmap Indexes

The MV CREATE Statement With Concatenated Rollup

Sample Script for the COST Cube

Administration

Authentication of Users

Management Tools

Data Transfer

Localization

Applications Support

Programming Environment

Communications

Metadata

Programming Language Changes

New Commands

Obsolete Commands

UPDATE and COMMIT

How to Upgrade an Express Database