Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle OLAP?

• Oracle Database 11g Release 2 (11.2.0.2) Oracle OLAP
• Oracle Database 11g Release 2 (11.2) Oracle OLAP
• Oracle Database 11g Release 1 (11.1) Oracle OLAP

1 Overview

• OLAP Technology in the Oracle Database
  • Full Integration of Multidimensional Technology
  • Ease of Application Development
  • Ease of Administration
  • Security
  • Unmatched Performance and Scalability
  • Reduced Costs
• Developing Reports and Dashboards Using SQL Tools and Application Builders
• Overview of the Dimensional Data Model
  • Cubes
  • Measures
  • Dimensions
  • Hierarchies and Levels
    • Level-Based Hierarchies
    • Value-Based Hierarchies
  • Attributes

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 Plugins
• Upgrading Metadata From Oracle OLAP 10g

3 Creating Dimensions and Cubes

• Designing a Dimensional Model for Your Data
• Introduction to Analytic Workspace Manager
• Creating a Dimensional Data Store Using Analytic Workspace Manager
  • Adding Functionality to Dimensional Objects
  • When Does Analytic Workspace Manager Save Changes?
• Creating Dimensions
  • Creating Levels
  • Creating Hierarchies
  • Creating Attributes
    • Automatically Defined Attributes
    • User-Defined Attributes
    • Unique Key Attributes
  • Mapping Dimensions
    • Dimension Mapping Window
    • Source Data Query
  • Loading Data Into Dimensions
  • Displaying the Dimension View
  • Displaying the Default Hierarchy
• Creating Cubes
  • Creating Measures
  • Mapping Cubes
  • Choosing a Partitioning Strategy
    • Choosing a Dimension for Partitioning
    • Example of a Partitioned Dimension
  • Loading Data Into Cubes
  • Displaying the Data in a Cube
  • Displaying the Cube View Descriptions
• Choosing a Data Maintenance Method
  • Creating and Executing Custom Cube Scripts
    • Creating Cube Scripts
    • Running a Cube Script
  • Creating and Executing Maintenance Scripts
    • Creating Maintenance Scripts
    • Running Maintenance Scripts
  • Adding Materialized View Capability to a Cube
• Supporting Multiple Languages
• Defining Measure Folders
• Using Templates to Re-Create Dimensional Objects

4 Querying Dimensional Objects

• Exploring the OLAP Views
  • Cube Views
    • Discovering the Names of the Cube Views
    • Discovering the Columns of a Cube View
    • Displaying the Contents of a Cube View
  • Dimension and Hierarchy Views
    • Discovering the Names of Dimension and Hierarchy Views
    • Discovering the Columns of a Dimension View
    • Displaying the Contents of a Dimension View
    • Discovering the Columns of a Hierarchy View
    • Displaying the Contents of a Hierarchy View
• 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
• Viewing Execution Plans
  • Generating Execution Plans
  • Types of Execution Plans
• Querying the Data Dictionary

5 Enhancing Your Database with Analytic Content

• What Is a Calculated Measure?
• Functions for Defining Calculations
  • Arithmetic Operators
  • Analytic Functions
  • Single-Row Functions
• Creating Calculated Measures
  • Modifying a Template
  • Choosing a Range of Time Periods
• Using Calculation Templates
  • Arithmetic Calculations
  • Index
  • Prior and Future Periods
  • Period to Date
  • Share
  • Rank
  • Parallel Period
  • Moving Calculations
  • Cumulative Calculations
  • Nested Calculations
• Creating User-Defined Expressions
  • Using the OLAP Expression Syntax
  • Expression Syntax Example Using an Arithmetic Operator
  • Free-Form Calculation Example Using an Analytic Function
  • Expression Syntax Analytic Functions
• Creating Calculated Measures Using the OLAP DML
  • Selecting an OLAP DML Calculation Type
  • OLAP DML Expression Examples
  • OLAP DML Function Example

6 Developing Reports and Dashboards

• Developing OLAP Applications
• 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

7 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
• Dictionary Views and System Tables
  • Static Data Dictionary Views
  • System Tables
  • Analytic Workspace Tables
  • Maintenance Logs
• Partitioned Cubes and Parallelism
  • Querying Metadata for Cube Partitioning
  • Creating and Dropping Partitions
  • Parallelism
• Analyzing Cubes and Dimensions
• 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
  • Monitoring Disk Space
• Backup and Recovery
• Export and Import
• Cube Materialized Views
  • Acquiring Information From the Data Dictionary
    • Identifying Cube Materialized Views
    • Identifying the Refresh Logs
  • Initiating a Data Refresh
    • Using DBMS_CUBE
    • Using DBMS_MVIEW
  • Refresh Methods
    • Refresh Method Descriptions
    • Fast Solve Refreshes
  • Using Query Rewrite
  • Acquiring Additional Information About Cube Materialized Views

8 Security

• Security of Multidimensional Data in Oracle Database
  • Security Management
  • Types of Security
  • About the Privileges
  • Layered Security
• Setting Object Security
  • Using SQL to Set Object Security
    • Setting Object Security on an Analytic Workspace
    • Setting Object Security on Dimensions
    • Setting Object Security on Cubes
  • Using Analytic Workspace Manager to Set Object Security
    • Setting Object Security on an Analytic Workspace
    • Setting Object Security on Dimensions
    • Setting Object Security on Cubes
• Creating Data Security Policies on Dimensions and Cubes

9 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
• Example: Aggregating the Units Cube
  • Selecting the Aggregation Operators and Hierarchies
  • Choosing the Percentage of Precomputed Values

A Designing a Dimensional Model

• Case Study Scenario
  • Reporting Requirements
  • Business Goals
  • Information Requirements
    • Business Analysis Questions
    • Summary of Information Requirements
• Identifying Required Business Facts
• Designing a Dimensional Model for Global Computing
  • Identifying Dimensions
  • Identifying Levels
  • Identifying Hierarchies
  • Identifying Stored Measures

B Keyboard Shortcuts

• Menu Bar
• Navigation Tree
• Property Sheets
• Shuttle Keys
• Mapping Canvas

Glossary

Index