Join tables into a model, in this page, and see why a wrong join breaks the totals.
THE TABLES
Two Facts, Four Dimensions
Facts hold the numbers. Dimensions hold the words you slice them by. Every table shows its grain, which is what one row of it represents.
Fact
sales
one row per order line
order_iddate_keyproduct_keystore_keyunitsvalue
Fact
returns
one row per returned item
return_iddate_keyproduct_keystore_keyunitsrefund
Dimension
product
one row per product
product_keyproductcategoryprice
Dimension
store
one row per store
store_keystoreregioncountry
Dimension
date
one row per day
date_keydatemonthquarteryear
Dimension
customer
one row per customer
customer_keycustomersegmentcountry
THREE EXERCISES
Join, Check, Learn
Click one table, then another, to join them. A wrong join is explained rather than blocked, because the explanation is the lesson.
0 of 3 exercises solved
1
Exercise 1 of 3
Join a fact to a dimension
Not attempted
A star schema has one fact table in the middle, holding the numbers, and dimension tables around it holding the words you slice by. Each join uses a key that is unique in the dimension and repeated in the fact.
Your task
Join the sales fact to the product dimension, so you can report revenue by category.
Hint
Both tables carry product_key. It is unique in product and repeated in sales.
What this tells you: One key, unique on the dimension side. That is the whole pattern, repeated for every dimension.
2
Exercise 2 of 3
Build the full star
Not attempted
A fact usually joins to several dimensions at once. Each one adds a way to slice the same numbers, without changing them.
Your task
Join sales to product, store and date, so revenue can be sliced by category, region and month.
Hint
Three separate joins, all from sales. Dimensions never join to each other.
What this tells you: Every dimension hangs off the fact. That shape is what makes a star readable and fast.
3
Exercise 3 of 3
Two facts, shared dimensions
Not attempted
When two facts share a dimension, it is called a conformed dimension: the same product list explains both tables, so results line up.
Your task
Connect returns to the same product and store dimensions that sales uses, so returns and sales can be compared.
Hint
Do not join returns to sales. Both should point at the shared dimensions.
What this tells you: Facts never join to each other. They meet through the dimensions they share.
WHY GRAIN MATTERS
The Mistake That Doubles Your Totals
Grain is what one row represents. Join two tables at different grains and rows multiply, quietly.
One order, three lines
An orders table has one row per order. An order lines table has one row per item. Join them and the order value repeats on every line.
The symptom
Totals look too high, and get worse as you add detail. A £2,880 month becomes £6,400 when you add the product breakdown.
The fix
Aggregate to a common grain before joining, or join through a dimension rather than fact to fact.
Build Your Confidence with Data
Every skill path on Insyt is built around practice like this.
