Online Analytical Processing

[admin]

Online analytical processing (OLAP) is an approach to quickly answer multi-dimensional analytical queries. Speed is achieved by pre-calculating consolidations (also called aggregation) and calculating elements (also called measures) before they are needed by analysts. When a query is run, much of the data is available ‘ready-made’ and this radically reduces the time required to search the database and return the result.

OLAP consists of numeric facts called measures which are categorised by dimensions. The cube metadata is typically created from a star schema or snowflake schema of tables in a relational database. Measures are derived from the records in the fact table and dimensions are derived from the dimension tables. Each measure can be thought of as having a set of labels, or metadata associated with it. A dimension is what describes these labels; it provides information about the measure.

The output of an OLAP query is typically displayed in a matrix (or pivot) format. The dimensions form the rows and columns of the matrix and the measures form the values.

An example from Wikipedia:

A simple example would be a cube that contains a store's sales as a measure, and Date/Time as a dimension. Each Sale has a Date/Time label that describes more about that sale.

Any number of dimensions can be added to the structure such as Store, Cashier, or Customer by adding a column to the fact table. This allows an analyst to view the measures along any combination of the dimensions:

See attachment 1 below.

Common applications of OLAP are in business reporting for business process management (BPM), budgeting and forecasting, financial reporting, management reporting, marketing and sales.

OLAP is a category of business intelligence, other categories are relational reporting and data mining.

Many types of OLAP exist:

• DOLAP – Desktop OLAP.
• HOLAP – Hybrid OLAP. Combines MOLAP and ROLAP. It is where a database will divide data between relational and specialised storage.
• MOLAP – the original type of OLAP. It stores data in an optimised multi-dimensional array storage, rather than in a relational database. Therefore it requires the pre-computation and storage of information in the cube. Microsoft Analysis Services, Hyperion, Cognos and Business Objects are all examples of MOLAP applications used within organisations.
• ROLAP – works directly with relational databases. The base data and the dimension tables are stored as relational tables and new tables are created to hold the aggregated information.
• RTOLAP – Real-Time OLAP. A RTOLAP Server will calculate values on the fly, when they are required. The essential characteristic of RTOLAP system is that it holds all the data in RAM. TM1 is an example of a RTOLAP application.
• WOLAP – Web-based OLAP.