| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
The real power of the OLAP DML is apparent when you begin using it to analyze your data. Using the OLAP DML you can:
Define special OLAP DML objects (sometimes called calculation specification objects) that you can use to aggregate, allocate, or model data. (See "Defining Calculation Specification Objects Using the OLAP DML" for a general discussion of this process.)
Write programs to perform complex analysis.You can write a OLAP DML programs to perform almost any type of complicated multidimensional analysis.
This section provides overview information about the following types of programs:
For more information on creating an OLAP DML program, see Chapter 5, " OLAP DML 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 discussed in "Programs Executed When Attaching Analytic Workspaces".
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.
The startup programs named PERMIT_READ and PERMIT_WRITE are also known as permission programs. 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—Depending on the statements in the permission program, the user is granted or denied access to specific objects or sets of object values. Within an ONATTACH program, you can use ACQUIRE statements to provide access to individual workspace objects. Within a permission program for read-only or read/write attachment, you can specify PERMIT commands 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".
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.
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.
The OLAP DML provides support for loading data to and from relational tables, flat files, and spreadsheets.
You can embed SQL statements in OLAP DML programs using the OLAP DML SQL command. 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 command. You can use this OLAP DML command 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 commands within an OLAP DML program. You cannot use them interactively in the OLAP Worksheet.
SQL DECLARE CURSOR defines a SQL cursor by associating it with a SELECT statement or procedure.
SQL OPEN activates a SQL cursor.
SQL FETCH and SQL IMPORT retrieve and process data specified by a cursor.
SQL CLOSE closes a SQL cursor.
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.
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.
Within an OLAP DML program you can use the IMPORT (from spreadsheet) command to imp;ort data from a spreadsheet into analytic workspace objects.
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 AW ATTACH, are called startup programs and are discussed in "Startup Programs" and in the topic for AW ATTACH.
Programs triggered by AW CREATE, AW DELETE, AW DETACH, DEFINE, MAINTAIN, PROPERTY, UPDATE, and SET are called trigger programs and are discussed in this section and in the topic for the TRIGGER command.
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 Trigger Programs" discusses these characteristics.
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 24-1, "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 24-1, "Trigger Program Characteristics " to determine if Oracle OLAP will pass values to the program. If it will, use the ARGUMENT command to declare these arguments in your program and the VARIABLE command to define program variables for the values. (See Table 24-2, "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 24-3, "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.
|
See also: The following statements:
|
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 "Defining Calculation Specification Objects Using the OLAP DML". 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 "Executing the Aggregation", "Allocating Data", and "Running a Model".
Oracle OLAP provides statements that you can use to forecast data using a sophisticated forecast context. To forecast using this context, take the following steps:
Create the objects that you need to hold the results of the forecast.
Within the contents of a forecasting program, issue the following statements in the order indicated:
FCOPEN function -- Creates a forecasting context.
FCSET command -- Specifies the characteristics of a forecast.
FCEXEC command -- Executes a forecast and populates Oracle OLAP variables with forecasting data.
FCQUERY function -- Retrieves information about the characteristics of a forecast or a trial of a forecast.
FCCLOSE command -- Closes a forecasting context.
For examples of forecasting programs, see Example 12-10, "A Forecasting Program".
You can export an entire workspace, several workspace objects, a single workspace object, or a portion of a workspace object to a specially formatted EIF file. Then you can import the information into a different workspace within the same Oracle database or a different one. The OLAP DML statements for importing and exporting data are listed in Table A-28, "Statements for Importing and Exporting Data".
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'
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|
