OLAP & Multidimensional Analysis
266 words
1 min read
Visual companion
Python
Type and operator map
Python Week 1: the first filter for runtime behavior
View
Revision summary
What this note is really saying
Short form
# OLAP & Multidimensional Analysis ## 🎯 Learning Objectives - Explain the star schema and snowflake schema - Perform OLAP operations (roll-up, drill-down, slice, dice, pivot) - Understand OLAP cube design - Compare MOLAP, ROLAP, and HOLAP ## 📖 Core Content ### 3.1 Star Schema The star schema has a central **fact t...
OLAP & Multidimensional Analysis
🎯 Learning Objectives
- Explain the star schema and snowflake schema
- Perform OLAP operations (roll-up, drill-down, slice, dice, pivot)
- Understand OLAP cube design
- Compare MOLAP, ROLAP, and HOLAP
📖 Core Content
3.1 Star Schema
The star schema has a central fact table (measures) surrounded by dimension tables (descriptive attributes).
(Diagram)
3.2 OLAP Operations
| Operation | Description | Example |
|---|---|---|
| Roll-up | Aggregate to higher level | Daily → Monthly sales |
| Drill-down | Decompose to lower level | Annual → Quarterly sales |
| Slice | Filter one dimension | Sales for 2024 only |
| Dice | Filter multiple dimensions | Sales in Jan 2024 for Electronics |
| Pivot | Reorient the view | Swap rows and columns |
3.3 OLAP Architectures
| Type | Storage | Performance | Use Case |
|---|---|---|---|
| MOLAP | Multidimensional cubes | Fast query | Pre-aggregated, small-medium data |
| ROLAP | Relational tables | Slower (SQL) | Large data, real-time |
| HOLAP | Hybrid (cubes + relational) | Balanced | Most enterprise deployments |
📝 Practice Questions
Q1: Why is star schema denormalized?Denormalization reduces the number of JOINs for queries. In a normalized schema, querying "total sales by product category" would require JOINs across multiple tables. In star schema, all product attributes are in one dimension table — one JOIN to the fact table. Q2: What's the difference between roll-up and drill-down?Roll-up increases the granularity (climbs up the hierarchy): day → month → quarter → year. Drill-down decreases granularity (climbs down): year → quarter → month → day. Roll-up reduces data size; drill-down increases detail. Q3: What is a degenerate dimension?A dimension stored directly in the fact table (no separate dimension table). Example: invoice number or order number. They're "degenerate" because they don't have additional attributes beyond the key itself. Useful for tracking individual transactions. Join Discord PreviousETL ProcessNextBig Data: Hadoop & Spark