OLAP Operations

OLAP (Online Analytical Processing) is a set of tools and software techniques that allow users to analyze information from multiple database systems at the same time. It allows us to gain insight into the data through special data structures known as OLAP cubes and operations such as drill-down, roll-up, slicing, dicing, and pivot. This article provides an overview of `OLAP and its uses.

What is OLAP?

OLAP or Online Analytical Processing is a category of software that allows users to extract and examine business data from different points of view. It makes use of pre-calculated and pre-aggregated data from multiple databases to improve the data analysis process. OLAP databases are divided into several data structures known as OLAP cubes.

OLAP Cube

The OLAP cube or Hypercube is a special kind of data structure that is optimized for very quick multidimensional data analysis and storage. It is a screenshot of data at a specific point in time. As seen in the figure, using certain OLAP operations, a user can request a specified view of the hypercube. Hence, OLAP cubes allow users to perform multidimensional analytical querying on the data.

Types of OLAP Servers

There exist mainly three types of OLAP systems: Relational OLAP (ROLAP): These systems work directly with relational databases and use complex SQL queries to retrieve information from the database. It can handle large volumes of data but provides slower data processing.

Multidimensional OLAP (MOLAP): MOLAP is also known as the classic form of OLAP. It uses an optimized multi-dimensional array storage system for data storage. It makes use of positional techniques to access the data physically stored in multidimensional arrays.

Hybrid OLAP (HOLAP): It uses a best-of-both-worlds approach and is a combination of ROLAP and MOLAP. It provides the high scalability feature of ROLAP systems along with the fast computation functionality of MOLAP systems.

OLAP Operations

OLAP provides various operations to gain insights from the data stored in multidimensional hypercubes. These operations include:

Drill Down

Drill down operation allows a user to zoom in on the data cube i.e., the less detailed data is converted into highly detailed data. It can be implemented by either stepping down a concept hierarchy for a dimension or adding additional dimensions to the hypercube.

Example: Consider a cube that represents the annual sales (4 Quarters: Q1, Q2, Q3, Q4) of various kinds of clothes (Shirt, Pant, Shorts, Tees) of a company in 4 cities (Delhi, Mumbai, Las Vegas, New York) as shown below:

Here, the drill-down operation is applied on the time dimension and the quarter Q1 is drilled down to January, February, and March. Hence, by applying the drill-down operation, we can move down from quarterly sales in a year to monthly or weekly records.

Roll up

It is the opposite of the drill-down operation and is also known as a drill-up or aggregation operation. It is a dimension-reduction technique that performs aggregation on a data cube. It makes the data less detailed and it can be performed by combining similar dimensions across any axis.

Example: Considering the above-mentioned clothing company sales example: Here, we are performing the Roll-up operation on the given data cube by combining and categorizing the sales based on the countries instead of cities.

Dice

Dice operation is used to generate a new sub-cube from the existing hypercube. It selects two or more dimensions from the hypercube to generate a new sub-cube for the given data.

Example: Considering our clothing company sales example: Here, we are using the dice operation to retrieve the sales done by the company in the first half of the year i.e., the sales in the first two quarters.

Slice

Slice operation is used to select a single dimension from the given cube to generate a new sub-cube. It represents the information from another point of view.

Example: Considering our clothing company sales example:

Here, the sales done by the company during the first quarter are retrieved by performing the slice operation on the given hypercube.

Pivot

It is used to provide an alternate view of the data available to the users. It is also known as Rotate operation as it rotates the cube’s orientation to view the data from different perspectives.

Example: Considering our clothing company sales example: Here, we are using the Pivot operation to view the sub-cube from a different perspective.