Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 39 ยท Getting Started

Data Warehouses and OLAP

Understand data warehouse components, star schemas, ETL, metadata and OLAP operations like roll-up, drill-down and slice, with pandas examples you can run.

Data mining needs data, and in a company that data lives in many separate systems: sales, billing, support, web logs. A data warehouse collects it into one place, organized for analysis rather than for day-to-day transactions. Mining on top of a good warehouse is much easier, which is why the two ideas are taught together.

A classic definition says a warehouse is subject-oriented (organized around topics like sales or customers), integrated (one consistent format), time-variant (history is kept) and non-volatile (data is added, not overwritten).

OLTP versus OLAP

OLTP (operational) OLAP (analytical)
Purpose Run the business: record a sale Analyze the business: sales by region and month
Operations Many small reads and writes Few large read-only queries
Data Current, detailed Historical, summarized and detailed
Design Normalized tables Star or snowflake schema
Users Clerks, applications Analysts, managers

Components of a data warehouse

Warehouse building blocks

Source systems Databases, files, logs.
ETL Extract, transform, load.
Warehouse storage Central, historical tables.
Access tools OLAP, reports, mining.

Metadata describes every layer.

1 / 2
  • ETL (extract, transform, load) pulls data from sources, cleans and unifies it (the same issues as in Data Cleaning), and loads it on a schedule.
  • Metadata is data about the data. Technical metadata records tables, columns, types and ETL rules. Business metadata gives definitions ("active customer = purchase in the last 90 days"). Operational metadata records load times and row counts. It is how analysts find and trust data.
  • Data marts are smaller warehouses for one department.
  • Staging area: temporary space where raw data is cleaned before loading.

Star schema

Warehouses model data as a central fact table (measurable events: one row per sale, with numbers like amount) surrounded by dimension tables (descriptive context: product, store, date, customer). Joining facts to dimensions lets you slice measures any way you like.

flowchart TD
    F["Fact: sales<br/>amount, quantity"]
    P["Dim: product"] --- F
    S["Dim: store"] --- F
    D["Dim: date"] --- F
    C["Dim: customer"] --- F

A star schema. A snowflake schema further normalizes the dimensions.

Python Build a small star schema with pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

OLAP operations

OLAP (online analytical processing) views data as a cube: measures such as amount along dimensions such as product, city and month. The standard operations are:

  • Roll-up: aggregate to a coarser level (city to country, month to year).
  • Drill-down: the opposite, to finer detail.
  • Slice: fix one dimension to one value (only January).
  • Dice: select a sub-cube by several conditions.
  • Pivot (rotate): swap which dimensions form rows and columns.
Python OLAP operations in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Storage models

  • ROLAP stores the cube in relational tables and generates SQL on demand: scales to large data, slower queries.
  • MOLAP precomputes aggregates in a multidimensional array: fast queries, more storage, less flexible.
  • HOLAP mixes the two.

Modern columnar engines and cloud warehouses blur these lines, but the vocabulary and the star schema remain.

From warehouse to mining

OLAP answers questions you already know to ask ("sales by region"). Data mining discovers what you did not think to ask ("these customers behave alike"). They complement each other: OLAP to browse and form hypotheses, mining to dig into them. A warehouse also gives mining a head start: data is already integrated and consistent, so the later preprocessing steps are lighter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a fact table contain?

    Fact tables hold measures like amount and quantity, plus keys linking to dimension tables.
  2. 2. Aggregating monthly sales up to yearly sales is a:

    Roll-up moves to a coarser level of a dimension hierarchy.
  3. 3. ETL stands for:

    ETL pulls data from sources, transforms it and loads it into the warehouse.
  4. 4. Which is a property of a data warehouse?

    Warehouses are subject-oriented, integrated, time-variant and non-volatile.

Summary

  • A data warehouse integrates historical data for analysis; OLTP systems record transactions.
  • Components: sources, staging, ETL, storage, metadata, data marts and access tools.
  • Star schemas have one fact table and several dimension tables.
  • OLAP operations: roll-up, drill-down, slice, dice, pivot.
  • OLAP browses known questions; mining discovers new patterns.

Next: Data Cleaning