Common Types of OLAP DML Programs

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

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.

  
  

  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

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.

ONATTACH Programs

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.

TRIGGER_AW Program

When you create a program named TRIGGER_AW program, the execution of any AW command (including an AW ATTACH statement) becomes an event that triggers the execution of the TRIGGER_AW program.

Programs that Copy Data From Relational Tables to Workspace Objects

You can embed SQL statements in OLAP DML programs using the OLAP DML SQL statement. Oracle OLAP provides statements that you can use in a program to 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 implicit 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.

File-Reading Programs

Oracle OLAP provides a number of statements that you can use to read data from flat files. These statements (listed in "File Reading and Writing Statements") are frequently used together in a special program.

Spreadsheet Import Programs

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

Creating a 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:

1. Define the program as described in DEFINE PROGRAM.

2. Determine what to name the program and whether the program can be a user-defined program. (See Table 5-3, "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.

3. Code the actual program as described in"Specifying Program Contents".

4. Keep the following points in mind when coding trigger programs:

   • Use Table 5-3, "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 5-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 5-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.

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

6. 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 Trigger Programs

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.

Table 5-3, "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 5-3, "Trigger Program Characteristics". Descriptions of these arguments are provided in Table 5-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 5-4, "Arguments Passed to Trigger Programs" for more information and Example 24-8, "An ASSIGN Trigger on a Variable" for an example.) Within the Assign trigger program, you can use aTRIGGERASSIGN 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 24-10, "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 5-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

Forecasting Using a Forecast Context

To forecast using a forecasting context, take the following steps:

1. Create the objects that you need to hold the results of the forecast.

2. 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 to be used by the "expert system".

   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 the a forecasting context to forecast data see Example 12-9, "A Forecasting Program". For examples of simple forecasts using the FORECAST command, see Example 13-27, "Using the EXPONENTIAL Method" and Example 13-28, "Using the WINTERS Method".

Further Reading on Forecasting

For additional information about forecasting and forecasting methods, Oracle suggests the latest editions of the following books.

• Levenbach, Hans, and Cleary, James P. The Beginning Forecaster. Belmont, CA: Lifetime Learning Publications.

• Mosteller, Frederick, and Tukey, John W. Data Analysis and Regression. Reading, MA: Addison-Wesley Publishing Co. Inc.

• Makridakis, Spyros, and Wheelwright, Steven C. Interactive Forecasting. San Francisco, CA: Holden-Day Inc.