Common Types of OLAP DML Programs

This section provides overview information about the following types of programs:

Startup Programs
Data Import and Export Programs
Trigger Programs
Aggregation, Allocation, and Modeling Programs
Forecasting Programs
Programs to Export and Import Workspace Objects

Startup Programs

Startup programs are programs that you write and that Oracle OLAP checks for by name when an AW ATTACH statement executes. Startup programs do not exist within an analytic workspace unless you define and write them. In a startup program you can execute any OLAP DML statements, or run any of your own programs. For example, a startup program might set options to values appropriate to your application.

The types of startup programs that are recognized by Oracle OLAP are discussed in this topic. The order in which these programs are executed is outlined in "Programs Executed When Attaching Analytic Workspaces".

There are two major categories of startup programs:

Startup programs that are defined within the same analytic workspace for which an AW ATTACH statement triggers their execution. Within this category of startup programs are:
- Permission Programs
- AUTOGO Programs and ONATTACH Programs
A program named TRIGGER_AW that is defined in an analytic workspace and that executes when an AW ATTACH statement is issued for any other analytic workspace. See TBD for more information.

Permission Programs

Permission programs are programs that you write that give permission to users to access workspace data. When a user attaches an analytic workspace, Oracle OLAP checks to see if a permission program that is appropriate for the attachment mode exists. (The permission program for each attachment mode must have a particular name as outlined in Table 4-2, "Names of Permission Programs for Different Attachment Modes".) When an appropriate permission program exists, Oracle OLAP executes the program. When a user specifies a password when attaching the analytic workspace, then the password is passed as an argument to the permission program for processing.

Table 4-2 Names of Permission Programs for Different Attachment Modes

Attachment Modes	Name of Program
Read-only	PERMIT_READ
Multiwriter, Read/write	PERMIT_WRITE

Note:

A dimension surrogate has the access permissions of its dimension. Use a PERMIT on a dimension to grant or deny permission to access the values of a dimension surrogate for that dimension.

Permission programs allow you to control two levels of access to the analytic workspace in which they reside.

Access at the analytic workspace level—Depending on the return value of the permission program, the user is or is not granted access to the entire analytic workspace. You can use the return value to indicate to Oracle OLAP whether or not the user has the right to attach the workspace.
Access at the object level—Within a permission program for read-only or read/write attachment, you can specify PERMIT statements that grant or restrict access to individual workspace objects. PERMIT programs must be in the same workspace as the objects for which they issue PERMIT statements.

Note:
All of the objects referred to in a given permission program must exist in the same analytic workspace.

To create a permission program, define a user-defined function (as described in "Creating User-Defined Functions") with one of the recognized names, then define the contents for the program as described in "Specifying Program Contents".

AUTOGO Programs and ONATTACH Programs

You can create an Autogo program by defining a program with any name, and specifying that name in the AW ATTACH statement after the AUTOGO keyword.

You can create an Onattach program in one of two ways:

You can define a program named ONATTACH. Each time you attach the workspace, the ONATTACH program executes automatically unless you include a NOOTTACH keyword in the AW ATTACH statement.
You can define a program and give it any name you want. When attaching the workspace using a AW ATTACH statement, you can run the program by specifying its name after the ONATTACH keyword. This is useful for application developers; an application can run a different startup program depending on the users' choices.

Data Import and Export Programs

The OLAP DML provides support for importing data from relational tables, flat files, and spreadsheets into analytic workspace objects; and for exporting data from analytic workspace objects to relational tables, flat files, and spreadsheets.

Importing Data to and Exporting Data from Relational Tables

You can embed SQL statements in OLAP DML programs using the OLAP DML SQL statement. Using the OLAP DML SQL statement you can import data from relational tables into analytic workspace objects and export data from analytic workspace objects to relational tables.

Importing Data From Relational Tables to Workspace Objects

Using the OLAP DML SQL statement within an OLAP DML program you can copy relational data into analytic workspace objects using either an implicit cursor or an explicit cursor:

To copy data from relational tables into analytic workspace objects using an implicit cursor, use the SQL SELECT statement. You can use this OLAP DML statement interactively in the OLAP Worksheet or within an OLAP DML program.
To copy data from relational tables into analytic workspace objects using an explicit cursor, use the following statements in the order indicated. You can only use these statements within an OLAP DML program. You cannot use them interactively in the OLAP Worksheet.
1. SQL DECLARE CURSOR defines a SQL cursor by associating it with a SELECT statement or procedure.
2. SQL OPEN activates a SQL cursor.
3. SQL FETCH and SQL IMPORT retrieve and process data specified by a cursor.
4. SQL CLOSE closes a SQL cursor.
5. SQL CLEANUP cancels a SQL cursor declaration and frees the memory resources of an SQL cursor.

For examples of programs that copy table data into workspace objects, see SQL FETCH and SQL IMPORT.

Exporting Data from OLAP DML Objects to Relational Tables

Within a program, you can use an OLAP DML SQL statement with the INSERT keyword to copy data from analytic workspace objects into relational tables. Typically, you do this by issuing the following statements in your OLAP DML program:

SQL PREPARE statements, to precompile the INSERT and UPDATE statements.
SQL EXECUTE statements, to execute the statements that you precompiled in Step 1.

Importing Data to and Exporting Data from Flat Files

Oracle OLAP provides a number of statements that you can use to read data from flat files or to write data to flat files. These statements are frequently used together in a special program.

Importing Data to and Exporting Data from Spreadsheets

Within an OLAP DML program you can use an IMPORT statement to import data from a spreadsheet into analytic workspace objects. You can use an EXPORT statement to export data from analytic workspace objects into a spreadsheet.

Trigger Programs

DEFINE, MAINTAIN, PROPERTY, SET (=) UPDATE, and AW commands are recognized by Oracle OLAP as events that can trigger the execution of OLAP DML programs.

Programs triggered by DEFINE, MAINTAIN, PROPERTY, UPDATE, or SET commands, are called object trigger programs and are discussed in this section and in the topic for the TRIGGER command.
A program named TRIGGER_AW that is defined within one analytic workspace and which is triggered when another analytic workspace is created, attached, detached or deleted. See the discussion of the "TRIGGER_AW" for more information.

Trigger programs are frequently written to maintain application-specific metadata. Trigger programs have certain characteristics depending on the statement that triggers them. Some trigger programs execute before the triggering statement executes; some after. Oracle OLAP passes arguments to programs triggered by some statements, but not others. Oracle OLAP does not change dimension status before most trigger programs execute, but does change dimension status before some MAINTAIN statements trigger program execution. In most cases, you can give a trigger program any name that you choose, but some events require a program with a specific name. "Characteristics of Object Trigger Programs" discusses these characteristics.

Creating an Object Trigger Program

Once an object is defined in an analytic workspace, you can create a trigger program for that object by following the following procedure:

Define the program as described in DEFINE PROGRAM.
Determine what to name the program and whether the program can be a user-defined program. (See Table 4-3, "Object Trigger Program Characteristics".) If the program can be a user-defined program, decide whether or not you want to define the trigger program as a user-defined function.
Code the actual program as described in"Specifying Program Contents".
Keep the following points in mind when coding trigger programs:
- Use Table 4-3, "Object Trigger Program Characteristics" to determine if Oracle OLAP will pass values to the program. If it will, use an ARGUMENT statement to declare these arguments in your program and the VARIABLE statement to define program variables for the values. (See Table 4-4, "Arguments Passed to Trigger Programs" for specific information about the arguments.)
- A program that is triggered by an Assign event is executed each time Oracle OLAP assigns a value to the object for which the event was defined. Thus, a program triggered by an Assign event is often executed over and over again as the assignment statements loops through a object assigning values. You can use TRIGGERASSIGN to assign a value that is different from the value specified by the assignment statement that triggered the execution of the program.
- In some cases, Oracle OLAP changes the status of the dimension being maintained when a Maintain event triggers the execution of a program. See Table 4-5, "How Programs Triggered by Maintain Events Effect Dimension Status" for details
- Use the CALLTYPE function within a program to identify that the program was invoked as a trigger.
When the trigger program is not a TRIGGER_AFTER_UPDATE, TRIGGER_BEFORE_UPDATE, or TRIGGER_DEFINE program, associate the program with the desired object and event using the TRIGGER command.
There is no support for recursive triggers. You must set the USETRIGGERS option to NO before you issue the same DML statement within a trigger program that triggered the program itself. For example, assume that you have written a program named TRIGGER_MAINTAIN_ADD that is triggered by MAINTAIN ADD statements. Within the TRIGGER_MAINTAIN_ADD program, you must set the USETRIGGERS option to NO before you issue a MAINTAIN statement.

Characteristics of Object Trigger Programs

Object t rigger programs have certain characteristics depending on the statement that triggers them. Some trigger programs execute before the triggering statement executes; some after. Oracle OLAP passes arguments to programs triggered by some statements, but not others. Oracle OLAP does not change dimension status before most trigger programs execute, but does change dimension status before some MAINTAIN statements trigger program execution. In most cases, you can give a trigger program any name that you choose, but some events require a program with a specific name.

Table 4-3, "Object Trigger Program Characteristics" lists the OLAP DML statements that trigger programs, the required name of the program (if any), whether or not Oracle OLAP uses values returned by the program, and whether or not Oracle OLAP passes arguments to the program.

Keep the following points in mind when designing trigger programs:

Triggers that execute before the DML statement—For trigger programs that execute before the triggering OLAP DML statement executes, you can define the trigger program as a user-defined function that returns a BOOLEAN value. The value returned by the program determines whether or not Oracle OLAP executes the statement that triggered the execution of the trigger program. When the program returns FALSE, Oracle OLAP does not execute the triggering statement; when it returns TRUE or NA, the triggering statement executes.
Arguments passed to trigger programs—Oracle OLAP passes arguments to some trigger programs. These programs are identified in Table 4-3, "Object Trigger Program Characteristics". Descriptions of these arguments are provided in Table 4-4, "Arguments Passed to Trigger Programs". Use the ARGUMENT statement to declare these arguments in your program. Use VARIABLE to define program variables for the values. Use the WKSDATA function to retrieve the data type of an argument with a WORKSHEET data type.
Assign trigger programs—Oracle OLAP executes a program triggered by an Assign event each time it assigns a value to the object for which the event was defined. Thus, a program triggered by an Assign event is often executed over and over again as the assignment statements loops through a object assigning values. With each execution, the value to be assigned is passed as argument1 to the Assign trigger program. (See Table 4-4, "Arguments Passed to Trigger Programs" for more information and Example 10-162, "An ASSIGN Trigger on a Variable" for an example.) Within the Assign trigger program, you can use a TRIGGER ASSIGN statement to assign a different value than that specified by the assignment statement that triggered the execution of the Assign trigger program.

You can only assign values to a formula when the formula has an Assign trigger defined for it. When you assign a value to a formula with an Assign event, Oracle OLAP executes the trigger program for the event for assigned value and passes the assigned value to the trigger program. The Assign trigger does not change the definition of the formula itself. See Example 10-164, "An ASSIGN Trigger on a Formula" for an example of an Assign trigger on a formula.
Maintain trigger programs and dimension status —In some cases, Oracle OLAP changes the status of the dimension being maintained when a Maintain event triggers the execution of a program. See Table 4-5, "How Programs Triggered by Maintain Events Effect Dimension Status" for details.
Maintain triggers and dimension surrogates—Maintain triggers for dimension surrogates are different than Maintain triggers for other objects. You can only successfully issue a MAINTAIN statement against a dimension surrogate, when the dimension surrogate has a Maintain trigger. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error. Also, for Maintain Add and Maintain Merge triggers, whether or not an argument is passed to the program depends on the object on which the trigger is defined:
- For dimension surrogates with a Maintain trigger, Oracle OLAP executes the trigger program one time for each value added or merged and passes that value into the program.
- For other objects with a Maintain trigger, Oracle OLAP executes the trigger program only once after the MAINTAIN statement executes and no values are passed into the program

Table 4-3 Object Trigger Program Characteristics

Triggering Statement (event)	Program Name	Return Values	Passed Arguments
= (assignment) statement (SET)	No required name	No	Yes
DEFINE	TRIGGER_DEFINE	No	No
MAINTAIN ADD	No required name	No	No
MAINTAIN DELETE (not ALL)	No required name	Yes	No
MAINTAIN DELETE ALL	No required name	Yes	No
MAINTAIN MERGE	No required name	No	No
MAINTAIN MOVE	No required name	Yes	Yes
MAINTAIN RENAME	No required name	Yes	Yes
PROPERTY	No required name	Yes	Yes
UPDATE (Update AW)	TRIGGER_AFTER_UPDATE	No	No
UPDATE (Update AW)	TRIGGER_BEFORE_UPDATE	Yes	No
UPDATE (Update Multi)	No required name	No	No

Table 4-4 Arguments Passed to Trigger Programs

Event	Argument1	Argument2
Property	When the PROPERTY statement is assigning a property to an object, the name of the property. When the PROPERTY statement is deleting one or more properties, `the literal DELETE`. (`TEXT` data type)	When the value of argument1 is `DELETE`, the name of the property or the literal `ALL`. In all other cases, the name of the property. (`WORKSHEET` data type)
Assignment	The value that you want to assign. When you know the data type of the object to which the value is assigned, specify that data type for the argument. When you do not know the actual data type, specify `WORKSHEET` as the data type of the argument.	None. Oracle OLAP passes only one argument to the program.
Maintain Add		(Dimension surrogates only) The value added. (`WORKSHEET data type)`
Maintain Rename	The dimension value that you want to rename. (`TEXT` data type)	The new name of the dimension member. (`WORKSHEET` data type)
Maintain Merge		(Dimension surrogates only) The value merged. (`WORKSHEET data type)`
Maintain Move	The position of the dimension value that you want to move. (`TEXT` data type)	The literal `BEFORE` or `AFTER`. (`WORKSHEET` data type)

Table 4-5 How Programs Triggered by Maintain Events Effect Dimension Status

Event Subevent	Dimension Status Before Program Execution
Maintain Add	Status set to dimension values just added.
Maintain Delete	Status set to dimension values about to be deleted.
Maintain Delete All	Current status is not changed.
Maintain Merge	Status set to dimension values just merged.
Maintain Move	Status set to dimension values about to be moved.
Maintain Rename	Current status is not changed.

Aggregation, Allocation, and Modeling Programs

To aggregate, allocate, or model data using the OLAP DML, you first specify the calculation that you want performed by defining a calculation specification as outlined in "Creating Calculation Objects". Later, if you want to populate variables with aggregated, allocated or modeled values as a Database maintenance procedure, you write a program to execute the calculation object. For more information on the OLAP DML statements that you use in these programs, see "Running a Model", "Executing the Aggregation", and "Allocating Data".

Forecasting Programs

The OLAP DML has several related statements that allow you to forecast data using the Geneva Forecasting engine which is a statistical forecasting engine from Roadmap Technologies that is used extensively in demand planning applications.

To forecast using the Geneva Forecasting engine, take the following steps:

Add the future time values to the time dimension.
Create a variable to hold the results of the forecast.
Write a forecasting program. Within the program, issue the following statements in the order indicated:
1. FCOPEN function -- Creates a forecasting context.
2. FCSET command -- Specifies the forecast characteristics.
3. FCEXEC command -- Executes a forecast and populates Oracle OLAP variables with forecasting data.
4. FCQUERY function -- Retrieves information about the characteristics of a forecast or a trial of a forecast.
5. FCCLOSE command -- Closes a forecasting context.

For examples of using these statements to forecast data see Example 9-118, "A Forecasting Program".

Programs to Export and Import Workspace Objects

You can export an entire workspace, several workspace objects, a single workspace object, or a portion of an analytic workspace object to a specially formatted EIF file. Then you can import the information into a different workspace within the same schema or a different one.

One reason for exporting and importing is to move your data to a new location. Another purpose is to remove extra space from your analytic workspace after you have added and then deleted many objects or dimension values. To do this, issue an EXPORT statement to put all the data in an EIF file, create another workspace with a different name, and then use an IMPORT statement to import the EIF file into the new workspace. When you have imported into the same Database, you can delete the old workspace and refer to the new one with the same workspace alias that you used for the original one.

The following statement copies all the data and definitions from the current analytic workspace to an EIF file called reorg.eif in a directory object called mydir.

EXPORT ALL TO EIF FILE 'mydir/reorg.eif'