The event
A customer buys coffee
The checkout captures one observable business event: a product was sold.
Receipt R-9007 · Line 1 · qty 2 · $24
An example-driven field guide
Kimball data modeling techniques
Learn dimensional modeling by growing one tiny retail sale into a model that can answer historical, operational, and enterprise questions—without losing sight of what one row means.
★
The durable idea: capture measurements at a declared business grain, surround them with descriptive context, and reuse that context consistently across business processes.
Chapter 1 · Mental model
Measurements in context
A dimensional model is a deliberate presentation model. It makes business events easy to count, sum, filter, and compare.
The measurement
What happened?
Quantity, sales amount, discount, and cost are numeric facts produced by that event.
Facts: 2 units · $24 gross · $3 discount
The context
Who, what, where, when?
Date, product, store, customer, and promotion dimensions describe the event.
Dimensions: Tuesday · Coffee · Sydney · Maya
Quick test: if a value is used to group or filter, it is probably dimension context. If it is produced by the event and meaningfully aggregated, it is probably a fact. Numeric identifiers such as product codes are still dimensions, not measurements.
Chapter 2 · Foundations
Design in four decisions
Order matters
Do not pick columns first. The grain decides which dimensions and facts are valid.
Choose the process
Model an observable business activity, not a department or a subject noun.
Checkout saleDeclare the grain
Write the exact meaning of one fact-table row in plain language.
One product scan on one completed receiptIdentify dimensions
Ask which descriptions are true for exactly one row at that grain.
Date · Product · Store · Customer · PromotionIdentify facts
Add measurements produced by the process and valid at the declared grain.
Quantity · Sales · Discount · CostBefore the four steps: discovery and collaboration
Business requirements + data realities
Balance what people need to decide with what source systems actually capture.
Example: users want walk-in conversion, but no footfall is recorded; that fact cannot be invented.
Collaborative modeling workshop
Business representatives, source experts, and DW designers make the four decisions together.
Example: finance defines “net sales” while POS engineers confirm discount fields and timing.
Graceful extension
Add compatible dimensions, attributes, facts, or lower-grain rows without invalidating old queries.
Example: add cashier context to each receipt line; existing product-by-store analysis still works.
Business process, not department
“Sales” is vague. Ordering, shipping, invoicing, payment, and return are distinct measurement processes.
Example: order date belongs to ordering; delivery date belongs to fulfilment.
Chapter 3 · Grain
One row means one thing
The grain is a binding contract. Prefer the most atomic event the business captures, then keep every key and measurement faithful to it.
Declared grain
One row per product scan on a completed receipt.
| Receipt | Line | Product | Quantity | Line sales | Why it belongs |
|---|---|---|---|---|---|
| R-9007 | 1 | Coffee beans | 2 | $24 | One product scan |
| R-9007 | 2 | Filter papers | 1 | $6 | Another product scan |
| R-9007 | — | All products | 3 | $30 | Wrong: receipt total mixed with lines |
Header trap
Do not repeat $12 freight
If the receipt-level freight charge is copied to both lines, summing returns $24.
Fix: allocate it to line grain, or keep it in a header-grain fact.Mixed-grain trap
Do not add daily totals
A daily store total represents a different row meaning. Put it in a separate aggregate fact.
Rule: different grains live in different physical fact tables.Chapter 4 · The star
Put the event at the center
Foreign keys and measurements live in the fact table. Wide, flat dimension tables hold the human descriptions used to filter and group those measurements.
fact_sales · receipt-line grain
date_keyproduct_key
store_keycustomer_key
promotion_keyreceipt_number quantitynet_sales
discountcost
dim_date
date_keyday_namemonthfiscal_periodholiday_flag
dim_product
product_keyskuproduct_namebrandcategory
dim_store
store_keystore_namecityregion
dim_customer
customer_keycustomer_idnamecityloyalty_tier
dim_promotion
promotion_keypromotion_namemedia_typediscount_type
Key / relationshipAdditive measureDescriptive attribute
Additive
Safe to sum across every dimension. Net sales and quantity.
Semi-additive
Safe across some dimensions, not time. Daily closing inventory.
Non-additive
Never sum directly. Margin %; divide summed margin by summed sales.
Fact-table foundations: structure, nulls, drill, and aggregation
Fact table structure
Foreign keys plus same-grain measurements; usually narrow and deep.
Each receipt line carries five dimension keys and four amounts.
Nulls in facts
Numeric facts may be null when legitimately unknown. Foreign keys should point to an explicit Unknown dimension row.
Unknown customer → customer_key 0, not a null join.
Conformed facts
Use identical definitions and units across processes—or use different names.
“Net sales” must treat tax and discounts the same in store and web sales.
Drill down
Add lower-level dimension attributes to the grouping; no special physical structure is required.
Year → month → day, or category → brand → product.
Aggregate fact / cube
Pre-summarize atomic facts for speed using conformed rollups.
Monthly brand sales derived from receipt lines.
Consolidated fact
Combine measurements only when their grain is exactly compatible.
Monthly product actual and forecast can share a row; daily SKU sales cannot.
Chapter 5 · Fact patterns
Choose the table that matches time
The same business can need several fact tables because event history, regular state, and workflow progress answer different questions.
Insert-only events
One row per product scan
Rows appear only when a sale happens. This is the atomic, flexible foundation for detailed analysis.
Question: what sold, where, and for how much?
10 AugStore 7Beans$24
10 AugStore 7Filters$6
11 AugStore 3Beans$12
Regular state
One row per store × product × day
Dense snapshots record state for each scheduled period—even when there was no activity.
Closing balance is semi-additive: do not sum it through time.
10 AugBeanssold 8close 42
11 AugBeanssold 0close 42
12 AugBeanssold 5close 37
One evolving workflow row
One row per order line
Insert at order time, then update predictable milestone dates and duration facts as fulfilment advances.
Best for stable workflows, not unpredictable repeated events.
Order 1001 · ordered 10 Aug · packed 11 Aug · shipped 12 Aug · delivered 14 Aug
Ordered
day 0Packed
+1 dayShipped
+2 daysDelivered
+4 days
day 0Packed
+1 dayShipped
+2 daysDelivered
+4 days
The row is the event
Scheduled versus attended
A fact can contain only dimension keys. Count rows to measure occurrence; subtract activity from valid coverage to find non-events.
Build only legitimate coverage combinations, not an unbounded Cartesian product.Coverage
Maya · Coffee class · Monday
Noah · Coffee class · Monday
Attendance
Maya · Coffee class · Monday
Missing: Noah was absent
Fact-pattern atlas: six extensions and anti-patterns
Fact surrogate key
Optional row ID helps ETL resume, backout, and identify a fact row directly.
load_row_id 8421 tracks an interrupted batch insert.
Lag / duration facts
Store milestone lag from a common start; any pairwise duration is subtraction.
ship_lag=2, deliver_lag=4 → transit=2 days.
Header + line facts
Copy header dimension keys and degenerate IDs onto a line-grain fact.
Order channel applies to every line; freight must be allocated.
Multiple currencies
Store transaction-currency and standard-currency facts plus currency context.
€18 local and A$30 standard using the approved order-date rate.
Multiple units of measure
Store agreed base-unit measures and row-level conversion factors; expose audience-specific views.
6 bottles × 750 mL → 4.5 base litres.
Centipede fact table
Avoid many keys for hierarchy levels and tiny flags.
One product key replaces separate brand/category/department keys; a junk dimension absorbs flags.
Numeric attribute or fact?
Calculate with it → fact. Filter/group stable meaning → dimension attribute or band. Sometimes keep both.
package_weight is product context; shipped_weight is an event measure.
Year-to-date
Calculate YTD from atomic facts in the query/semantic layer.
Do not persist sales_ytd, sales_qtd, sales_mtd on every row.
Timespan fact
Rare pattern: effective/expiry dates represent long-lived, slowly changing measurements.
Account credit limit stays $5,000 from Jan–Jun without six identical snapshots.
Measure type dimension
Usually avoid generic measure_name/value rows; they inflate data and hinder math.
Use only when hundreds of possible measures are extremely sparse.
Chapter 6 · Dimension toolkit
Make context human
Dimensions trade storage efficiency for usability. They are deliberately wide, descriptive, and easy to browse.
Identity
Surrogate keys preserve versions
A warehouse-controlled integer identifies one dimension row version. The source ID remains an attribute.
customer_key 101
C-42 · Sydney · effective until 9 Aug
customer_key 205
C-42 · Melbourne · effective from 10 Aug
durable_key P-8
Stable identity shared by both history rows
Calendar
Turn dates into vocabulary
A reusable date dimension carries fiscal periods, holidays, weekdays, and reporting labels.
| date_key | day | month | fiscal | holiday? |
|---|---|---|---|---|
| 20260818 | Tuesday | Aug 2026 | FY27 P02 | No |
Role-playing date
One physical Date dimension appears as Order Date, Ship Date, and Delivery Date through separate keys/views.
order_date_key → Date
ship_date_key → Date
delivery_date_key → Date
ship_date_key → Date
delivery_date_key → Date
Degenerate dimension
A transaction identifier with no descriptive attributes stays in the fact—without a one-column dimension table.
receipt_number = R-9007
Junk dimension
Bundle small flags into one dimension; create observed combinations instead of every possible combination.
profile 17 → coupon: yes · gift wrap: no · mobile: yes
Dimension-pattern atlas: shape, labels, and hierarchies
Natural + durable keys
Natural keys come from sources and can change; durable keys identify the same entity across renumbering and row versions.
Employee 742 and rehired employee 991 share durable person P-18.
Flattened dimension
Put hierarchy labels on each lowest-level row for simpler queries.
Product row repeats brand, category, and department.
Flags + indicators
Meaningful labels belong in dimensions, not unexplained 0/1 fields in facts.
promotion_status = “Active”, not promo_flag = 1.
Null dimension attributes
Use explicit business labels such as Unknown, Not Applicable, or Not Yet Available.
A missing suburb groups under “Unknown suburb” instead of disappearing.
Multiple hierarchies
One dimension may carry alternate valid rollups.
Day → Month → Year and Day → Week → Fiscal Period.
Fixed-depth hierarchy
Use positional columns when level names and depth are stable.
Product → Brand → Category → Department.
Slightly ragged hierarchy
Force a small known depth range into fixed positions using consistent fill rules.
Australia → NSW → Sydney versus Singapore → [country/city].
Ragged hierarchy
Use an ancestor–descendant bridge for arbitrary or shared paths.
An employee may be six levels deep and report through two structures.
Snowflaked dimension
Normalized dimension tables are accurate but harder to browse and usually discouraged in the presentation layer.
Prefer Product with brand/category columns over chained product→brand→category tables.
Outrigger dimension
A dimension-to-dimension reference is a selective exception.
A customer row may reference an account-manager dimension when that reusable context is genuinely separate.
Chapter 7 · Slowly changing dimensions
Decide what history should mean
Maya moves from Sydney to Melbourne on 10 August. The right technique depends on the question historical sales should answer.
Correct or restate
Replace Sydney with Melbourne on the same row. All old sales now report as Melbourne.
101C-42 · Sydney Melbourne
Preserve “as was”
Close the Sydney version and insert a Melbourne version. Facts retain the key effective at event time.
101Sydney · to 9 Aug
205Melbourne · from 10 Aug
Keep one prior view
Store current_city and previous_city on one row. Useful for a limited current-versus-prior comparison.
101current: Melbourne
previous: Sydney
previous: Sydney
Type 2 query lens
Where should old sales appear?
As-was joins each fact to its historical surrogate row. Sales retain the customer city effective when they happened.
The complete SCD ladder: Types 0 and 4–7
Type 0 · Retain original
Never change the attribute.
Original signup channel remains “Store” forever.
Type 4 · Mini-dimension
Split rapidly changing profile attributes; the fact carries base customer and profile keys.
Age band, loyalty tier, and risk band form a small Profile dimension.
Type 5 · Mini + current outrigger
Type 4 plus a Type-1 current-profile reference on the base dimension.
Facts answer tier “as was”; Customer answers current tier without scanning facts.
Type 6 · Historical + current columns
Type-2 rows also carry overwritten current attributes.
Every Maya version retains historical_city and is updated with current_city=Melbourne.
Type 7 · Dual-key access
Facts store version surrogate and durable entity keys; views expose historical or current joins.
customer_key gives “as was”; durable_customer_key gives “as is”.
Type 2 safeguards
Use effective/expiry timestamps and a current flag; allow one current, non-overlapping row per durable identity.
Never update old fact keys to the newest customer row.
Chapter 8 · Complex relationships
When one key is not enough
A classic star expects one member of each dimension per fact row. Use bridges or allocations only when the business relationship genuinely breaks that rule.
Many-to-many example
One treatment, two diagnoses
Treatment fact
T1 · diagnosis_group G10
cost $900
Bridge
G10 → Asthma · 60%
G10 → Allergy · 40%
Diagnosis
Asthma
Allergy
Double-counting guard: joining through a bridge repeats the fact. Use approved weights when apportioning $900, and verify weights sum to 1. Sometimes full event credit is valid for each diagnosis—but the cost still must not be summed twice.
Different-grain example
Allocate header freight to lines
Order freight$12header grain
Coffee machine · 3 kg$9
Filters · 1 kg$3
Reconciliation$12 ✓
The driver—weight, value, volume, quantity—must be business-approved and auditable. Copying $12 to both lines would double it.
Relationship atlas: sets, comments, steps, and special dimensions
Dimension-to-dimension joins
Correlating two Type-2 dimensions through an outrigger can explode versions; a periodic fact can observe their relationship instead.
Monthly customer × account-manager assignment.
Behavior tag series
Persist periodic text classifications when users compare positions over time.
New → Active → Active → Lapsed customer tags.
Behavior study group
Persist an expensive analysis as a durable-key set; combine sets by union, intersection, or difference.
“Bought espresso machine, not beans within 30 days.”
Aggregated fact as attribute
Copy selected lifetime metrics or bands onto a dimension for fast filtering.
Customer lifetime_spend_band = “$1k–$5k”.
Dynamic value banding
Range-join a small band dimension at query time when thresholds change.
Basket $74 joins to “$50–$99” without rewriting facts.
Text comments
Free text belongs in a comments dimension referenced by a fact, not in numeric fact columns.
Return reason note “seal broken on arrival”.
Step dimension
Describe a sequential event’s current step and remaining steps.
Fulfilment step 3 of 5 = Packed; 2 steps remain.
Hot-swappable dimension
Pair one fact with alternative dimension copies when each audience has a private classification.
Investors classify the same stocks using different sector taxonomies.
Abstract generic dimension
Avoid one vague Person or Location dimension for unrelated business roles.
Customer and employee have different attributes and should stay legible.
Chapter 9 · Enterprise integration
Connect processes through shared language
Build incrementally by process. Conformed dimensions let separate fact tables align without forcing them into one giant model.
The bus matrix
Processes × reusable dimensions
| Business process | Date | Product | Customer | Store | Supplier |
|---|---|---|---|---|---|
| Sales receipt line |
✓ | ✓ | ✓ | ✓ | |
| Returns returned line |
✓ | ✓ | ✓ | ✓ | |
| Inventory product/store/day |
✓ | ✓ | ✓ | ||
| Purchasing PO line |
✓ | ✓ | ✓ |
Read across to design one process. Read down to see which dimensions need enterprise agreement. A check mark promises shared definitions, values, and governance—not merely a shared column name.
Drilling across
Compare sales and returns safely
1 · Aggregate Sales
Group sales by conformed Month + Brand.
2 · Aggregate Returns
Group returns independently by the same labels.
3 · Merge result sets
Align identical row headers; calculate return rate.
Never join atomic fact tables directly. Multiple sales × multiple returns for the same product creates fan-out and inflated amounts. Aggregate separately, then sort/merge on conformed labels.
Integration atlas: rollups, value chains, and roadmap tools
Conformed dimension
Attributes and domain values mean the same thing across fact tables.
Product Brand is identical in Sales, Returns, Inventory, and Purchasing.
Shrunken rollup dimension
A row/column subset inherits vocabulary from a base conformed dimension.
Month rolls up Date; Brand rolls up Product for monthly forecasts.
Conformed fact
A measure has identical business meaning and units across processes.
If web “revenue” includes tax but store “revenue” does not, rename rather than falsely conform.
Value chain
Map the natural sequence of measurement processes.
Purchase → Receive → Stock → Sell → Return.
Bus architecture
Deliver manageable process increments integrated by conformed dimensions; it is not tied to a platform.
Ship Sales first, then Returns reusing Product, Customer, Store, and Date.
Opportunity / stakeholder matrix
Cross candidate initiatives with interested groups to prioritize delivery.
Returns analytics helps Store Ops, Finance, and Merchandising—high shared value.
Multipass SQL
Technical implementation of drilling across: independent aggregates, then merge.
Sales pass + inventory pass joined only after both reach Month × Brand.
Chapter 10 · Operations + edge cases
Design for imperfect reality
Feeds arrive late, definitions change, loads fail, and some products refuse a single schema. These techniques keep the presentation model honest.
Late fact
Use event time
A 5 Aug sale arrives on 15 Aug. Maya moved on 10 Aug, so look up the customer row effective on 5 Aug: Sydney key 101.
occurred 5 Aug → key 101
loaded 15 Aug ≠ current key 205
Load time is not historical truth.
loaded 15 Aug ≠ current key 205
Late dimension
Create a placeholder
A sale arrives for C-77 before customer details. Create one placeholder with Unknown labels, attach the fact, then Type-1 fill it later.
key 301 · C-77 · “Details not yet available”
Reuse the placeholder; do not create one per fact.
Operational atlas: lineage, real time, product variation, and quality
Audit dimension
Tag fact rows with load metadata and quality outcomes.
run 882 · code v3.4 · source POS-A · currency rule FX-17 · passed 6 checks.
Multiple time zones
Carry universal and local role-playing date/time keys.
Checkout: 23:30 UTC on 17 Aug = 09:30 Sydney on 18 Aug.
Real-time fact table
Keep a small hot partition minimally indexed; blend it with stable historical partitions.
Today’s orders refresh every minute; closed days remain optimized.
Error event schema
Use an ETL-only fact at one detected error plus detail rows for implicated columns.
Error 88: invalid currency on order 1001; details identify source field and rejected value.
Supertype + subtype schemas
Keep common measurements in a core schema and specialized facts in product-specific schemas.
All accounts share balance; Mortgage adds LTV/term, Checking adds overdraft/fee plan.
Profit + loss allocation
Revenue minus allocated component costs enables atomic profit, but allocation governance is substantial.
Product profit includes weight-allocated freight and activity-allocated warehouse cost.
Derived behavior + bands
Move expensive, shared classifications into governed ETL only when query-time derivation is too costly.
Nightly lifecycle tag; quarterly lifetime-value band.
Retroactive Type 2 correction
Insert the correctly dated version and restate affected fact foreign keys.
A city change effective 1 Aug is received on 20 Aug; re-key facts from the missing interval.
Chapter 11 · Apply it
A design review you can reuse
Walk these questions in order. If an early answer is fuzzy, later schema details are premature.
What observable process are we measuring?
Name a verb: order, ship, receive, pay, return—not a team, report, or entity.
What exactly does one row mean?
Declare every atomic qualifier. Test three sample rows against the sentence.
Does every key have one value per row?
If not, revisit grain or deliberately design a bridge.
Does every fact belong at that grain?
Allocate header facts, or move different-grain measures to another table.
How may each fact aggregate?
Label additive, semi-additive, and non-additive behavior; store additive components.
What should a changed attribute do to history?
Choose Type 0, 1, 2, 3, or an advanced hybrid per attribute—not per whole dimension.
Which dimensions must conform?
Use the bus matrix to expose shared vocabulary across process fact tables.
How will late, unknown, and bad data behave?
Define Unknown rows, placeholders, event-time lookups, audit context, and reconciliation.
Final test: give the model to an analyst with one business question. Can they find the right fact, understand one row, choose descriptive labels, and aggregate without hidden rules? If yes, the design is doing its job.
Chapter 12 · Continue learning
Canonical sources
This lesson paraphrases the Kimball Group’s official technique pages. The examples and teaching sequence are original to this page.
- Complete dimensional modeling techniques index (all 81)
- Four-step dimensional design process
- Grain
- Star schemas and OLAP cubes
- Additive, semi-additive, and non-additive facts
- Transaction fact tables
- Periodic snapshot fact tables
- Accumulating snapshot fact tables
- Dimension surrogate keys
- Slowly changing dimension Type 2
- Multivalued dimensions and bridge tables
- Conformed dimensions
- Drilling across
- Enterprise data warehouse bus matrix
- Late-arriving facts
- Late-arriving dimensions
Kimball Group is the source of the modeling techniques and terminology. No text on this page is intended as a substitute for the canonical descriptions or The Data Warehouse Toolkit.