FIT5137 Chap.2 Star Schemas and Fact Design
Star Schemas and Fact Design
Star Schemas and Fact Design centres dimensional modelling on one question: what does a fact row represent? The answer is the grain, and every dimension key and measure must describe that same event. A star schema places measurable events in a fact table and descriptive context in dimensions, giving analysts simple grouping paths without sacrificing meaning.
This chapter distinguishes an event grain from a report summary, tests whether measures are additive across dimensions and explains why descriptive attributes belong in dimensions rather than repeated fact columns. It also covers role-playing dimensions, warehouse surrogate keys, unknown members, and aggregation layers.
The design discipline is prospective: identify what future question a grain would prevent before discarding detail. A schema diagram becomes defensible only when its grain, relationship cardinalities, measure behaviour and source-to-fact reconciliation can be explained in words and tested with sample events.
What this chapter covers
- 01
Select one measurable business process
- 02
Declare one fact row in a complete sentence
- 03
Choose dimensions that describe that event
- 04
Place descriptive attributes in dimensions
- 05
Test each measure for valid additivity
- 06
Distinguish date roles and unknown members
- 07
Reconcile qualifying source events to fact rows
- 08
Preserve detail needed by plausible future questions
Worked example · free
Detect fare duplication after a grain mismatch
- 1State the stored grain: one row represents one trip-stop participation, not one trip.
- 2The table stores four rows for the trip and repeats $18 on each row. Summing fare gives 4 times $18, which equals $72.
- 1Compare with the recorded trip fare of $18. The overstatement is $72 minus $18, which equals $54.
- 2Repair by keeping fare in a one-row-per-trip fact and storing stop participation in a separate fact, unless a documented allocation is required.
- 1Reconcile the trip fact so the one qualifying trip contributes exactly $18 before any stop analysis.
Key terms
- Star schema
- A dimensional model with a central fact table connected to descriptive dimensions.
- Fact table
- A table of events or states at a declared grain, carrying dimension keys and measures.
- Dimension table
- A table of descriptive attributes used to filter, group and interpret facts.
- Additive measure
- A measure that can be summed meaningfully across the relevant dimensions.
- Role-playing dimension
- One shared dimension joined to a fact under distinct roles such as origin and destination.
- Unknown member
- A managed dimension row used when context is unresolved without discarding the fact.
Star Schemas and Fact Design FAQ
What is a good fact-grain sentence?
It names one repeatable business occurrence, such as one completed hire identifier or one maintenance work order. Phrases like monthly performance or customer analysis describe reports, not rows. The sentence should let another person decide whether a candidate record belongs in the fact.
Can two processes share one fact table?
Only when they genuinely have the same row meaning and compatible dimensions and measures. Booking, completed journey and maintenance are related but distinct events. Combining them usually creates ambiguous rows. Separate facts can still be compared through conformed dimensions after aggregation to compatible levels.
Why keep attributes out of the fact?
Attributes such as vehicle type or station region describe dimension members and repeat across many events. Centralising them reduces duplication and provides one governed place for corrections and history. An attribute that varies by event may belong in the fact context, so the determinant must be tested.
Should I always keep maximum detail?
Detailed facts preserve analytical options but incur storage, privacy and processing costs. Choose the finest reliable detail needed by plausible questions, then build summaries for performance when justified. Keep the detailed layer as a reconciliation authority and document what each aggregate discards.
Assessment move
For each schema, cover the labels and reconstruct it from the grain sentence. Ask whether every dimension describes the same row and whether every measure is valid at that level. Work a one-row example and then a duplicate-producing counterexample. Practise separating a transaction fact from participation or snapshot facts. Build a small bus matrix that marks processes against candidate dimensions and date roles.
In written answers, name the analytical consequence of a defect: duplicated totals, ambiguous counts, inconsistent history or a lost drill path. Extend each drill with an aggregation question. Sum the measure across time, product and location, noting where its meaning fails. If a ratio is proposed, write its additive numerator and denominator.
If a snapshot is proposed, ask which instant it represents and whether summing across dates is valid. Redraw one flawed schema after changing the grain and list every column that must move or disappear. Then test source mapping with three events: an ordinary record, a missing dimension key and a late correction. This forces the diagram to confront loading behaviour rather than remaining an abstract picture.
Keep the final grain sentence beside the schema throughout revision. Finish by comparing two legitimate designs for the same process: a detailed transaction fact and a daily summary. List the queries each supports, the controls connecting them and the questions lost in the summary. This contrast prevents the convenient aggregate from being mistaken for the recorded event model.
Explain why both layers can be valid simultaneously while serving different questions, refresh cycles and performance needs.
