Writing Data from Analytic Workspace Objects into Relational Tables

To copy data from analytic workspace object you can simply use the SQL INSERT or UPDATE statements as arguments to the OLAP DML SQL command. In this case, you code the OLAP DML SQL in a loop and you use the analytic workspace variables as input host variables in your SQL statements. However, you can improve performance by doing a direct insert using the PREPARE and EXECUTE statements as arguments to the OLAP DML command.

Tip: You can access data in an analytic workspace in a SQL SELECT statement without copying data from the analytic workspace into relational tables by defining a view of the analytic workspace data. For more information on defining relational views of analytic workspace data, see Oracle9i OLAP User's Guide.

Using SQL PREPARE and SQL EXECUTE

The syntax of the PREPARE and EXECUTE statements is shown below.

SQL PREPARE statement-name FROM sql-statement [insert-options]
SQL EXECUTE statement-name

The arguments for these statements are described below:

• statement-name is the name that you assign to the executable code produced from sql-statement. You can redefine statement-name just by issuing another SQL PREPARE command.
• sql-statement is the SQL statement that you want to precompile for more efficient execution. It cannot contain ampersand (&) substitution or variables that are undefined when the program is compiled.
• insert-options are DIRECT, NOLOG, and PARTITION that apply when sql-statement is an INSERT statement. When you prepare an INSERT statement and do not specify any values for the insert options, Oracle OLAP specifies NO for the DIRECT and NOLOG insert options and does not specify a value for the PARTITION option. Thus, by default, a prepared INSERT is a normal insert, redo information is recorded in the redo log files, and other sessions cannot insert data into the table into which your program is inserting values. You can improve performance of your INSERT, by changing the values of these options. You can specify that you want a direct insert, that you do not want the redo information recorded in the redo log files, and the partition or subpartition that you want locked (that is, the partition or subpartition into which you do not want another session to be able to insert data).

Performing a Direct Insert

Direct-path insert enhances performance during insert operations and is similar to the functionality of Oracle's direct-path loader utility, SQL*Loader. To specify a direct-path insert, specify DIRECT=YES as the first insert option in the OLAP DML SQL PREPARE INSERT command.

Inserting Workspace Data into Relational Tables: Example

Suppose that you have been using the OLAP DML to plan the introduction of a new product line, and now you want to add information about the product ids and the product names for these new products to the Sales History database. You can copy this information from your analytic workspace into the products table using an OLAP DML program. The definitions for the analytic workspace objects that contain the data are shown in Example 10-24.

The program fragment in Example 10-25 shows how you would use a FOR loop so that all product values currently in status are copied to a table named Products. Example 10-25 will run much more efficiently when the INSERT statement is compiled with the PREPARE statement. Example 10-26 shows the PREPARE statement being used to compile the INSERT statement with a name of write_products, which is then run by an EXECUTE statement within the FOR loop.

Example 10-24 Analytic Workspace definitions for add_newprods program

DEFINE aw_prod_id DIMENSION NUMBER (10,0)
DEFINE aw_product_name DIMENSION TEXT 

The program fragment in Example 10-25 shows how you would use a FOR loop so that all product values currently in status are copied to the relational table named products. Example 10-25 will run much more efficiently when the INSERT statement is compiled with the PREPARE statement. Example 10-26 shows the PREPARE statement being used to compile the INSERT statement with a name of write_products, which is then run by an EXECUTE statement within the FOR loop. Example 10-27 shows the PREPARE statement being used to compile the INSERT statement for direct insert (DIRECT=YES).

Example 10-25 Inefficient FOR Loop

FOR prod
   DO
      SQL INSERT INTO products -
         VALUES(:aw_prod_id, :aw_product_name)
      IF SQLCODE NE 0
         THEN BREAK
   DOEND

Example 10-26 Improving Efficiency Using Precompiled Code

SQL PREPARE write_products FROM -
   INSERT INTO products -
      VALUES(:aw_prod_id, :aw_product_name)
      .
      .
      .
FOR prod
   DO
      SQL EXECUTE write_products
      IF SQLCODE NE 0
         THEN BREAK
   DOEND

Example 10-27 Improving Efficiency Using a Direct Insert

SQL PREPARE write_products FROM -
   INSERT INTO products -
      VALUES(:aw_prod_id, :aw_product_name)
      DIRECT=YES
      .
      .
      .
FOR prod
   DO
      SQL EXECUTE write_products
      IF SQLCODE NE 0
         THEN BREAK
   DOEND

Conditionally Updating a Relational Table

You can also use the values of an analytic workspace variable to update the values in a relational table. Using a FOR loop, your OLAP DML program steps through the specified dimension value by value and uses a WHERE clause to point to the corresponding row in the relational table.

The program fragment in Example 10-28 updates only those rows in the products table where the values in the prod_id column match the aw_prod_id dimension values currently in status.

Example 10-28 Conditionally Updating a Relational Table

FOR prod
   DO
      SQL UPDATE products -
      SET product_name = :aw_newproduct_name -
         WHERE prod_id = :aw_prod_id
      IF SQLCODE NE 0
         THEN BREAK
   DOEND