OLAP: Cubes, Drill-Downs, and Real-Time Analytics

TAKEAWAYS:

• OLAP enables multidimensional analysis of data using cubes, dimensions, and measures to support decision-making.
• Key OLAP operations (slice, dice, roll-up, drill-down, pivot) transform data views for deeper insights.
• OLAP architectures (MOLAP, ROLAP, HOLAP) differ in storage, processing speed, and flexibility.
• OLAP is widely used in business intelligence tools like Tableau, Power BI, and eSewa’s sales analytics.
• Star and snowflake schemas optimize OLAP performance by structuring fact and dimension tables.
• Real-world applications include Nepal’s NTC’s network performance analysis and Daraz’s customer segmentation.

1. Introduction to OLAP

OLAP (Online Analytical Processing) is a technology for analyzing complex data from multiple perspectives. Unlike OLTP (Online Transaction Processing), which focuses on transactional efficiency, OLAP is designed for querying, reporting, and data analysis.

Key Concepts

• Multidimensional Data Model: Data is organized in facts (measures) and dimensions (attributes).
  • Example: Sales data can be analyzed by Product (dimension), Region (dimension), Time (dimension), and Revenue (measure).
• OLAP Cube: A data structure that allows users to view data from different angles.
  • Think of it as a 3D spreadsheet where each axis represents a dimension.

Visual: OLAP Cube Structure

Real-World Example: eSewa’s Sales Analytics

eSewa uses OLAP to analyze transaction trends by:

• Dimension: Payment method (Khalti, eSewa Wallet, Debit Card).
• Measure: Transaction volume, average amount.
• Operation: Drill-down to see which payment method is most popular in Kathmandu vs. Pokhara.

2. OLAP Operations

OLAP operations allow users to navigate and manipulate data in the cube.

A. Slice and Dice

• Slice: Select a single 2D slice from the cube (e.g., sales in 2023).
• Dice: Select a sub-cube (e.g., sales of laptops in Kathmandu in Q1 2023).

B. Roll-Up and Drill-Down

• Roll-Up: Aggregate data (e.g., sum sales by region → sum by country).
• Drill-Down: Break down aggregated data (e.g., see sales by district within Kathmandu).

C. Pivot

• Rotate the cube to view data from a different dimension (e.g., switch from time-based to product-based analysis).

Worked Example: Daraz’s Inventory Analysis

Scenario: Daraz wants to analyze mobile phone sales by region and month.

3. OLAP Architectures

Three main architectures define how OLAP systems store and process data:

Visual: OLAP Architecture Comparison

mindmap
  root((OLAP Architectures))
    MOLAP["Pre-aggregated\nFast\nStatic"]
    ROLAP["Relational DB\nSlower\nFlexible"]
    HOLAP["Hybrid\nBalanced\nDynamic"]

Real-World Example: NTC’s Network Performance

NTC uses MOLAP to:

• Pre-aggregate monthly call drop rates by district.
• Quickly generate reports for decision-making (e.g., "Which district has the worst connectivity?").

4. OLAP vs. OLTP

5. OLAP Tools and Applications

Popular OLAP Tools

• Tableau: Visualizes OLAP cubes with dashboards.
• Power BI: Microsoft’s BI tool for OLAP analysis.
• Pentaho: Open-source OLAP and data mining tool.

Real-World Example: Pathao’s Driver Performance

Pathao uses OLAP to:

1. Track ride completions by driver (fact table).
2. Dimensions: Driver ID, Region, Time of Day.
3. Measure: Average rating, earnings.
4. Operation: Drill-down to see which drivers in Kathmandu have the highest ratings.

6. Star and Snowflake Schemas

These are database schemas optimized for OLAP.

A. Star Schema

• Simpler, with fact table directly connected to dimension tables.
• Example:

B. Snowflake Schema

• Normalized, with sub-dimensions (e.g., Time broken into Date, Month, Year).
• Example:

When to Use Which?

• Star Schema: Faster queries, simpler design (e.g., eSewa’s transaction reports).
• Snowflake Schema: Better for highly normalized data (e.g., NEPSE’s stock market analysis).

7. OLAP in Nepal: Case Study – NEPSE

Nepal Stock Exchange (NEPSE) uses OLAP to:

• Track stock prices by sector (dimension: Banking, Hydropower, etc.).
• Measure: Volume, price changes.
• Operation: Roll-up to see monthly trends for all stocks.

Worked Example: NEPSE’s Quarterly Analysis

## In the Real World

1. eSewa’s Fraud Detection

   • Uses OLAP cubes to analyze transaction patterns by user, time, and amount.
   • Operation: Dice to isolate suspicious transactions (e.g., high-value payments at odd hours).
   • Tool: Power BI for real-time dashboards.

2. Daraz’s Supply Chain Optimization

   • Star schema tracks inventory levels by product, warehouse, and supplier.
   • Operation: Roll-up to identify low-stock products across regions.
   • Impact: Reduces out-of-stock scenarios by 30%.

3. NTC’s Network Planning

   • MOLAP cubes store call drop rates by district and time.
   • Operation: Drill-down to find peak congestion hours in Kathmandu.
   • Action: Deploy more towers in high-traffic areas.

## Exam Tip

1. Define OLAP clearly: Mention multidimensional analysis, cubes, and operations (slice, dice, roll-up, drill-down).
2. Compare MOLAP, ROLAP, HOLAP: Know their storage, speed, and use cases.
3. Star vs. Snowflake: Explain when each is used (simplicity vs. normalization).
4. Real-world examples: Be ready to link OLAP to eSewa, Daraz, NTC, or NEPSE.
5. Worked examples: Practice calculating aggregates (sum, avg) after operations like dice or roll-up.
6. Diagrams: Always draw OLAP cubes and schema diagrams in exams.

Final Note: OLAP is the backbone of business intelligence. Mastering it will help you analyze data like a pro—whether for Nepal’s startups or global giants like Google Analytics. 🚀