|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Manipulating Multidimensional Data, 3 of 4
Following are descriptions of some of the basic categories of OLAP DML commands and functions.
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.
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:
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.
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
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
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
productid and variables named
units.sold. The DBA previously created a directory alias named
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.
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.
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
A model is a set of interrelated equations. These are some of the modeling features supported by the OLAP DML:
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
actual, which both have a
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'
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
returns a value of
Statistical operations include standard deviation, rank, and correlation. For example, the
STDDEV function calculates the standard deviation. The function call
returns the standard deviation of values in the
units measure for all months that are currently selected.
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
extracts the first 8 characters, which contains the characters
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.