Describe any five types of OLAP operations.

 Online Analytical Processing Server (OLAP) is based on the multidimensional data model. It allows managers, and analysts to get an insight into the information through fast, consistent, and interactive access to information. Since OLAP servers are based on a multidimensional view of data, we will discuss OLAP operations in multidimensional data. Here is the list of OLAP operations:

• Roll-up
• Drill-down
• Slice and
• dice
• Pivot (rotate)

Roll-Up

• The roll-up operation (also known as drill-up or aggregation operation) performs aggregation on a data cube, by climbing up concept hierarchies, i.e., dimension reduction. 

• Roll-up is like zooming out on the data cubes. The figure shows the result of roll-up operations performed on the dimension location. The hierarchy for the location is defined as the Order Street, city, province, or state, country. 

• The roll-up operation aggregates the data by ascending the location hierarchy from the level of the city to the level of province. When a roll-up is performed by dimensions reduction, one or more dimensions are removed from the cube. 

• For example, consider a sales data cube having two dimensions, location and time. Roll-up may be performed by removing, the time dimensions, appearing in an aggregation of the total sales by location, relatively than by location and by time. The following diagram illustrates how roll-up works when the sales data cube is rolled-up from cities to province:

Drill-Down

The drill-down operation (also called roll-down) is the reverse operation of roll-up. Drill-down is like zooming in on the data cube. It navigates from less detailed records to more detailed data. Drill-down can be performed by either stepping down a concept hierarchy for a dimension or adding additional dimensions. The following diagram illustrates how Drill-down works when drill-down on time from quarter to month:

Slice

The slice operation selects one particular dimension from a given cube and provides a new sub-cube. Consider the following diagram that shows how slice works when sliced for first quarter i.e., Q1.

Pivot

The pivot operation is also known as rotation. It rotates the data axes in view in order to provide an alternative presentation of data. Consider the following diagram that shows the pivot operation between location and product dimension.

Dice

Dice selects two or more dimensions from a given cube and provides a new sub-cube. Consider the following diagram that shows the dice operation.

 OLAP operations:

There are five basic analytical operations that can be performed on an OLAP cube:

1. Drill down: In drill-down operation, the less detailed data is converted into highly detailed data. It can be done by:

• Moving down in the concept hierarchy

• Adding a new dimension

In the cube given in the overview section, the drill-down operation is performed by moving down in the concept hierarchy of the Time dimension (Quarter -> Month).

2. Roll up: It is just the opposite of the drill-down operation. It performs aggregation on the OLAP cube. It can be done by:

• Climbing up in the concept hierarchy

• Reducing the dimensions

In the cube given in the overview section, the roll-up operation is performed by climbing up in the concept hierarchy of Location dimension (City -> Country).

3. Dice: It selects a sub-cube from the OLAP cube by selecting two or more dimensions. In the cube given in the overview section, a sub-cube is selected by selecting the following dimensions with criteria:

• Location = “Delhi” or “Kolkata”

• Time = “Q1” or “Q2”

• Item = “Car” or “Bus”

4. Slice: It selects a single dimension from the OLAP cube which results in a new sub-cube creation. In the cube given in the overview section, Slice is performed on the dimension.

5. Pivot: It is also known as rotation operation as it rotates the current view to get a new view of the representation. In the sub-cube obtained after the slice operation, performing pivot operation gives a new view of it.