OLAP Cubes, Dimensions, Measures & Multidimensional Analysis

TAKEAWAYS:

• OLAP enables multidimensional analysis of business data (e.g., sales by region, product, time) using cubes, dimensions, and measures—unlike flat relational tables.
• OLAP servers (MOLAP, ROLAP, HOLAP) optimize query performance for complex aggregations (e.g., "Total sales in Kathmandu for winter 2023").
• Drill-down/roll-up and slice-and-dice operations let users explore data hierarchically (e.g., from country → district → shop for Daraz sales).
• Star schema (fact tables + dimension tables) and snowflake schema are the two core OLAP data modeling approaches, each with trade-offs.
• OLAP vs. OLTP: OLAP is for analytical queries (e.g., "Why did NEPSE shares drop?"), while OLTP handles transactional data (e.g., stock trades).
• Real-world use: Ncell uses OLAP to analyze call-drop patterns by district; eSewa leverages it for fraud detection in payment trends.

1. What is OLAP?

OLAP (Online Analytical Processing) is a technology for querying and analyzing aggregated business data from multiple perspectives. Unlike OLTP (Online Transaction Processing), which focuses on fast, simple transactions (e.g., bank deposits), OLAP supports complex analytical queries (e.g., "What products sold best in Province 2 last quarter?").

Key Characteristics of OLAP

mindmap
  root((OLAP Characteristics))
    Fast Query Performance
    Multidimensional Data Analysis
    Aggregation & Summarization
    Ad-hoc Query Support
    User-friendly Interface

Why OLAP?

• Businesses need to slice data (e.g., by time, region, product) and drill down (e.g., from national sales → district → shop).
• Example: Nabil Bank uses OLAP to analyze loan defaults by branch, customer segment, and economic cycle.

2. Multidimensional Data Model

OLAP data is stored in a multidimensional structure (not flat tables). Think of it as a 3D cube where:

• Facts = Numerical data (e.g., sales amount, profit).
• Dimensions = Categories (e.g., time, product, region).
• Measures = Aggregated values (e.g., sum, average, count).

Example: Sales Data Cube for Daraz

Visualization of a Sales Cube:

graph TD
    A["Sales Revenue (Measure)"] --> B["Product: Laptop, Mobile, etc."]
    A --> C["Region: Kathmandu, Pokhara, etc."]
    A --> D["Time: 2023 Q1, Q2, etc."]
    B --> E["Brand: Apple, Samsung"]
    C --> F["District: Lalitpur, Kaski"]
    D --> G["Month: Jan, Feb"]

• Fact Table: Contains sales revenue, quantity, and foreign keys to dimensions.
• Dimension Tables: Store descriptive attributes (e.g., product name, region name).

3. OLAP Architectures

Three types of OLAP servers optimize performance differently:

When to Use Which?

• MOLAP: Best for read-heavy analytics (e.g., historical sales trends).
• ROLAP: Best for frequently updated data (e.g., real-time Pathao ride analytics).
• HOLAP: Compromise for large datasets (e.g., NTC’s telecom usage patterns).

4. OLAP Operations

OLAP enables interactive data exploration through these operations:

A. Drill-Down vs. Roll-Up

flowchart LR
    A["National Sales (Nepal)"] -->|"Drill-Down"| B["Province 1 Sales"]
    B -->|"Drill-Down"| C["Kathmandu District Sales"]
    C -->|"Roll-Up"| B
    B -->|"Roll-Up"| A

• Drill-Down: Move from summary to detail (e.g., national → district sales).
• Roll-Up: Move from detail to summary (e.g., shop → branch → national).
• Example: eSewa drills down from "Total transactions" → "Transactions by district" to identify fraud hotspots.

B. Slice and Dice

• Slice: Select a single dimension (e.g., "Show sales for 2023 only").
• Dice: Select a subset of dimensions (e.g., "Show sales for laptops in Kathmandu in Q1").
• Example: Daraz might dice data to analyze "Mobile sales in Province 3 during Diwali."

C. Pivot and Sort

• Pivot: Rotate data axes (e.g., switch rows/columns in a report).
• Sort: Order data (e.g., "Sort products by profit margin").

5. OLAP Schemas: Star vs. Snowflake

Two common ways to structure OLAP data:

Visual Comparison:

graph TD
    subgraph Star Schema
        A["Fact: Sales"] --> B["Dim: Product"]
        A --> C["Dim: Time"]
        A --> D["Dim: Region"]
    end
    subgraph Snowflake Schema
        A["Fact: Sales"] --> B["Dim: Product"]
        B --> E["Sub-Dim: Brand"]
        A --> C["Dim: Time"]
        C --> F["Sub-Dim: Month"]
        A --> D["Dim: Region"]
        D --> G["Sub-Dim: District"]
    end

Example for Nepali Context:

• Star Schema: A local grocery shop tracks sales by product, date, and cashier.
• Snowflake Schema: NTC tracks call data with normalized dimensions (e.g., subscriber → plan type → usage tier).

6. OLAP vs. OLTP: Key Differences

Real-World Analogy:

• OLTP = Cashier at a supermarket (fast, simple transactions).
• OLAP = Store manager analyzing sales trends to restock inventory.

7. Worked Example: NEPSE Stock Analysis with OLAP

Scenario: An investor wants to analyze NEPSE stock trends using OLAP.

Step 1: Define Dimensions and Measures

• Dimensions:
  • Time (Year, Quarter, Month)
  • Sector (Banking, Hydropower, FMCG)
  • Company (Nabil Bank, Himalayan Java, etc.)
• Measures:
  • Closing Price
  • Volume Traded
  • Market Cap

Step 2: Build a Cube

graph TD
    A["Stock Data Cube"] --> B["Time: 2020-2023"]
    A --> C["Sector: Banking, Hydropower"]
    A --> D["Company: Nabil, Himalayan Java"]
    A --> E["Measures: Price, Volume"]

Step 3: Perform OLAP Operations

1. Slice: "Show Banking sector stocks in 2023."
2. Dice: "Show Nabil Bank’s monthly closing prices in 2023."
3. Drill-Down: From "Banking sector" → "Nabil Bank" → "Q1 2023 trades."
4. Roll-Up: From "Daily trades" → "Monthly average."

Output:

Insight: Nabil Bank’s stock was more volatile but had higher trading volume.

8. Real-World Applications in Nepal

A. Ncell: Call Drop Analysis

• Problem: Ncell wants to reduce call drops in Kathmandu.
• OLAP Solution:
  • Dimensions: Time (hour, day), Location (cell tower), Network Type (4G/5G).
  • Measures: Call drop rate, retry attempts, customer complaints.
  • Operation: Drill down to identify specific towers in Thapathali with high drop rates during peak hours.
  • Action: Reinforce those towers or reroute traffic.

B. eSewa: Fraud Detection

• Problem: eSewa detects unusual transaction patterns.
• OLAP Solution:
  • Dimensions: User ID, Transaction Time, Amount, Location.
  • Measures: Frequency, Average Amount, Deviation from Norm.
  • Operation: Slice data for "Transactions > Rs. 50,000 in Lalitpur after 10 PM."
  • Action: Flag suspicious users for manual review.

C. Daraz: Inventory Management

• Problem: Daraz wants to optimize warehouse stock.
• OLAP Solution:
  • Dimensions: Product, Region, Season (e.g., winter sales).
  • Measures: Sales Volume, Return Rate, Stockout Frequency.
  • Operation: Roll up from "Shop-level sales" → "District-level demand."
  • Action: Stock more winter coats in Pokhara and fewer in Chitwan.

9. Case Study: Chaudhary Group’s OLAP Implementation

Challenge: Chaudhary Group (owners of Daraz, Nabil Bank, etc.) needed to integrate sales, finance, and supply chain data across Nepal.

Solution:

1. Data Warehouse: Centralized data from all subsidiaries.
2. OLAP Cube: Built with dimensions:
   • Product, Region, Time, Customer Segment.
   • Measures: Revenue, Profit Margin, Inventory Turnover.
3. Tools: Used Microsoft Power BI for dashboards.
4. Operations:
   • Drill-down: From "National sales" → "Pokhara electronics sales."
   • Slice: "Show Q4 2023 sales for laptops."
   • Pivot: Switch from "Sales by product" to "Sales by region."

Outcome:

• 30% reduction in overstocking (by analyzing regional demand).
• 15% increase in cross-selling (identifying complementary products).

10. Advantages and Disadvantages of OLAP

Advantages

mindmap
  root((OLAP Benefits))
    Fast Query Performance
    Multidimensional Analysis
    Supports Complex Aggregations
    User-friendly for Business Users
    Enables Data Mining & Predictive Analytics

Disadvantages

mindmap
  root((OLAP Limitations))
    High Storage Requirements
    Complex Setup & Maintenance
    Not Real-Time (unless ROLAP/HOLAP)
    Requires Skilled Data Modelers
    Costly Licensing for Enterprise Tools

11. Exam Tip

How This Unit is Tested in TU Exams:

1. Definitions: Expect questions on OLAP vs. OLTP, MOLAP/ROLAP/HOLAP, and star vs. snowflake schemas.

   • Example Question: "Differentiate between ROLAP and MOLAP with examples."

2. Diagrams: Draw and label:

   • An OLAP cube with dimensions and measures.
   • A star schema vs. snowflake schema comparison.

3. Scenario-Based Questions:

   • "A bank wants to analyze loan defaults. Design an OLAP cube with appropriate dimensions and measures."
   • "How would Daraz use drill-down operations to improve inventory management?"

4. Advantages/Disadvantages:

   • "List three advantages of using OLAP for NEPSE stock analysis."

5. Real-World Applications:

   • "Explain how Ncell could use OLAP to reduce call drops in Kathmandu."

Marks Distribution:

• Short Answers (2-5 marks): Definitions, comparisons.
• Diagrams (5-10 marks): OLAP cube, schema designs.
• Long Answers (10-15 marks): Case studies, scenario analysis.

Pro Tip:

• Memorize the OLAP operations (slice, dice, drill-down, roll-up, pivot) with real examples (e.g., "eSewa uses drill-down to find fraud").
• Practice drawing schemas—examiners love clear, labeled diagrams.
• Relate to Nepali companies (Ncell, eSewa, Daraz, NEPSE) in answers to score extra marks.

Final Note: OLAP is the backbone of business analytics. Master it, and you’ll ace the exam—and impress future employers with your ability to turn raw data into actionable insights.