Data Truth or Dare: OLAP

December 09, 2013

Data Truth or Dare: OLAP – Acronym for On-Line Application Programming OR On-Line Analytical Processing?

Welcome to another exciting game of Data Truth or Dare. This week’s question is about the term OLAP. What does it stand for: On-Line Application Programming OR On-Line Analytical Processing?

Data Truth: OLAP databases optimize business analytics. An acronym for On-Line Analytical Processing, an OLAP database is an approach to building databases that are optimized for analytical purposes.

With OLAP, data can easily be aggregated on one of more dimensions. It enables a deeper analysis of the data thanks to the ability to slice and dice. In OLAP, dimensions are often organized into hierarchies that facilitate drill-downs to detailed views or roll up to higher level values.

The term OLAP was created as a slight modification of the traditional database term OLTP (Online Transaction Processing.

The core of the OLAP is the system’s cube. This consists of numeric facts (measures), categorized by dimensions. The measures are placed at the intersections of the cube. Think of a typical interface to manipulate an OLAP cube as a matrix interface such as the Pivot tables in a spreadsheet that perform projection operations along the dimensions, such as aggregation or averaging.

The metadata is generated from either a star 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. Stay with me now.

Think of the metadata as the labels for each measure. The dimension, then, is what describes these labels, providing information about the measure.

OLAP databases are typically categorized one of three ways:

  1. Relational: Works directly with relational databases
  2. Multidimensional: stores data in an optimized multi-dimensional array and requires pre-computation and storage. 
  3. Hybrid: Divides data between relational and specialized storage

Interested in learning more? Have a look at the free TARGIT ebook, the BI Dictionary, to hone your skills for the next Data Truth or Dare.