Basic Categories of OLAP DML Commands

Following are descriptions of some of the basic categories of OLAP DML commands and functions.

Aggregation

The OLAP DML supports a variety of aggregation methods including first, last, average, weighted average, and sum. In a multidimensional data object, the aggregation method can vary by dimension. Some of the data can be aggregated and stored, while other data is aggregated at runtime. A technique called "skip level" aggregation pre-aggregates every other level in a dimension hierarchy. The DBA can choose whatever method seems appropriate: by level, individual member, member attribute, time range, data value, or other criteria.

Allocation

Allocations are a critical part of planning applications. Given a target for the organization -- whether for sales quota, product growth, salary, or equipment -- managers must allocate that target among its contributors. Some of the key features of the allocation system are:

• Support for hierarchies so the data is distributed based on parentage.
• Support for arbitrary selections so that data is distributed among selected members, regardless of parentage or in the absence of a hierarchy.
• A variety of allocation methods, including:
  • Copy methods (hierarchical copy, minimum, maximum, first, last)
  • Even distribution (even, hierarchical even)
  • Proportional distribution (including weighted distributions and user-defined multidimensional functions.
• Cell-level locking prevents certain cells from being overwritten by the allocation. This feature is used when some values for the planning period are known.
• Logging records how far an allocation has progressed and whether any errors have occurred.

Data Selection

Data selection within the analytic workspace is persistent throughout a session, which is a feature that supports the iterative nature of analytic queries. Users can select data in multiple steps, with each step refining the previous query. The OLAP DML provides data selection methods that are specifically designed for multidimensional data, such as hierarchical relations, levels of aggregation, attributes, time series functions, and data values.

Data Exchange

SQL statements can be embedded in the OLAP DML, which allows applications to select data from SQL tables and write data back to them. This can be done at runtime or as a data maintenance procedure. Access to SQL tables is controlled by the privileges and roles granted to the user's database ID.

The following embedded SQL statements define a cursor and fetch data from a relational table named products into a workspace dimension named prod and a measure named prod_label.

SQL DECLARE highprice CURSOR FOR SELECT prod_id, prod_name -
     FROM products WHERE suggested_price > :set_price
SQL OPEN highprice
SQL FETCH highprice LOOP INTO :prod, :prod_label

File Reading and Writing

Data can be read from flat files or spreadsheets into multidimensional objects. This is typically done as a data maintenance procedure. Access to external files is controlled by BFILE security. DBAs can set up aliases for directories and control which users and groups can use those aliases, as described in "Controlling Access to External Files". The security system does not allow users to access directories without an alias.

The following program copies data from a file named unit and stores it in a dimensions named month and productid and variables named productname and units.sold. The DBA previously created a directory alias named mydat.

DEFINE read.product PROGRAM
PROGRAM
VARIABLE fi INT                  "Define a local integer variable
fi = FILEOPEN('mydat/unit' READ) "Store a file handle in the variable
FILEREAD fi COLUMN 1 WIDTH 5 month -
     COLUMN 6 WIDTH 6 productid -
     COLUMN 12 WIDTH 30 productname -
     COLUMN 44 WIDTH 22 units.sold
FILECLOSE fi
END

The next example creates a file named custom.eif as a private data store that contains the data and definitions for a custom measure named mysales. The user can import mysales during another session.

EXPORT mysales TO EIF FILE 'userdat/custom.eif' DATA DFNS

Financial Operations

The financial functions include interest rate calculations, depreciation, and payment schedules, similar to those provided in spreadsheets.

For example, the FPMTSCHED function calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods. The following call to FPMTSCHED calculates 36 payments based on the amounts listed in the loans variable, at the interest rates listed in the rates variable, for the month dimension of these variables.

FPMTSCHED(loans, rates, 36, month)

Forecasts and Regressions

The OLAP DML offers the most sophisticated and up-to-date forecasting and regression tools of Roadmap Geneva Forecasting, including simple linear regressions, non-linear regression methods, single exponential smoothing, double exponential smoothing, and the Holt-Winters method.

For example, the following FORECAST command uses the EXPONENTIAL method to forecast sales for the next 12 months based on historical data stored in the sales measure. It stores the results of the calculation in a second measure named fcst.sales.

FORECAST LENGTH 12 METHOD EXPONENTIAL FCNAME fcst.sales TIME month sales

Models

A model is a set of interrelated equations. These are some of the modeling features supported by the OLAP DML:

• You can perform calculations for individual dimension members following unique calculation rules.
• Oracle OLAP determines the order of the calculations, so you can list them in any order without concern for dependencies.
• Oracle OLAP solves simultaneous equations.

You can assign results either to a variable or to a dimension member. Dimension-based equations provide flexibility; since you do not need to specify the modeling variable until you solve a model, you can run the same model with any other measure with the same dimension. For example, you could run the same model on budget and actual, which both have a line dimension.

The following is an example of a modeling program.

'cost of goods' = 'raw materials'+labor+'fixed overhead'
'fixed overhead' = 'capital equipment'+'building costs'
'building costs' = 'building depreciation'+electric+heat+maintenance
'labor' = salary+benefits
'capital equipment' = 'equipment maintenance'+'equipment depreciation'

Numeric Computations

Functions are available to perform a wide variety of computations (such as sine, cosine, square root, minimum, and maximum) and data type conversions.

For example, the CEIL function returns the smallest whole number greater than or equal to a specified number. The function call

CEIL(-6.457)

returns a value of -6.

Statistical Operations

Statistical operations include standard deviation, rank, and correlation. For example, the STDDEV function calculates the standard deviation. The function call

STDDEV(units month)

returns the standard deviation of values in the units measure for all months that are currently selected.

Text Manipulation

The OLAP DML provides support for manipulating both single- and multibyte character sets, with functions for concatenating strings, locating a string within a larger body of text, inserting a string, and so forth.

For example, the EXTCHARS function extracts a portion of text. The function call

EXTCHARS('lastname,firstname', 1,8)

extracts the first 8 characters, which contains the characters

lastname

Time Series Manipulation

The time series functions perform operations such as lead, lag, and moving average. For example, the MOVINGTOTAL function calculates a series of totals over time. The following example returns a 3-month total on the sales measure for all currently selected months.

MOVINGTOTAL(sales, -2, 0, 1, month)