Oracle OLAP Application Developer's Guide - Personal Web Pages

414 Pages·2003·4.72 MB·English

by  Oracle Corporation

Oracle® OLAP
Application Developer's Guide
10g Release 1 (10.1)
Part No. B10333-02
December 2003
Oracle OLAP Application Developer’s Guide, 10g Release 1 (10.1)
Part No. B10333-02
Copyright © 2002, 2003 Oracle Corporation. All rights reserved.
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent and other intellectual and industrial property
laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required
to obtain interoperability with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error-free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Express, Personal Express, Oracle Discoverer, PL/SQL, and
SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Other names may be
trademarks of their respective owners.
Contents
Send Us Your Comments ................................................................................................................ xxi
Preface......................................................................................................................................................... xxiii
Intended Audience ............................................................................................................................. xxiii
Documentation Accessibility ............................................................................................................ xxiii
Structure.............................................................................................................................................. xxiv
Related Documents............................................................................................................................ xxvi
Conventions....................................................................................................................................... xxvii
Part I Fundamentals
1 Overview
OLAP Technology Within the Oracle Database ........................................................................... 1-1
Problems Maintaining Two Distinct Systems .......................................................................... 1-1
Full Integration of Multidimensional Technology .................................................................. 1-2
Using OLAP to Answer Business Questions ................................................................................ 1-2
Common Analytical Applications ................................................................................................... 1-3
Deciding When to Use Analytic Workspaces................................................................................ 1-4
Working With Oracle OLAP ............................................................................................................. 1-5
OLAP Analytic Engine ................................................................................................................ 1-6
Analytic Workspaces.................................................................................................................... 1-6
Analytic Workspace Manager .................................................................................................... 1-6
OLAP Worksheet.......................................................................................................................... 1-6
SQL Interface to OLAP ................................................................................................................ 1-7
iii
OLAP DML.................................................................................................................................... 1-7
OLAP Catalog ............................................................................................................................... 1-7
Analytic Workspace Java APIs ................................................................................................... 1-8
OLAP API ...................................................................................................................................... 1-8
Oracle Enterprise Manager.......................................................................................................... 1-8
Oracle Warehouse Builder........................................................................................................... 1-9
Process Overview: Creating and Maintaining Analytic Workspaces ....................................... 1-9
2 The Multidimensional Data Model
The Logical Multidimensional Data Model .................................................................................. 2-1
Logical Cubes ................................................................................................................................ 2-2
Logical Measures .......................................................................................................................... 2-2
Logical Dimensions ...................................................................................................................... 2-3
Logical Hierarchies and Levels................................................................................................... 2-3
Logical Attributes ......................................................................................................................... 2-4
The Relational Implementation of the Model .............................................................................. 2-4
Dimension Tables ......................................................................................................................... 2-5
Fact Tables...................................................................................................................................... 2-6
Materialized Views....................................................................................................................... 2-6
The Analytic Workspace Implementation of the Model............................................................. 2-6
Multidimensional Data Storage in Analytic Workspaces....................................................... 2-7
Database Standard Form Analytic Workspaces....................................................................... 2-9
Analytic Workspace Dimensions ............................................................................................. 2-10
Use of Dimensions in Standard Form Analytic Workspaces ........................................ 2-10
Analytic Workspace Variables.................................................................................................. 2-11
Use of Variables to Store Measures................................................................................... 2-12
Use of Variables to Store Attributes.................................................................................. 2-12
Analytic Workspace Formulas.................................................................................................. 2-12
Analytic Workspace Relations .................................................................................................. 2-13
3 The Sample Schema
Case Study Scenario ........................................................................................................................... 3-1
Reporting Requirements.............................................................................................................. 3-2
Business Goals............................................................................................................................... 3-3
Information Requirements .......................................................................................................... 3-3
iv
Business Analysis Questions ............................................................................................... 3-3
What products are profitable? ............................................................................................. 3-4
Who are our customers, and what and how are they buying?....................................... 3-4
What accounts are most profitable?.................................................................................... 3-5
What is the performance of each distribution channel? .................................................. 3-5
Is there still a seasonal variance to the business? ............................................................. 3-6
Summary of Information Requirements ............................................................................ 3-6
Identifying Required Business Facts.......................................................................................... 3-6
Designing a Logical Data Model for Global Computing........................................................ 3-7
Identifying Dimensions ........................................................................................................ 3-7
Identifying Levels.................................................................................................................. 3-7
Identifying Hierarchies......................................................................................................... 3-8
Identifying Stored Measures................................................................................................ 3-8
The Global Star Schema.................................................................................................................... 3-9
Dimension Table: TIME_DIM................................................................................................... 3-11
Dimension Table: CUSTOMER_DIM ...................................................................................... 3-12
Dimension Table: PRODUCT_DIM......................................................................................... 3-13
Dimension Table: CHANNEL_DIM........................................................................................ 3-14
Fact Tables: UNITS_HISTORY_FACT and _UPDATE_FACT............................................. 3-14
Fact Tables: PRICE_AND_COST_HISTORY_FACT and _UPDATE_FACT ..................... 3-15
Mapping the Global Schema to an Analytic Workspace .......................................................... 3-16
Global Product Dimension Mapping ...................................................................................... 3-16
Global Time Dimension Mapping............................................................................................ 3-17
Global Price Cube Mapping...................................................................................................... 3-19
4 Developing Java Applications for OLAP
Building Analytical Java Applications........................................................................................... 4-1
About Java ..................................................................................................................................... 4-1
The Java Solution for OLAP........................................................................................................ 4-2
Oracle Java Development Environment.................................................................................... 4-3
Introducing the BI Beans................................................................................................................... 4-3
Metadata ........................................................................................................................................ 4-4
Navigation ..................................................................................................................................... 4-4
Formatting ..................................................................................................................................... 4-4
Graphs ............................................................................................................................................ 4-5
v
Crosstabs ........................................................................................................................................ 4-5
Tables.............................................................................................................................................. 4-5
Data Beans ..................................................................................................................................... 4-6
Wizards .......................................................................................................................................... 4-6
Understanding the OLAP API.......................................................................................................... 4-6
How the OLAP API Accesses Multidimensional Data ........................................................... 4-7
Calculation Capabilities ............................................................................................................... 4-8
Intelligent Caching ....................................................................................................................... 4-8
Managing Data Sources for the BI Beans and OLAP API .......................................................... 4-9
Part II Fundamentals of Creating and Using Analytic Workspaces
5 Defining a Logical Multidimensional Model
Introduction to OLAP Metadata ...................................................................................................... 5-1
Creating Metadata for Your Source Data ................................................................................. 5-3
For Source Data in a Basic Star or Snowflake Schema ..................................................... 5-3
For Dimension Tables with Complex Hierarchies............................................................ 5-4
For Other Schema Configurations ...................................................................................... 5-5
Creating Metadata for Your Analytic Workspace ................................................................... 5-5
Creating Metadata for Your Applications................................................................................. 5-6
Overview of the OLAP Catalog........................................................................................................ 5-6
OLAP Catalog Components........................................................................................................ 5-7
About CWM1 ......................................................................................................................... 5-7
About CWM2 ......................................................................................................................... 5-7
Steps for Creating OLAP Metadata............................................................................................ 5-8
Choosing a Tool for Creating OLAP Catalog Metadata .............................................................. 5-8
Creating Metadata for an Analytic Workspace........................................................................ 5-8
Creating Metadata Using Oracle Enterprise Manager............................................................... 5-11
Procedure: Accessing OLAP Management ............................................................................. 5-11
Defining Metadata for Dimension Tables ............................................................................... 5-12
Information That You Supply for Dimensions................................................................ 5-12
Time Dimension................................................................................................................... 5-12
Procedure: Defining a Logical Dimension in the OLAP Catalog ................................. 5-13
vi
Defining Metadata for Fact Tables ........................................................................................... 5-13
Information That You Supply for Cubes ......................................................................... 5-13
Procedure: Defining a Logical Cube in the OLAP Catalog........................................... 5-14
Case Study: Creating Metadata for the GLOBAL Star Schema............................................... 5-14
Defining a Logical Time Dimension for the Global Schema ................................................ 5-15
Defining a Logical Units Cube for the Global Schema.......................................................... 5-16
Creating Metadata Using PL/SQL ................................................................................................. 5-16
CWM2 Packages for Creating OLAP Dimensions................................................................. 5-17
CWM2 Packages for Creating Cubes....................................................................................... 5-17
CWM2 Package for Mapping Metadata.................................................................................. 5-17
CWM2 Package for Creating Level-Based Dimension Tables ............................................ 5-18
CWM2 Packages for Classification and Validation............................................................... 5-18
6 Creating an Analytic Workspace
Methods of Creating a Workspace................................................................................................... 6-1
Introduction to Analytic Workspace Manager.............................................................................. 6-3
OLAP Catalog View .................................................................................................................... 6-4
Object View .................................................................................................................................. 6-5
OLAP Worksheet.......................................................................................................................... 6-6
Opening a Database Connection With Analytic Workspace Manager................................. 6-7
Creating a Standard Form Workspace Using Analytic Workspace Manager.......................... 6-7
Choosing a Schema for the Analytic Workspace ..................................................................... 6-8
Setting Advanced Storage Options............................................................................................ 6-8
Defining a Composite Dimension....................................................................................... 6-8
Ordering the Dimensions in a Cube ................................................................................... 6-9
Setting the Segment Size ...................................................................................................... 6-9
Choosing Build Options ............................................................................................................ 6-10
Generating Scripts ...................................................................................................................... 6-10
Basic Steps for Creating a Standard Form Workspace.......................................................... 6-10
Case Study: Creating the Global Analytic Workspace .............................................................. 6-12
Defining the GLOBAL_AW Workspace User ........................................................................ 6-12
Examining Sparsity Characteristics for GLOBAL.................................................................. 6-12
Running the Create Analytic Workspace Wizard ................................................................. 6-13
Manually Changing Object Definitions................................................................................... 6-13
Completing the Build................................................................................................................. 6-15
vii
Case Study: Creating the Sales History Analytic Workspace................................................... 6-16
Defining Startup Parameters for the SH Build ....................................................................... 6-16
Defining Tablespaces for SH ..................................................................................................... 6-16
Examining the Sparsity Characteristics of SH Data............................................................... 6-17
Managing the SH Build.............................................................................................................. 6-17
Running the Create Analytic Workspace Wizard.................................................................. 6-18
Building the Sales History Analytic Workspace .................................................................... 6-19
Generating Aggregate Data ............................................................................................................ 6-19
Strategies for Calculating Aggregates ..................................................................................... 6-19
How to Select Levels to Pre-Aggregate and Store ................................................................. 6-20
About Aggregation Plans .......................................................................................................... 6-20
How to Create and Deploy an Aggregation Plan .................................................................. 6-21
Creating an Aggregation Plan ........................................................................................... 6-21
Changing the Aggregation Operator................................................................................ 6-21
Deploying an Aggregation Plan........................................................................................ 6-22
Case Study: Aggregating Data in the GLOBAL Analytic Workspace .................................... 6-23
Identifying Levels for Precalculation....................................................................................... 6-23
Aggregating the Global Price Cube ......................................................................................... 6-24
Enabling an Analytic Workspace for an Application ................................................................ 6-24
How to Enable an Analytic Workspace................................................................................... 6-25
About Enabling for the BI Beans .............................................................................................. 6-25
Star Schema of Views.......................................................................................................... 6-25
OLAP Catalog Metadata for Analytic Workspaces........................................................ 6-26
How to Enable an Analytic Workspace for Oracle Discoverer............................................ 6-27
About Enabling for Oracle Discoverer ................................................................................... 6-27
Views Created for Discoverer............................................................................................ 6-28
Refreshing the Data in an Analytic Workspace .......................................................................... 6-31
Using the Refresh Wizard.......................................................................................................... 6-31
Refreshing From Different Relational Tables ......................................................................... 6-31
Case Study: Refreshing the Units Cube................................................................................... 6-32
When a Data Refresh Requires Re-Enabling .......................................................................... 6-33
viii
7 SQL Access to Analytic Workspaces
Overview of SQL Access ................................................................................................................... 7-1
Manipulating Analytic Workspace Data................................................................................... 7-1
Querying an Analytic Workspace.............................................................................................. 7-3
About the Active Catalogs .......................................................................................................... 7-3
Support for Custom Measures ......................................................................................................... 7-3
Methods of Defining Custom Measures ................................................................................... 7-3
Analytic Support for Custom Measures.................................................................................... 7-4
Forecasts and Regressions ................................................................................................... 7-4
Time Series Manipulation .................................................................................................... 7-4
Financial Operations ............................................................................................................ 7-5
Statistical Operations ............................................................................................................ 7-5
Numeric Computations........................................................................................................ 7-5
Text Manipulation................................................................................................................. 7-6
Allocation ............................................................................................................................... 7-6
Aggregation ........................................................................................................................... 7-6
Models .................................................................................................................................... 7-7
Creating Custom Measures Using DBMS_AW_UTILITIES ...................................................... 7-7
Case Study: Adding Sales to Global Using DBMS_AW_UTILITIES ...................................... 7-8
Acquiring Information About the Analytic Workspace ........................................................ 7-8
Using DBMS_AW_UTILITIES to Define Sales as a Custom Measure.................................. 7-9
Viewing the Workspace Formula............................................................................................. 7-10
Querying the Sales Custom Measure ...................................................................................... 7-10
Creating Custom Measures Using OLAP_EXPRESSION......................................................... 7-11
Case Study: Adding Sales to Global Using OLAP_EXPRESSION......................................... 7-11
Using OLAP_TABLE for Direct Access to Workspace Data ..................................................... 7-12
Designing Views of an Analytic Workspace .......................................................................... 7-12
Process Overview ....................................................................................................................... 7-13
Using OLAP_TABLE.................................................................................................................. 7-13
Using the SELECT MODEL Clause ......................................................................................... 7-14
Case Study: Using OLAP_TABLE to Create Global Custom Measures................................. 7-15
Defining Formulas in the Analytic Workspace...................................................................... 7-16
Querying an Analytic Workspace Using OLAP_TABLE ..................................................... 7-16
OLAP_TABLE Function ..................................................................................................... 7-18
SELECT Statement .............................................................................................................. 7-18
ix
Using OLAP_TABLE to Create a Measure View for the BI Beans ..................................... 7-18
Creating and Executing the SQL Script............................................................................ 7-19
About the Sample Script..................................................................................................... 7-20
Defining OLAP Catalog Metadata for Workspace Views .................................................... 7-20
8 Exploring a Standard Form Analytic Workspace
About Workspaces Created Using OLAP Tools ............................................................................ 8-2
About Database Standard Form ................................................................................................. 8-2
Standard Form Implementation of the Logical Model............................................................ 8-3
Additional Requirements for OLAP Tools ............................................................................... 8-4
Querying a Standard Form Analytic Workspace .......................................................................... 8-4
Querying the Standard Form Catalogs...................................................................................... 8-4
Querying Properties ..................................................................................................................... 8-5
Standard Form Dimensions .............................................................................................................. 8-6
Dimdef Dimension ....................................................................................................................... 8-6
Contents of an Analytic Workspace Dimension ............................................................... 8-7
Properties of an Analytic Workspace Dimdef Dimension .............................................. 8-7
Standard Form Metadata for Dimensions................................................................................. 8-9
ALL_DIMENSIONS Dimension.......................................................................................... 8-9
ALL_DESCRIPTIONS Variable for Dimensions............................................................... 8-9
AW_NAMES Variable for Dimensions .............................................................................. 8-9
DIM_LEVELS Valueset......................................................................................................... 8-9
Standard Form Hierarchies ............................................................................................................... 8-9
Hierlist Dimension...................................................................................................................... 8-10
Contents of a Hierlist Dimension...................................................................................... 8-10
Properties of a Hierlist Dimension.................................................................................... 8-10
Member_Parentrel Relation ...................................................................................................... 8-11
Contents of a Member_Parentrel Relation....................................................................... 8-11
Properties of a Member_Parentrel Relation..................................................................... 8-12
Member_Gid Variable................................................................................................................ 8-12
Contents of a Member_GID Variable ............................................................................... 8-12
Properties of a Member_Gid Variable.............................................................................. 8-13
Member_Inhier Variable............................................................................................................ 8-13
Contents of a Member_Inhier Variable ............................................................................ 8-14
Properties of a Member_Inhier Variable.......................................................................... 8-14
x