Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Organization

Related Documentation

Conventions

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

The OLAP API 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

Operators

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

Using NASKIP

Using NASKIP2

Using NAFILL

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

Using SQL PREPARE and SQL EXECUTE

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

SQLCODE Option

SQLERRM Option

SQLMESSAGES Option

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

Index