OLAP Operations

Data Warehouse Tutorial



[fblike]

OLAP Operations : Introduction

  • OLAP Operations(Online Analytical Processing Operations) refers to the act of performing actions on an OLAP system. The various OLAP operations are adopted in order to attain the goal of an OLAP system i.e. Decision making & Analytics from historical data.
  • As Online Analytical Processing Operations is a multidimensional data model, these operations are performed over the data cubes. The concept of data cube is used because to represent the data in three dimensional space and so that analysts can turn around the data cube along its dimensions for all its possible space combinations to determine the every aspect of available data.
  • There are five OLAP operations that can be applied over the data cube. These are:

 

This image describes the various OLAP operations that are used in data warehouse.

OLAP Operations : Types

 

1. Roll Up OLAP Operations

  • Roll up is a dimension reduction technique on a given data cube. Dimension reduction can be done by combining similar dimension across any axis of the data cube using notion of concept hierarchy.
  • Consider the example of sales of four companies C1, C2, C3 &C4 per quarter on the basis of product category(Men’s, Women’s, Electronics &Home). Out of the four companies, two companies are form India(C1 & C2) and two are from America(C3 & C4). So, if we want to perform the Roll-Up operation on the given data cube, we can do it by combining Indian companies together and American companies together.  

 

This image describes the roll up operation through an example.

Roll-Up : OLAP Operations

 

2. Drill Down OLAP Operations

  • Drill down is a dimension expansion technique that can be applied on the data cube. Dimension expansion means, adding new dimension or expanding existing dimensions across any axis of the data cube using the notion of concept hierarchy.
  • Consider the example of sales of four companies C1, C2, C3 &C4 per quarter on the basis of product category(Men’s, Women’s, Electronics &Home). Out of the four companies, two companies are form India(C1 & C2) and two are from America(C3 & C4). So, if we want to perform the Drill down operation on given data cube, we can do it by expanding the available existing shopping categories such as :
    • Men’s : Clothing & Footwear.
    • Women’s : Clothing & Footwear.
    • Home : Appliances & Decor.
    • Electronics : Mobile & Camera.

 

This image describes the drill down operation in data warehouse through an example.

Drill Down : OLAP Operations

 

3. Slice OLAP Operations

  • Performing slice operation, a single dimension of the data cube can be extracted out to form a new cube. Similarly more than one dimension can also be extracted out from same data cube as required.
  • Consider the example of sales of four companies C1, C2, C3 &C4 per quarter on the basis of product category(Men’s, Women’s, Electronics &Home). Out of the four companies, two companies are form India(C1 & C2) and two are from America(C3 & C4). A dimension (Shopping, Sales Per Quarter) can be sliced from the data cube through the technique of slice operation.

 

This image describes the slice operation in data warehouse through an example.

Slice : OLAP Operations

 

4. Dice OLAP Operations

  • Through Dice operation, a sub cube can be generated by selecting two or more than two dimension from the data cube.
  • Consider the example of sales of four companies C1, C2, C3 &C4 per quarter on the basis of product category(Men’s, Women’s, Electronics &Home). Out of the four companies, two companies are form India(C1 & C2) and two are from America(C3 & C4). So, if we want to perform Dice operation on the given data cube, we can do it by selecting any two parameters across all the three dimensions i.e. Companies(C1, C2), Category(Home, Appliances) & Sales(Q1,Q2).

 

This image describes the dice operation in data warehouse through an example.

Dice : OLAP Operations

 

5. Pivot OLAP Operations

  • Rotation of data cube’s orientation to check for its other data views is known as pivot operation. Pivot operation provides alternate views of data available to the users.
  • Consider the example of sales of four companies C1, C2, C3 &C4 per quarter on the basis of product category(Men’s, Women’s, Electronics &Home). Out of the four companies, two companies are form India(C1 & C2) and two are from America(C3 & C4). So, if we want to perform Pivot operation, we can do it by rotating any one the dimension of the data cube.
This image describes the pivot operation in data warehouse through an example.

Pivot : OLAP Operations