Support for Custom Measures - OLAP

A custom measure is calculated from one or more measures stored in the analytic workspace. Often, it is created by an analyst just for the duration of a session. However, a custom measure can also be saved as a permanent part of the analytic workspace.

These saved custom measures can either be solved at run-time or stored in variables. Run-time calculations do not require disk storage space and do not extend the processing time required for data maintenance. However, they may slow performance. You need to decide which measures to calculate on demand and which, if any, to store. The custom measures described in this chapter are calculated for a query.

Methods of Defining Custom Measures

Two PL/SQL packages support custom measures in an analytic workspace:

  • DBMS_AW_UTILITIES contains procedures for creating, updating, and deleting custom measures. This package operates only on the views created by the enabler for the BI Beans. The custom measures are stored in the predefined columns provided in these views for custom measures. You can define a custom measure to persist either for the duration of the session or permanently.
  • DBMS_AW contains various procedures to execute OLAP DML commands. Several of them can be used in SELECT statements to execute a calculation or data manipulation in the analytic workspace. The calculations are returned along with the rest of the result set. This type of custom measure exists only for the duration of the SELECT statement.

In addition, you can use the OLAP_TABLE function to define and access custom measures outside of the framework of standard form, as described in "Using OLAP_TABLE for Direct Access to Workspace Data".

Analytic Support for Custom Measures

Regardless of the method that you use to define a custom measure, you will express the calculation itself using the OLAP DML. Following are descriptions of the many functions and commands available for manipulating your data. In addition, you can perform inter-row calculations using operators for multiplication (*), division (/), addition (+), subtraction (-), and so forth.

Forecasts and Regressions

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

Time Series Manipulation

The time series functions perform operations such as lead, lag, and moving average. Table describes the time series functions, which can easily be incorporated into custom measures.

OLAP DML Time Series Functions

OLAP DML Time Series Functions

OLAP DML Time Series Functions

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)

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.

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 MAX function compares two expressions and returns the larger value. This function call

MAX(actual, forecast)

compares the ACTUAL and FORECAST measures and returns the larger values for all dimension members 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



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. The supported allocation methods include:

  • Copy methods (hierarchical copy, minimum, maximum, first, last)
  • Even distribution (even, hierarchical even)
  • Proportional distribution (including weighted distributions and user-defined multidimensional functions)


Aggregation is a basic feature of analytic workspaces. When you create a standard form analytic workspace, it contains a default aggregation plan for each cube. Wizards in Analytic Workspace Manager enable you to identify stored aggregate levels quickly and easily.

The OLAP DML offers a broader range of aggregation methods than are currently available through Analytic Workspace Manager or PL/SQL procedures. You can choose whatever method seems appropriate: by level, individual member, member attribute, time range, data value, or other criteria.


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.

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

OLAP Topics