Modeling for analytics

Pro lesson Intermediate

Facts and dimensions

Separate what happened from what it happened to.

10 min9 hands-on steps

In this lesson you will

  • Tell measures from descriptions in a wide table
  • Split it into dimension tables and a fact table at a declared grain
  • Change a description in one place, and know which numbers you must never add up

Words you'll meet

Measure
A number about an event that you add up, like quantity or amount.
Fact table
One row per event at a declared grain: keys pointing at dimensions, plus measures.
Dimension table
One row per thing (a customer, a store) with the columns that describe it. You filter and group by them.
Additive
A measure you can add up across any dimension and still get a true total. Prices, averages and ratios are not.
UPDATE
Changes values in rows that already exist: UPDATE table SET column = value WHERE test.

The steps

Each step teaches one idea, then has you try it hands-on in your browser, and checks your work.

  1. 1One wide table
  2. 2Two kinds of columns
  3. 3Build a dimension
  4. 4One row per key
  5. 5Build the fact table
  6. 6Join it back
  7. 7Change it in one place
  8. 8Not every number adds up
  9. 9Challenge

This lesson is part of LearnDE Pro

$12 a month or $100 a year: every lesson, spaced-repetition flashcards, and Learn by teaching. Refund on request within 14 days.