OLAP & Multidimensional Analysis: Cubes, Hierarchies, Slicing & Drill-Down

TAKEAWAYS:

• OLAP enables multidimensional data analysis by organizing data into cubes with dimensions (e.g., time, product, region) and measures (e.g., sales, profit).
• Hierarchies (e.g., Year → Quarter → Month) allow users to analyze data at different granularities.
• OLAP operations (slice, dice, drill-down, roll-up) let users explore data interactively without rewriting queries.
• OLAP cubes are precomputed for fast query performance, unlike traditional relational databases.
• Multidimensional modeling is critical for business dashboards (e.g., Nabil Bank’s loan analytics, Daraz’s sales trends).
• ROLAP, MOLAP, and HOLAP are storage architectures balancing speed and flexibility.

1. Introduction to OLAP

OLAP (Online Analytical Processing) is a technology for analyzing complex business data by organizing it into multidimensional structures called OLAP cubes. Unlike OLTP (Online Transaction Processing), which focuses on fast data entry (e.g., eSewa transactions), OLAP is designed for complex queries, reporting, and data exploration.

Why OLAP?

• Speed: Precomputed aggregations (e.g., monthly sales) answer queries in seconds.
• Flexibility: Users can explore data from multiple angles (e.g., "Show me sales by region and product category").
• Decision Support: Helps managers answer "what-if" questions (e.g., "What if we reduce prices in Kathmandu by 10%?").

OLAP vs. OLTP

2. Multidimensional Data Model

OLAP organizes data in a cube structure with:

• Dimensions: Categories for analysis (e.g., Time, Product, Customer, Region).
• Measures: Numerical values (e.g., Sales, Profit, Quantity).
• Hierarchies: Levels within a dimension (e.g., Time: Year → Quarter → Month).

Example: Retail Sales Cube

Consider a cube for Daraz’s sales data:

• Dimensions:
  • Time (Year → Quarter → Month → Day)
  • Product (Category → Subcategory → Product)
  • Region (Country → State → District → City)
• Measures:
  • Sales Revenue
  • Quantity Sold
  • Profit Margin

mindmap
  root((OLAP Cube for Daraz Sales))
    Dimensions
      Time["Year → Quarter → Month → Day"]
      Product["Category → Subcategory → Product"]
      Region["Country → State → District → City"]
    Measures
      Sales Revenue
      Quantity Sold
      Profit Margin
    Example Query
      "Show sales of electronics in Kathmandu for Q1 2024"

3. OLAP Operations

OLAP supports four key operations to explore data:

A. Slice

• Definition: Selecting a single 2D "slice" of the cube (fixing one dimension).
• Example: In Daraz’s cube, slicing by Region = Kathmandu shows sales only for Kathmandu across all products and time.
• Visualization:

  flowchart TD
    A["Full Cube"] -->|"Slice by Region = Kathmandu"| B["2D Table: Products vs. Time for Kathmandu"]

B. Dice

• Definition: Selecting a 3D sub-cube by fixing multiple dimensions.
• Example: Dice by Region = Kathmandu AND Product = Electronics to see only electronics sales in Kathmandu.
• Visualization:

  flowchart TD
    A["Full Cube"] -->|"Dice by Region + Product"| B["3D Sub-Cube: Electronics in Kathmandu"]

C. Drill-Down/Drill-Up

• Drill-Down: Moving from higher to lower granularity (e.g., Year → Quarter → Month).
• Drill-Up: Moving from lower to higher granularity (e.g., Month → Quarter → Year).
• Example: Start with total sales in 2023 (Year), then drill down to Q1 2023 (Quarter), then to January 2023 (Month).
• Visualization:

  flowchart TD
    A["Year 2023"] -->|"Drill-Down"| B["Q1 2023"]
    B -->|"Drill-Down"| C["January 2023"]

D. Roll-Up

• Definition: Aggregating data across dimensions (similar to drill-up but with calculations).
• Example: Rolling up monthly sales in Kathmandu to quarterly sales for all districts in Bagmati Province.

4. OLAP Architectures

OLAP systems use different storage techniques:

Real-World Example: Nabil Bank’s Loan Analytics

• Use Case: Nabil Bank uses MOLAP cubes to analyze loan defaults by region, customer segment, and time.
• How OLAP Helps:
  • Slice: View defaults in Kathmandu vs. Pokhara.
  • Dice: Combine defaults with customer income levels.
  • Drill-Down: Identify high-risk loans in January 2024.
• Architecture: HOLAP (precomputed aggregates for speed + detailed transactional data for updates).

5. OLAP Tools and Implementations

Popular OLAP tools include:

• Microsoft SQL Server Analysis Services (SSAS)
• Oracle OLAP
• IBM Cognos
• Mondrian (Pentaho)
• Google BigQuery (for cloud-based OLAP)

Example: eSewa’s Subscription Analytics

• Problem: eSewa wants to analyze monthly subscription renewals by user age group and region.
• Solution:
  1. Build an OLAP cube with dimensions: Time (Month), User (Age Group), Region.
  2. Use drill-down to see why renewals dropped in Kathmandu in June 2024.
  3. Use slice to compare renewals between Kathmandu and Pokhara.

6. Multidimensional Schema Design

Designing OLAP cubes involves:

1. Identifying Dimensions: What categories will users analyze by? (e.g., Time, Product, Customer).
2. Defining Hierarchies: How are dimensions structured? (e.g., Time: Year → Quarter → Month).
3. Choosing Measures: What numerical data is needed? (e.g., Sales, Profit).
4. Fact and Dimension Tables:
   • Fact Table: Stores measures (e.g., Sales_Fact with Sales_Amount, Quantity).
   • Dimension Tables: Store dimension attributes (e.g., Product_Dimension with Product_ID, Category, Subcategory).

Example Schema for Daraz:

erDiagram
  SALES_FACT ||--o{ PRODUCT_DIM : contains
  SALES_FACT ||--o{ TIME_DIM : contains
  SALES_FACT ||--o{ REGION_DIM : contains
  PRODUCT_DIM {
    int Product_ID PK
    string Category
    string Subcategory
    string Product_Name
  }
  TIME_DIM {
    int Time_ID PK
    string Year
    string Quarter
    string Month
  }
  REGION_DIM {
    int Region_ID PK
    string Country
    string State
    string District
  }

7. Advantages and Limitations of OLAP

Advantages

• Fast Query Performance: Precomputed aggregations answer complex queries in seconds.
• Interactive Exploration: Users can slice, dice, and drill without technical skills.
• Decision Support: Enables "what-if" analysis (e.g., "What if we reduce prices by 15%?").
• Scalability: Handles large datasets (e.g., NEPSE’s stock market trends).

Limitations

• Storage Overhead: MOLAP cubes require significant disk space.
• Update Latency: MOLAP cubes need rebuilding after data changes.
• Complexity: Designing multidimensional schemas requires expertise.

## In the Real World

1. Nabil Bank’s Loan Portfolio Analysis

   • OLAP Use: Uses HOLAP cubes to analyze loan defaults by region, customer segment, and time.
   • How It Works:
     • Slice: Compare default rates in Kathmandu vs. Pokhara.
     • Dice: Combine defaults with customer income levels to identify high-risk groups.
     • Drill-Down: Pinpoint loans defaulted in January 2024.
   • Impact: Helps bankers target high-risk areas with better loan policies.

2. Daraz’s Sales and Marketing Strategy

   • OLAP Use: Analyzes product sales trends by region, time, and category.
   • Example Query:
     • "Which electronics products sold best in Kathmandu in Q1 2024?"
     • "How did sales change after a 10% discount in Pokhara?"
   • Tool: Uses Mondrian (Pentaho) for OLAP operations.

3. NTC’s Network Performance Monitoring

   • OLAP Use: Tracks call drop rates, data usage, and network congestion by district and time.
   • Operations:
     • Roll-Up: Aggregate monthly call drops to quarterly trends.
     • Drill-Down: Identify peak congestion hours in Kathmandu.
   • Impact: Helps NTC optimize network resources and reduce outages.

## Worked Example: Kathmandu Traffic Routes Optimization

Scenario: The Kathmandu Metropolitan City wants to optimize traffic flow using OLAP.

Step 1: Define the OLAP Cube

• Dimensions:
  • Time (Hour → Day → Week → Month)
  • Route (Main Road → Sub-Road → Intersection)
  • Vehicle Type (Car → Bus → Motorcycle)
• Measures:
  • Average Speed
  • Congestion Index
  • Number of Vehicles

Step 2: Build the Cube

Step 3: Apply OLAP Operations

1. Slice: View congestion on Ring Road only.
2. Dice: Combine Ring Road + 8:00 AM + All Vehicle Types.
3. Drill-Down: See congestion at specific intersections (e.g., Thapathali Chowk).
4. Roll-Up: Aggregate hourly data to daily trends.

Step 4: Decision Making

• Insight: Congestion is worst on Ring Road at 8:00 AM for buses.
• Action: Introduce bus-only lanes or traffic signal adjustments during peak hours.

## Exam Tip

1. Understand the Cube Structure: Always draw a cube diagram in exams. Label dimensions, measures, and hierarchies clearly.
2. OLAP Operations: Know the difference between slice, dice, drill-down, and roll-up. Use real examples (e.g., Daraz sales, Nabil Bank loans).
3. Architectures: Compare MOLAP, ROLAP, and HOLAP in a table. Mention when each is used (e.g., MOLAP for dashboards, ROLAP for large datasets).
4. Worked Examples: Expect numerical problems (e.g., "Calculate sales after a 10% discount using OLAP operations"). Show step-by-step calculations.
5. Tools: Mention SSAS, Mondrian, or BigQuery in case studies. Link them to real companies (e.g., Nabil Bank uses SSAS).
6. Limitations: Be ready to discuss trade-offs (e.g., MOLAP is fast but inflexible).

Common Pitfalls:

• Confusing drill-down (navigation) with roll-up (aggregation).
• Forgetting to include measures in cube definitions.
• Not labeling hierarchies in diagrams (e.g., Time: Year → Quarter → Month).