Release 1 (9.0.1)
Part Number A86720-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Using Embedded SQL, 15 of 15
The sample relational database used for SQL examples contains corporate data for a fictitious electronics company. All of the examples are derived from four tables: Stores, Products, Shipments, and Shipments_Detail.
The examples show selected data from these tables as displayed using SQL*Plus.
The Stores table has a row for each retail outlet. The STORE_ID column is the primary key and uniquely identifies each store.
The following table shows sample data from three columns of the Stores table.
SQL> select store_id, store_name, city from stores; STORE_ID STORE_NAME CITY -------- --------------------- ------------ G4 Sears Boston G5 Cambridge Sound Cambridge G6 New England Stereo Boston G7 Tweeter Burlington G8 Tweeter Nashua . . .
The Products table contains a row with information about each product. The PROD_ID column is the primary key and uniquely identifies each product.
SQL> select * from products; PROD_ID PROD_NAME SUGGESTED_PRICE ---------- ---------------------- --------------- P9 CD Player 248.95 P10 Receiver 298.95 P11 Amplifier 189.95 P12 Cassette Deck 159.95 P15 Color TV 499.95 P16 B & W TV 74.95 . . .
The Shipments table contains a row for each purchase order.
The ORDER_NO column is the primary key.
The STORE_ID column is a foreign key and contains store identification numbers listed in the STORE_ID column of the Stores table. The STORE_ID columns of the Shipments and Stores tables create a relationship between the two tables.
The same store identification numbers can appear in numerous rows, once for each order placed by a particular retail outlet. In the following sample of data from the Shipments table, store G16 appears twice.
SQL> select * from shipments; ORDER_NO STORE_ID CUST_PO TIMESTAMP ------------ -------- ------------- --------- OR25 G6 2FD963718 12-MAR-96 OR26 G20 2243-3122-05 12-MAR-96 OR27 G21 379711 12-MAR-96 OR28 G11 2332628347 12-MAR-96 OR29 G13 786219190 19-MAR-96 OR30 G16 163222 19-MAR-96 . . .
The Shipments_Detail table identifies the quantity and type of items ordered.
The primary key is composed of two columns, ORDER_NO and PROD_ID. The ORDER_NO column is also a foreign key containing values from the ORDER_NO column of the Shipments table. The ORDER_NO columns of the Shipments and Shipments_Detail tables create a relationship between these two tables.
The PROD_ID column is also a foreign key containing values from the PROD_ID column of the Products table. The PROD_ID columns of the Products and Shipments_Detail tables create a relationship between these two tables.
SQL> select * from shipments_detail where units < 100; ORDER_NO PROD_ID UNITS SELLING_PRICE ------------ ---------- ---------- ------------- OR51 P10 54 14528.97 OR51 P11 73 12479.71 OR51 P12 76 10940.58 OR51 P19 59 9555.35 OR51 P23 83 59756.27 OR51 P24 55 32172.52 OR52 P9 76 17028.18 OR52 P11 74 12650.67 . . .
Following is a diagram of the database. The primary keys are shaded and the relationships between tables are identified by arrows between them.
The SQL examples in this chapter use the tables shown in the previous topic to create an OLAP DML data variable named V.UNITS. It is dimensioned by geography, product, and time dimensions, which are named GEOG, PROD, and TIME, respectively.
Since the relational tables provide codes for both stores and products, the Geography and Product dimensions use these codes as their dimension values. The full names for the stores and products are stored in separate text variables so that they are available for labeling the data in reports, but they are not used for storing and selecting data.
In the relational database, the order number is critically important for tracking individual sales. However, it is not an important factor from the standpoint of analytical processing. This information is not retrieved into the analytic workspace.
The values for GEOG, the geography dimension, are fetched from the STORE_ID column of the Stores table. The GEOG dimension contains identifications numbers for all the stores, since the numbers are obtained from a primary key. The definition for the GEOG dimension is shown below, followed by a report of the first five values in GEOG.
DEFINE GEOG DIMENSION TEXT LD All stores GEOG -------------- G11 G12 G13 G14 G15
The product dimension, PROD, also acquires its values from a primary key to get a complete list of all products. The product values are fetched from the PROD_ID column of the Products table. The definition for the PROD dimension is shown below, followed by a report of the first eight values in PROD.
DEFINE PROD DIMENSION TEXT LD All products PROD -------------- P10 P11 P12 P15 P16 P17 P19 P20
Time dimension values are fetched from the TIMESTAMP column of the Shipments table. The definition for the TIME dimension is shown below, followed by a report of the first eight values in TIME.
DEFINE TIME DIMENSION WEEK ENDING SATURDAY LD Time dimension TIME -------------- W6.95 W7.95 W8.95 W9.95 W10.95 W11.95 W12.95 W13.95
Notice that the date values from the relational table were converted to a weekly format when they were brought into the TIME dimension in the analytic workspace.
The number of items sold is fetched from the UNITS column of the Shipments_Detail column and stored in a variable named V.UNITS. This variable is dimensioned by GEOG, PROD, and TIME. Here is the object definition for V.UNITS.
DEFINE V.UNITS VARIABLE SHORTINTEGER <GEOG PROD TIME> LD Units sold
The report command
report down prod across time: v.units
produces the following report.
GEOG: G7 ------------V.UNITS------------- --------------TIME-------------- PROD W3.96 W8.96 W11.96 -------------- ---------- ---------- ---------- P10 185 137 127 P11 153 153 155 GEOG: G11 ------------V.UNITS------------- --------------TIME-------------- PROD W3.96 W8.96 W11.96 -------------- ---------- ---------- ---------- P10 455 NA 180 P11 854 NA 773
You can easily rotate a multidimensional data cube for different types of analyses. In the next example, products identify the rows, and stores identify the columns. Each time period appears in a separate report. The data is much easier to identify this time because the report shows the full descriptive labels for stores and products.
The report command
report w 20 down labels.p across labels.g: v.units
produces the following report.
TIME: W3.96 ------------V.UNITS------------- --------------GEOG-------------- Sams Club Tweeter - Wal-Mart - LABELS.P - New York Burlington New York -------------------- ---------- ---------- ---------- Receiver 455 185 166 Amplifier 854 153 63 Cassette Deck 347 63 175 Color TV 530 127 129 B & W TV 436 189 134 Portable TV 217 64 174 Standard VCR 670 170 110 Stereo VCR 813 161 151
The following table provides a summary of the OLAP DML objects that are created from the sample relational database.
The GET_PRODUCTS program fetches product codes into the PROD dimension and fetches descriptive labels into a text variable named LABELS.P.
Both PROD and LABELS.P have been defined in the database, but they do not yet have values.
The SELECT statement of the SQL DECLARE CURSOR command compares the value of a local variable with the suggested price for the product to determine whether or not to include a product in the fetch. Notice that you do not need to use the DISTINCT keyword in the SELECT statement; duplicate values from the fetch are disregarded when adding dimension values.
The object definitions are listed below.
DEFINE PROD DIMENSION TEXT LD Product dimension DEFINE LABELS.P VARIABLE TEXT <PROD> LD Product labels DEFINE GET_PRODUCTS PROGRAM LD Get product dimension values and labels PROGRAM variable set_price short set_price = 2 trap on error " Connect to database if communications " have not been established already. . . . " Declare a cursor named HIGHPRICE sql declare highprice cursor for - select Prod_ID, Prod_Name from products - where Suggested_Price > :set_price if sqlcode ne 0 then signal err1 'Declare cursor failed.' " Open the cursor sql open highprice if sqlcode ne 0 then signal err2 'Cursor open failed.' " Fetch the data into the PROD dimension and " LABELS.P variable sql fetch highprice loop into :append prod, :labels.p if sqlcode ne 0 and sqlcode ne 100 then signal err3 'Fetch failed.' " Close the cursor sql close highprice if sqlcode ne 0 then signal err4 'Cursor close failed.' show 'Product dimension created.' update goto cleanup ERROR: show 'Product dimension build failed.' CLEANUP: " Free the cursor sql rollback END
In this example, two columns of information are needed to describe the geography values uniquely. Since many of the stores are located in more than one city, the store name and the city must be combined into a unique descriptive label.
The GET_GEOLABELS program fetches the store and city names into temporary text variables first. Then the JOINCHARS function combines them into text for the LABELS.G variable. The temporary variables are discarded at the end of the program, regardless of whether or not the program completes successfully.
The object definitions are listed below.
DEFINE GEOG DIMENSION TEXT LD Geography dimension DEFINE LABELS.G VARIABLE TEXT <GEOG> LD Store and city labels
The GET_GEOLABELS program is listed below.
DEFINE GET_GEOLABELS PROGRAM LD Get store and city names for geography labels PROGRAM define store.g variable text <geog> temp define city.g variable text <geog> temp trap on ERROR " Connect to database if communications " have not been established already. . . . " Declare a cursor named GEOLABELS sql declare geolabels cursor for - select Store_ID, Store_Name, City from stores if sqlcode ne 0 then signal err1 'Declare cursor failed.' " Open the cursor sql open geolabels if sqlcode ne 0 then signal err2 'Open cursor failed.' " Fetch the data into the GEOG dimension and " temporary variables STORE.G and CITY.G sql fetch geolabels loop into :append geog, :store.g, :city.g if sqlcode ne 0 and sqlcode ne 100 then signal err3 'Fetch failed.' " Close the cursor sql close geolabels if sqlcode ne 0 then signal err4 'Close cursor failed.' " Maintain the permanent LABELS.G variable labels.g = joinchars(store.g, ' - ', city.g) delete store.g city.g update show 'Geography dimension and labels maintained.' goto cleanup ERROR: delete store.g city.g show 'Geography dimension build failed.' CLEANUP: " Free the cursor sql rollback END
The GET_TIME program adds new values to the TIME dimension, which is already defined with dimension values. Two arguments define the range into which the new values must fall.
GET_TIME fetches the new values from the relational table into a dimension named NEWTIME instead of TIME. Consequently, if the fetch fails while values are being read into the analytic workspace, then the analytic workspace can be restored easily to its previous state. The new values are added to the TIME dimension, using a MAINTAIN MERGE command, only after all values have been fetched successfully.
To run the program, you issue a command like the following one.
call get_time ('01APR95' '30JUN95')
The arguments in this program are defined with a DATE data type. These arguments are passed directly to the data source, so they must be in a date format that the data source understands. The format is controlled by the OLAP DML DATEFORMAT setting. Whenever a program uses a DATE argument, you must make sure that DATEFORMAT is set to an appropriate format.
You could, however, define the arguments with a TEXT or ID data type. When a program uses a TEXT or ID argument to pass a date, you must make sure to call the program with arguments in a date format that is acceptable to the data source.
In the sample GET_TIME program, the OLAP DML date format is the default "01JAN95" when the starting and ending dates are passed to the data source. Later in the program, the date format changes to "January 1st, 1995". Most data sources do not accept this date format. A date value passed in this date format would cause the OPEN CURSOR statement to fail.
The GET_TIME program also creates descriptive labels for time periods by converting the dimension values into more descriptive text. This is done entirely in the OLAP DML, using the DATEFORMAT option and the CONVERT function.
The following table will give you an idea of the various ways date data can be formatted after being fetched into the analytic workspace.
The object definitions are listed below.
DEFINE TIME DIMENSION WEEK ENDING SATURDAY LD Time dimension DEFINE NEWTIME DIMENSION WEEK ENDING SATURDAY LD Intermediate Time dimension DEFINE LABELS.T VARIABLE TEXT <TIME> LD Time descriptions
The GET_TIME program is listed below.
DEFINE GET_TIME PROGRAM LD Get time periods PROGRAM arg start_time date arg end_time date push dateformat trap on error " Connect to database if communications " have not been established already. . . . " Declare a cursor named ADDTIME sql declare addtime cursor for - select Timestamp from shipments - where Timestamp between :(start_time) and :end_time if sqlcode ne 0 then signal err1 'Declare cursor failed.' " Open the cursor sql open addtime if sqlcode ne 0 then signal err2 'Cursor open failed.' " Fetch time periods into dimension newtime sql fetch addtime loop into :append newtime if sqlcode ne 0 and sqlcode ne 100 then signal err3 'Fetch failed.' " Close the cursor sql close addtime if sqlcode ne 0 then signal err4 'Close cursor failed.' " Add new time periods to permanent TIME dimension maintain time merge newtime " Create time labels in format of January 1st, 1995 limit time to newtime dateformat = '<mtextl> <dtl>, <yyyy>' labels.t = convert(time, date) show 'Time dimension and labels maintained.' update goto cleanup ERROR: show 'Time dimension maintenance failed.' CLEANUP: " Free the cursor sql rollback pop dateformat maintain newtime delete all END
The GET_UNITS program fetches data into a variable named V.UNITS, which is dimensioned by the GEOG, PROD, and TIME dimensions. V.UNITS is already defined as an OLAP DML object. Notice that the SELECT portion of the DECLARE CURSOR statement uses an ORDER BY clause listing the column with the slowest-varying dimension values first.
This sort order fetches the data in basically the same order that it will be stored, as shown by the following table. The table shows the results of a SELECT statement that was issued using the interactive interface of a relational manager.
Store_ID Prod_ID Timestamp Units -------- --------- ---------------------- --------- G17 P10 Jan 7 1995 8:56AM 103 G19 P10 Jan 7 1995 9:02AM 54 G5 P10 Jan 7 1995 9:10AM 69 G17 P11 Jan 7 1995 9:14AM 74 G19 P11 Jan 7 1995 9:23AM 73 G5 P11 Jan 7 1995 9:49AM 197 . . . G51 P32 Oct 21 1995 4:57PM 205 G5 P33 Oct 21 1995 5:04PM 251 G51 P33 Oct 21 1995 5:09PM 257 G5 P9 Oct 21 1995 5:18PM 83 G51 P9 Oct 21 1995 5:22PM 105
The object definitions are listed below.
DEFINE V.UNITS VARIABLE SHORT <GEOG PROD TIME> LD Units sold DEFINE TIME DIMENSION WEEK ENDING SATURDAY LD Time dimension
The GET-UNITS program is listed below.
DEFINE GET_UNITS PROGRAM LD Fetch units sold data PROGRAM trap on error allstat " Connect to database if communications " have not been established already. . . . " Declare a cursor named GRABDATA sql declare grabdata cursor for - select Store_ID, Prod_ID, Timestamp, Units - from shipments, shipments_detail - where shipments.Order_No - = shipments_detail.Order_No - order by Timestamp, Prod_ID, Store_ID if sqlcode ne 0 then signal err1 'Declare cursor failed.' " Open the cursor sql open grabdata if sqlcode ne 0 then signal err2 'Open cursor failed.' " Fetch new values into V.UNITS variable. Use " dimensions to align data in V.UNITS. sql fetch grabdata loop into :geog, :prod, :time,:v.units if sqlcode ne 0 and sqlcode ne 100 then signal err3 'Fetch failed.' " Close the cursor sql close grabdata if sqlcode ne 0 then signal err4 'Close cursor failed.' update show 'V.UNITS variable populated.' goto cleanup ERROR: show 'V.UNITS data incomplete.' CLEANUP: " Free the cursor sql rollback END
The CREATE_PRODUCTS program defines a new table in the relational database and populates it with data from an analytic workspace. It uses PREPARE and EXECUTE statements so that the INSERT statement will not have to be recompiled for each dimension value.
The object definitions are listed below.
DEFINE PROD DIMENSION TEXT LD Products DEFINE LABELS.P VARIABLE TEXT <PROD> LD Descriptive product names DEFINE SUGGEST.P VARIABLE SHORT <PROD> LD Suggested price
The CREATE_PRODUCTS program is listed below.
DEFINE CREATE_PRODUCTS PROGRAM LD Create Products table PROGRAM trap on error " Connect to database if communications " have not been established already. . . . " Create the products table using appropriate " data types for the relational manager sql create table products - (Prod_IDtext data type, - Prod_Nametext data type, - Suggested_Pricedecimal data type) if sqlcode ne 0 then signal err1 'Create table failed.' " Prepare the INSERT statement with a statement name " of WRITE_PRODUCTS sql prepare write_products from insert into products - values(:prod, :labels.p, :suggest.p) if sqlcode ne 0 then signal err2 'Prepare table failed.' " Begin transaction, if appropriate . . . " Loop over PROD dimension to execute INSERT statement " for each product value in status for prod do sql execute write_products if sqlcode ne 0 then break doend if sqlcode ne 0 then signal err3 'Insert data failed.' " Save changes to the products table sql commit show 'Products table populated.' return ERROR: "Discard all changes to the database sql rollback sql drop table products show 'Products table discarded.' END
The UPDATE_PRODS program sets the status of the PROD dimension to a value specified when you run the program.
UPDATE_PRODS uses a FOR command to loop over the selected products. The SQL UPDATE command updates the SUGGEST.P column of the Products table with the values from an OLAP DML variable named SUGGEST.P.
To run the program, you would issue a command like the one shown here.
call update_prods ('last 5')
The object definitions are listed below.
DEFINE PROD DIMENSION TEXT LD Products DEFINE SUGGEST.P VARIABLE SHORT <PROD> LD Suggested price
The UPDATE_PRODS program is listed below.
DEFINE UPDATE_PRODS PROGRAM LD Update products table PROGRAM arg _prodlist text trap on error push prod limit prod to &_prodlist " Connect to database if communications " have not been established already. " Begin transaction, if appropriate . . . " Loop over products in status to update table for prod do sql update products set - Suggested_Price = :suggest.p - where Prod_ID = :prod if sqlcode ne 0 then break doend if sqlcode ne 0 then signal err 'Update data in table failed.' show 'Products table updated.' goto cleanup ERROR: "Discard all changes to the products table sql rollback show 'Product table was not updated.' CLEANUP: pop prod END
To improve performance, you should modify this program to use PREPARE and EXECUTE, as shown in the following program fragment.
sql prepare new_prices from update products - set Suggested_Price = :suggest.p - where Prod_ID = :prod . . . for prod do execute new_prices if sqlcode ne 0 then break doend
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|