Skip Headers

Oracle® OLAP Application Developer's Guide
10g Release 1 (10.1)

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

Go to next page
View PDF


Title and Copyright Information

Send Us Your Comments


Intended Audience
Documentation Accessibility
Related Documents

Part I Fundamentals

1 Overview

1.1 OLAP Technology Within the Oracle Database
1.1.1 Problems Maintaining Two Distinct Systems
1.1.2 Full Integration of Multidimensional Technology
1.2 Using OLAP to Answer Business Questions
1.3 Common Analytical Applications
1.4 Deciding When to Use Analytic Workspaces
1.5 Working With Oracle OLAP
1.5.1 OLAP Analytic Engine
1.5.2 Analytic Workspaces
1.5.3 Analytic Workspace Manager
1.5.4 OLAP Worksheet
1.5.5 SQL Interface to OLAP
1.5.6 OLAP DML
1.5.7 OLAP Catalog
1.5.8 Analytic Workspace Java APIs
1.5.9 OLAP API
1.5.10 Oracle Enterprise Manager
1.5.11 Oracle Warehouse Builder
1.6 Process Overview: Creating and Maintaining Analytic Workspaces

2 The Multidimensional Data Model

2.1 The Logical Multidimensional Data Model
2.1.1 Logical Cubes
2.1.2 Logical Measures
2.1.3 Logical Dimensions
2.1.4 Logical Hierarchies and Levels
2.1.5 Logical Attributes
2.2 The Relational Implementation of the Model
2.2.1 Dimension Tables
2.2.2 Fact Tables
2.2.3 Materialized Views
2.3 The Analytic Workspace Implementation of the Model
2.3.1 Multidimensional Data Storage in Analytic Workspaces
2.3.2 Database Standard Form Analytic Workspaces
2.3.3 Analytic Workspace Dimensions Use of Dimensions in Standard Form Analytic Workspaces
2.3.4 Analytic Workspace Variables Use of Variables to Store Measures Use of Variables to Store Attributes
2.3.5 Analytic Workspace Formulas
2.3.6 Analytic Workspace Relations

3 The Sample Schema

3.1 Case Study Scenario
3.1.1 Reporting Requirements
3.1.2 Business Goals
3.1.3 Information Requirements Business Analysis Questions What products are profitable? Who are our customers, and what and how are they buying? What accounts are most profitable? What is the performance of each distribution channel? Is there still a seasonal variance to the business? Summary of Information Requirements
3.1.4 Identifying Required Business Facts
3.1.5 Designing a Logical Data Model for Global Computing Identifying Dimensions Identifying Levels Identifying Hierarchies Identifying Stored Measures
3.2 The Global Star Schema
3.2.1 Dimension Table: TIME_DIM
3.2.2 Dimension Table: CUSTOMER_DIM
3.2.3 Dimension Table: PRODUCT_DIM
3.2.4 Dimension Table: CHANNEL_DIM
3.3 Mapping the Global Schema to an Analytic Workspace
3.3.1 Global Product Dimension Mapping
3.3.2 Global Time Dimension Mapping
3.3.3 Global Price Cube Mapping

4 Developing Java Applications for OLAP

4.1 Building Analytical Java Applications
4.1.1 About Java
4.1.2 The Java Solution for OLAP
4.1.3 Oracle Java Development Environment
4.2 Introducing the BI Beans
4.2.1 Metadata
4.2.2 Navigation
4.2.3 Formatting
4.2.4 Graphs
4.2.5 Crosstabs
4.2.6 Tables
4.2.7 Data Beans
4.2.8 Wizards
4.3 Understanding the OLAP API
4.3.1 How the OLAP API Accesses Multidimensional Data
4.3.2 Calculation Capabilities
4.3.3 Intelligent Caching
4.4 Managing Data Sources for the BI Beans and OLAP API

Part II Fundamentals of Creating and Using Analytic Workspaces

5 Defining a Logical Multidimensional Model

5.1 Introduction to OLAP Metadata
5.1.1 Creating Metadata for Your Source Data For Source Data in a Basic Star or Snowflake Schema For Dimension Tables with Complex Hierarchies For Other Schema Configurations
5.1.2 Creating Metadata for Your Analytic Workspace
5.1.3 Creating Metadata for Your Applications
5.2 Overview of the OLAP Catalog
5.2.1 OLAP Catalog Components About CWM1 About CWM2
5.2.2 Steps for Creating OLAP Metadata
5.3 Choosing a Tool for Creating OLAP Catalog Metadata
5.3.1 Creating Metadata for an Analytic Workspace
5.4 Creating Metadata Using Oracle Enterprise Manager
5.4.1 Procedure: Accessing OLAP Management
5.4.2 Defining Metadata for Dimension Tables Information That You Supply for Dimensions Time Dimension Procedure: Defining a Logical Dimension in the OLAP Catalog
5.4.3 Defining Metadata for Fact Tables Information That You Supply for Cubes Procedure: Defining a Logical Cube in the OLAP Catalog
5.5 Case Study: Creating Metadata for the GLOBAL Star Schema
5.5.1 Defining a Logical Time Dimension for the Global Schema
5.5.2 Defining a Logical Units Cube for the Global Schema
5.6 Creating Metadata Using PL/SQL
5.6.1 CWM2 Packages for Creating OLAP Dimensions
5.6.2 CWM2 Packages for Creating Cubes
5.6.3 CWM2 Package for Mapping Metadata
5.6.4 CWM2 Package for Creating Level-Based Dimension Tables
5.6.5 CWM2 Packages for Classification and Validation

6 Creating an Analytic Workspace

6.1 Methods of Creating a Workspace
6.2 Introduction to Analytic Workspace Manager
6.2.1 OLAP Catalog View
6.2.2 Object View
6.2.3 OLAP Worksheet
6.2.4 Opening a Database Connection With Analytic Workspace Manager
6.3 Creating a Standard Form Workspace Using Analytic Workspace Manager
6.3.1 Choosing a Schema for the Analytic Workspace
6.3.2 Setting Advanced Storage Options Defining a Composite Dimension Ordering the Dimensions in a Cube Setting the Segment Size
6.3.3 Choosing Build Options
6.3.4 Generating Scripts
6.3.5 Basic Steps for Creating a Standard Form Workspace
6.4 Case Study: Creating the Global Analytic Workspace
6.4.1 Defining the GLOBAL_AW Workspace User
6.4.2 Examining Sparsity Characteristics for GLOBAL
6.4.3 Running the Create Analytic Workspace Wizard
6.4.4 Manually Changing Object Definitions
6.4.5 Completing the Build
6.5 Case Study: Creating the Sales History Analytic Workspace
6.5.1 Defining Startup Parameters for the SH Build
6.5.2 Defining Tablespaces for SH
6.5.3 Examining the Sparsity Characteristics of SH Data
6.5.4 Managing the SH Build
6.5.5 Running the Create Analytic Workspace Wizard
6.5.6 Building the Sales History Analytic Workspace
6.6 Generating Aggregate Data
6.6.1 Strategies for Calculating Aggregates
6.6.2 How to Select Levels to Pre-Aggregate and Store
6.6.3 About Aggregation Plans
6.6.4 How to Create and Deploy an Aggregation Plan Creating an Aggregation Plan Changing the Aggregation Operator Deploying an Aggregation Plan
6.7 Case Study: Aggregating Data in the GLOBAL Analytic Workspace
6.7.1 Identifying Levels for Precalculation
6.7.2 Aggregating the Global Price Cube
6.8 Enabling an Analytic Workspace for an Application
6.8.1 How to Enable an Analytic Workspace
6.8.2 About Enabling for the BI Beans Star Schema of Views OLAP Catalog Metadata for Analytic Workspaces
6.8.3 How to Enable an Analytic Workspace for Oracle Discoverer
6.8.4 About Enabling for Oracle Discoverer Views Created for Discoverer
6.9 Refreshing the Data in an Analytic Workspace
6.9.1 Using the Refresh Wizard
6.9.2 Refreshing From Different Relational Tables
6.9.3 Case Study: Refreshing the Units Cube
6.9.4 When a Data Refresh Requires Re-Enabling

7 SQL Access to Analytic Workspaces

7.1 Overview of SQL Access
7.1.1 Manipulating Analytic Workspace Data
7.1.2 Querying an Analytic Workspace
7.1.3 About the Active Catalogs
7.2 Support for Custom Measures
7.2.1 Methods of Defining Custom Measures
7.2.2 Analytic Support for Custom Measures Forecasts and Regressions Time Series Manipulation Financial Operations Statistical Operations Numeric Computations Text Manipulation Allocation Aggregation Models
7.3 Creating Custom Measures Using DBMS_AW_UTILITIES
7.4 Case Study: Adding Sales to Global Using DBMS_AW_UTILITIES
7.4.1 Acquiring Information About the Analytic Workspace
7.4.2 Using DBMS_AW_UTILITIES to Define Sales as a Custom Measure
7.4.3 Viewing the Workspace Formula
7.4.4 Querying the Sales Custom Measure
7.5 Creating Custom Measures Using OLAP_EXPRESSION
7.6 Case Study: Adding Sales to Global Using OLAP_EXPRESSION
7.7 Using OLAP_TABLE for Direct Access to Workspace Data
7.7.1 Designing Views of an Analytic Workspace
7.7.2 Process Overview
7.7.3 Using OLAP_TABLE
7.7.4 Using the SELECT MODEL Clause
7.8 Case Study: Using OLAP_TABLE to Create Global Custom Measures
7.8.1 Defining Formulas in the Analytic Workspace
7.8.2 Querying an Analytic Workspace Using OLAP_TABLE OLAP_TABLE Function SELECT Statement
7.8.3 Using OLAP_TABLE to Create a Measure View for the BI Beans Creating and Executing the SQL Script About the Sample Script
7.8.4 Defining OLAP Catalog Metadata for Workspace Views

8 Exploring a Standard Form Analytic Workspace

8.1 About Workspaces Created Using OLAP Tools
8.1.1 About Database Standard Form
8.1.2 Standard Form Implementation of the Logical Model
8.1.3 Additional Requirements for OLAP Tools
8.2 Querying a Standard Form Analytic Workspace
8.2.1 Querying the Standard Form Catalogs
8.2.2 Querying Properties
8.3 Standard Form Dimensions
8.3.1 Dimdef Dimension Contents of an Analytic Workspace Dimension Properties of an Analytic Workspace Dimdef Dimension
8.3.2 Standard Form Metadata for Dimensions ALL_DIMENSIONS Dimension ALL_DESCRIPTIONS Variable for Dimensions AW_NAMES Variable for Dimensions DIM_LEVELS Valueset
8.4 Standard Form Hierarchies
8.4.1 Hierlist Dimension Contents of a Hierlist Dimension Properties of a Hierlist Dimension
8.4.2 Member_Parentrel Relation Contents of a Member_Parentrel Relation Properties of a Member_Parentrel Relation
8.4.3 Member_Gid Variable Contents of a Member_GID Variable Properties of a Member_Gid Variable
8.4.4 Member_Inhier Variable Contents of a Member_Inhier Variable Properties of a Member_Inhier Variable
8.4.5 Standard Form Metadata for Hierarchies ALL_HIERARCHIES Dimension ALL_DESCRIPTIONS Variable for Hierarchies DIM_HIERARCHIES Valueset DEFAULT_HIER Relation
8.5 Standard Form Levels
8.5.1 Levellist Dimension Contents of a Levellist Dimension Properties of a Levellist Dimension
8.5.2 Member_Levelrel Relation Contents of a Level Relation Properties of a Member_Levelrel Relation
8.5.3 Member_Familyrel Relation Contents of a Family Relation Properties of a Member_Familyrel Relation
8.5.4 Standard Form Metadata for Levels ALL_LEVELS Dimension ALL_DESCRIPTIONS Variable for Levels DIM_LEVELS Valueset
8.6 Standard Form Attributes
8.6.1 ALL_LANGUAGES Dimension
8.6.2 Standard Form Metadata for Attributes ALL_ATTRIBUTES Dimension ALL_DESCRIPTIONS Variable for Attributes AW_NAMES Variable for Attributes
8.7 Standard Form Measures
8.7.1 Measure Variable
8.7.2 Measuredef Formula
8.7.3 Standard Form Metadata for Measures ALL_MEASURES Dimension ALL_DESCRIPTIONS Variable for Measures AW_NAMES Variable for Measures CUBE_MEASURES Valueset
8.8 Standard Form Cubes
8.8.1 Cubedef Dimension Contents of a Cubedef Dimension Properties of a Cubedef Dimension
8.8.2 Comspec Aggregation Map
8.8.3 Loopspec Composite Dimension
8.8.4 Standard Form Metadata for Cubes ALL_CUBES Dimension ALL_DESCRIPTIONS Variable for Cubes AW_NAMES Variable for Cubes CUBE_MEASURES Valueset
8.9 Standard Form Catalogs
8.10 OLAP API Enabler Catalogs
8.11 AWCREATE Catalogs

Part III Acquiring Data From Additional Sources

9 Adding Measures to a Standard Form Analytic Workspace

9.1 Working in a Standard Form Analytic Workspace
9.2 Methods of Executing OLAP DML Commands
9.2.1 Using Analytic Workspace Manager to Execute OLAP DML
9.2.2 Using OLAP Worksheet to Execute OLAP DML Procedure: Opening OLAP Worksheet from Analytic Workspace Manager Procedure: Using the Editor in OLAP Worksheet
9.2.3 Using DBMS_AW.EXECUTE to Execute OLAP DML DBMS_AW.EXECUTE Command Format Adding Contents to a DML Program From SQL
9.3 Adding Custom Measures to a Cube
9.3.1 Defining a Standard Form Measure Variable
9.3.2 Defining a Formula
9.3.3 Registering a New Measure ALL_MEASURES Dimension ALL_DESCRIPTIONS Variable AW_NAMES Variable CUBE_MEASURES Valueset
9.4 Case Study: Adding Measures to the Global Analytic Workspace
9.4.1 Creating Measures for SALES, EXTENDED_COST, and MARGIN Creating New Variables in GLOBAL Calculating and Storing Values in Variables Creating Measure Formulas Aggregating the New Global Variables
9.4.2 Adding More Custom Measures to GLOBAL
9.4.3 Using an OLAP DML Program to Add Measures to GLOBAL

10 Predicting Future Performance

10.1 Creating a Forecast
10.1.1 Steps for Creating a Forecast
10.1.2 Creating the Forecast Time Periods
10.1.3 Defining Variables for the Results
10.2 Developing a Forecast Program
10.2.1 Generating a Forecast
10.3 Defining a New Cube
10.3.1 Creating a Cubedef Object
10.3.2 Creating a Default Aggregation Map
10.3.3 Registering a New Cube Adding a Cube to the ALL_CUBES Dimension Adding a Cube to the ALL_DESCRIPTIONS Variable Adding a Cube to the AW_NAMES Variable Adding Measures to the New Cube in the CUBE_MEASURES Valueset
10.3.4 Troubleshooting a Hand-Crafted Cube
10.4 Case Study: Forecasting Global Sales
10.4.1 Defining a New Cube for Forecast Measures
10.4.2 Defining the Forecasting Measures for Global Sales
10.4.3 Developing a Forecasting Program for Global Sales Identifying Historical and Forecast Time Periods Arguments to the FORECAST_SALES Sample Program
10.4.4 Reviewing the Forecast Data for Global Sales
10.4.5 Aggregating and Enabling the Forecast Measure

11 Acquiring Data From Other Sources

11.1 Overview of OLAP Data Acquisition Subsystems
11.2 How to Manually Create a Standard Form Analytic Workspace
11.3 Reading Flat Files
11.3.1 About the File Reader Programs
11.3.2 Writing a Program for Reading Files
11.3.3 Mapping Fields to Workspace Objects Reading Ruled Files Reading Structured PRN Files Reading CSV Files
11.3.4 Setting Dimension Status for Reading Measures
11.3.5 Optimizing a Data Load
11.3.6 Reading and Maintaining Dimension Members
11.3.7 Transforming Incoming Values Basic Transformations Using Relations to Align Dimension Values
11.4 Fetching Data From Relational Tables
11.4.1 OLAP DML Support for SQL
11.4.2 Process: Copying Data From Relational Tables Into Analytic Workspace Objects
11.4.3 Fetching Dimensions Members From Tables
11.4.4 Sorting Dimension Members
11.4.5 Fetching Measures From Tables
11.5 Populating Additional Metadata Objects
11.5.1 Using ___POP.FMLYREL
11.5.2 Using ___ORDR.HIERARCHIES
11.6 Case Study: Creating the GLOBALX Workspace From Alternative Sources
11.6.1 Designing and Implementing the GLOBALX Star Schema GLOBALX Schema Diagram Procedure: Creating the GLOBALX Sample Schema
11.6.2 Creating OLAP Catalog Metadata for the GLOBALX Schema
11.6.3 Creating the GLOBALX Analytic Workspace
11.6.4 Fetching the Price Cube From Relational Tables Loading Products From GLOBAL.PRODUCT_DIM Loading Time From GLOBAL.TIME_DIM Loading the PRICE Cube From PRICE_AND_COST_HISTORY_FACT
11.6.5 Loading the Units Cube From Flat Files Loading Channels From CHANNELS.DAT Loading Customers From CUSTOMERS.DAT Reading the UNITS_CUBE.DAT File
11.6.6 Populating Additional Standard Form Metadata Objects
11.6.7 Using Tools with the GLOBALX Analytic Workspace

Part IV Database Administration for OLAP

12 Administering Oracle OLAP

12.1 Administration Overview
12.2 Creating Tablespaces for Analytic Workspaces
12.2.1 Creating an UNDO Tablespace
12.2.2 Creating a Permanent Tablespace for Analytic Workspaces
12.2.3 Creating a Temporary Tablespace for Analytic Workspaces
12.2.4 Querying the Size of an Analytic Workspace
12.3 Setting Up User Names
12.3.1 SQL Access For DBAs and Application Developers
12.3.2 SQL Access for Analysts
12.3.3 Access to Database Objects Using the BI Beans
12.4 Initialization Parameters for Oracle OLAP
12.4.1 Procedure: Setting System Parameters for OLAP
12.4.2 About the OLAP_PAGE_POOL_SIZE Setting
12.4.3 About the PGA_AGGREGATE_TARGET Setting
12.5 Initialization Parameters for the BI Beans
12.6 Permitting Access to External Files
12.6.1 Creating a Database Directory
12.6.2 Granting Access Rights to a Database Directory
12.6.3 Example: Creating and Using a Database Directory
12.7 Understanding Data Storage
12.7.1 Analytic Workspace Tables
12.7.2 System Tables
12.8 Monitoring Performance

13 Materialized Views for the OLAP API

13.1 Summary Management with Oracle OLAP
13.2 Overview and Requirements
13.2.1 Materialized Views Required for a Cube
13.2.2 Materialized Views and OLAP Metadata
13.3 Example: Dimension Materialized View
13.3.1 CREATE Materialized View for a Dimension Hierarchy
13.3.2 Bitmap Indexes for a Dimension Hierarchy
13.3.3 Statistics for a Dimension Hierarchy
13.4 Example: Fact Materialized View
13.4.1 CREATE Fact Materialized View
13.4.2 Bitmap Indexes for Fact Materialized Views
13.4.3 Statistics for Fact Materialized Views
13.5 Using the DBMS_ODM Package
13.5.1 Procedure: Create Grouping Set Materialized Views
13.5.2 Example: Create Grouping Set Materialized Views for a Sales Cube

A Database Standard Form for Analytic Workspaces

A.1 Overview of Database Standard Form
A.1.1 Purpose of Database Standard Form
A.1.2 Audience for Database Standard Form
A.1.3 Logical Model and Workspace Objects
A.1.3.1 Implementation of a Cube
A.1.3.2 Implementation of a Measure
A.1.3.3 Implementation of a Dimension
A.1.4 Classes of Workspace Objects
A.1.5 Properties of Workspace Objects
A.2 Object Naming Conventions
A.2.1 Logical Names
A.2.2 Name Space Organization
A.2.3 Simple Logical Names and Full Names
A.3 Workspace Object Properties
A.3.1 Properties Specific to Implementation Class Objects
A.3.2 System Properties on All Workspace Objects
A.3.3 Role Property on All Workspace Objects
A.3.4 Role Property Values for Implementation Class Objects
A.3.5 Role Property Values for Catalogs Class Objects
A.3.6 Role Property Values for Features Class Objects
A.3.7 Role Property Values for Extensions Class Objects
A.3.8 Terminology: Using Role Names to Describe Objects
A.4 Implementation Class Objects
A.4.1 Cube Objects
A.4.1.1 Cubedef Dimension
A.4.1.2 Loopspec Composite
A.4.2 Measure Objects
A.4.2.1 Measuredef Object
A.4.2.2 COMPSPEC Aggmap
A.4.3 Dimension Objects
A.4.3.1 Dimdef Dimension
A.4.3.2 Hierlist Dimension
A.4.3.3 Levellist Dimension
A.4.3.4 Member_Levelrel Relation
A.4.3.5 Member_Parentrel Relation
A.4.3.6 Hier_Levels Valueset
A.4.3.7 Attrdef Object
A.5 Catalogs Class Objects
A.5.1 Lists of Objects
A.5.1.1 ALL_CUBES Dimension
A.5.1.2 ALL_MEASURES Dimension
A.5.1.3 ALL_DIMENSIONS Dimension
A.5.1.4 ALL_HIERARCHIES Dimension
A.5.1.5 ALL_LEVELS Dimension
A.5.1.6 ALL_ATTRIBUTES Dimension
A.5.1.7 ALL_OBJECTS Dimension
A.5.2 Lists of Types, Roles, and Languages
A.5.2.1 ALL_OBJTYPES Dimension
A.5.2.2 ALL_DESCTYPES Dimension
A.5.2.3 ALL_ATTRTYPES Dimension
A.5.2.4 AW_ROLES Dimension
A.5.2.5 ALL_LANGUAGES Dimension
A.5.3 Lists of Cube and Dimension Objects
A.5.3.1 CUBE_MEASURES Valueset
A.5.3.2 DIM_HIERARCHIES Valueset
A.5.3.3 DIM_LEVELS Valueset
A.5.3.4 DIM_ATTRIBUTES Valueset
A.5.4 Supporting Object Information
A.5.4.1 AW_NAMES Variable
A.5.4.2 AW_COMPSPECS Variable
A.5.4.3 AW_LOOPSPECS Variable
A.6 Features Class Objects
A.6.2 ATTR_INHIER Variable
A.6.3 DEFAULT_HIER Relation
A.6.4 VISIBLE Variable
A.6.5 Member_Inhier Variable
A.6.6 Member_Createdby Variable
A.6.7 Member_Familyrel Relation
A.6.8 Member_Gid Variable
A.6.9 OBJ_CREATEDBY Variable
A.6.10 OBJ_STATE Variable
A.6.11 VERSION Variable
A.7 Extensions Class Objects

B Upgrading From Express Server

B.1 Administration
B.1.1 Management Tools
B.1.2 Authentication of Users
B.1.3 Data Transfer
B.1.4 Localization
B.2 Applications Support
B.2.1 Programming Environment
B.2.2 Communications
B.2.3 Metadata
B.3 Programming Language Changes
B.3.1 New Commands
B.3.2 Obsolete Commands
B.4 Converting Oracle Express Databases to Standard Form
B.4.1 Who Should Use CREATE_DB_STDFORM
B.4.1.1 What CREATE_DB_STDFORM Does For You
B.4.1.2 What CREATE_DB_STDFORM Does Not Do For You
B.4.2 Converting From Oracle Express Objects Metadata
B.4.2.2 Procedure: Converting From Oracle Express Objects to Standard Form
B.4.3 Populating Time Attributes
B.4.3.1 Sorting Time Dimension Members
B.4.3.2 Creating and Populating End Date and Time Span Attributes
B.4.3.3 Setting Properties on Time Objects
B.4.4 Revising the Load Programs
B.4.5 Example: Converting the XADEMO Database to Standard Form
B.4.5.1 Creating a Standard Form XADEMO Analytic Workspace
B.4.5.2 About the Time Dimension in XADEMO
B.4.5.3 Populating the XADEMO Time Attributes

C Programs Used to Create GLOBALX

C.1 SQL Scripts for Defining Users and Tablespaces
C.2 SQL Scripts for the GLOBALX Star Schema
C.3 SQL Scripts for OLAP Catalog Metadata