Facts, measures and dimensions
The vocabulary of dimensional modelling: fact, grain, measure and its additivity, dimension, attribute, hierarchy, time dimension, degenerate dimension, and the four design steps.
- 25 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
- Understand
A few cards from the deck
Fact table: what is it?
Show the answer
The central table of an analytical model: it keeps one row per measured event (a sale, a shipment) with its figures and the keys that tie it to its context.
It is the biggest table in the model, often millions of rows. It holds almost only keys and figures; the descriptive text lives next to it.
A sales table mixes rows meaning “one product on one receipt” and rows meaning “receipt total”. What is the problem?
- The grain is not uniform: a sum counts the same amount twice
- The receipt should be a separate dimension with its own table
- The table does not have enough dimensions to describe a receipt
- Receipt totals are non-additive measures that have to be removed
Show the answer
The grain is not uniform: a sum counts the same amount twice
In the table, a receipt's total and its lines say the same thing at two levels. Summing the amount column doubles the revenue. The total is computed by grouping the rows at the fine grain.
To design a dimensional model, you choose in this order the business …, the …, the …, then the ….
Show the answer
To design a dimensional model, you choose in this order the business process, the grain, the dimensions, then the facts.
The grain comes before the dimensions: until you have said what a row is, you cannot know which dimensions it carries or which figures it can hold.
Measure: what is it?
Show the answer
A numeric value recorded at each event, on which totals, averages or counts are computed: an amount, a quantity.
A numeric column is not necessarily a measure: a ticket number or a postcode are numbers, but summing them means nothing.
A manager wants to know which products are bought together on the same receipt. Which grain should the sales table have?
- One row per receipt, with its total
- One row per store and per day
- One row per customer and per month
- One row per product and per receipt
Show the answer
One row per product and per receipt
To see which products end up on the same receipt, you need a row per product on each receipt. At the other grains, a receipt's products are already merged into a total: they can no longer be separated.