Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

Part I Using the OLAP DML

1 Introduction to the OLAP DML

1.1 What is the OLAP DML?

1.2 Basic Syntactical Units

1.2.1 OLAP DML Options

1.2.1.1 Categories of Options

1.2.1.2 Syntax for Specifying and Retrieving Option Values

1.2.2 OLAP DML Properties

1.2.3 OLAP DML Commands and Functions

1.2.3.1 OLAP DML Commands

1.2.3.2 OLAP DML Functions

1.2.4 OLAP DML Programs

1.3 OLAP DML as a Data Definition Language

1.3.1 Statements for Creating Analytic Workspaces

1.3.2 Defining Analytic Workspace Objects

1.3.2.1 Defining Data Objects Using the OLAP DML

1.3.2.2 Defining Calculation Specification Objects Using the OLAP DML

1.3.3 Viewing Data Definitions

1.4 OLAP DML as a Data Manipulation Language

1.4.1 Startup Programs

1.4.1.1 ONATTACH Programs

1.4.1.2 Permission Programs

1.4.1.3 AUTOGO Programs

1.4.1.4 TRIGGER_AW Program

1.4.2 Data Loading Programs

1.4.2.1 Programs that Copy Data From Relational Tables to Workspace Objects

1.4.2.2 File-Reading Programs

1.4.2.3 Spreadsheet Import Programs

1.4.3 Trigger Programs

1.4.4 Aggregation, Allocation, and Modeling Programs

1.4.5 Forecasting Programs

1.4.6 Programs to Export and Import Workspace Objects

2 Data Types and Operators

2.1 OLAP DML Data Types

2.1.1 Numeric Data Types

2.1.1.1 Using LONGINTEGER Values

2.1.1.2 Using NUMBER Values

2.1.2 Text Data Types

2.1.2.1 Literals

2.1.2.2 Escape Sequences

2.1.3 Boolean Data Type

2.1.4 Date Data Types

2.1.4.1 Date and Time Options

2.1.4.2 DATE Values

2.1.4.3 DATETIME Values

2.1.4.4 Calculating Dates

2.1.5 Converting from One Data Type to Another

2.2 OLAP DML Operators

2.2.1 Arithmetic Operators

2.2.2 Comparison and Logical Operators

2.2.3 Assignment Operator

3 Expressions

3.1 Introducing OLAP DML Expressions

3.1.1 How the Data Type of an Expression is Determined

3.1.2 Changing the Data Type of an Expression

3.2 Using Workspace Objects in Expressions

3.2.1 Syntax for Specifying an Object in an Expression

3.2.1.1 Considerations When Creating and Using Qualified Object Names

3.2.2 How Objects Behave in Expressions

3.2.3 Using Variables in Expressions

3.2.4 Using Variables Defined with Composites in Expressions

3.3 Dimensionality of OLAP DML Expressions

3.3.1 Determining the Dimensions of an Expression

3.3.2 How Dimension Status Affects the Results of Expressions

3.3.3 Changing the Dimensionality of an Expression

3.4 Numeric Expressions

3.4.1 Numeric Options

3.4.2 Mixing Numeric Data Types

3.4.3 Automatic Conversion of Numeric Data Types

3.4.4 Using Dimensions in Arithmetic Expressions

3.4.5 Using Dates in Arithmetic Expressions

3.4.6 Limitations of Floating Point Calculations

3.4.7 Controlling Errors During Calculations

3.5 Text Expressions

3.5.1 Working with Dates in Text Expressions

3.5.2 Working with NTEXT Data

3.6 Boolean Expressions

3.6.1 Creating Boolean Expressions

3.6.2 Comparing NA Values in Boolean Expressions

3.6.3 Controlling Errors When Comparing Numeric Data

3.6.3.1 Controlling Errors Due to Numerical Precision

3.6.3.2 Controlling Errors When Comparing Floating Point Numbers

3.6.3.3 Controlling Errors When Comparing Different Numeric Data Types

3.6.4 Comparing Dimension Values

3.6.5 Comparing Dates

3.6.6 Comparing Text Data

3.6.6.1 Comparing a Text Value to a Text Pattern

3.6.6.2 Comparing Text Literals to Relations

3.7 Conditional Expressions

3.8 Substitution Expressions

3.9 Working with Empty Cells in Expressions

3.9.1 Specifying a Value of NA

3.9.2 Controlling how NA values are treated

3.9.2.1 Working with the $NATRIGGER Property

3.9.2.2 Using NASKIP

3.9.2.3 Using NASKIP2

3.9.2.4 Using NAFILL

3.10 Working with Subsets of Data

3.10.1 Working with Dimension Status

3.10.1.1 Changing the Status List of a Dimension

3.10.1.2 Saving and Restoring Current Dimension Status

3.10.2 Specifying a List of Dimension Values for an Expression or Subexpression

3.10.3 Specifying a Single Data Value in an Expression

3.10.3.1 Form of a Qualified Data Reference

3.10.3.2 Qualifying a Variable

3.10.3.3 Replacing a Dimension in a Variable

3.10.3.4 Qualifying a Relation

3.10.3.5 Qualifying a Dimension

3.10.3.6 Using Ampersand Substitution with QDRs

3.10.3.7 Using the QUAL Function to Specify a QDR

4 Formulas, Aggregations, Allocations, and Models

4.2 Aggregations

4.2.1 Aggregating Data

4.2.2 Executing the Aggregation

4.2.3 Creating Custom Aggregates

4.3 Allocations

4.3.1 Allocating Data

4.3.2 Handling NA Values

4.4.1 Creating Models

4.4.1.1 Nesting Models

4.4.1.2 Dimension Status and Model Equations

4.4.1.3 Using Data from Past and Future Time Periods

4.4.1.4 Handling NA Values

4.4.1.5 Solving Simultaneous Equations

4.4.1.6 Modeling for Multiple Scenarios

4.4.2 Compiling a Model

4.4.2.1 Understanding Dependencies

4.4.2.2 Checking for Additional Problems

4.4.3 Running a Model

4.4.4 Dimensions of Solution Variables

4.4.5 Solution Variables Dimensioned by a Composite

4.4.6 Debugging a Model

5 OLAP DML Programs

5.1 Creating OLAP DML Programs

5.1.1 Specifying Program Contents

5.1.1.1 Creating User-Defined Functions

5.1.1.2 Passing Arguments

5.1.1.3 Program Flow-of-Control

5.1.2 Preserving the Environment Settings

5.1.2.1 Changing the Program Environment

5.1.2.2 Ways to Save and Restore Environments

5.1.2.3 Saving the Status of a Dimension or the Value of an Option

5.1.2.4 Saving Several Values at Once

5.1.2.5 Using Level Markers

5.1.2.6 Using CONTEXT to Save Several Values at Once

5.1.3 Handling Errors

5.1.3.1 Trapping an Error

5.1.3.2 Suppressing Error Messages

5.1.3.3 Creating Your Own Error Messages

5.1.3.4 Handling Errors in Nested Programs

5.1.3.5 Handling Errors While Saving the Session Environment

5.2 Compiling Programs

5.2.1 Finding Out If a Program Has Been Compiled

5.2.2 Programming Methods That Prevent Compilation

5.3 Testing and Debugging Programs

5.3.1 Error and Debugging Options

5.3.2 Generating Diagnostic Messages

5.3.3 Identifying Bad Lines of Code

5.3.4 Sending Output to a Debugging File

5.4 Executing Programs

Part II Alphabetic Reference

6 $AGGMAP to AGGMAP

$AGGREGATE_FROM

$AGGREGATE_FROMVAR

$STORETRIGGERVAL

BREAKOUT DIMENSION

DIMENSION (for aggregation)

MEASUREDIM (for aggregation)

MODEL (in an aggregation)

RELATION (for aggregation)

AGGMAP ADD or REMOVE model

7 AFFMAPINFO to ARCCOS

AGGREGATE command

AGGREGATE function

ALLOCERRLOGFORMAT

ALLOCERRLOGHEADER

DIMENSION (for allocation)

MEASUREDIM (for allocation)

RELATION (for allocation)

8 ARCSIN to CHARLIST

9 CHGDFN to DDOF

CONTEXT command

CONTEXT function

10 DECIMALCHAR to DELETE

DECIMALOVERFLOW

DEFAULTAWSEGSIZE

DEFINE COMPOSITE

DEFINE DIMENSION

DEFINE DIMENSION (simple)

DEFINE DIMENSION (DWMQY)

DEFINE DIMENSION (conjoint)

DEFINE DIMENSION CONCAT

DEFINE DIMENSION ALIASOF

DEFINE PARTITION TEMPLATE

DEFINE RELATION

DEFINE SURROGATE

DEFINE VALUESET

DEFINE VARIABLE

DEFINE WORKSHEET

11 DEPRDECL to EXISTS

EIFEXTENSIONPATH

12 EXPORT to FILEMOVE

EXPORT (to EIF)

EXPORT (to spreadsheet)

13 FILENEXT to FULLDSC

FORECAST.REPORT

14 GET to IMPORT

HIERHEIGHT command

HIERHEIGHT function

IF...THEN...ELSE

IMPORT (from EIF)

IMPORT (from text)

IMPORT (from spreadsheet)

15 INF_STOP_ON_ERROR to LIKEESCAPE

INF_STOP_ON_ERROR

INFO (FORECAST)

16 LIKENL to MAX

LIMIT command (using values)

LIMIT command (using LEVELREL)

LIMIT command (using related dimension)

LIMIT command (using parent relation)

LIMIT command (NOCONVERT)

LIMIT command (using POSLIST)

MAINTAIN ADD for TEXT, ID, and INTEGER Values

MAINTAIN ADD for DAY, WEEK, MONTH, QUARTER, and YEAR Values

MAINTAIN ADD SESSION

MAINTAIN ADD TO PARTITION

MAINTAIN DELETE

MAINTAIN DELETE dimension

MAINTAIN DELETE composite

MAINTAIN DELETE FROM PARTITION

MAINTAIN MOVE dimension value

MAINTAIN MOVE TO PARTITION

MAINTAIN RENAME

17 MAXBYTES to MODTRACE

DIMENSION (in models)

18 MONITOR to NVL2

19 OBJ to QUAL

20 RANDOM to REPORT

RANDOM.SEED.1 and RANDOM.SEED.2

21 RESERVED to SPARSEINDEX

ROUND (for dates and time)

ROUND (for numbers)

22 SQL to STATVAL

SQL DECLARE CURSOR

23 STDDEV to TRACKPRG

24 TRAP to ZSPELL

TRIGGER command

TRIGGER function

TRIGGER_AFTER_UPDATE

TRIGGER_BEFORE_UPDATE

TRIGGERMAXDEPTH

TRUNC (for dates and time)

TRUNC (for numbers)

WEEKDAYSNEWYEAR

Part III Appendixes

A Functions and Commands by Functional Category

A.1 Session Statements

A.2 Data Type Conversion

A.3 Assignment Statements

A.4 Statements for Working with NA Values

A.5 Text Functions

A.5.1 General Character Functions

A.5.2 Byte Functions

A.5.3 Multiline Text Functions

A.6 Date and Time Functions

A.7 Numeric Functions

A.7.1 General Numeric Functions

A.7.2 Financial Functions

A.7.3 Statistical Functions

A.7.4 Time-Series Functions

A.7.5 Aggregation Functions

A.8 Forecast and Regression Statements

A.8.1 Simple Forecasts and Regressions

A.8.2 Statements for Forecasting Using a Forecasting Context

A.9 Aggregation Statements

A.10 Allocation Statements

A.11 Workspace Object Operation Statements

A.12 Dimension and Composite Operation Statements

A.13 Formula Statements

A.14 Modeling Statements

A.15 Programming Statements

A.15.1 Statements for Handling Programs

A.15.2 Statement Used Only in Programs

A.15.3 Statements Used Primarily in Programs

A.15.4 Statements for Program Debugging

A.15.5 Statements for Working with Startup and Trigger Programs

A.16 File Reading and Writing Statements

A.17 Statements for Importing and Exporting Data

A.18 Reporting Statements

A.19 Statements Related to Using OLAP_TABLE in SQL

B OLAP DML Statement Changes

B.1 Statements Added

B.2 Statements Deleted

B.3 Statements Significantly Changed

B.4 Statements Renamed

Index