Data Cube: OLAP, Aggregation, Drill-Down & Slicing

What is a Data Cube?

A data cube is a multidimensional data structure that stores aggregated data for fast querying and analysis. Unlike flat tables, it organizes data along dimensions (e.g., time, product, region) and measures (e.g., sales, profit).

Key Components

1. Facts: Quantitative data (e.g., sales amount, quantity).
2. Dimensions: Qualitative attributes (e.g., date, product category, store location).
3. Hierarchies: Levels within dimensions (e.g., Day → Month → Year).
4. Cells: Intersection of dimensions and measures (e.g., "Sales of Product A in Kathmandu in Q1 2024").

graph LR
    A["Data Cube"] --> B["Facts (Measures)"]
    A --> C["Dimensions"]
    C --> D["Time\n(Day→Month→Year)"]
    C --> E["Product\n(Category→Brand→Model)"]
    C --> F["Location\n(District→Region→Country)"]
    B --> G["Sales Amount\nQuantity"]

How Data Cubes Work: Multidimensional Modeling

A data cube is built using OLAP (Online Analytical Processing) techniques. It allows users to:

• View data from different angles (e.g., sales by product vs. sales by region).
• Aggregate data (sum, avg, count) at different levels (e.g., daily → monthly → yearly).

Example: Sales Data Cube

Dimensions:

• Product (Laptop, Mobile)
• Region (Kathmandu, Pokhara)
• Time (Jan, Feb)

Measure: Sales (₹)

OLAP Operations on Data Cubes

Data cubes support six key operations for analysis:

Worked Example: Roll-Up and Drill-Down

Scenario: Daraz wants to analyze sales trends.

Step 1: Raw Data (Daily Sales)

Step 2: Roll-Up (Daily → Monthly)

Step 3: Drill-Down (Monthly → Daily)

Advantages and Disadvantages of Data Cubes

In the Real World

1. eSewa (Nepal)

   • Uses data cubes to analyze transaction trends (e.g., "How many users paid electricity bills in Kathmandu last month?").
   • Operation: Slice → Filter by region (Kathmandu) and time (Jan 2024).

2. NTC (Nepal Telecom)

   • Tracks call data records (CDRs) in a cube to detect fraud (e.g., "Which districts have the highest call volumes?").
   • Operation: Roll-Up → Aggregate daily calls to monthly trends.

3. Google Analytics (Global)

   • Uses data cubes to let marketers drill down into website traffic (e.g., "Which countries visited our site in Q1?").
   • Operation: Drill-Down → From country → city → device type.

Exam Tip

• Focus on OLAP operations: Be able to apply roll-up, drill-down, slice, and dice on given datasets.
• Draw a data cube: In exams, sketch a cube with dimensions, hierarchies, and measures to explain concepts.
• Compare with flat tables: Highlight why cubes are better for analytics (speed, flexibility).
• Real-world mapping: Relate to eSewa, NTC, or Daraz in short-answer questions.

Visual: Data Cube Operations

graph LR
    A["Data Cube\n(Sales Data)"] --> B["Roll-Up\n(Yearly Sales)"]
    A --> C["Drill-Down\n(Daily Sales)"]
    A --> D["Slice\n(Laptops Only)"]
    A --> E["Dice\n(Kathmandu, Jan 2024)"]
    B --> F["Total: ₹25M"]
    C --> G["Day 1: ₹500K\nDay 2: ₹600K"]
    D --> H["Laptop Sales: ₹10M"]
    E --> I["₹1.1M"]

IMAGE: OLAP Cube Visualization

OLAP data cube diagram | A labeled 3D cube showing dimensions (Time, Product, Region) and measures (Sales).

IMAGE: eSewa Dashboard (Example Output)

eSewa transaction analytics dashboard | A screenshot of a real dashboard showing monthly bill payments by district (Kathmandu, Pokhara, etc.).