OLAP: Cubes, Dimensions, Measures & Query Techniques

What is OLAP?

OLAP stands for Online Analytical Processing. It is a technology used for analyzing business data from multiple perspectives. Unlike OLTP (Online Transaction Processing), which focuses on capturing and processing transactions (e.g., bank deposits, eSewa payments), OLAP is designed for complex queries, reporting, and data analysis.

Key Characteristics of OLAP:

• Multidimensional data analysis: Data is stored in a cube structure with dimensions (e.g., time, product, region) and measures (e.g., sales, profit).
• Fast query performance: Optimized for read-heavy operations (e.g., "Show me sales trends in Kathmandu for 2023").
• Support for complex queries: Includes operations like slice, dice, drill-down, roll-up, and pivot.
• Aggregation and summarization: Pre-computes summaries (e.g., monthly sales totals) for faster retrieval.

---

OLAP vs. OLTP: A Comparison

OLAP and OLTP serve different purposes. Here’s a comparison:

---

OLAP Data Model: The OLAP Cube

An OLAP cube is a multidimensional data structure that stores data in a way that allows for efficient analysis. It consists of:

• Dimensions: Categories or attributes (e.g., time, product, location).
• Measures: Numerical values (e.g., sales, profit, quantity).
• Hierarchies: Levels within a dimension (e.g., Year → Quarter → Month → Day).

Visualizing an OLAP Cube

Consider a simple OLAP cube for Daraz sales data:

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

---

OLAP Operations

OLAP supports several operations to analyze data from different angles:

1. Slice

A slice is a subset of the cube selected by fixing one or more dimensions. For example:

• Query: "Show me sales for the Product dimension = Electronics in 2023."
• Result: A 2D table with Time (Month) and Region (City) as rows/columns, and Sales Revenue as values.

2. Dice

A dice is a slice of a slice—selecting a sub-cube by fixing multiple dimensions. For example:

• Query: "Show me sales for Electronics in Kathmandu for Q1 2023."
• Result: A smaller subset of the cube.

3. Drill-Down/Drill-Up

• Drill-Down: Move from a higher level to a lower level in a hierarchy (e.g., from Year to Month).
  • Example: Start with "Sales in 2023" → Drill down to "Sales in Q1 2023" → Drill down further to "Sales in January 2023."
• Drill-Up: Move from a lower level to a higher level (e.g., from Month to Quarter).

4. Roll-Up

A roll-up aggregates data across dimensions (e.g., sum sales by region to get national totals).

• Example: "Sum sales for all products in Kathmandu to get total sales for Kathmandu."

5. Pivot

A pivot rotates the cube to change the perspective of the data (e.g., switch rows and columns in a report).

---

Worked Example: OLAP Query on eSewa Transactions

Suppose we have an OLAP cube for eSewa transactions with the following dimensions and measures:

• Dimensions:
  • Time (Year → Month → Day)
  • Service (Bill Payment, Recharge, Transfer)
  • Location (Province → District → Municipality)
• Measures:
  • Number of Transactions
  • Total Amount

Query: "Show the total amount spent on recharge services in Province 1 for January 2023, broken down by municipality."

Step-by-Step Solution:

1. Slice: Fix the Service = Recharge and Time = January 2023.
2. Dice: Further fix Location = Province 1.
3. Drill-Down: Break down by Municipality within Province 1.
4. Result: A table showing municipalities in Province 1, with columns for:
   • Municipality Name
   • Number of Transactions
   • Total Amount

---

OLAP Tools and Technologies

Popular OLAP tools include:

1. Microsoft SQL Server Analysis Services (SSAS): Supports OLAP cubes and multidimensional analysis.
2. Oracle OLAP: Integrated with Oracle databases for advanced analytics.
3. IBM Cognos: Business intelligence tool with OLAP capabilities.
4. Tableau: Visualization tool that connects to OLAP cubes for interactive dashboards.
5. Power BI: Microsoft’s BI tool with OLAP integration.

---

In the Real World

OLAP is widely used in businesses and everyday applications. Here are three concrete examples:

1. eSewa Analytics Dashboard:

   • Idea Used: OLAP cubes for multidimensional analysis.
   • How: eSewa uses OLAP to analyze transaction patterns by time (hourly/daily), service type (recharge, bill payment), and location (district/province). This helps them identify peak usage times and optimize server resources.
   • Example Query: "Which districts in Province 2 have the highest recharge transactions during weekends?"

2. Daraz Sales Performance:

   • Idea Used: Drill-down and roll-up operations.
   • How: Daraz uses OLAP to track sales performance. A manager can start with national sales data and drill down to see sales by product category, subcategory, and even individual products in a specific city like Kathmandu.
   • Example Query: "What are the top-selling electronics products in Kathmandu during the Dashain festival?"

3. NTC Network Traffic Analysis:

   • Idea Used: Slice and dice operations.
   • How: NTC uses OLAP to analyze network traffic data. They can slice data by time (hourly/daily) and location (region/district) to identify congestion points and plan infrastructure upgrades.
   • Example Query: "Which districts in the Kathmandu Valley experience the highest data usage between 6 PM and 9 PM?"

---

OLAP Schema Design: Star and Snowflake Schemas

OLAP schemas are designed to optimize query performance. The two most common types are:

1. Star Schema

• Simplest OLAP schema.
• Consists of:
  • One fact table (central table with measures).
  • Multiple dimension tables (connected to the fact table via foreign keys).
• Advantage: Easy to understand and query.
• Disadvantage: Can lead to redundancy if dimensions have complex hierarchies.

2. Snowflake Schema

• Normalized version of the star schema.
• Dimension tables are further normalized (e.g., a dimension table for "Product" may have separate tables for "Category" and "Subcategory").
• Advantage: Reduces redundancy and improves data integrity.
• Disadvantage: More complex queries due to additional joins.

---

Worked Example: Star Schema for Ncell Recharge Data

Let’s design a star schema for Ncell recharge data with the following tables:

1. Fact Table: RechargeTransactions

   • TransactionID (Primary Key)
   • CustomerID (Foreign Key → Customers)
   • RechargeAmount
   • RechargeDate (Foreign Key → Time)
   • DistrictID (Foreign Key → Districts)
   • RechargeType (e.g., Top-up, Data, Special)

2. Dimension Tables:

   • Time: Contains RechargeDate, Year, Month, Day.
   • Districts: Contains DistrictID, DistrictName, Province.
   • Customers: Contains CustomerID, CustomerSegment (e.g., Youth, Senior).

Query: "Show the total recharge amount by district and month for January 2023."

SQL-like OLAP Query (using MDX or SQL):

SELECT
    d.DistrictName,
    t.Month,
    SUM(r.RechargeAmount) AS TotalRechargeAmount
FROM
    RechargeTransactions r
JOIN
    Time t ON r.RechargeDate = t.RechargeDate
JOIN
    Districts d ON r.DistrictID = d.DistrictID
WHERE
    t.Year = 2023 AND t.Month = 'January'
GROUP BY
    d.DistrictName, t.Month
ORDER BY
    TotalRechargeAmount DESC;

Result:

---

Advantages and Disadvantages of OLAP

Advantages:

1. Fast Query Performance: Pre-aggregated data allows for quick analysis.
2. Multidimensional Analysis: Supports complex queries across multiple dimensions.
3. Scalability: Can handle large volumes of data efficiently.
4. Business Intelligence: Enables data-driven decision-making (e.g., identifying sales trends, customer segments).

Disadvantages:

1. High Storage Requirements: OLAP cubes can consume significant storage.
2. Complexity: Designing and maintaining OLAP schemas can be challenging.
3. Latency in Updates: OLAP cubes are typically refreshed periodically (e.g., nightly), so real-time updates may not be possible.
4. Cost: Advanced OLAP tools and hardware can be expensive.

---

Exam Tip

For exams, focus on the following key areas:

1. Definitions: Be able to define OLAP, OLAP cube, dimensions, measures, and OLAP operations (slice, dice, drill-down, roll-up, pivot).
2. OLAP vs. OLTP: Understand the differences in purpose, data volume, and query types.
3. OLAP Schemas: Know the structure of star and snowflake schemas and when to use each.
4. Worked Examples: Practice designing OLAP cubes and writing queries for real-world scenarios (e.g., eSewa, Daraz, Ncell).
5. Applications: Relate OLAP concepts to business intelligence tools like Power BI, Tableau, and SQL Server Analysis Services.
6. Visuals: Be prepared to draw OLAP cubes and explain how operations like slice and dice work on them.

Common Exam Questions:

• Explain the difference between OLAP and OLTP with examples.
• Design a star schema for a given dataset (e.g., bank transactions, hospital records).
• Write a query to perform a slice or dice operation on an OLAP cube.
• Compare star and snowflake schemas and discuss their advantages and disadvantages.

---