Data Warehousing – OLAP



Stands for Online Analytical Processing. On the other hand, OLAP systems are purely built to support an organization’s decision support system. Organizations need large pile of data at their disposal before they take any decision which would improve the revenue over a period of time (for example).

The analytical system again consist of suite of applications, database (read data warehouse) and data integration layer for integration data from different source system. The analytic application should have a scalable and robust engine which can handle large sums of data and calculate aggregations, generates various cutting edge charts, delivers scheduled reports to end users concurrently, provide flexibility to users to download reports and charts to PPT/PDF/Excel etc.

Types of OLAP servers:-

  • Relational OLAP (ROLAP)
  • Multidimensional OLAP (MOLAP)
  • Hybrid OLAP (HOLAP)

OLAP Operations:-

OLAP servers consist of multidimensional data. We can perform different types of operation on this data.

  • Slice and Dice.
  • Roll up
  • Drill down
  • Pivot (rotate)

Slice:-

Slice operation performs a selection on one dimension of the given cube, thus creates subset a cube. Below example depicts how slice operation works- 

Slice operation in OLAP

Dice:-

Dice operation performs a selection on two or more dimension from a given cube and creates a sub-cube. Below example depicts how slice operation works- 

Dice operation in OLAP

Roll-up:-

The roll-up operation (also called drill-up or aggregation operation) performs aggregation on a data cube, either by climbing up a concept hierarchy for a dimension or by climbing down a concept hierarchy, i.e. dimension reduction. Below example depicts how slice operation works- 

Rollup operation in OLAP

Drill-down:-

Drill-down is the reverse operation of roll-up. It allows users to navigate among different levels of data i.e. most summarized (up) to most details (down). Below example depicts how slice operation works- 

drill down in olap

Pivot:-

Pivot also known as rotation changes the dimensional rotation of the cube, i.e. rotates the axes to view the data from different perspectives. The below cubes shows 2D representation of Pivot-

pivot operation in olap