Unit I — Data Warehouse

Operational DB vs Data Warehouse

OLTP — operational database

Runs day-to-day transactions (orders, payments).
Current, detailed data; frequent small read/writes.
Normalized (3NF) to avoid redundancy.
Optimised for fast inserts/updates.

OLAP — data warehouse

Subject-oriented, integrated, time-variant, non-volatile.
Historical, summarised data; read-mostly.
De-normalized (star/snowflake) for fast queries.
Optimised for complex analytical queries.

ETL — Extract, Transform, Load: data is extracted from many source systems, transformed (cleaned, integrated, formatted), and loaded into the warehouse on a schedule. It's the bridge from operational systems to the warehouse.

The OLAP Cube, Live

A data cube stores measures across dimensions — here Product × Time × Location. Click an OLAP operation and watch the cube respond. Each operation answers a different analytical question.

Data cube — OLAP operationsclick an operation

Full cube: every cell holds a measure (e.g. sales) for one Product, Time and Location. OLAP operations reshape this cube to explore the numbers.

Schema Design

A central fact table (measures + foreign keys) surrounded by dimension tables. Toggle between the two classic shapes.

Star vs Snowflake schematoggle ↓

Star schema: one fact table linked directly to de-normalized dimension tables. Simple and fast — the most common design.

Key Concepts

DW characteristics

Subject-oriented, Integrated, Time-variant, Non-volatile (Inmon's four properties).

Fact table

Holds numeric measures and foreign keys to dimensions. Usually the largest table.

Dimension table

Descriptive attributes (who/what/where/when) used to filter and group facts.

Additive measures

Summable across all dimensions (e.g. sales amount). Semi-additive sum across some (e.g. balance, not over time); non-additive never sum (e.g. ratios).

Fact-less fact

A fact table with no measures — records that an event happened (e.g. student attendance).

Star schema

Fact table + de-normalized dimensions. Fewer joins, fast queries.

Snowflake schema

Dimensions normalized into sub-dimensions. Less redundancy, more joins.

Fact constellation

Multiple fact tables sharing dimension tables (a galaxy schema).

OLAP operations

Roll-up, drill-down, slice, dice, pivot — to navigate the cube.

ROLAP / MOLAP / HOLAP

Relational storage / multidimensional arrays / hybrid of both.

Watch & Learn

Pick a clear explainer for each topic — these open curated YouTube searches.

Concept · warehouseData warehouse & OLAP basicsArchitecture, ETL, OLTP vs OLAP and the data cube.Open on YouTube ↗

Concept · modellingStar vs Snowflake schemaFact tables, dimensions and schema design walkthrough.Open on YouTube ↗

Concept · OLAPOLAP operations (roll-up, slice, dice)See the cube operations demonstrated step by step.Open on YouTube ↗

The Data Warehouse

What this unit covers