Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle OLAP Applications Development?

• Oracle Database 10g Release 10.2 Oracle OLAP
• Oracle Database 10g Release 10.1.0.4 Oracle OLAP

Part I Fundamentals

1 Overview

• OLAP Technology Within Oracle Database
• • Problems Maintaining Two Distinct Systems
  • Full Integration of Multidimensional Technology
• Using OLAP to Answer Business Questions
• Common Analytical Applications
• Tools for Querying OLAP Data Stores
• • Formulating Queries
  • Creating Custom Measures
• The Logical Dimensional Data Model
• • Logical Cubes
  • Logical Measures
  • Logical Dimensions
  • Logical Hierarchies and Levels
  • Logical Attributes
• About Multidimensional Data Stores
• • Creating Analytic Workspaces
  • Summary Data
• Deciding When to Use Analytic Workspaces
• • When to Use Analytic Workspaces
  • When to Use Relational Schemas
  • Structured and Unstructured Data Stores
  • Processing Analytic Queries
  • Creating Summary Data
  • • How Analytic Workspaces Store Summary Data
    • How Relational Schemas Store Aggregate Data
• Components of Oracle OLAP
• • OLAP Analytic Engine
  • Analytic Workspaces
  • Analytic Workspace Manager
  • OLAP Worksheet
  • SQL Interface to OLAP
  • OLAP DML
  • Analytic Workspace Java APIs
  • OLAP API
  • OLAP Catalog
• Implementing an Analytic Workspace
• • Identifying Business Goals
  • Identifying Data Sources
  • Defining a Logical Model
  • Mapping, Loading, and Aggregating the Data
  • Generating Information-Rich Data
• Implementing a Relational Data Warehouse for OLAP
• • Identifying Business Goals
  • Identifying Data Sources
  • Defining a Logical Model
  • Generating Summary Data
• Upgrading Oracle Database 10g Release 1 Analytic Workspaces
• Upgrading Oracle9i Analytic Workspaces
• • Upgrading the Physical Storage Format
  • Upgrading the Standard Form Metadata

2 The Sample Schema

• Case Study Scenario
• • Reporting Requirements
  • Business Goals
  • 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
• Identifying Required Business Facts
• Designing a Logical Data Model for Global Computing
• • Identifying Dimensions
  • Identifying Levels
  • Identifying Hierarchies
  • Identifying Stored Measures
• The Global Schema

Part II Creating and Managing Analytic Workspaces

3 Creating an Analytic Workspace

• Introduction to Analytic Workspace Manager
• • Model View
  • Object View
  • OLAP Worksheet
• Getting Started with Analytic Workspace Manager
• • Installing Analytic Workspace Manager
  • Opening Analytic Workspace Manager
  • Defining a Database Connection
  • Opening a Database Connection
• Identifying the Source Data
• • Schema Requirements
  • • Star Schema
    • Snowflake Schema
    • Other
    • Making Transformations in Your Source Data
  • Choosing a Build Tool
• Creating a Standard Form Workspace Using Analytic Workspace Manager
• • How Analytic Workspace Manager Saves Changes
  • Basic Steps for Creating a Standard Form Workspace
  • Adding Functionality to a Standard Form Analytic Workspace
• Creating Logical Dimensions
• • Creating Dimensions
  • • Defining a Time Dimension
    • Creating Unique Dimension Members
    • Opening the Create Dimension Dialog Box
  • Creating Levels
  • Creating Hierarchies
  • Creating Attributes
  • • Automatically Defined Attributes
    • User Attributes
• Creating Logical Cubes
• • Creating Cubes
  • Creating Measures
  • Creating Calculated Measures
• Making Data Storage Decisions
• • What is Sparsity?
  • • Sparsity Patterns
    • Physical Storage of Sparse Data
    • Manually Calculating Sparsity in a Cube
  • Ordering the Dimensions in a Cube
  • Partitioning Large Measures
  • Defining Rules for Summarizing Data
  • • Basic Strategy for Summarizing Analytic Workspace Data
    • Selecting Levels to Aggregate in the Builds
    • Choosing Aggregation Methods
• Mapping Logical Objects to Data Sources
• • Mapping Dimensions
  • Mapping Cubes
• Maintaining the Data
• • Submitting Maintenance Tasks to the Oracle Job Queue
  • Managing Maintenance Jobs
• Defining Measure Folders
• Supporting Multiple Languages
• Creating Calculation Plans
• Case Study: Creating the Global Analytic Workspace
• • Defining the GLOBAL_AW User
  • Examining Sparsity Characteristics for GLOBAL
  • Identifying Levels for Precalculation
  • Creating the GLOBAL Analytic Workspace
  • Creating GLOBAL Dimensions and Attributes
  • Creating GLOBAL Cubes and Measures
  • Mapping the GLOBAL Logical Model to Data Sources
  • Loading and Aggregating the Data
  • Creating Calculated Measures
  • Creating a Measure Folder
• Case Study: Creating the Sales History Analytic Workspace
• • Creating the SH Analytic Workspace
  • Defining Database Parameters
  • Defining Tablespaces for Sales History
  • Defining the SH_AW User
  • Defining the Logical Dimensions for Sales History
  • • Defining TIMES_DIM
    • Defining CUSTOMERS_DIM
    • Defining PRODUCTS_DIM, CHANNELS_DIM, and PROMOTIONS_DIM
  • Defining the Logical Sales Cube for Sales History
  • • About the Sparsity Advisor
    • Sample Program for Evaluating Sales History Tables
    • Interpreting the Results from the Sparsity Advisor
  • Maintaining Sales History

4 Predicting Future Performance

• Creating a Forecast
• • Steps for Creating a Forecast
  • Creating the Forecast Time Periods
  • Defining a Measure for the Results
  • Defining Supporting Variables (Optional)
  • Developing a Forecast Program
  • Generating a Forecast
  • Aggregating the Forecast Data
• Case Study: Forecasting Global Sales
• • Defining the Sales Forecast Measure for Global Sales
  • Defining a Variable for Seasonal Adjustment
  • Developing a Forecasting Program for Global Sales
  • • Historical and Forecast Time Periods
    • The FORECAST_SALES Program
    • Generating the Global Sales Forecast
    • Aggregating the Sales Forecast Measure

5 Developing Java Applications for OLAP

• Building Analytical Java Applications
• • About Java
  • The Java Solution for OLAP
  • Oracle Java Development Environment
• Introducing OracleBI Beans
• • Metadata
  • Navigation
  • Formatting
  • Graphs
  • Crosstabs
  • Data Beans
  • Wizards
  • JSP Tag Library
• Understanding the OLAP API
• • How the OLAP API Accesses Dimensional Data
  • Calculation Capabilities
  • Intelligent Caching
• Managing Data Sources for OracleBI Beans and the OLAP API
• Building Java Applications that Manage Analytic Workspaces

6 Administering Oracle OLAP

• Administration Overview
• Creating Tablespaces for Analytic Workspaces
• • Creating an UNDO Tablespace
  • Creating a Permanent Tablespace for Analytic Workspaces
  • Creating a Temporary Tablespace for Analytic Workspaces
  • Querying the Size of an Analytic Workspace
• Setting Up User Names
• • SQL Access For DBAs and Application Developers
  • SQL Access for Analysts
  • Access to Database Objects Using OracleBI Beans
  • Access to the Oracle JVM
• Initialization Parameters for Oracle OLAP
• • Procedure: Setting System Parameters for OLAP
  • About the PGA_AGGREGATE_TARGET Setting
• Initialization Parameters for OracleBI Beans
• Permitting Access to External Files
• • Creating a Directory Object
  • Granting Access Rights to a Directory Object
  • Example: Creating and Using a Directory Object
• Understanding Data Storage
• • Analytic Workspace Tables
  • System Tables and Views
• Monitoring Performance
• Copying and Backing Up Analytic Workspaces

Part III Creating a Relational Data Warehouse

7 Using the OLAP Catalog

• Choosing a Method for Creating OLAP Catalog Metadata
• • For Source Data in a Basic Star or Snowflake Schema
  • For Dimension Tables with Complex Hierarchies
  • For Other Schema Configurations
• Overview of the OLAP Catalog
• • OLAP Catalog Components
  • • About CWM1
    • About CWM2
  • Steps for Creating OLAP Metadata
• Creating Metadata Using Enterprise Manager Database Control
• • Procedure: Accessing OLAP Management
  • Defining Metadata for Dimension Tables
  • • Information That You Supply for Dimensions
    • Time Dimension
    • Procedure: Defining a Logical Dimension in the OLAP Catalog
  • Defining Metadata for Fact Tables
  • • Information That You Supply for Cubes
    • Procedure: Defining a Logical Cube in the OLAP Catalog
• Case Study: Creating Metadata for the GLOBAL Star Schema
• • Defining a Logical Time Dimension for the Global Schema
  • Defining a Logical Price and Cost Cube for the Global Schema
• Creating Metadata Using PL/SQL
• • CWM2 Packages for Creating OLAP Dimensions
  • CWM2 Packages for Creating Cubes
  • CWM2 Package for Mapping Metadata
  • CWM2 Package for Creating Level-Based Dimension Tables
  • CWM2 Packages for Classification and Validation

8 Materialized Views for the OLAP API

• Summary Management with Oracle OLAP
• Overview and Requirements
• • Materialized Views Required for a Cube
  • Materialized Views and OLAP Metadata
• A Dimension Materialized View
• • CREATE Materialized View for a Dimension Hierarchy
  • Bitmap Indexes for a Dimension Hierarchy
  • Statistics for a Dimension Hierarchy
• A Fact Materialized View
• • CREATE Fact Materialized View
  • Bitmap Indexes for Fact Materialized Views
  • Statistics for Fact Materialized Views
• Using the DBMS_ODM Package
• • Procedure: Automatically Generate the Materialized Views
  • Procedure: Manually Generate the Materialized Views
• Example: Automatically Generate the Materialized Views for a Price Cube
• Example: Manually Generate the Materialized Views for a Sales Cube

A Database Standard Form for Analytic Workspaces

• Overview of Database Standard Form
• • Terminology: Using Role Names to Identify Objects
• Querying a Standard Form Analytic Workspace
• • Querying the Standard Form Catalogs
  • Querying Properties
• Standard Form Implementation of the Logical Model
• • Relationships Among Logical Objects
  • Classes of Workspace Objects
• Object Naming Conventions
• • Logical Names
  • Simple Logical Names and Full Names
  • Name Space Organization
• Workspace Object Properties
• • System Properties on All Workspace Objects
  • Properties Specific to Implementation Class Objects
  • Role Property Values for Implementation Class Objects
  • Role Property Values for Catalogs Class Objects
  • Role Property Values for Features Class Objects
  • Role Property Values for Extensions Class Objects
• Implementation Class Objects
• • Cube Objects
  • • Cubedef Dimension
  • Measure Objects
  • • Measuredef Object
  • Dimension Objects
  • • Dimdef Dimension
    • Hierlist Dimension
    • Levellist Dimension
    • Member_Levelrel Relation
    • Member_Parentrel Relation
    • Hier_Levels Valueset
    • Attrdef Object
• Catalogs Class Objects
• • Lists of Objects
  • • ALL_CUBES Dimension
    • ALL_MEASURES Dimension
    • ALL_DIMENSIONS Dimension
    • ALL_HIERARCHIES Dimension
    • ALL_LEVELS Dimension
    • ALL_ATTRIBUTES Dimension
    • ALL_OBJECTS Dimension
  • Lists of Types and Languages
  • • ALL_DESCTYPES Dimension
    • ALL_ATTRTYPES Dimension
    • ALL_LANGUAGES Dimension
  • Lists of Cube and Dimension Objects
  • • CUBE_MEASURES Relation
    • DIM_HIERARCHIES Relation
    • DIM_LEVELS Relation
    • DIM_ATTRIBUTES Relation
  • Supporting Object Information
  • • AW_NAMES Variable
• Features Class Objects
• • ALL_DESCRIPTIONS Variable
  • DEFAULT_HIER Relation
  • VISIBLE Variable
  • Member_Inhier Valueset
  • Member_Createdby Variable
  • Member_Familyrel Relation
  • Member_Gid Variable
  • OBJ_CREATEDBY Variable
  • VERSION Variable
• Extensions Class Objects

B Upgrading From Express Server

• Administration
• • Management Tools
  • Authentication of Users
  • Data Transfer
  • Localization
• Applications Support
• • Programming Environment
  • Communications
  • Metadata
• Programming Language Changes
• • New Commands
  • Obsolete Commands
  • UPDATE and COMMIT
• Transforming Oracle Express Databases to Standard Form
• • Who Should Use the Transformation Tool
  • • What the Transformation Tool Does For You
    • What the Transformation Tool Does Not Do For You
  • Converting From Oracle Express Objects Metadata
  • • Procedure: Converting From Oracle Express Objects to Standard Form
  • Populating Time Attributes
  • • Sorting Time Dimension Members
    • Creating and Populating End Date and Time Span Attributes
    • Setting Properties on Time Objects
  • Revising the Load Programs
  • Example: Converting the XADEMO Database to Standard Form
  • • Creating a Standard Form XADEMO Analytic Workspace
    • About the Time Dimension in XADEMO
    • Populating the XADEMO Time Attributes

Glossary

Index