Big Data - Data Warehouse - OLAP

Hi guys ! It’s me again :laughing: . As scheduled, today we will go together in a series on data warehosue. In the previous section, we learned about the architecture of data warehouse. In this section, we will answer the question what OLAP is?
Note :Those who have not read the previous section can review follow this link:
Big Data - Data Warehouse- Architecture

Yooo! :love_you_gesture: :love_you_gesture: :love_you_gesture:

First answer the question: What is OLAP?

  1. OLAP stands for On-Line Analytical Processing.
  2. OLAP is a powerful technology

Next we answer the question, what benefits does OLAP bring?
Some of the benefits that OLAP offers:

  1. Respond much faster
  2. Gives a multi-dimensional view of the data
  3. Trustworthy Data and Calculations

We will answer a very important question, How OLAP works?

Fundamentally, It pre-calculates most of the queries that are typically very hard to execute over tabular databases, namely aggregation, joining, and grouping. …

That is why the response time of OLAP is very fast :))
That calculation usually takes place overnight, so the next morning everything is ready :innocent: :innocent: :innocent:

Now, let’s focus !

To dive into this section, we will explore a basic concept : OLAP Cube - the core of OLAP techonology

Basically, the OLAP cube is a data structure optimized for very quick data analysis.

In the past, we have learned data like 2-dimensional, 3-dimensional array … similarly, OLAP contains multidimensional data, with data usually obtained from a different and unrelated source. Hence the OLAP cube is also known as a hypercube

For ease of visualization, let’s take a look at a simple hypercube
OLAP_cube

We can see there are 3 dimensions

  • Location
  • Time
  • Items

Note:

  1. The OLAP Cube consists of numeric facts called measures which are categorized by dimensions.
  2. In fact, the dimensionality of an OLAP cube could be much larger than what you see here

So, if you have mastered this simple concept, let’s learn how to manipulate this hyper cube together :ok_hand:

A picture of more than a thousand words

OLAP_operation

1. Roll Up

It performs aggregation on the OLAP cube.

It can be done by:

  • Climbing up in the concept hierarchy
  • Reducing the dimensions

We can see, in the location dimension, we climb from city to country.

2. Drill-down

In contrast to the roll-up, in drill-down operation, the less detailed data is converted into highly detailed data.
Drill-down is like zooming-in on the data cube.

It can be done by:

  • Moving down in the concept hierarchy
  • Adding a new dimension

3. Slice

One dimension is selected, and a new sub-cube is created.

4. Dice

Dice selects two or more dimensions from a given cube and provides a new sub-cube.

5. 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.

Very easy to understand, right?
Finally, let’s learn about ROLAP, MOLAP and HOLAP and when to use them :smiley:

An overview picture

1. ROLAP

  • ROLAP is an extended RDBMS along with multidimensional data mapping to perform the standard relational operation.
  • Query response is frequently slower with ROLAP storage than with the MOLAP or HOLAP storage mode.
  • Processing time is also frequently slower with ROLAP.
  • Scalability

2. MOLAP

  • Uses array-based multidimensional storage engines for multidimensional views of data.
  • Highly optimize to maximize query performance.
  • Query response times can be reduced substantially by using aggregations

3. HOLAP

  • Hybrid OLAP is a combination of both ROLAP and MOLAP.
  • Offers higher scalability of ROLAP and faster computation of MOLAP.
  • Very complicated

4. Use case
Obviously, the above OLAP models have their own strengths and weaknesses, so the choice depends on many factors.

There are a few things we can learn to apply:

If a user wants to store large data then ROLAP is recommended. Whereas MOLAP is suggested when a user wants faster processing.

So that’s all for this section ! :smiley:

This section has references from the following articles:

ROLAP and MOLAP

Data Warehouse Tutorial

See you soon ! :love_you_gesture: :love_you_gesture: :love_you_gesture: :love_you_gesture:

3 Likes