Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

1.3 OLAP DML as a Data Definition Language

The OLAP DML provides statements that you can use to create and manage analytic workspaces and the object definitions within them.

This section provides overview information about the statements that you use to:

1.3.1 Statements for Creating Analytic Workspaces

Table 1-2, "Statements for Creating and Managing Analytic Workspaces" lists the OLAP DML statements that you use to create and manipulate analytic workspaces. Table 1-3, "Options Related to Creating or Attaching Analytic Workspaces" lists the OLAP DML options that relate to these statements.

Table 1-2 Statements for Creating and Managing Analytic Workspaces

Statement Description
AW command
Creates a new workspace; allocates space for a workspace; attaches a workspace to a session; deletes a workspace; detaches a workspace from a session; sets up a workspace for multiple segments; or sends to the current outfile a list of the active workspaces, along with their update status.
Executes a SQL COMMIT statement.
Moves analytic workspace changes from a temporary area to the database table in which the workspace is stored. The table is not saved until you execute a COMMIT command, either from Oracle OLAP or from SQL.

Table 1-3 Options Related to Creating or Attaching Analytic Workspaces

Statement Description
An option that contains the number of seconds that AW ATTACH with the if the WAIT keyword waits for an analytic workspace to become available for access.
An option that specifies the default maximum segment size for an analytic workspace created in your database session.

1.3.2 Defining Analytic Workspace Objects

An analytic workspace contains two types of objects:

  • Data objects that contain the data that you want to analyze and the results of the analysis.

  • Calculation specifications that contain OLAP DML statements that specify the analysis that you want performed.

Table 1-4, "Workspace Object Data Definition Statements" lists the OLAP DML statements that relate to defining analytic workspace objects. For more specific information, see "Defining Data Objects Using the OLAP DML" and ore information on calculation specification objects, see "Defining Calculation Specification Objects Using the OLAP DML".

Table 1-4 Workspace Object Data Definition Statements

Statement Description
Changes certain aspects of the definitions of certain objects.
Identifies a definition as the current definition. This enables you to add a description, property, calculation specification, or trigger (event) to an object.
Defines a new object in the analytical workspace and uses the same definition as a specified object in the current workspace or in an attached workspace.
Adds a new object to the analytic workspace.
Deletes one or more objects from a workspace.
Assigns a description to an object that has already been defined.
Moves an object name to a new position in the NAME dimension of a workspace.
Causes the values of permission conditions to be reevaluated. Permission conditions consist of one or more Boolean expressions that designate the criteria used by PERMIT commands associated with an object.
Assigns a property to an object. A property is a named value that is associated with a given object definition.
Changes the name of an object in an analytical workspace and updates associated objects.
TRIGGER command
Associates a previously-created program to an object and identifies the object event that automatically executes the program; or a disassociates a trigger program from the object
VALSPERPAGE Calculates the maximum number of values for a variable of a given width that will fit on one page. Pages are units of storage in the workspace. Defining Data Objects Using the OLAP DML

Data objects contain the data that you want to analyze and the results of the analysis. Data objects are implemented as arrays and indexes.

Table 1-5, "OLAP Data Object Definition Statements" briefly describes the data objects that you can define in an analytic workspace and the OLAP DML statements that you use to define these objects.

Table 1-5 OLAP Data Object Definition Statements

Object Name Description DEFINE command
Variable An array of values that you want to analyze or an array of values that are the result of the analysis. DEFINE VARIABLE
Dimension A dimension or index to one or more variables or relations, or provide a list of values to an OLAP DML program. DEFINE DIMENSION
Composite A list of dimension value combinations that you use to dimension variables when you do not want the variable to have empty cells. DEFINE COMPOSITE
Relation A multidimensional array whose values specify correspondence between the values of one or more dimensions. For example, a parent relation for a hierarchical dimension describes the child-parent relationship of the values within the dimension. DEFINE RELATION

Oracle OLAP also supports the definition of dimension surrogates and valuesets that you can use in calculations instead of dimensions. (You cannot use these objects to dimension variables or relations.) See DEFINE SURROGATE and DEFINE VALUESET for more information. Defining Calculation Specification Objects Using the OLAP DML

Calculation specifications contain OLAP DML statements that specify analysis that you want performed. Types of Calculation Specifications

Using the OLAP DML you can define objects that are specifications for different types of OLAP calculation.

  • Formulas—A formula is a saved expression.

  • Aggregations—An aggregation is a specification for how data should be aggregated..

  • Allocations—An allocation is a specification for how data should be allocated.

  • Models—A model is a set of interrelated equations. The calculations in an equation can be based either on variables or on dimension values. You can assign the results of the calculations directly to a variable or you can specify a dimension value for which data is being calculated.

  • Programs—An OLAP DML program is a collection of OLAP DML statements that helps you accomplish some workspace management or analysis task. You can use OLAP DML programs as user-defined commands and functions. Creating Calculation Specification Objects

The general process of creating a calculation specification object is the following two step process:

  1. Define the calculation object using the appropriate DEFINE command.

  2. Add the calculation specification to the object definition. You can add the calculation specification to the definition of a calculation object in the following ways:

    • At the command line level of the OLAP Worksheet, in an input file, or as an argument to a PL/SQL function. In this case, ensure that the object is the current object (issue a CONSIDER statement, if necessary), and, then, issue the appropriate command that includes the specification as a multiline text argument. To code the specification as a multiline text, you can use a JOINLINES function where each of the text arguments of JOINLINES is a statement that specifies the desired processing, and where the final statement is END.

    • In an Edit Window of the OLAP Worksheet. In this case, at the command line level of the OLAP Worksheet, issue an EDIT statement with the appropriate keyword. This opens an Edit Window for the specified object. You can then type each statement as an individual line in the Edit Window. Saving the specification and closing the Edit Window when you are finished.

Table 1-6 outlines the OLAP DML statements that you use to create each type of calculation specification. For more detailed information on creating calculation specifications, see the relevant DEFINE statement, Chapter 4, " Formulas, Aggregations, Allocations, and Models", and Chapter 5, " OLAP DML Programs".

Table 1-6 Commands for Defining Calculation Specifications

Specification Type Definition Statement Command for Entering Specification Statement for Opening Edit Window
EDIT AGGMAP aggmap-name
EDIT AGGMAP  aggmap-name
EDIT FORMULA formula-name
EDIT MODEL model-name
EDIT [PROGRAM] program-name

1.3.3 Viewing Data Definitions

Table 1-7, "Statements for Viewing Definitions" lists the OLAP DML statements that you can use to view definitions stored in an analytic workspace

Table 1-7 Statements for Viewing Definitions

Statement Description
AW function
Returns information about currently attached workspaces.
EXISTS Returns a value that indicates whether an object is defined in any attached workspace.
Lists all objects in a workspace that are dimensioned by or related to one or more specified dimensions or composites.
LISTNAMES Lists the names of the objects in a workspace.
OBJ Returns information about a workspace object.
Lists the objects that in one or more workspaces that you specify.
Sends information about the current analytic workspace to the current outfile.
Lists the base definition of one or more workspace objects.
Lists the definition of one or more workspace objects, including the properties and triggers of the object(s).