|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
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
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
||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
||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.|
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
||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.|
||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.|
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.
Calculation specifications contain OLAP DML statements that specify analysis that you want performed.
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.
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
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|
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
||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).|