Skip Headers

Table of Contents Image Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index


Go to next page


Title and Copyright Information

Send Us Your Comments


Related Documentation
Documentation Accessibility

What's New in the OLAP DML?

Oracle9i Release 2 (9.2) New Features in the OLAP DML

Part I Introduction

1 Basic Concepts

What Is the OLAP DML?
Analytic Workspaces
SQL and the OLAP DML
Using the OLAP DML
How to Use the OLAP DML to Analyze Data
Creating an Analytic Workspace
Loading Data Into Analytic Workspaces
Temporary vs. Persistent Analytic Workspaces
Sharing Data In Analytic Workspaces
Accessing a Workspace from OLAP Worksheet
Procedures: How to Open OLAP Worksheet
Establishing a Connection
Executing Commands
Editing an OLAP DML Program
Closing the Connection
Accessing a Workspace from SQL-Based Applications
Using SQL SELECT Statements
Using Embedded OLAP DML Commands
Accessing a Workspace from a Java Application
Using OLAP Metadata
Using Embedded OLAP DML Commands

2 Defining and Working with Analytic Workspaces

Using the OLAP DML to Work with Analytic Workspaces
Current Analytic Workspace
How to Create An Analytic Workspace
How to Attach an Analytic Workspace
Specifying the Analytic Workspace Attachment Mode
Sharing Analytic Workspaces
How to Detach an Analytic Workspace
How to Delete an Analytic Workspace
Workspace Localization Settings
Attaching Multiple Analytic Workspaces
Qualified Object Names
Multiple AUTOGO and Permission Programs
Using Names and Aliases for Analytic Workspaces
Workspace Names
Workspace Aliases
Saving Analytic Workspace Changes
UPDATE Command
COMMIT Command
Effect of the ROLLBACK Command
Minimizing Analytic Workspace Growth
Executing Programs Automatically
Program Names
AUTOGO Program Example
Adding Security to an Analytic Workspace
Permission Programs
Creating and Designing Permission Programs
Importing and Exporting Workspace Objects
Obtaining Analytic Workspace Information
Obtaining General Information About an Analytic Workspace
Viewing Objects in an Analytic Workspace
Obtaining Information About Objects

3 Defining Data Objects

Overview: Defining Workspace Objects
Workspace Objects That You Can Define
Data Types
Numeric Data Types
Examples of Literal Numeric Values
Text Data Types
Escape Sequences
Examples of Literal Text Values
Boolean Data Type
Date Data Types
Defining Dimensions
Determining What Dimensions to Define
How Data For Simple Flat Dimensions Is Stored
Defining Dimension Surrogates
Differences Between Dimensions and Dimension Surrogates
Defining Relations
How Relations Are Dimensioned
How Relation Data Is Stored
Example: Relation Between Two Dimensions
Example: Self-relation
Defining Variables
Types of Variables
How Variable Data Is Stored
Defining Variables That Handle Sparse Data Efficiently
Definition: Composite
Why You Should Use Named Composites
How to Use Composites
Naming, Renaming, and Unnaming Composites
Adding Data to a Variable That Uses a Composite
Defining a Variable with a Single-Dimension Composite
Defining Hierarchical Dimensions and Variables That Use Them
Defining a Variable with a Hierarchical Dimension
Example: Variable with a Hierarchical Dimension
Defining Concat Dimensions and Variables That Use Them
Example: Variable with a Concat Dimension
Changing the Definition of an Object

4 Working with Expressions

Introducing Expressions
Data Types of Expressions
How the Data Type of an Expression is Determined
Changing the Data Type of an Expression
Saving an Expression
Dimensionality of Expressions
Determining the Dimensions of an Expression
How Dimension Status Affects the Results of Expressions
Specifying a Single Value for the Dimension of an Expression
Qualifying a Variable
Replacing a Dimension in a Variable
Qualifying a Relation
Qualifying a Dimension
Using Ampersand Substitution with QDRs
Using the QUAL Function to Specify a QDR
Using Workspace Objects in Expressions
Using Dimensions or Dimension Surrogates in Expressions
Using Composites in Expressions
Using Variables in Expressions
Using Variables Defined with Composites in Expressions
Default Behavior of Commands That Loop Over Variables
Using Relations In Expressions
Using Functions in Expressions
Numeric Expressions
Arithmetic Operators
Mixing Numeric Data Types
Automatic Conversion of Numeric Data Types
Using Dimensions in Arithmetic Expressions
Using Dates in Arithmetic Expressions
Limitations of Floating Point Calculations
Controlling Errors During Calculations
Text Expressions
Working with Dates in Text Expressions
Working with NTEXT Data
Boolean Expressions
Creating Boolean Expressions
Comparing NA Values in Boolean Expressions
Controlling Errors When Comparing Numeric Data
Controlling Errors Due to Numerical Precision
Controlling Errors When Comparing Floating Point Numbers
Controlling Errors When Comparing Different Numeric Data Types
Comparing Dimension Values
Comparing Dates
Comparing Text Data
Comparing a Text Value to a Text Pattern
Comparing Text Literals to Relations
Conditional Expressions
Substitution Expressions
Working with NA Values
Controlling how NA values are treated
Working with the NATRIGGER Property

5 Populating Workspace Data Objects

Overview: Populating an Analytic Workspace
Maintaining Dimensions and Composites
How Maintaining a Dimension Affects Dimension Status
Avoiding Deferred Maintenance
Adding Values to Dimensions
Updating Relations When Merging New Values
Deleting Values from Dimensions
Deleting Values from Conjoint Dimensions
Changing the Position of Dimension Values
Storing Dimension Values in Sorted Order
Maintaining Composites and Conjoint Dimensions
Maintaining Composites
Maintaining Conjoint Dimensions
Maintaining Concat Dimensions
Assigning Values to Data Objects
Using Objects in Assignment Statements
How Values Are Assigned to Variables with Composites
Assigning Values to Relations
Assigning Values to Dimensions
Assigning Values to Specific Cells of a Data Object
Calculating and Analyzing Data

6 Selecting Data

Introducing Dimension Status
Changing the Current Status List
Changing the Default Status List
Identifying and Retrieving Status Lists
Saving and Restoring Dimension Status
Limiting to a Simple List of Values
Limiting Using a Boolean Expression
How LIMIT Handles Boolean Multidimensional Expressions
Limiting to Values That Match an Expression
Limiting to the Top or Bottom Values
Limiting to the Values of a Related Dimension
How Limiting to a Related Dimension Determines Status
Suppressing the Sort When Limiting to a Related Dimension
Limiting Based on the Position of a Value in a Dimension
Limiting Using Value Position in its Dimension
Limiting Using Value Position in an Unrelated Dimension
Limiting Based on a Relationship Within a Hierarchy
Differences Between HIERARCHY and DESCENDANTS Keywords
Limiting Composites and Conjoint Dimensions
Ways of Limiting Conjoint Dimensions
Limiting Conjoint Dimensions Using Value Combinations
Limiting Conjoint Dimensions Using Base Dimension Values
Limiting Concat Dimensions
Working with Null Status
Managing Null Status in a Program
Errors When Limiting Status to a Null Value
Working with Valuesets
Creating a Valueset
Limiting Using a Valueset
Changing the Values of a Valueset
Identifying and Retrieving the Values in a Valueset
Retrieving the Values in a Valueset
Retrieving the Dimension Positions of Values in a Valueset

Part II Applications Development

7 Developing Programs

Introduction to OLAP DML Programs
Executing Programs
Executing User-Defined Functions
Defining and Editing Programs
Formatting Guidelines for Editing Programs
Using Variables in Programs
Global Versus Modular Design Approaches
Defining Temporary Variables
Defining Local Variables
Passing Arguments
Using the ARGUMENT Command
Using Multiple Arguments
Passing Arguments as Text with Ampersand Substitution
Passing Object Names and Keywords
Writing User-Defined Functions
Data Type of a User-Defined Function
Arguments in a User-Defined Function
Controlling the Flow of Execution
Guidelines for Constructing a Label
Alternatives to the GOTO Command
Directing Output
Capturing Error Messages
Preserving the Session Environment
Changing the Program Environment
Ways to Save and Restore Environments
Saving the Status of a Dimension or the Value of an Option
Saving Several Values at Once
Using Level Markers
Using CONTEXT to Save Several Values at Once
Handling Errors
How An Error Is Signaled
How An Error Is Trapped
Handling Errors While Saving the Session Environment
Suppressing Error Messages
Identifying the Error That Occurred
Creating Your Own Error Messages
Handling Errors in Nested Programs
Compiling Programs
Finding Out If a Program Has Been Compiled
Programming Methods That Prevent Compilation
Testing and Debugging Programs
Generating Diagnostic Messages
Identifying Bad Lines of Code
Sending Output to a Debugging File
Creating a debugging file
Specifying the contents of the debugging file

8 Working with Models

Using Models to Calculate Data
How Dimension Values Are Treated in a Model
Creating a Nested Hierarchy of Models
Working with the INCLUDE Command
Basic Modeling Commands
Writing Equations in a Model
Writing DIMENSION and INCLUDE Commands
Compiling a Model
Simple Blocks
Step Blocks
Simultaneous Blocks
Running a Model
Using Data from Past and Future Time Periods
Solving Simultaneous Equations
Debugging a Model
Modeling for Multiple Scenarios
Building a Scenario Model

9 Allocating Data

Introduction to Allocation
Preparing for an Allocation
Creating an Aggregation Map for Allocation
Using the Allocation Operators and Arguments
Using the HEVEN and MAX Operators and the ADD Argument
Using the COPY Operator and the PROTECT Argument
Using the HFIRST and HLAST Operators
Using the PROPORTIONAL Operator

Part III Analytic Workspace Management

10 Working with Relational Tables

Issuing SQL Statements Through the OLAP DML
Supported SQL Statements
Unsupported SQL Statements
Creating an Analytic Workspace from Relational Tables
Process: Designing and Defining an Analytic Workspace to Hold Relational Data
Process: Writing Programs that Populate Analytic Workspaces with Relational Data
Declaring a Cursor
Example: Declaring a Cursor
Using Variables in the WHERE Clause of the SELECT Statement
Using Conjunctions in a WHERE Clause
Opening a Cursor
Importing and Fetching Relational Table Data into Analytic Workspace Objects
Example: Copying Relational Table Data into Analytic Workspace Objects
Closing a Cursor
Cleaning up the SQL Cursors
Example: Creating an Analytic Workspace from Sales History Tables
Designing and Defining an Analytic Workspace for Sales History Data
Populating Analytic Workspace Objects with Sales History Data
Writing Data from Analytic Workspace Objects into Relational Tables
Performing a Direct Insert
Inserting Workspace Data into Relational Tables: Example
Conditionally Updating a Relational Table
Using Stored Procedures and Triggers
Executing a stored procedure
Checking for Errors

11 Reading Data from Files

Introducing Data-Reading Programs
Reading Files
Creating a Program to Read Data
Specifying File Names in the OLAP DML
Reading Data from Files
Reading Structured PRN Files
Reading and Maintaining Dimension Values
Adding New Dimension Values from a Data File
Reading Dimension Values by Position
The Use of Composites
Reading and Maintaining Conjoint Dimensions
Translating Coded Dimension Values
Processing Input Data
Specifying a Conversion Type for Data
Processing Records Individually
Reading Different Records
Processing Several Values for One Variable

12 Aggregating Data

About Aggregating Detail Data
Functionality Available with AGGREGATE
Process Overview: Aggregation
Preliminary Steps Prior to Aggregation
Identifying the Parent and Level Relations
Verifying That All Composites Use BTREE Indexes
Creating an Aggregation Map
How to Define an Aggmap Object
How to Add Contents to an Aggmap Object
Contents of an Aggregation Map
How to Compile an Aggregation Map
Aggregating Multiple Variables with a Single Command
About the RELATION Command
Specifying an Aggregation Method
Selecting Data For Aggregation
Caching Runtime Aggregates
Aggregating Non-Hierarchical Data
How to Generate Precalculated Data
Effects of Dimension Status
Monitoring Progress
How to Calculate Data at Runtime
Setting Up Calculation on the Fly
Adding the $NATRIGGER Property to a Variable
Creating Custom Aggregates
Balancing Precalculated and Runtime Aggregation
Selecting Dimensions for Runtime Calculation
Selecting Levels for Runtime Calculation
Performing Partial Aggregations
Aggregation Changes That Cause Problems
Incremental Data Loading
Problem: PRECOMPUTE Status List Is Inaccurate
Solution: Regenerate the PRECOMPUTE Status List
Using a Data-Dependent PRECOMPUTE Clause
Problem: Values of the Limit Clause Vary With Each Data Update
Solution: Maintain a Valueset
Changing a Hierarchy
Problem: Previously Aggregated Data is Incorrect
Solution: Re-Aggregate Changed Branches
How to Aggregate Branches of a Hierarchy
Combining AGGREGATE with Forecasts and Programs
When to Use Multiple Aggregation Maps
Problem: Different Aggregation Maps Generate Different Status Lists
Solution: Create a Separate AGGMAP for the AGGREGATE Function


Go to next page
Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index