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:

Define and manage analytic workspaces
Define analytic workspace objects
View analytic workspace definitions

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.
COMMIT	Executes a SQL COMMIT statement.
UPDATE	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
AWWAITTIME	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.
DEFAULTAWSEGSIZE	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
CHGDFN	Changes certain aspects of the definitions of certain objects.
CONSIDER	Identifies a definition as the current definition. This enables you to add a description, property, calculation specification, or trigger (event) to an object.
COPYDFN	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.
DEFINE	Adds a new object to the analytic workspace.
DELETE	Deletes one or more objects from a workspace.
LD	Assigns a description to an object that has already been defined.
MOVE	Moves an object name to a new position in the NAME dimension of a workspace.
PERMITRESET	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.
PROPERTY	Assigns a property to an object. A property is a named value that is associated with a given object definition.
RENAME	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.

1.3.2.1 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.

1.3.2.2 Defining Calculation Specification Objects Using the OLAP DML

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

1.3.2.2.1 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.

1.3.2.2.2 Creating Calculation Specification Objects

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

Define the calculation object using the appropriate DEFINE command.
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
Aggregation	DEFINE AGGMAP	AGGMAP	EDIT AGGMAP aggmap-name
Allocation	DEFINE AGGMAP	ALLOCMAP	EDIT AGGMAP aggmap-name
Formula	DEFINE FORMULA	EQ	EDIT FORMULA formula-name
Model	DEFINE MODEL	MODEL	EDIT MODEL model-name
Program	DEFINE PROGRAM	PROGRAM	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.
LISTBY	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.
OBJLIST	Lists the objects that in one or more workspaces that you specify.
AWDESCRIBE	Sends information about the current analytic workspace to the current outfile.
DESCRIBE	Lists the base definition of one or more workspace objects.
FULLDSC	Lists the definition of one or more workspace objects, including the properties and triggers of the object(s).