René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

OLAP [Oracle]

OLAP stands for On Line Analytical Processing. That has two immediate consequences: the on line part requires the answers of queries to be fast, the analytical part is a hint that the queries itself are complex.

Relevant PL/SQL packages/interfaces

OLAP DML

OLAP DML is a language to manipulate analytic workspaces. It is used to
  • create analytic workspaces
  • define data containers
  • manipulate the data within the data containers

Time series functions

  • cumsum
  • lag
  • lagabspct
  • lagdif
  • lagpct
  • lead
  • movingaverage
  • movingmax
  • movingmin
  • movingtotal

Financal functions

  • fpmtsched

Statistical functions

  • stddev

Numerical functions

  • max

Textual functions

  • extchars

Analytic workspace

OLAP metadata catalog

The OLAP catalog is a repository of metadata that describes the functionality of a star schema or a snowflake schema. It is used in conjunction with OLAP.
There is an API can be used to create this metadata catolog. The API consists of PL/SQL packages whose name begin with CWM2.

Entities

The OLAP catalog manages the following entities:
  • dimensions
  • dimension attributes
  • hierarchies
  • levels
  • level attributes
  • measures
  • measure folders
  • cubes
For each of these entities, there is a seperate package (such as cwm2_olap_cube.
An entity is identified by an owner and a name. The name is a varchar2(30).

Examples

Example one