An independent study reference written by Dr Phuc V. Nguyen. It is not official subject material — for assessment requirements always follow your subject outline and vUWS.
The star schema
A star schema organises analytical tables around one central fact table surrounded by dimension tables. The fact table holds the numbers you measure plus one foreign key per dimension, and it has a single declared grain, meaning what one row represents. Dimensions hold the descriptive attributes you filter and group by, kept deliberately wide and denormalised so a query joins once instead of walking a chain of lookup tables. The design trades some storage and some redundancy for query speed and, more importantly, for readability. A business user can see which table holds products and which holds dates without reading a data model.
Try it yourself
A shipping charge belongs to an order, not to a line. Choose where to store it, then itemise an order more finely and watch which numbers move. The rule to hold onto is that every measure in a fact table must be true at the declared grain.
| Order | Lines | Shipping charged | Reported |
|---|---|---|---|
| O1 | 4 | A$8.00 | A$32.00 |
| O2 | 1 | A$9.50 | A$9.50 |
| O3 | 2 | A$12.00 | A$24.00 |
| O4 | 2 | A$7.00 | A$14.00 |
| O5 | 1 | A$6.50 | A$6.50 |
| O6 | 2 | A$9.00 | A$18.00 |
| O7 | 2 | A$11.00 | A$22.00 |
| O8 | 2 | A$7.50 | A$15.00 |
| O9 | 1 | A$6.00 | A$6.00 |
Itemising splits the same 600 units of flat white pods across more lines. The customer paid the same amount either way.
The schema is a stylised toy, small enough to check by hand, not a real company. Every cell, subtotal and total above is recomputed from the fact rows whenever a control changes. Money is held in integer cents and rounded only where it is shown. The daily cash balances come from a fixed seeded generator, so they are the same every time this loads.
Why it matters
Picture a receipt in the middle of a table with reference cards fanned out around it. The receipt is the fact: what was bought, how many, how much. Each card describes one thing the receipt points at, the product, the store, the customer, the date. To answer a question you pick up the receipt and one or two cards. You never have to trace a chain of cards to find out what a product category is called, because the card already says it.
A fact table has a grain of one row per order line. Which measure can safely live in it?
Formulas
Worked examples
A team builds a sales fact table at a grain of one row per order line, and includes the order-level shipping charge on every row.
Shipping is charged once per order, so summing it over lines multiplies it by the number of lines. An order with four lines and eight dollars of shipping reports thirty-two dollars. Two fixes work. Allocate the shipping charge across the lines so it sums correctly, or hold it in a separate fact table at order grain. The second fix only holds if each table is aggregated on its own grain first and the two results are then combined on the order key. Joining the order-grain table straight onto the line-grain table repeats the charge on every line again, which is the original fault in a new place. The rule that catches this before it ships is simple: every measure in a fact table must be true at the declared grain.
A customer moves from Parramatta to Newcastle. The team overwrites the suburb in the customer dimension, and last year's regional sales report changes.
Overwriting is a type 1 change. It keeps only the current truth, so history is restated as though the customer had always lived in Newcastle. If last year's report must stay stable, the dimension needs a type 2 treatment: close the old row with an end date, insert a new row with a new surrogate key, and let each fact row point at the version that was current when the sale happened. The choice is a business decision about whether history is allowed to move.
Common mistakes
- ✗Denormalising dimensions is bad design because it repeats data. Normalisation protects update integrity where rows change constantly, and a warehouse dimension is written by one controlled pipeline, so read speed and readability are worth the redundancy.
- ✗The grain can be settled later. Grain determines which measures are valid and what a row count means, so choosing it after the table exists usually means rebuilding the table.
- ✗A snowflake schema is always better because it is more normalised. Snowflaking saves space on very large dimensions and helps with shared hierarchies, and it adds joins and makes the model harder for business users to read.
- ✗Facts are always individual transactions. Periodic snapshot facts, such as end-of-day inventory, and accumulating snapshot facts, which track a process through milestones, are also facts, and they behave differently when summed over time.
Revision bullets
- •Fact table in the centre: measures plus one foreign key per dimension
- •Dimensions hold descriptive attributes, wide and denormalised
- •Grain is declared first: what one fact row represents
- •Every measure in the table must be true at the declared grain
- •Type 2 slowly changing dimensions preserve history, type 1 overwrites it
- •Snowflaking normalises dimensions at the cost of extra joins and readability
Quick check
A fact table has a grain of one row per order line. Which measure can safely live in it?
A retailer needs last year's regional reports to stay unchanged even when customers relocate. Which dimension treatment does that require?
Connected topics
More in Data Foundations
Sources
- Kimball & Ross (2013)Kimball, R., & Ross, M. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd ed. Wiley, 2013.The standard treatment of grain, fact and dimension tables, and slowly changing dimension types.
- Codd, E. F. "A Relational Model of Data for Large Shared Data Banks." Communications of the ACM, 13(6), 377-387, 1970.The relational foundation whose normalisation goals a dimensional model deliberately relaxes for read performance.