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.0.3 Oracle OLAP
• 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
• About Multidimensional Data Stores
  • Creating Analytic Workspaces
  • Structured Data Stores
  • Processing Analytic Queries
  • Creating Summary Data
• Components of Oracle OLAP
  • OLAP Analytic Engine
  • Analytic Workspaces
  • Analytic Workspace Manager
  • OLAP Worksheet
  • OLAP DML
  • SQL Interface to OLAP
  • Analytic Workspace Java API
  • OLAP API
• Implementing an Analytic Workspace
  • Identifying Business Goals
  • Identifying Data Sources
  • Defining a Logical Model
  • Mapping, Loading, and Aggregating the Data
  • Generating Information-Rich 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 Logical Dimensional Data Model

• Overview of the Data Model
• Logical Cubes
• Logical Measures
• Logical Dimensions
• Logical Hierarchies and Levels
  • Level-Based Hierarchies
  • Value-Based Hierarchies
• Logical Attributes

3 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

4 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
• Building Java Applications That Manage Analytic Workspaces

Part II Creating and Managing Analytic Workspaces

5 Creating an Analytic Workspace

• Introduction to Analytic Workspace Manager
  • Model View
  • Object View
• 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 an Analytic Workspace
  • Adding Functionality to a Standard Form Analytic Workspace
• Creating Logical Dimensions
  • Creating Levels
  • Creating Hierarchies
  • Creating Attributes
    • Automatically Defined Attributes
    • User-Defined Attributes
• Creating Logical Cubes
  • Creating Cubes
  • Creating Measures
  • Creating Calculated Measures
• Mapping Logical Objects to Data Sources
  • Mapping Dimensions
  • Mapping Cubes
• Using the Sparsity Advisor
  • What is Sparsity?
  • Ordering the Dimensions in a Cube
  • Choosing a Data Type
  • Choosing Composite Types
  • Partitioning Large Measures
    • Effects of Partitioning on Performance
    • Choosing a Dimension for Partitioning
    • Example of a Partitioned Dimension
• Maintaining the Data
  • Submitting Maintenance Tasks to the Oracle Job Queue
  • Managing Maintenance Jobs
• Defining Measure Folders
• Supporting Multiple Languages
• Creating and Executing Calculation Plans
• Using Templates to Re-Create a Logical Model
• Using Plug-Ins
• Case Study: Creating the Global Analytic Workspace
  • Defining the GLOBAL_AW User
  • 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
  • Maintaining Sales History

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
• 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
• 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
• Basic Queries for Monitoring the OLAP Option
  • Is the OLAP Option Installed in the Database?
  • What Analytic Workspaces are in the Database?
  • How Big is the Analytic Workspace?
  • How Is the Analytic Workspace Stored?
  • When Were the Analytic Workspaces Created?
• How Dimensional Data is Stored in the Database
  • Analytic Workspace Tables
  • System Tables
  • Static Data Dictionary Views
• Monitoring Performance
• Copying and Backing Up Analytic Workspaces

Part III Generating Quality Information

7 Aggregating Data

• What is Aggregation?
• Managing Aggregate Data
  • Managing Aggregate Data in Relational Tables
  • Managing Aggregate Data in Analytic Workspaces
• Basic Strategies for Aggregating Data
  • Aggregating Non-Compressed Composites
    • Selecting Dimensions for Skip-Level Aggregation
    • Selecting the Levels to Skip
  • Aggregating Compressed Composites
  • Improving Aggregation Performance
    • Finish Data Updates on Time
    • Keep Within Allocated Resources
    • Provide Good Response Time
• Selecting Dimension Members for Aggregation
• Defining an Aggregation
• Aggregation Operators
  • Basic Operators
  • Hierarchical Operators
  • Scaled and Weighted Operators
• Case Study: Aggregating a Moderately Sparse or Dense Cube
• Case Study: Aggregating a Very Sparse Cube

8 Allocating Data

• What Is an Allocation?
• Creating Measures to Support an Allocation
  • Source Measures
  • Basis Measures
  • Target Measures
  • Weight Measures
• Selecting Dimension Members for an Allocation
  • Identifying the Sources and Targets
  • Identifying the Allocation Path
• Creating an Allocation
• Allocation Operators
  • Copy Operators
  • Even Distribution Operators
  • Proportional Distribution Operator
  • Relationships Between Allocation and Aggregation Operators
• Case Study: Allocating a Budget
  • Creating the Source Measure
  • Creating the Target Measure
  • Creating the Calculation Plan
  • Creating the Allocate Budget Step
  • Generating and Validating the Allocation

9 Generating Forecasts

• Introduction to Forecasting Considerations
• Choosing a General Forecasting Approach
  • Time Series
  • Causal Analysis
  • Expert Opinion
• About the Forecasting Engine
• Creating a Forecast
  • Creating the Forecast Time Periods
  • Creating a Forecast Measure
  • Selecting the Historical Data
  • Identifying the Levels for the Forecast
  • Creating a Forecast Step
  • Generating the Forecast
  • Evaluating the Forecast Results
• Designing Your Own Forecast
  • What is the Expert System?
  • What is the Verification Window?
  • When Should You Design a Forecast?
  • Overriding the Expert System
• Forecasting Method Descriptions
  • Automatic
  • Regressions
    • Linear Regression
    • Nonlinear Regression
    • Advanced Parameter for Regressions
  • Exponential Smoothing
    • Comparison Among Exponential Smoothing Methods
    • Advanced Parameters for Exponential Smoothing
• Advanced Parameter Descriptions
  • Setup Parameters
  • General Parameters
  • Historical Data Smoothing Parameters
• Case Study: Forecasting Sales for Global Enterprises
  • Creating the Sales Forecast Target Measure
  • Creating the Calculation Plan
  • Creating the Sales Forecast Step
  • Generating and Validating the Forecast
  • Creating an Allocation Basis Measure
  • Creating the Allocate Sales Forecast Step
  • Generating and Validating the Allocation
  • Creating the Sales Forecast Aggregation Step
  • Generating the Aggregation

Glossary

Index