Contents

List of Examples

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in the OLAP DML?

• New Features in the OLAP DML
• OLAP DML Statement Changes for Oracle 10g
• OLAP DML Statement Changes for Oracle 9i

Part I OLAP DML Basics

1 Introduction to the OLAP DML

• What is the OLAP DML?
• • Purpose of the OLAP DML
  • OLAP DML is Both a Data Definition Language and a Data Manipulation Language
• Basic Syntactical Units of the OLAP DML
• • OLAP DML Options
  • OLAP DML Properties
  • OLAP DML Commands and Functions
  • • OLAP DML Commands
    • OLAP DML Functions
  • OLAP DML Programs
• Creating New Workspaces and Objects
• • Creating Analytic Workspaces Using the OLAP DML
  • Defining Analytic Workspace Objects Using the OLAP DML
  • • Creating Data Objects Using the OLAP DML
    • Creating Calculation Specifications
• Working with Previously-Defined Analytic Workspaces
• • Viewing Analytic Workspace Definitions
• Making Selections and Working with Subsets of Data
• • Making a Subset of Data Available to All Statements Against a Workspace
  • • Current Status Lists
    • Default Status Lists
    • Changing the Current Status List of a Dimension
    • Saving and Restoring Current Dimension Status
  • Making a Subset of Data Available to a Single Statement

2 Expressions and Formulas

• Introducing OLAP DML Expressions
• • How the Data Type of an Expression is Determined
  • Saving Expressions
• OLAP DML Data Types
• • Numeric Data Types
  • • Using LONGINTEGER Values
    • Using NUMBER Values
  • Text Data Types
  • • Literals
    • Escape Sequences
  • Boolean Data Type
  • Date Data Types
  • • DATE Values
    • DATETIME Values
    • Valid DATE Values
    • • Numeric style
      • Packed numeric style
      • Month name style
    • Calculating Dates
  • Converting from One Data Type to Another
• OLAP DML Operators
• • Arithmetic Operators
  • Comparison and Logical Operators
  • Assignment Operator
• Using Workspace Objects in Expressions
• • Syntax for Specifying an Object in an Expression
  • • Considerations When Creating and Using Qualified Object Names
    • • When Not to Use Qualified Object Names
      • Using Ampersand Substitution for Workspace and Object Names
      • Passing Qualified Object Names to Programs
  • How Objects Behave in Expressions
  • Using Dimensions and Composites in Expressions
  • • Specifying a Value of a Composite
    • Specifying a Value of a CONCAT Dimension
    • Using Related Dimensions in Expressions
  • Using Variables and Relations in Expressions
  • • How OLAP DML Statements Loop Through Multidimensional Objects
    • Uniquely Identifying a Cell of Data in a Multidimensional Object
    • Using Objects Dimensioned by Composites in Expressions
• Dimensionality of OLAP DML Expressions
• • Determining the Dimensions of an Expression
  • How Dimension Status Affects the Results of Expressions
  • Changing the Dimensionality of an Expression
• Numeric Expressions
• • 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
• • Language of Text Expressions
  • Working with DATETIME Values in Text Expressions
  • Working with NTEXT Data
  • Using a Single Quotation Mark Within a Text Value
• 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 Empty Cells in Expressions
• • Specifying a Value of NA
  • Controlling how NA values are treated
• Limiting Dimension Values Directly in an Expression
• • Specifying a List of Dimension Values for an Expression or Subexpression
  • Specifying a Single Data Value in an Expression
  • • Form of a Qualified Data Reference
    • 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
• Formulas

3 Aggregations and Allocations

• Aggregations
• • Aggregating Data
  • Compiling Aggregation Specifications
  • Executing the Aggregation
  • Creating Custom Aggregates
• Allocations
• • Allocating Data
  • Handling NA Values When Allocating Data

4 Models

• What is an Analytic Workspace Model?
• Creating Models
• • Nesting Models
  • Dimension Status and Model Equations
  • Using Data from Past and Future Time Periods
  • Handling NA Values in Models
  • Solving Simultaneous Equations
  • Modeling for Multiple Scenarios
• Compiling Models
• • Resolving Names in Equations
  • Code for Looping Over Dimensions
  • Evaluating Program Arguments
  • Dependencies Between Equations
  • • Order of Simultaneous Equations
    • One-Way Dimensional Dependence
    • Two-Way Dimensional Dependence
  • Obtaining Analysis Results
  • Checking for Additional Problems
• Running a Model
• • Syntax for Running a Model
  • Dimensions of Solution Variables
  • • Special Cases of Solution Variables
    • Solution Variables Dimensioned by a Composite
• Debugging a Model

5 Programs

• Creating OLAP DML Programs
• Specifying Program Contents
• • Creating User-Defined Functions
  • Passing Arguments
  • • Using Multiple Arguments
    • Handling Arguments Without Converting Values to a Specific Data Type
    • Passing Arguments as Text with Ampersand Substitution
  • Program Flow-of-Control
  • • Looping Nature of OLAP DML Statements
    • Flow-of Control Statements
  • Preserving the Environment Settings
  • • 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
  • • Trapping an Error
    • Passing an Error to a Calling Program
    • • Passing an Error: Method One
      • Passing an Error: Method Two
    • Suppressing Error Messages
    • Creating Your Own Error Messages
    • Handling Errors in Nested Programs
    • Handling Errors While Saving the Session Environment
• 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
• Executing Programs
• Common Types of OLAP DML Programs
• • Startup Programs
  • • Permission Programs
    • AUTOGO Programs
    • ONATTACH Programs
    • TRIGGER_AW Program
  • Data Loading Programs
  • • Programs that Copy Data From Relational Tables to Workspace Objects
    • File-Reading Programs
    • Spreadsheet Import Programs
  • Trigger Programs
  • • Creating a Trigger Program
    • Characteristics of Trigger Programs
  • Aggregation, Allocation, and Modeling Programs
  • Forecasting Programs
  • • Forecasting Using a Forecast Context
    • Further Reading on Forecasting
  • Programs to Export and Import Workspace Objects

Part II Alphabetic Reference

6 $AGGMAP to AGGMAP

• $AGGMAP
• $AGGREGATE_FORCECALC
• $AGGREGATE_FORCEORDER
• $AGGREGATE_FROM
• $AGGREGATE_FROMVAR
• $ALLOCMAP
• $COUNTVAR
• $DEFAULT_LANGUAGE
• $NATRIGGER
• $STORETRIGGERVAL
• $VARCACHE
• ABS
• ACQUIRE
• ACROSS
• ADD_MONTHS
• AGGCOUNT
• AGGMAP
• • AGGINDEX
  • BREAKOUT DIMENSION
  • CACHE
  • DIMENSION (for aggregation)
  • DROP DIMENSION
  • MEASUREDIM (for aggregation)
  • MODEL (in an aggregation)
  • PRECOMPUTE
  • RELATION (for aggregation)
• AGGMAP ADD or REMOVE model
• AGGMAP SET

7 AGGMAPINFO to ARCCOS

• AGGMAPINFO
• AGGREGATE command
• AGGREGATE function
• AGGREGATION
• AGGROPS
• ALLCOMPILE
• ALLOCATE
• ALLOCERRLOGFORMAT
• ALLOCERRLOGHEADER
• ALLOCMAP
• • CHILDLOCK
  • DEADLOCK
  • DIMENSION (for allocation)
  • ERRORLOG
  • ERRORMASK
  • MEASUREDIM (for allocation)
  • RELATION (for allocation)
  • SOURCEVAL
  • VALUESET
• ALLOCOPS
• ALLSTAT
• ANTILOG
• ANTILOG10
• ANY
• ARCCOS

8 ARCSIN to CHARLIST

• ARCSIN
• ARCTAN
• ARCTAN2
• ARG
• ARGCOUNT
• ARGFR
• ARGS
• ARGUMENT
• ASCII
• AVERAGE
• AW command
• • AW ALIASLIST
  • AW ATTACH
  • AW CREATE
  • AW DELETE
  • AW DETACH
  • AW LIST
  • AW SEGMENTSIZE
  • AW TRUNCATE
• AW function
• AWDESCRIBE
• AWWAITTIME
• BACK
• BADLINE
• BASEDIM
• BASEVAL
• BEGINDATE
• BITAND
• BLANK
• BLANKSTRIP
• BMARGIN
• BREAK
• CALENDARWEEK
• CALL
• CALLTYPE
• CATEGORIZE
• CDA
• CEIL
• CHANGEBYTES
• CHANGECHARS
• CHARLIST

9 CHGDFN to DDOF

• CHGDFN
• CHGDIMS
• CLEAR
• COALESCE
• COLVAL
• COLWIDTH
• COMMAS
• COMMIT
• COMPILE
• COMPILEMESSAGE
• COMPILEWARN
• CONSIDER
• CONTEXT command
• CONTEXT function
• CONTINUE
• CONVERT
• COPYDFN
• CORRELATION
• COS
• COSH
• COUNT
• CUMSUM
• DATEFORMAT
• DATEORDER
• DAYABBRLEN
• DAYNAMES
• DAYOF
• DBGOUTFILE
• DDOF

10 DECIMALCHAR to DELETE

• DECIMALCHAR
• DECIMALOVERFLOW
• DECIMALS
• DECODE
• DEFAULTAWSEGSIZE
• DEFINE
• • DEFINE AGGMAP
  • DEFINE COMPOSITE
  • DEFINE DIMENSION
  • • DEFINE DIMENSION (simple)
    • DEFINE DIMENSION (DWMQY)
    • DEFINE DIMENSION (conjoint)
    • DEFINE DIMENSION CONCAT
    • DEFINE DIMENSION ALIASOF
  • DEFINE FORMULA
  • DEFINE MODEL
  • DEFINE PARTITION TEMPLATE
  • DEFINE PROGRAM
  • DEFINE RELATION
  • DEFINE SURROGATE
  • DEFINE VALUESET
  • DEFINE VARIABLE
  • DEFINE WORKSHEET
• DELETE

11 DEPRDECL to EXISTS

• DEPRDECL
• DEPRDECLSW
• DEPRSL
• DEPRSOYD
• DESCRIBE
• DIVIDEBYZERO
• DO ... DOEND
• DSECONDS
• ECHOPROMPT
• EDIT
• EIFBYTES
• EIFEXTENSIONPATH
• EIFNAMES
• EIFSHORTNAMES
• EIFTYPES
• EIFUPDBYTES
• EIFVERSION
• END
• ENDDATE
• ENDOF
• EQ
• ERRNAMES
• ERRORNAME
• ERRORTEXT
• ESCAPEBASE
• EVERSION
• EVERY
• EXISTS
• EXP

12 EXPORT to FILEMOVE

• EXPORT
• • EXPORT (to EIF)
  • EXPORT (to spreadsheet)
• EXPTRACE
• EXTBYTES
• EXTCHARS
• EXTCOLS
• EXTLINES
• FCCLOSE
• FCEXEC
• FCOPEN
• FCQUERY
• FCSET
• FETCH
• FILECLOSE
• FILECOPY
• FILEDELETE
• FILEERROR
• FILEGET
• FILEMOVE

13 FILENEXT to FULLDSC

• FILENEXT
• FILEOPEN
• FILEPAGE
• FILEPUT
• FILEQUERY
• FILEREAD
• FILESET
• FILEVIEW
• FILTERLINES
• FINDBYTES
• FINDCHARS
• FINDLINES
• FINTSCHED
• FLOOR
• FOR
• FORECAST
• FORECAST.REPORT
• FPMTSCHED
• FULLDSC

14 GET to IMPORT

• GET
• GOTO
• GREATEST
• GROUPINGID
• GROWRATE
• HEADING
• HIDE
• HIERCHECK
• HIERHEIGHT command
• HIERHEIGHT function
• IF...THEN...ELSE
• IMPORT
• • IMPORT (from EIF)
  • IMPORT (from text)
  • IMPORT (from spreadsheet)

15 INF_STOP_ON_ERROR to LIKEESCAPE

• INF_STOP_ON_ERROR
• INFILE
• INFO
• • INFO (FORECAST)
  • INFO (MODEL)
  • INFO (PARSE)
  • INFO (REGRESS)
• INITCAP
• INLIST
• INSBYTES
• INSCHARS
• INSCOLS
• INSLINES
• INSTAT
• INSTR
• INSTRB
• INTPART
• IRR
• ISDATE
• ISSESSION
• ISVALUE
• JOINBYTES
• JOINCHARS
• JOINCOLS
• JOINLINES
• KEY
• LAG
• LAGABSPCT
• LAGDIF
• LAGPCT
• LARGEST
• LAST_DAY
• LCOLWIDTH
• LD
• LEAD
• LEAST
• LIKECASE
• LIKEESCAPE

16 LIKENL to MAX

• LIKENL
• LIMIT command
• • 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)
• LIMIT function
• LIMIT BASEDIMS
• LIMITMAPINFO
• LIMIT.SORTREL
• LIMITSTRICT
• LINENUM
• LINESLEFT
• LISTBY
• LISTFILES
• LISTNAMES
• LOAD
• LOCK_LANGUAGE_DIMS
• LOG command
• LOG function
• LOG10
• LOWCASE
• LPAD
• LSIZE
• LTRIM
• MAINTAIN
• • MAINTAIN ADD
  • • 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 MERGE
  • MAINTAIN MOVE
  • • MAINTAIN MOVE dimension value
    • MAINTAIN MOVE TO PARTITION
  • MAINTAIN RENAME
• MAKEDATE
• MAX

17 MAXBYTES to MODTRACE

• MAXBYTES
• MAXCHARS
• MAXFETCH
• MEDIAN
• MIN
• MMOF
• MODDAMP
• MODE
• MODEL
• • DIMENSION (in models)
  • INCLUDE
• MODEL.COMPRPT
• MODEL.DEPRT
• MODEL.XEQRPT
• MODERROR
• MODGAMMA
• MODINPUTORDER
• MODMAXITERS
• MODOVERFLOW
• MODSIMULTYPE
• MODTOLERANCE
• MODTRACE

18 MONITOR to NVL2

• MONITOR
• MONTHABBRLEN
• MONTHNAMES
• MONTHS_BETWEEN
• MOVE
• MOVINGAVERAGE
• MOVINGMAX
• MOVINGMIN
• MOVINGTOTAL
• MULTIPATHHIER
• NAFILL
• NAME
• NASKIP
• NASKIP2
• NASPELL
• NEW_TIME
• NEXT_DAY
• NLS Options
• NONE
• NORMAL
• NOSPELL
• NPV
• NULLIF
• NUMBYTES
• NUMCHARS
• NUMLINES
• NVL
• NVL2

19 OBJ to QUAL

• OBJ
• OBJLIST
• OBSCURE
• OKFORLIMIT
• OKNULLSTATUS
• ONATTACH
• OUTFILE
• OUTFILEUNIT
• PAGE
• PAGENUM
• PAGEPRG
• PAGESIZE
• PAGING
• PARENS
• PARSE
• PARTITIONCHECK
• PERCENTAGE
• PERMIT
• PERMIT_READ
• PERMIT_WRITE
• PERMITERROR
• PERMITRESET
• POP
• POPLEVEL
• POUTFILEUNIT
• PRGTRACE
• PROGRAM
• PROPERTY
• PUSH
• PUSHLEVEL
• QUAL

20 RANDOM to REPORT

• RANDOM
• RANDOM.SEED.1 and RANDOM.SEED.2
• RANK
• RECAP
• RECNO
• RECURSIVE
• REDO
• REEDIT
• REGRESS
• REGRESS.REPORT
• RELATION command
• RELEASE
• REM
• REMBYTES
• REMCHARS
• REMCOLS
• REMLINES
• RENAME
• REPLBYTES
• REPLCHARS
• REPLCOLS
• REPLLINES
• REPORT

21 RESERVED to SPARSEINDEX

• RESERVED
• RESYNC
• RETURN
• REVERT
• ROLE
• ROOTOFNEGATIVE
• ROUND
• • ROUND (for dates and time)
  • ROUND (for numbers)
• ROW command
• ROW function
• RPAD
• RTRIM
• RUNTOTAL
• SECONDS
• SESSCACHE
• SESSION_NLS_LANGUAGE
• SET
• SET1
• SHOW
• SIGN
• SIGNAL
• SIN
• SINH
• SLEEP
• SMALLEST
• SMOOTH
• SORT command
• SORT function
• SORTCOMPOSITE
• SORTLINES
• SPARSEINDEX

22 SQL to STATVAL

• SQL
• • SQL CLEANUP
  • SQL CLOSE
  • SQL DECLARE CURSOR
  • SQL EXECUTE
  • SQL FETCH
  • SQL IMPORT
  • SQL OPEN
  • SQL PREPARE
  • SQL PROCEDURE
  • SQL SELECT
• SQLBLOCKMAX
• SQLCODE
• SQLERRM
• SQLFETCH
• SQLMESSAGES
• SQRT
• STARTOF
• STATALL
• STATDEPTH
• STATFIRST
• STATLAST
• STATLEN
• STATLIST
• STATMAX
• STATMIN
• STATRANK
• STATUS
• STATVAL
• STATIC_SESSION_LANGUAGE

23 STDDEV to TRACKPRG

• STDDEV
• STDHDR
• SUBSTR
• SUBSTRB
• SUBTOTAL
• SWITCH
• SYSDATE
• SYSINFO
• SYSTEM
• TALLY
• TAN
• TANH
• TCONVERT
• TEMPSTAT
• TEXTFILL
• THIS_AW
• THOUSANDSCHAR
• TMARGIN
• TO_CHAR
• TO_DATE
• TO_NCHAR
• TO_NUMBER
• TOD
• TODAY
• TOTAL
• TRACEFILEUNIT
• TRACKPRG

24 TRAP to ZSPELL

• TRAP
• TRIGGER command
• TRIGGER function
• TRIGGER_AFTER_UPDATE
• TRIGGER_AW
• TRIGGER_BEFORE_UPDATE
• TRIGGER_DEFINE
• TRIGGERASSIGN
• TRIGGERMAXDEPTH
• TRIGGERSTOREOK
• TRIM
• TRUNC
• • TRUNC (for dates and time)
  • TRUNC (for numbers)
• UNHIDE
• UNIQUELINES
• UNRAVEL
• UPCASE
• UPDATE
• USERID
• USETRIGGERS
• VALSPERPAGE
• VALUES
• VARCACHE
• VARIABLE
• VINTSCHED
• VNF
• VPMTSCHED
• WEEKDAYSNEWYEAR
• WEEKOF
• WHILE
• WIDTH_BUCKET
• WKSDATA
• WRAPERRORS
• WRITABLE
• YESSPELL
• YRABSTART
• YYOF
• ZEROROW
• ZEROTOTAL
• ZSPELL

A Statements by Functional Category

• System Properties by Category
• • System Properties Used When Aggregating Data
  • System Properties that You Use with NA Values
• OLAP DML Options by Category
• • Options Related to Analytic Workspace Management
  • Multi-Language Support Options
  • Aggregation Options
  • Allocation Options
  • Model Options
  • Compilation Options
  • Error and Debugging Options
  • SQL Embed Options
  • File Reading and Writing Options
  • EIF Options
  • Report Options
  • Options for Working with Empty Cells and Nonexistent Values
  • Date and Time Options
  • Numeric Options
  • Options for Monitoring the Behavior of RANK
• OLAP DML Commands, Functions, and Programs by Category
• • Session Statements
  • Workspace Object Definition Statements
  • Statements for Managing Analytic Workspaces
  • Statements for Managing Objects When in Multiwriter Mode
  • Data Type Conversion
  • Assignment Statements
  • Statements for Working with NA Values
  • Text Functions
  • • General Character Functions
    • Byte Functions
    • Multiline Text Functions
  • Date and Time Functions
  • Numeric Functions
  • • General Numeric Functions
    • Financial Functions
    • Statistical Functions
    • Time-Series Functions
    • Aggregation Functions
  • Forecast and Regression Statements
  • • Simple Forecasts and Regressions
    • Statements for Forecasting Using a Forecasting Context
  • Aggregation Statements
  • Allocation Statements
  • Workspace Object Operation Statements
  • Dimension and Composite Operation Statements
  • Formula Statements
  • Modeling Statements
  • Programming Statements
  • • Statements for Handling Programs
    • Statement Used Only in Programs
    • Statements Used Primarily in Programs
    • Statements for Program Debugging
    • Statements for Working with Startup and Trigger Programs
  • File Reading and Writing Statements
  • Statements for Importing and Exporting Data
  • Reporting Statements
  • Statements Related to Using OLAP _TABLE

Index