OLAP stands for Online Analytical Processing.

Online Analytical Processing (OLAP) is a category of data management and analysis

technology that enables users to interactively analyze multidimensional data from multiple
perspectives to support complex analytical and ad hoc queries with a rapid execution
time.

It’s a way of looking at large amounts of data from different angles to help people make
better business decisions.

Components of OLAP

1. Multidimensional Database (MDDB):

o Stores data in a cube-like format.
o Enables fast retrieval and aggregation across dimensions.
2. Dimensions:
o Independent perspectives or categories for analysis.
o Example: Time, Geography, Product, Customer.
3. Measures:
o Numeric values or metrics that are analyzed.
o Example: Sales, Revenue, Quantity Sold, Temperature.
4. Data Cube:
o Logical multidimensional structure that allows slicing, dicing, and aggregating
data.
o Pre-computed summaries make queries fast.
5. OLAP Engine:
o Software component that processes analytical queries, computes aggregates,
and manages cube data.

OLAP Operations

1. Slice

Query: “Show only sales from the North region.”

Action: Apply filter → Region = “North”

Date Product Sales

2025-10-01 Laptop 80,000

2025-10-02 Mobile 60,000

2. Dice

Query: “Show Laptop sales in North and South regions.”

Action: Apply multiple filters → Region = {North, South}, Product = Laptop
 Date Region Product Sales

2025-10-01 North Laptop 80,000

2025-10-01 South Laptop 70,000

3. Drill Down

Query: “See more details — from monthly to daily sales.”

Action: Expand from Month → Date in PivotTable.

Region Date Sales

North 2025-10-01 80,000

North 2025-10-02 60,000

4. Roll Up

Query: “Summarize daily sales to monthly totals.”

Action: Group by Month in PivotTable.

Month Total Sales

Oct-2025 3,75,000

5. Pivot (Rotate)

Query: “Swap Region and Product view.”

Action: Move Region to Columns, Product to Rows.

Product North South East

Laptop 80,000 70,000 90,000

Mobile 60,000 75,000 —

Types of OLAP (Online Analytical Processing)

OLAP systems are categorized based on how they store and process data for analysis.

There are three main types of OLAP systems:

 1. MOLAP – Multidimensional OLAP
2. ROLAP – Relational OLAP
3. HOLAP – Hybrid OLAP

1. MOLAP (Multidimensional OLAP)

Think of MOLAP as a ready-made cube that stores all summarized data in advance — like a
pre-packaged report that opens instantly.

Technical Definition:

MOLAP stores data in multidimensional cubes rather than relational databases.

Data is pre-computed and aggregated, which allows for very fast query performance.

Key Features:

 Data stored in optimized cube format.

 Pre-calculated summaries → fast retrieval.
 Excellent for trend analysis, forecasting, and dashboards.

Example:

A retail company builds a MOLAP cube to analyze sales by region, product, and month.
All summaries (monthly totals, region totals) are pre-computed, so users can explore results
instantly in Excel or Power BI.

Advantages:

✅ Very fast query response

✅ Pre-aggregated data = no need for runtime calculation
✅ Compact and easy to visualize

Disadvantages:

❌ Cube creation takes time

❌ Not suitable for huge raw data (storage limitations)
❌ Requires specialized cube design tools

Example Tools:

 Microsoft Analysis Services (SSAS – Multidimensional mode)

 IBM Cognos TM1
 Oracle Essbase

2. ROLAP (Relational OLAP)

ROLAP works directly on relational databases (like SQL tables) — it doesn’t store data in
cubes but runs queries live on the data.

Technical Definition:

ROLAP uses relational databases to store data and dynamically performs SQL queries to
generate summaries.
It relies on data warehouse tables and joins to analyze large data volumes.

Key Features:

 Data stored in rows and columns (tables).

 Aggregation done on the fly using SQL queries.
 Handles large datasets efficiently.

Example:

When a manager asks, “Show sales by region and quarter,”

the ROLAP engine sends a SQL query to the database and returns results instantly (no pre-
stored cube).

Advantages:

 Can handle very large data volumes

 Uses existing database structure
 Easier to integrate with other tools

Disadvantages:
 Slower than MOLAP (since calculations are done in real-time)
 Heavy database load for frequent queries
 Requires SQL optimization
Example Tools:

 MicroStrategy
 Oracle Express
 SAP BusinessObjects

3. HOLAP (Hybrid OLAP)

HOLAP combines the speed of MOLAP and the scalability of ROLAP.

It stores summaries in cubes and detailed data in relational tables.
Technical Definition:

HOLAP uses a hybrid storage architecture summary-level data is pre-aggregated and

stored in multidimensional format (MOLAP), while detailed data remains in relational form
(ROLAP).
Queries can access both levels as needed.

Key Features:

 Combines MOLAP’s fast summary queries with ROLAP’s deep detail access.
 Efficient balance between performance and storage.
 Ideal for large-scale analytical systems.

Example:

A telecom company keeps daily summaries (MOLAP cube) for quick reports but stores call-
level records (ROLAP) for deep investigation.

Advantages:

 Good performance + scalability

 Flexible for both summary and detailed analysis
 Balanced use of resources

Disadvantages:

❌ More complex to design and maintain

❌ Costlier infrastructure

Example Tools:

 Microsoft SQL Server Analysis Services (SSAS – Hybrid mode)

 SAP BW
 IBM Cognos

Comparison Table of OLAP Types

Feature MOLAP ROLAP HOLAP

Multidimensional
Full Form Relational OLAP Hybrid OLAP
OLAP

Multidimensional Both Cube +

Storage Type Relational Tables
Cubes Tables
 Feature MOLAP ROLAP HOLAP

Query
Very Fast Moderate Fast
Performance

Data Volume
Limited Very Large Large
Handling

Pre-aggregation Yes No Partial

Scalability Low High High

Data Storage Proprietary Cube SQL Database Hybrid

Maintenance Moderate Easy Complex

MicroStrategy, Cognos, SSAS

Example Tools Oracle Essbase, SSAS
BusinessObjects (Hybrid)

Data cube
A Data Cube is a multidimensional array of values that represents data along multiple dimensions.
It is used in OLAP systems to enable fast and flexible data analysis.

1. Dimensions
2. Measures

Each cell in the cube represents a measure (e.g., Sales, Profit, Quantity) corresponding to
specific values of dimensions (e.g., Product, Time, Region).

Let’s imagine we have a company that sells laptops and mobiles in different regions over
several months.

Product Region Month Sales

Laptop North Jan 80,000
Laptop South Jan 70,000
Mobile North Jan 60,000
Mobile South Jan 75,000

Now, we can visualize this as a 3-dimensional data cube:

 Dimension 1 (X-axis): Product

 Dimension 2 (Y-axis): Region
 Dimension 3 (Z-axis): Time (Month)
 Measure: Sales
Practical Example: OLAP Operations with Answers

Sample Data (Sales Cube)

Year Quarter Region Product Sales (₹)
2025 Q1 Delhi Laptop 2,50,000
2025 Q1 Mumbai Laptop 3,00,000
2025 Q2 Delhi Mobile 1,80,000
2025 Q2 Mumbai Mobile 2,00,000
2025 Q3 Delhi TV 2,20,000
2025 Q3 Mumbai TV 2,40,000
2025 Q4 Delhi Laptop 2,80,000
2025 Q4 Mumbai Mobile 2,10,000

1. Slice Operation

Question:
Show sales data for only Product = “Laptop”.

Step:
Filter the Product column → select only “Laptop”.

Answer (Result Table):

Year Quarter Region Product Sales (₹)

2025 Q1 Delhi Laptop 2,50,000
2025 Q1 Mumbai Laptop 3,00,000
2025 Q4 Delhi Laptop 2,80,000

Explanation:
The slice operation fixed one dimension (Product) to view only laptop sales across all
regions and quarters.

2. Dice Operation

Question:
Show sales data for Product = “Mobile” in Region = “Mumbai”.

Step:
Apply two filters: Product = Mobile and Region = Mumbai.

Answer (Result Table):

Year Quarter Region Product Sales (₹)

2025 Q2 Mumbai Mobile 2,00,000
2025 Q4 Mumbai Mobile 2,10,000
Explanation:
Dice applies multiple filters across dimensions (Product and Region here).

3️. Drill Down Operation

Question:
You have annual sales data for 2025.
Now, drill down to see sales by Quarter and Product.

Step:
In PivotTable → drag Quarter and Product under Rows; Sum of Sales under Values.

Answer (Example Summary Table):

Quarter Product Total Sales (₹)

Q1 Laptop 5,50,000
Q2 Mobile 3,80,000
Q3 TV 4,60,000
Q4 Laptop & Mobile 4,90,000

Explanation:
Drill down breaks data from Yearly to Quarterly or more detailed levels.

[Link] Up Operation

Question:
You have quarterly data and want to summarize into yearly totals.

Step:
Group the data by Year and sum up the Sales.

Answer (Result):

Year Total Sales (₹)

2025 18,80,000

Explanation:
Roll up aggregates lower-level (quarterly) data to higher-level (yearly) data.
Pivot (Rotate) Operation

Question:
Currently, the report shows Products in rows and Regions in columns.
Now, swap the layout to show Regions in rows and Products in columns.

Step:
In PivotTable → drag Region to Rows and Product to Columns.

Answer (Pivoted Summary Table):

Region Laptop Mobile TV Total Sales (₹)

Delhi 5,30,000 1,80,000 2,20,000 9,30,000
Mumbai 3,00,000 4,10,000 2,40,000 9,50,000
Total 8,30,000 5,90,000 4,60,000 18,80,000

Explanation:
Pivot changes the viewing angle of data — rotating dimensions to get new insights.