|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
The basic syntactic units of the OLAP DML are:
Options to which you assign a value and that can influence the analytic workspace processing environment in various ways
Properties that Oracle OLAP checks to determine processing
Commands that initiate actions and functions that initiate actions and return a value.
Programs that perform complicated analysis and reporting
OLAP DML commands, functions, options, programs, and properties are collectively referred to as OLAP DML statements. Part I of this manual introduces basic elements of the OLAP DML. The complete syntax of each statement, usage notes, and examples is provided in Part II of this manual. Lists of statements, arranged by functional category are presented in Appendix A
An option is a special type of analytic workspace object that specifies the characteristic of some aspect of how Oracle OLAP calculates or formats data or what Oracle OLAP operations are activated. You cannot define an option as part of a workspace. However, you can use any of the options that are defined as part of the Oracle OLAP DML.
Some options are read-only, while others are read/write options for which you can specify values. Read/write options have default values.
OLAP DML options fall into the following general categories:
Aggregating data. See Table 6-1.
Allocating data. See Table 7-4.
Modeling data. See Table 17-1.
Compiling programs, models and aggregation specifications. See Table 9-1.
Handling errors. See Table 5-2.
Debugging programs and models. See Table 5-3.
Embedding SQL in OLAP DML programs. See Table 22-1.
File reading and writing. See Table 15-1.
Importing and exporting analytic workspaces. See Table 12-1.
OLAP DML reports. See Table 20-6.
Working with empty cells. See Table 3-5.
Using date and time values. See Table 2-6.
Calculating data. See Table 3-2.
Oracle Database options. See Table 18-2.
The general syntax for specifying and retrieving option values is shown in Table 1-1, "Syntax for Specifying and Retrieving Option Values".
A property is a named value that is associated with a definition of an analytic workspace object. You name, create, and assign properties to an object using a PROPERTY statement.
Properties that begin with a
$ (dollar sign) are recognized by Oracle OLAP as system properties. You assign system properties to objects the same way that you create other properties; however, you must give them the appropriate name in order for Oracle OLAP to recognize them. Part II, " Alphabetic Reference " includes tables that list two types of system properties:
Table 6-2 lists system properties that you can use to specify default behavior when aggregating or allocating data.
Table 3-6 lists system properties that you can use to specify behavior in regard to empty data cells.
OLAP has other system properties that are not as integral to the use of the OLAP DML. For example, properties are part of the object definitions for an analytic workspace that has database standard form.
Most OLAP DML statements are either OLAP DML commands and functions. OLAP DML commands and commands work in much the same way as commands and functions in other programming languages—the one exception is the "looping" nature of OLAP DML commands and functions discussed in "Looping Nature of OLAP DML Commands and Functions".
Many OLAP DML statements are commands that perform complex actions. Some of these commands are data definition commands that you use to create an analytic workspace or define objects within an analytic workspace. Data definition commands are introduced in "OLAP DML as a Data Definition Language".
Other OLAP DML commands are complex data manipulation commands. For example, you can use the OLAP DML SQL command to embed SQL statements in an OLAP DML program in order to copy data from relational tables into analytic workspace data objects, or you can use the AGGREGATE command to calculate summary data. You can also augment the functionality of the OLAP DML by writing an OLAP DML program for use as a command.
Most of the OLAP DML functions are simple text or calculation functions (that is, numeric, financial, statistical, date, time, time-series functions, and aggregation functions), or data type conversion functions. For tables listing these standard functions, see:
Other OLAP DML functions return more complex information. For example, the OLAP DML provides the AW function that you can use to retrieve many different types of information about an analytic workspace and the AGGREGATE function that you can use to calculate aggregate data on-the-fly at user request.
You can also augment the functionality of the OLAP DML by writing an OLAP DML program for use as a function.
Some OLAP DML statements are actually the names of OLAP DML programs provided as part of the OLAP DML. Some of these programs produce reports that you can print or see online. For example, the AWDESCRIBE program produces a report that consists of a summary page; an alphabetic list of analytic workspace objects showing name, type, and description; and a list of object definitions by object type.
Other programs provided as part of the OLAP DML perform standard calculations of use to programmers and database administrators. For example, VALSPERPAGE program calculates the maximum number of values for a variable of a given width that will fit on one analytic workspace page.
You execute programs provided as part of the OLAP DML the same way that you do any other OLAP DML statement following the syntax provided for that program in Part II, " Alphabetic Reference ".
You can also write your own OLAP DML programs to augment the functionality of the OLAP DML as described in Chapter 5, " OLAP DML Programs".