Fetching Data into an Analytic Workspace

Pretesting SELECT statements

Before writing the code to fetch data into an analytic workspace, you should write down SELECT statements that you think will retrieve the data you want to fetch. When possible, use an interactive interface such as SQL*Plus or SQL Worksheet to test these SQL statements and make sure that they produce the results you expect. Afterward, you can modify these SELECT statements for use in your OLAP DML programs.

Tip: Use ORDER BY clauses where necessary so that the data for multidimensional variables is fetched with the slowest-varying dimension values first. Use GROUPBY clauses to perform simple aggregation of the data to the level at which it becomes useful for data analysis.

Definition: Cursor

You cannot issue a SELECT statement in the OLAP DML. Instead, you must define a cursor using embedded SQL in an OLAP DML program. In the context of a query, a cursor can be thought of as simply a row marker in a table of data resulting from a query. Instead of receiving the results of a query all at once, your program receives the results row by row using the cursor.

Using cursors

You must declare and open a cursor from within a single OLAP DML program. Then you can fetch the data and close the cursor either in the same program or a different program.

Summary of cursor support

Several special SQL statements are used to define and use cursors.

The following commands are associated with cursors. Each of them is discussed in detail in a separate topic.

OLAP DML Command	Description
SQL DECLARE CURSOR	Contains a SELECT statement to identify the data to be retrieved and associates this selection with the name of a cursor.
SQL OPEN	Opens the cursor so it can be used in a FETCH statement.
SQL FETCH	Retrieves the data associated with the cursor and stores it in one or more OLAP DML objects.
SQL CLOSE	Closes the cursor so that the program can no longer access results through it.