Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)
Part Number B28126-02
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
Contents
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 Oracle11
g
OLAP DML Statement Changes for Oracle10
g
OLAP DML Statement Changes for Oracle9
i
1
OLAP DML Basic Concepts
What is the OLAP DML?
What You Can Do Using the OLAP DML
Basic Syntactic Units of the OLAP DML
How to Execute OLAP DML Statements
OLAP DML as a Definition Language
Introduction to Analytic Workspaces
Privileges Needed to Create and Delete Analytic Workspaces
Defining a New Analytic Workspace
Working with Previously-Defined Analytic Workspaces
Viewing Information About an Analytic Workspace
About OLAP DML Data Objects
Types of OLAP DML Data Objects
Variables
Objects that Can Dimension Variables
Relations
Valueset and Surrogate Objects
Objects that Support the Use of Hierarchical Dimensions
Hierlist Dimension
Parentrel Relation
Levellist Dimension
Hierlevels Valueset
Inhier Valueset or Variable
Levelrel Relation
Familyrel Relation
Gidrel Relation
OLAP DML Statements Apply to All of the Values of a Data Object
Changing the Default Looping Behavior of Statements
How to Specify the Set of Data that OLAP DML Operations Work Against
About Status Lists
Default Status Lists
Current Status Lists
Changing the Current Status of a Dimension to Work with a Subset of Data
Saving and Restoring Current Dimension Status
Using a Subset of Data Without Changing Status
Populating Multidimensional Hierarchical Data Objects
2
Data Types, Operators, and Expressions
OLAP DML Data Types
Numeric Data Types
Using LONGINTEGER Values
Using NUMBER Values
Text Data Types
Text Literals
Escape Sequences
Date-only Data Type
Date-only Input Values
Date-only Dimension Values
DATE-only Variable Display Styles
Datetime and Interval Data Types
Datetime and Interval Fields
Datetime Format Templates
String-to-Date Conversion Rules
DATETIME Data Type
TIMESTAMP Data Type
TIMESTAMP_TZ Data Type
TIMESTAMP_LTZ Data Type
YMINTERVAL Data Type
DSINTERVAL Data Type
Boolean Data Type
RAW Data Type
Row Identifier Data Types
ROWID Data Type
UROWID Data Type
Converting from One Data Type to Another
Automatic Conversion of Textual Data Types
Automatic Conversion of Numeric Data Types
OLAP DML Operators
Arithmetic Operators
Comparison and Logical Operators
Assignment Operator
OLAP DML Expressions
About OLAP DML Expressions
How the Data Type of an Expression is Determined
How the Dimensionality of an Expression is Determined
Determining the Dimensions of an Expression
How Dimension Status Affects the Number of Values in the Results of Expressions
Using Workspace Objects in Expressions
How OLAP DML Data Objects Behave in Expressions
Syntax for Specifying an Object in an Expression
Considerations When Creating and Using Qualified Object Names
Specifying Values of 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
Limiting a Dimension to a Single Value Without Changing Status
Syntax 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
Working with Empty Cells in Expressions
Specifying a Value of NA
Controlling how NA values are treated
Numeric Expressions
Mixing Numeric Data Types
Using Text Dimensions 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
Datetime and Interval Expressions
Datetime Expressions
Interval Expressions
Datetime/Interval Arithmetic
Date-only Expressions
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
IF...THEN...ELSE expression
SWITCH Expressions
Substitution Expressions
3
Formulas, Models, Aggregations, and Allocations
Creating Calculation Objects
OLAP DML Formulas
OLAP DML Model Objects
What is an OLAP DML 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
Obtaining Analysis Results
Checking for Additional Problems
Running a Model
Syntax for Running a Model
Dimensions of Solution Variables
Debugging a Model
OLAP DML Aggregation Objects
What is an OLAP DML Aggregation?
Aggregating Data Using the OLAP DML
Compiling Aggregation Specifications
Executing the Aggregation
Creating Custom Aggregates
OLAP DML Allocation Objects
Introduction to Allocating Data Using the OLAP DML
Features of Allocation in Oracle OLAP
Allocating Data
Handling NA Values When Allocating Data
4
OLAP DML Programs
Programs Provided With the OLAP DML
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
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 and ONATTACH Programs
Data Import and Export Programs
Importing Data to and Exporting Data from Relational Tables
Importing Data From Relational Tables to Workspace Objects
Exporting Data from OLAP DML Objects to Relational Tables
Importing Data to and Exporting Data from Flat Files
Importing Data to and Exporting Data from Spreadsheets
Trigger Programs
Creating an Object Trigger Program
Characteristics of Object Trigger Programs
Aggregation, Allocation, and Modeling Programs
Forecasting Programs
Programs to Export and Import Workspace Objects
User-Written Programs that OLAP Looks For
User-Written Programs Looked For by Oracle OLAP
ONATTACH
ONDETACH
PERMIT_READ
PERMIT_WRITE
TRIGGER_AFTER_UPDATE
TRIGGER_AW
TRIGGER_BEFORE_UPDATE
TRIGGER_DEFINE
5
OLAP DML Properties
About OLAP DML Properties
System Properties: Alphabetical Listing
System Properties by Category
$AGGMAP
$AGGREGATE_FORCECALC
$AGGREGATE_FORCEORDER
$AGGREGATE_FROM
$AGGREGATE_FROMVAR
$ALLOCMAP
$COUNTVAR
$DEFAULT_LANGUAGE
$GID_DEPTH
$GID_LIST
$GID_TYPE
$LOOP_AGGMAP
$LOOP_DENSE
$LOOP_VAR
$NATRIGGER
$STORETRIGGERVAL
$VARCACHE
6
OLAP DML Options
About Options
Options: Alphabetical Listing
Options by Category
ALLOCERRLOGFORMAT
ALLOCERRLOGHEADER
AWWAITTIME
BADLINE
BMARGIN
CALENDARWEEK
COLWIDTH
COMMAS
COMPILEMESSAGE
COMPILEWARN
DATEFORMAT
DATEORDER
DAYABBRLEN
DAYNAMES
DECIMALCHAR
DECIMALOVERFLOW
DECIMALS
DEFAULTAWSEGSIZE
DIVIDEBYZERO
DSECONDS
ECHOPROMPT
EIFBYTES
EIFEXTENSIONPATH
EIFNAMES
EIFSHORTNAMES
EIFTYPES
EIFUPDBYTES
EIFVERSION
ERRNAMES
ERRORNAME
ERRORTEXT
ESCAPEBASE
EXPTRACE
INF_STOP_ON_ERROR
LCOLWIDTH
LIKECASE
LIKEESCAPE
LIKENL
LIMIT.SORTREL
LIMITSTRICT
LINENUM
LINESLEFT
LOCK_LANGUAGE_DIMS
LSIZE
MAXFETCH
MODDAMP
MODERROR
MODGAMMA
MODINPUTORDER
MODMAXITERS
MODOVERFLOW
MODSIMULTYPE
MODTOLERANCE
MODTRACE
MONTHABBRLEN
MONTHNAMES
MULTIPATHHIER
NASKIP
NASKIP2
NASPELL
NLS_CALENDAR
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANG
NLS_LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY
NOSPELL
OKFORLIMIT
OKNULLSTATUS
OUTFILEUNIT
PAGENUM
PAGEPRG
PAGESIZE
PAGING
PARENS
PERMITERROR
POUTFILEUNIT
PRGTRACE
RANDOM.SEED.1 and RANDOM.SEED.2
RECURSIVE
ROLE
ROOTOFNEGATIVE
SECONDS
SESSCACHE
SESSION_NLS_LANGUAGE
SORTCOMPOSITE
SPARSEINDEX
SQLBLOCKMAX
SQLCODE
SQLERRM
SQLMESSAGES
STATIC_SESSION_LANGUAGE
THIS_AW
THOUSANDSCHAR
TMARGIN
TRACEFILEUNIT
TRIGGERMAXDEPTH
TRIGGERSTOREOK
USERID
USETRIGGERS
VARCACHE
WEEKDAYSNEWYEAR
WRAPERRORS
YESSPELL
YRABSTART
ZEROROW
ZSPELL
7
OLAP DML Functions: A - K
About OLAP DML Functions
Functions: Alphabetical Listing
Functions by Category
ABS
ADD_MONTHS
AGGCOUNT
AGGMAPINFO
AGGREGATE function
AGGREGATION
AGGROPS
ALLOCOPS
ANTILOG
ANTILOG10
ANY
ARCCOS
ARCSIN
ARCTAN
ARCTAN2
ARG
ARGCOUNT
ARGFR
ARGS
ASCII
AVERAGE
AW function
BACK
BASEDIM
BASEVAL
BEGINDATE
BIN_TO_NUM
BITAND
BLANKSTRIP
CALLTYPE
CATEGORIZE
CEIL
CHANGEBYTES
CHANGECHARS
CHANGEDRELATIONS
CHANGEDVALUES
CHARLIST
CHARTOROWID
CHGDIMS
CHR
COALESCE
COLVAL
CONTEXT function
CONVERT
CORRELATION
COS
COSH
COUNT
CUMSUM
CURRENT_DATE
CURRENT_TIMESTAMP
DAYOF
DBTIMEZONE
DDOF
DECODE
DEPRDECL
DEPRDECLSW
DEPRSL
DEPRSOYD
ENDDATE
ENDOF
EVERY
EXISTS
EXP
EVERSION
EXTBYTES
EXTCHARS
EXTCOLS
EXTLINES
EXTRACT
FCOPEN
FCQUERY
FILEERROR
FILEGET
FILENEXT
FILEOPEN
FILEQUERY
FILTERLINES
FINDBYTES
FINDCHARS
FINDLINES
FINTSCHED
FLOOR
FPMTSCHED
FROM_TZ
GET
GREATEST
GROUPINGID function
GROWRATE
HIERCHECK
HIERHEIGHT function
INFO
INFO (FORECAST)
INFO (MODEL)
INFO (PARSE)
INFO (REGRESS)
INITCAP
INLIST
INSBYTES
INSCHARS
INSCOLS
INSLINES
INSTAT
INSTR functions
INTPART
IRR
ISDATE
ISEMPTY
ISSESSION
ISVALUE
JOINBYTES
JOINCHARS
JOINCOLS
JOINLINES
KEY
8
OLAP DML Functions: L - Z
LAG
LAGABSPCT
LAGDIF
LAGPCT
LARGEST
LAST_DAY
LEAD
LEAST
LENGTH functions
LIMIT function
LIMITMAPINFO
LNNVL
LOCALTIMESTAMP
LOG function
LOG10
LOWCASE
LPAD
LTRIM
MAKEDATE
MAX
MAXBYTES
MAXCHARS
MEDIAN
MIN
MMOF
MODE
MODULO
MONTHS_BETWEEN
MOVINGAVERAGE
MOVINGMAX
MOVINGMIN
MOVINGTOTAL
NA2
NAFILL
NAFLAG
NEW_TIME
NEXT_DAY
NLSSORT
NONE
NORMAL
NPV
NULLIF
NUMBYTES
NUMCHARS
NUMLINES
NUMTODSINTERVAL
NUMTOYMINTERVAL
NVL
NVL2
OBJ
OBJLIST
OBSCURE
PARTITION
PARTITIONCHECK
PERCENTAGE
QUAL
RANDOM
RANK
RECNO
REM
REMAINDER
REMBYTES
REMCHARS
REMCOLS
REMLINES
REPLBYTES
REPLCHARS
REPLCOLS
REPLLINES
RESERVED
ROUND
ROUND (datetime)
ROUND (number)
ROW function
ROWIDTOCHAR
ROWIDTONCHAR
RPAD
RTRIM
RUNTOTAL
SESSIONTIMEZONE
SIGN
SIN
SINH
SMALLEST
SMOOTH
SORT function
SORTLINES
SQLFETCH
SQRT
STARTOF
STATALL
STATDEPTH
STATEQUAL
STATFIRST
STATLAST
STATLEN
STATLIST
STATMAX
STATMIN
STATRANK
STATVAL
STDDEV
SUBSTR functions
SUBTOTAL
SYS_CONTEXT
SYSDATE
SYSINFO
SYSTEM
SYSTIMESTAMP
TALLY
TAN
TANH
TCONVERT
TEXTFILL
TO_CHAR
TO_DATE
TO_DSINTERVAL
TO_NCHAR
TO_NUMBER
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TOD
TODAY
TOTAL
TRANSLATE
TRIGGER function
TRIM
TRUNCATE
TRUNCATE (datetime)
TRUNCATE (number)
TZ_OFFSET
UNIQUELINES
UNRAVEL
UPCASE
VALSPERPAGE
VALUES
VINTSCHED
VPMTSCHED
WEEKOF
WIDTH_BUCKET
WKSDATA
WRITABLE
YYOF
9
OLAP DML Commands: A-G
About OLAP DML Commands
Commands: Alphabetical Listing
Commands by Category
ACQUIRE
ACROSS
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
AGGREGATE command
ALLCOMPILE
ALLOCATE
ALLOCMAP
CHILDLOCK
DEADLOCK
DIMENSION (for allocation)
ERRORLOG
ERRORMASK
MEASUREDIM (for allocation)
RELATION (for allocation)
SOURCEVAL
VALUESET
ALLSTAT
ARGUMENT
AW command
AW ALIASLIST
AW ATTACH
AW CREATE
AW DELETE
AW DETACH
AW FREEZE
AW LIST
AW PURGE CACHE
AW ROLLBACK TO FREEZE
AW SEGMENTSIZE
AW THAW
AW TRUNCATE
AWDESCRIBE
BLANK
BREAK
CALL
CDA
CHGDFN
CLEAR
COMMIT
COMPILE
CONSIDER
CONTEXT command
CONTINUE
COPYDFN
DATE_FORMAT
DBGOUTFILE
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
DESCRIBE
DO ... DOEND
EDIT
EQ
EXPORT
EXPORT (EIF)
EXPORT (spreadsheet)
FCCLOSE
FCEXEC
FCSET
FETCH
FILECLOSE
FILECOPY
FILEDELETE
FILEMOVE
FILEPAGE
FILEPUT
FILEREAD
FILESET
FILEVIEW
FOR
FORECAST
FORECAST.REPORT
FULLDSC
GOTO
GROUPINGID command
10
OLAP DML Commands: H-Z
HEADING
HIDE
HIERDEPTH
HIERHEIGHT command
IF...THEN...ELSE command
IMPORT
IMPORT (EIF)
IMPORT (text)
IMPORT (spreadsheet)
INFILE
LD
LIMIT command
LIMIT (using values) command
LIMIT using LEVELREL command
LIMIT (using related dimension) command
LIMIT command (using parent relation)
LIMIT NOCONVERT command
LIMIT command (using POSLIST)
LIMIT BASEDIMS
LISTBY
LISTFILES
LISTNAMES
LOAD
LOG command
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
MODEL
DIMENSION (in models)
INCLUDE
MODEL.COMPRPT
MODEL.DEPRT
MODEL.XEQRPT
MONITOR
MOVE
OUTFILE
PAGE
PARSE
PERMIT
PERMITRESET
POP
POPLEVEL
PROGRAM
PROPERTY
PUSH
PUSHLEVEL
RECAP
REDO
REEDIT
REGRESS
REGRESS.REPORT
RELATION command
RELEASE
RENAME
REPORT
RESYNC
RETURN
REVERT
ROW command
SET
SET1
SHOW
SIGNAL
SLEEP
SORT command
SQL
SQL CLEANUP
SQL CLOSE
SQL DECLARE CURSOR
SQL EXECUTE
SQL FETCH
SQL IMPORT
SQL OPEN
SQL PREPARE
SQL PROCEDURE
SQL SELECT
STATUS
STDHDR
SWITCH command
TEMPSTAT
TRACKPRG
TRAP
TRIGGER command
TRIGGERASSIGN
UNHIDE
UPDATE
VARIABLE
VNF
WHILE
ZEROTOTAL
A
OLAP_TABLE SQL Function
Creating Relational Views Using OLAP_TABLE
Required OLAP DML Objects
Creating Logical Tables for Use by OLAP_TABLE
Using OLAP_TABLE With Predefined ADTs
Using OLAP_TABLE With Automatic ADTs
Adding Calculated Columns to the Relational View
Using OLAP DML Expressions in SELECT FROM OLAP_TABLE Statements
Using OLAP DML Expressions as Single-Row Functions
Modifying an Analytic Workspace From Within a SELECT FROM OLAP_TABLE Statement
OLAP_TABLE
OLAP_CONDITION
OLAP_EXPRESSION
OLAP_EXPRESSION_BOOL
OLAP_EXPRESSION_DATE
OLAP_EXPRESSION_TEXT
B
DBMS_AW PL/SQL Package
Managing Analytic Workspaces
Converting an Analytic Workspace to Oracle 11
g
Storage Format
Procedure: Convert an Analytic Workspace to the Latest Storage Format
Procedure: Import an Analytic Workspace from an Older Release of Oracle Database into a Newer Release of the Database
Embedding OLAP DML in SQL Statements
Methods for Executing OLAP DML Commands
Guidelines for Using Quotation Marks in OLAP DML Commands
Using the Sparsity Advisor
Data Storage Options in Analytic Workspaces
Selecting the Best Data Storage Method
Using the Sparsity Advisor
Example: Evaluating Sparsity in the GLOBAL Schema
Advice from Sample Program
Information Stored in AW_SPARSITY_ADVICE Table
Using the Aggregate Advisor
Aggregation Facilities within the Workspace
Example: Using the ADVISE_REL Procedure
Summary of DBMS_AW Subprograms
ADD_DIMENSION_SOURCE Procedure
ADVISE_CUBE Procedure
ADVISE_DIMENSIONALITY Function
ADVISE_DIMENSIONALITY Procedure
ADVISE_PARTITIONING_DIMENSION Function
ADVISE_PARTITIONING_LEVEL Function
ADVISE_REL Procedure
ADVISE_SPARSITY Procedure
AW_ATTACH Procedure
AW_COPY Procedure
AW_CREATE Procedure
AW_DELETE Procedure
AW_DETACH Procedure
AW_RENAME Procedure
AW_TABLESPACE Function
AW_UPDATE Procedure
CONVERT Procedure
EVAL_NUMBER Function
EVAL_TEXT Function
EXECUTE Procedure
GETLOG Function
INFILE Procedure
INTERP Function
INTERPCLOB Function
INTERP_SILENT Procedure
OLAP_ON Function
OLAP_RUNNING Function
PRINTLOG Procedure
RUN Procedure
SHUTDOWN Procedure
SPARSITY_ADVICE_TABLE Procedure
STARTUP Procedure
C
OLAP_API_SESSION_INIT
Initialization Parameters for the OLAP API
Viewing the Configuration Table
ALL_OLAP_ALTER_SESSION View
Summary of OLAP_API_SESSION_INIT Subprograms
ADD_ALTER_SESSION Procedure
CLEAN_ALTER_SESSION Procedure
DELETE_ALTER_SESSION Procedure
Index