Contents

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

1 Overview

• OLAP Technology in the Oracle Database
  • Full Integration of Multidimensional Technology
  • Ease of Administration
  • Security
  • Unmatched Performance and Scalability
  • Reduced Costs
• About Multidimensional Data Stores
  • Creating Analytic Workspaces
  • Structured Data Stores
  • Processing Analytic Queries
  • Creating Summary Data
• Using SQL Tools to Query Dimensional Objects
• Using OLAP Tools to Query Dimensional Objects
  • Formulating Queries
  • Creating Calculations
• Overview of the Dimensional Data Model
  • Cubes
  • Measures
  • Dimensions
  • Hierarchies and Levels
    • Level-Based Hierarchies
    • Value-Based Hierarchies
  • Attributes
• Upgrading Oracle Database 10g Release 1 Analytic Workspaces
• Upgrading Oracle9i Analytic Workspaces
  • Upgrading the Physical Storage Format
  • Upgrading the Standard Form Metadata

2 Getting Started with Oracle OLAP

• Installing the Sample Schema
• Database Management Tasks
• Granting Privileges to DBAs and Application Developers
• Getting Started with Analytic Workspace Manager
  • Installing Analytic Workspace Manager
  • Opening Analytic Workspace Manager
  • Defining a Database Connection
  • Opening a Database Connection
  • Installing Plug-ins

3 Creating Dimensions and Cubes

• Designing a Dimensional Model for Your Data
• Introduction to Analytic Workspace Manager
  • Model View
  • Object View
• Creating a Dimensional Data Store Using Analytic Workspace Manager
  • Basic Steps for Creating an Analytic Workspace
  • Adding Functionality to Dimensional Objects
  • How Analytic Workspace Manager Saves Changes
• Creating Dimensions
  • Creating Levels
  • Creating Hierarchies
  • Creating Attributes
    • Automatically Defined Attributes
    • User-Defined Attributes
  • Mapping Dimensions
    • Mapping Window
    • Source Data Query
  • Loading Data Into Dimensions
  • Displaying the Dimension Members
• Creating Cubes
  • Creating Measures
  • Mapping Cubes
  • Choosing a Data Storage Strategy
    • Partitioning a Cube
    • Choosing a Dimension for Partitioning
    • Example of a Partitioned Dimension
  • Loading Data Into a Cube
  • Displaying the Data in a Cube
• Defining Measure Folders
• Supporting Multiple Languages
• Using Templates to Re-Create Dimensional Objects

4 Querying Dimensional Objects Using SQL

• Querying Dimensional Data in SQL
• Exploring the Shape of OLAP Views
  • Cube Views
  • Dimension Views
• Creating Basic Queries
  • Applying a Filter to Every Dimension
  • Allowing the Cube to Aggregate the Data
  • Query Processing
• Creating Hierarchical Queries
  • Drilling Down to Children
  • Drilling Up to Parents
  • Drilling Down to Descendants
  • Drilling Up to Ancestors
• Using Calculations in Queries
• Using Attributes for Aggregation
  • Aggregating Measures Over Attributes
  • Aggregating Calculated Measures Over Attributes
• Querying the Active Catalog

5 Querying Dimensional Objects Using OLAP Tools

• Analyzing Dimensional Data in a Spreadsheet
  • Getting Started With the OracleBI Spreadsheet Add-In
  • Creating a Query Using the Add-In
  • Using Excel Features on Oracle Dimensional Data
• Creating Reports in Discoverer Plus OLAP
  • Getting Starting with Discoverer Plus OLAP
  • Creating a Query
  • Formatting the Data in Discoverer Plus

6 Enhancing Your Database With Analytic Content

• What Is a Calculated Measure?
• Functions for Defining Calculations
• Creating Calculated Measures
• Using the Calculation Wizard
  • Basic Arithmetic Operations
  • Percent Variance
  • Index
  • Rank
  • Share
  • Cumulative Total
  • Prior and Future Time Periods
  • Moving Calculations
  • Period to Date Calculations
  • Nested Calculations
• Generating Forecasts, Allocations, and Aggregations

7 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 Data
  • 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 the Forecast
  • Validating the Forecast
  • Preparing the Sales Forecast Measure for Querying

8 Advanced Aggregations

• What is Aggregation?
• Aggregation Operators
  • Basic Operators
  • Scaled and Weighted Operators
  • Hierarchical Operators
• When Does Aggregation Order Matter?
  • Using the Same Operator for All Dimensions of a Cube
    • Order Has No Effect
    • Order Changes the Aggregation Results
    • Order May Be Important
  • Example: Mixing Aggregation Operators
• Aggregating Compressed Cubes
• Aggregating Uncompressed Cubes
  • Selecting Dimensions for Skip-Level Aggregation
  • Selecting the Levels to Skip
• Aggregating a Slice of a Measure
• Improving Aggregation Performance
  • Finish Data Updates on Time
  • Keep Within Allocated Resources
  • Provide Good Response Time

9 Allocations

• 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
• Case Study: Allocating a Sales Forecast
  • Creating an Allocation Basis Measure
  • Creating the Allocate Sales Forecast Step
  • Generating and Validating the Allocation

10 Developing Reports and Dashboards

• Developing SQL Applications for Dimensional Data
• Developing a Report Using BI Publisher
  • Creating an OLAP Report in BI Publisher
  • Creating a Template in Microsoft Word
  • Generating a Formatted Report
  • Adding Dimension Choice Lists
    • Creating a List of Values
    • Creating a Menu
    • Editing the Query
• Developing a Dashboard Using Application Express
  • Creating an OLAP Application in Application Express
  • Adding Dimension Choice Lists
    • Creating a Region
    • Creating a List of Values
    • Creating the Choice List
    • Editing the Query
  • Drilling on Dimension Columns
    • Creating Hidden Items
    • Editing the Query
    • Adding Links to the Dimension Columns

11 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

12 Administering Oracle OLAP

• Setting Database Initialization Parameters
• Storage Management
  • Creating an Undo Tablespace
  • Creating Permanent Tablespaces for OLAP Use
  • Creating Temporary Tablespaces for OLAP Use
  • Spreading Data Across Storage Resources
• Security of Multidimensional Data in Oracle Database
  • Security Management
  • Granting Querying Privileges
• Dictionary Views and System Tables
  • Static Data Dictionary Views
  • System Tables
  • Analytic Workspace Tables
  • Build Logs
• Partitioned Cubes and Parallelism
  • Creating and Dropping Partitions
  • Parallelism
• Monitoring Analytic Workspaces
  • Dynamic Performance Views
  • 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?
    • When Were the Analytic Workspaces Created?
  • OLAP DBA Scripts
  • Scripts for Monitoring Performance
  • Scripts for Monitoring Disk Space
• Backup and Recovery
• Export and Import

A Designing a Dimensional Model

• 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?
    • Which 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 Dimensional Model for Global Computing
  • Identifying Dimensions
  • Identifying Levels
  • Identifying Hierarchies
  • Identifying Stored Measures

Glossary

Index