Unit 1 — Data Warehousing and OLAP

Topics Covered

Syllabus topics: Introduction to Data Warehouse, differences between database systems and data warehouse, data warehouse characteristics, data warehouse architecture and its components, data modeling, schema design, star and snowflake schema, fact constellation, fact table, OLAP cube, OLAP operations.

1.1 What a data warehouse is

1.2 Database systems versus data warehouses

1.3 Architecture and components

   SOURCES              STAGING              WAREHOUSE            ACCESS
┌────────────┐      ┌─────────────┐      ┌──────────────┐    ┌────────────┐
│ OLTP DBs   │      │             │      │              │    │ Reporting  │
│ Flat files │─────►│  EXTRACT    │─────►│ Central      │───►│ OLAP tools │
│ ERP / CRM  │      │  TRANSFORM  │      │ warehouse    │    │ Data mining│
│ Web logs   │      │  LOAD       │      │              │    │ Dashboards │
│ External   │      │  (ETL)      │      │ ┌──────────┐ │    └────────────┘
└────────────┘      └─────────────┘      │ │Data marts│ │
                           │             │ └──────────┘ │
                           ▼             └──────────────┘
                    ┌─────────────┐             │
                    │  METADATA   │◄────────────┘
                    └─────────────┘

ETL

Transform is where the work is. Two systems calling the same customer Asha Kumari and KUMARI, ASHA; one storing dates as DD/MM/YYYY and another as YYYY-MM-DD; three definitions of "active customer". Integration means resolving all of that before the data lands, because after it lands every report inherits the mess.

⚠️ ETL versus ELT

Modern cloud warehouses often invert the last two steps: ELT loads raw data first and transforms inside the warehouse, using its own compute. The syllabus teaches ETL, which is correct and still ubiquitous; knowing that ELT exists and why — cheap elastic compute makes it viable — is the kind of remark that lifts an answer.

Three-tier architecture

1.4 Data modeling: the multidimensional model

⚠️ Decide the grain first

"What does one row of the fact table mean?" must be answered before anything else is designed, in one sentence, and it must be the finest grain you can afford. Store daily totals and you can always aggregate to monthly; store monthly and the daily question is unanswerable forever.

Getting this wrong is the most expensive mistake in warehouse design, because correcting it means reloading everything.

1.5 Fact tables and dimension tables

-- Dimension: wide, descriptive, relatively few rows
CREATE TABLE DimProduct (
  product_key   INT PRIMARY KEY,     -- SURROGATE key, not the source's SKU
  sku           VARCHAR(20),         -- the natural/business key
  product_name  VARCHAR(100),
  brand         VARCHAR(50),
  category      VARCHAR(50),
  department    VARCHAR(50)
);

-- Fact: narrow, numeric, enormous
CREATE TABLE FactSales (
  product_key  INT REFERENCES DimProduct(product_key),
  store_key    INT REFERENCES DimStore(store_key),
  date_key     INT REFERENCES DimDate(date_key),
  customer_key INT REFERENCES DimCustomer(customer_key),
  quantity     INT,                  -- measure
  amount       DECIMAL(12,2),        -- measure
  discount     DECIMAL(12,2),        -- measure
  PRIMARY KEY (product_key, store_key, date_key, customer_key)
);

Types of fact table

A factless fact table records that something happened when there is nothing to measure — attendance, a promotion being in effect. Counting rows is the measure. It is a favourite two-mark question precisely because the name sounds like a contradiction.

1.6 Star, snowflake and fact constellation

              DimDate
                 │
  DimProduct ── FactSales ── DimStore
                 │
             DimCustomer

SELECT p.category, s.city, SUM(f.amount)
FROM   FactSales f
JOIN   DimProduct p ON p.product_key = f.product_key
JOIN   DimStore   s ON s.store_key   = f.store_key
GROUP  BY p.category, s.city;

Snowflake schema

The dimensions are normalised into sub-dimensions, so hierarchies become separate tables.

                        DimDate
                           │
DimDepartment ─ DimCategory ─ DimProduct ─ FactSales ─ DimStore ─ DimCity ─ DimState

SELECT c.category_name, ci.city_name, SUM(f.amount)
FROM   FactSales f
JOIN   DimProduct  p  ON p.product_key  = f.product_key
JOIN   DimCategory c  ON c.category_key = p.category_key   -- extra join
JOIN   DimStore    s  ON s.store_key    = f.store_key
JOIN   DimCity     ci ON ci.city_key    = s.city_key       -- extra join
GROUP  BY c.category_name, ci.city_name;

Fact constellation (galaxy schema)

Multiple fact tables sharing dimensions. The shared dimensions are called conformed dimensions, and they are what make cross-process analysis possible.

   DimProduct ─────┬───── FactSales ───── DimStore
        │          │                         │
        └──── FactInventory ─────────────────┘
                   │
                DimDate  (shared by both)

With DimProduct and DimDate conformed across both facts, "how did stock levels affect sales?" becomes answerable. Without conformed dimensions the two marts cannot be compared at all, and you have two truths in one company.

⚠️ This unit contradicts Database Management Systems, deliberately

Database Management Systems taught you to normalise: eliminate redundancy, avoid update anomalies, reach 3NF. Star schemas throw that away — DimProduct repeats the category name on every row of the same category, exactly the redundancy 2NF forbids.

It is not a mistake, and saying why earns marks. Normalisation optimises for writes: many small updates, each touching one place. A warehouse barely writes — it loads on a schedule, then serves millions of reads. Denormalising trades cheap storage for fewer joins, and a query with three joins beats one with nine.

The update anomalies normalisation prevents simply do not arise, because users never update. The load process rewrites the dimension, all rows at once.

Storage is cheap; analyst time is not. That single sentence is the whole justification.

1.7 The OLAP cube

A cube arranges measures along dimensions. Three dimensions make a literal cube; more than three is a hypercube, and the name persists anyway.

                 Product
                   ▲
                   │  ┌────┬────┬────┐
                   │ ╱    ╱    ╱    ╱│
                   │┌────┬────┬────┐ │
                   ││    │    │    │ │ ──► Time
                   │├────┼────┼────┤ │
                   ││ 45 │ 62 │ 51 │ │     each cell = a measure
                   │└────┴────┴────┘╱      e.g. sales of Product P
                   ╱                       in Store S in Month M
              Store

Each cell holds the measures for one combination of dimension members.

Cubes are sparse. A shop with 50,000 products, 200 stores and 1,000 days has ten billion possible cells, but most products do not sell in most stores on most days. Real cubes are often over 95% empty, which is exactly why MOLAP's dense array storage struggles and ROLAP — which stores only the rows that exist — scales better.

Lattice of cuboids

For n dimensions there are 2ⁿ cuboids, from the finest (the base cuboid, all dimensions) to the coarsest (the apex cuboid, a single grand total).

With Product, Store and Time — n = 3, so 2³ = 8 cuboids:

                 (apex: grand total)
                 /        |        \
          (Product)    (Store)    (Time)
            /    \      /    \     /   \
   (Product,Store) (Product,Time) (Store,Time)
                 \        |        /
              (Product, Store, Time)   ← base cuboid

Pre-computing all 2ⁿ cuboids makes every query instant and is usually impossible — with 10 dimensions that is 1,024 cuboids, and with hierarchies far more. Choosing which to materialise is the central engineering problem of cube design.

1.8 OLAP operations

Five operations. Each is a guaranteed exam question, and each is easiest to learn with a concrete before-and-after.

-- from
SELECT city,  SUM(amount) FROM ... GROUP BY city;
-- to
SELECT state, SUM(amount) FROM ... GROUP BY state;

SELECT product, store, SUM(amount) FROM ... WHERE quarter = 'Q1-2026' GROUP BY product, store;

⚠️ Slice versus dice

The distinction is examined constantly and is exactly this:

Slice reduces the dimensionality; dice reduces the size but keeps the shape.

Other operations

• Drill-across — query two fact tables through their conformed dimensions (this needs a constellation schema).

• Drill-through — go past the cube to the underlying detail rows.

SQL's cube extensions

SELECT category, city, SUM(amount)
FROM   sales
GROUP  BY ROLLUP (category, city);      -- subtotals per category + grand total

SELECT category, city, SUM(amount)
FROM   sales
GROUP  BY CUBE (category, city);        -- ALL 2^2 = 4 combinations

ROLLUP(a, b) gives the hierarchy (a,b), (a), () — 3 groupings. CUBE(a, b) gives all four: (a,b), (a), (b), (). For n columns, ROLLUP produces n + 1 groupings and CUBE produces 2ⁿ.

Practice problems

CREATE TABLE DimStudent (
  student_key INT PRIMARY KEY, roll_no VARCHAR(15),
  name VARCHAR(80), gender CHAR(1), category VARCHAR(20),
  programme VARCHAR(40), department VARCHAR(40), faculty VARCHAR(40));

CREATE TABLE DimSubject (
  subject_key INT PRIMARY KEY, subject_code VARCHAR(12),
  subject_name VARCHAR(80), credits INT,
  course_number INT, semester INT, year INT);

CREATE TABLE DimTime (
  time_key INT PRIMARY KEY, exam_date DATE,
  session VARCHAR(20), month VARCHAR(12), quarter CHAR(2), academic_year VARCHAR(9));

CREATE TABLE DimFaculty (
  faculty_key INT PRIMARY KEY, staff_id VARCHAR(12),
  name VARCHAR(80), designation VARCHAR(30), department VARCHAR(40));

CREATE TABLE FactResult (
  student_key INT, subject_key INT, time_key INT, faculty_key INT,
  marks_internal INT,          -- additive
  marks_external INT,          -- additive
  marks_total    INT,          -- additive
  credits_earned INT,          -- additive
  attendance_pct DECIMAL(5,2), -- NON-additive: never SUM this
  PRIMARY KEY (student_key, subject_key, time_key));

-- (a) roll-up
SELECT country, SUM(amount) FROM sales_cube GROUP BY country;

-- (b) slice
SELECT product, city, SUM(amount) FROM sales_cube
WHERE quarter = 'Q1' GROUP BY product, city;

-- (c) dice
SELECT product, state, quarter, SUM(amount) FROM sales_cube
WHERE category IN ('Dairy','Bakery')
  AND state    IN ('Andhra Pradesh','Telangana')
  AND quarter  IN ('Q1','Q2')
GROUP BY product, state, quarter;

-- (d) drill-down
SELECT year, quarter, SUM(amount) FROM sales_cube GROUP BY year, quarter;

-- (e) pivot -- no aggregation change, presentation only
SELECT * FROM (SELECT month, product, amount FROM sales_cube)
PIVOT (SUM(amount) FOR month IN ('Jan','Feb','Mar'));

Time: 4 levels → 5 choices
Three others: 3 levels each → 4 choices each
Total = 5 × 4 × 4 × 4 = 320 cuboids

Exam questions from this unit

Two marks

1. Define a data warehouse (Inmon's definition).
2. What does "non-volatile" mean for a data warehouse?
3. Distinguish OLTP from OLAP.
4. Distinguish a fact table from a dimension table.
5. Distinguish slice from dice.
6. What is a factless fact table?
7. What is granularity, and why fix it first?
8. What is a conformed dimension?
9. Distinguish additive, semi-additive and non-additive measures.
10. How many cuboids does an n-dimensional cube have?

Five marks

1. Explain the four characteristics of a data warehouse.
2. Explain the three-tier data warehouse architecture with a diagram.
3. Explain ETL with an example of each stage.
4. Explain the five OLAP operations with examples.
5. Compare star and snowflake schemas.
6. Explain ROLAP, MOLAP and HOLAP.
7. Explain data warehouse, data mart and data lake.

Ten marks

1. Design a star schema for a given business case, state the grain, and explain every table and key decision.

2. Explain star, snowflake and fact constellation schemas exhaustively, with diagrams and a comparison.

3. Explain the data warehouse architecture and all OLAP operations with a worked cube.

Mistakes that cost marks