Home » Data Warehouse

What is OLAP (Online Analytical Processing) Cube, Types and Concepts in Data Warehousing

What is Online Analytical Processing ?

OLAP stands for On-Line Analytical Processing. The key feature of OLAP is "Multidimensional." In other words, the ability to analyze metrics in different dimensions such as time, geography, gender, product, etc. For example, sales for the company are up. What region is most responsible for this increase? Which store in this region is most responsible for the increase? What particular product category or categories contributed the most to the increase? Answering these types of questions in order means that you are performing an OLAP analysis.

In this tutorial, you will learn:

What is a OLAP Cube ?

OLAP Cube

An OLAP Cube is a data structure that allows fast analysis of data according to the multiple Dimensions that define a business problem. A multidimensional cube for reporting sales might be, for example, composed of 7 Dimensions: Salesperson, Sales Amount, Region, Product, Region, Month, Year.

Basic analytical operations of OLAP

There are four types of OLAP Operations:

  1. Roll-up
  2. Drill-down
  3. Slice and dice
  4. Pivot (rotate)

1. Roll-Up:

Roll-up is also known as "consolidation" or "aggregation." The Roll-up operation can be performed in 2 ways

  1. Reducing dimensions
  2. Climbing up concept hierarchy. Concept hierarchy is a system of grouping things based on their order or level.
OLAP Rollup

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

2. Drill-down:

In drill-down data is fragmented into smaller parts. It is the opposite of the rollup process. It can be done via

  • Moving down the concept hierarchy
  • Increasing a dimension
OLAP Drilldown

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

3. Slice and dice:

Slice 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 Time = “Q1”

OLAP Slicing

Dice 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 following dimensions with criteria:

  • Location=Delhi or Kolkata
  • Time = Q1 or Q2
  • Item = Car or Bus
OLAP Dice

4. Pivot:

Pivot 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

OLAP Pivot

Types of OLAP systems

  1. ROLAP
  2. MOLAP
  3. HOLAP

ROLAP

This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement

Advantages of ROLAP Cube

  • High data efficiency. It offers high data efficiency because query performance and access language are optimized particularly for the multidimensional data analysis.
  • Scalability. This type of OLAP system offers scalability for managing large volumes of data, and even when the data is steadily increasing.

Disadvantages of ROLAP Cube

  • Demand for higher resources: ROLAP needs high utilization of manpower, software, and hardware resources.
  • Aggregately data limitations. ROLAP tools use SQL for all calculation of aggregate data. However, there are no set limits to the for handling computations.
  • Slow query performance. Query performance in this model is slow when compared with MOLAP

MOLAP

MOLAP uses array-based multidimensional storage engines to display multidimensional views of data. Basically, they use an OLAP cube

Hybrid OLAP

HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data

  1. Aggregated or computed data is stored in a multidimensional OLAP cube
  2. Detailed information is stored in a relational database.

Advantages of OLAP

  • OLAP is a platform for all type of business includes planning, budgeting, reporting, and analysis.
  • Information and calculations are consistent in an OLAP cube. This is a crucial benefit.
  • Quickly create and analyze "What if" scenarios
  • Easily search OLAP database for broad or specific terms.
  • OLAP provides the building blocks for business modeling tools, Data mining tools, performance reporting tools.
  • Allows users to do slice and dice cube data all by various dimensions, measures, and filters.
  • It is good for analyzing time series.
  • Finding some clusters and outliers is easy with OLAP.
  • It is a powerful visualization online analytical process system which provides faster response times

Disadvantages of OLAP

  • OLAP requires organizing data into a star or snowflake schema. These schemas are complicated to implement and administer
  • You cannot have large number of dimensions in a single OLAP cube
  • Transactional data cannot be accessed with OLAP system.
  • Any modification in an OLAP cube needs a full update of the cube. This is a time-consuming process




SQL.info