Fetching Data From Relational Tables - OLAP

You can embed SQL statements in OLAP DML programs using the OLAP DML

SQL command. SQL sql_statement

When formatting a SQL statement that is an argument to the OLAP DML, be sure to use single quotes (') wherever you need quotes. In the OLAP DML, a double quote (") indicates the beginning of a comment.

OLAP DML Support for SQL

You can use almost any SQL statement that is supported by Oracle in the OLAP DML SQL command. You can use SELECT to copy data from relational tables into analytic workspace objects. You can use the INSERT command to copy data from analytic workspace objects into relational tables.

The following Oracle SQL extensions are also supported:

  • The FOR UPDATE clause in the SELECT statement of a cursor declaration, so that you can update or delete data associated with the cursor
  • The WHERE CURRENT OF cursor clause in UPDATE and DELETE statements for interactive modifications to a table
  • Stored procedures and triggers

COMMIT and ROLLBACK are ignored as arguments to the SQL command. To commit your changes, issue the OLAP DML UPDATE and COMMIT commands. You cannot roll back using the OLAP DML.

Most SQL commands are submitted directly to the SQL command processor; however, a small number are first processed in the OLAP DML, and their syntax may be slightly different from standard SQL. Table describes the OLAP DML commands that support embedded SQL.

OLAP DML Commands for Embedded SQL

OLAP  DML  Commands for Embedded SQL

Process: Copying Data From Relational Tables Into Analytic Workspace Objects

Using the OLAP DML, you can populate a standard form analytic workspace from relational tables by taking the following steps:

  1. Define the analytic workspace objects that will hold the relational table data.
    Follow the steps listed in "How to Manually Create a Standard Form Analytic Workspace". Then browse the analytic workspace to identify the objects you need to populate.
  2. Write an OLAP DML program for each dimension. Compile and run the programs.
    Read the following instructions in "Fetching Data From Relational Tables".
  3. Write an OLAP DML program for each cube. Compile and run the programs.

Fetching Dimensions Members From Tables

There are several strategies for fetching dimension members. The best practice is to fetch just the dimension members first, and fetch their attributes as a separate step. For Time members, the best practice is to fetch one level at a time, making a separate pass through the source table for each level. This practice enables you to fetch the Time members in the correct order so that they do not need to be sorted afterward.

However, the simplest method, and the one shown here, populates dimension members at all levels, and all of the objects that support hierarchies, at the same time. Before using this method, be sure that SEGWIDTH is set correctly, as discussed in "Setting the Segment Size". Example is a template that you can use for fetching dimensions in one pass. The program does the following:

  • Reads the level columns one at a time, with their attribute columns, beginning with the top level (or most aggregate) and concluding with the base level. The syntax supports one hierarchy.
    Because the parent relation is being populated at the same time, the parents must be added to the workspace dimension before their children. Otherwise, an error will occur, because a relation only accepts as legitimate values the members of a particular dimension. This is not an issue if you load all dimension members first.
  • Populates a Boolean member_inhier variable manually. The n shown in the syntax is either a 1 (for yes) or a 0 (for no).
  • Populates the member_levelrel relation manually with the appropriate level name for the column. Level names must exactly match the members of the levellist dimension.
  • Populates the member_parentrel relation with the parent dimension member from the appropriate column.
  • Includes commands for handling errors, which are omitted from other examples so they are easier to read.

Template for Fetching Dimension Members

Sorting Dimension Members

When you fetch dimension members at all levels in a single pass through the source table, they are mixed together in the target workspace dimension. For most dimensions, the order does not affect processing, although you can sort the members by level if you wish.

However, it is very important for Time dimension members to be ordered chronologically within levels so that the Time dimension supports time series analysis. Functions such as LEAD, LAG, and MOVINGAVERAGE use the relative position of Time members in their calculations. For example, LAG returns the dimension member that is a specified number of values before the current member. If the time periods are not in chronological order, the returned value is meaningless. Your analytic workspace will perform better if you load the dimensions in the correct order instead of sorting them afterward. Example contains an OLAP DML program template for sorting the Time dimension. It does the following:

  • Defines a valueset in which to hold the sorted values.
  • Sorts the Time dimension by level first, then by end date within level.
  • Stores the sorted values in the valueset.
  • Reorders the Time dimension members.

Template for Sorting the Time Dimension

Fetching Measures From Tables

To fetch data from relational tables, you must write a program that defines a SQL cursor with the selection of data that you want to fetch, then retrieves the data into the analytic workspace objects that you previously created. Example is a template that you can use for fetching all of the measures for a particular cube. It does the following:

  • Identifies a key column with the members of each dimension. When the data is fetched into a variable, the dimensions are limited to the appropriate cell by these values.
  • Orders the source data to match the target variable.
    The ORDER BY clause in the SELECT statement is the reverse of the dimension list for the variable. If a variable is dimensioned by <Product Geography Time> so that Product is the fastest varying and Time is the slowest, then the ORDER BY clause sorts the rows by Time, Geography, and Product. This ordering speeds the data load.
  • Requires that values in the key columns match existing members in the target workspace dimensions.
    A required match prevents new dimension members from being created without their level, parentage, attributes, and so forth.

Template for Fetching Measures

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics