# Kimball Concepts Reference

> Core dimensional modelling concepts for consultant reference.
> Based on Ralph Kimball's "The Data Warehouse Toolkit".

---

## The Four Steps of Dimensional Modelling

Every fact table is designed by answering these four questions in order:

1. **Select the business process** — what event or process are we measuring?
2. **Declare the grain** — what does one row represent?
3. **Identify the dimensions** — what is the context around the fact?
4. **Identify the facts** — what are the measurable outcomes?

**Critical:** Never skip step 2. Everything else depends on the grain.

---

## Fact Tables

### Transaction Fact Table
One row per discrete business event (e.g., one order line, one booking, one support ticket).
- **When to use:** When events are individual, discrete, and time-stamped
- **Time dimension:** Date of the transaction
- **Example grain:** "One row per product SKU per order"

### Periodic Snapshot Fact Table
One row per entity per time period, capturing state at a regular interval.
- **When to use:** When you need to track cumulative measures or status over time
- **Time dimension:** The period (day, week, month)
- **Example grain:** "One row per SKU per warehouse per day"
- **Warning:** Facts are typically semi-additive (cannot sum across time periods)

### Accumulating Snapshot Fact Table
One row per entity, updated as it passes through lifecycle stages.
- **When to use:** When you need to track a process through defined stages
- **Time dimension:** Multiple date foreign keys (one per stage)
- **Example grain:** "One row per subscription (updated as it moves through trial → paid → churned)"
- **Unique characteristic:** Rows are updated, not inserted, as milestones are reached

---

## Dimension Tables

### Standard dimension
A table of descriptive context. Wide (many columns), denormalised, with a surrogate key.

### Slowly Changing Dimensions (SCD)

**Type 1 — Overwrite**
- Old value is replaced with new value
- No history preserved
- Use for: corrections, typos, low-analytical-importance changes
- Example: fixing a misspelled product name

**Type 2 — Add new row**
- A new row is inserted with the new value and effective dates
- History is fully preserved
- Use for: analytically significant changes where "what was it then?" matters
- Example: customer moving from SMB to Enterprise segment
- Requires: surrogate key, effective_from_date, effective_to_date, is_current flag

**Type 3 — Add new column**
- A new column stores the "previous" value
- Only one level of history preserved
- Use sparingly: only when exactly one prior value is needed
- Example: "previous_region" alongside "current_region" after a territory realignment

### Degenerate Dimension
An attribute with analytical value that has no supporting dimension table.
- Stored directly on the fact table as a column
- Example: order_number, invoice_number, booking_reference
- No dimension table needed because there are no additional attributes to store

### Role-Playing Dimension
The same physical dimension table used multiple times in the same fact table, each time in a different role.
- Example: Date dimension used as order_date, ship_date, and return_date in the same fact table
- Implemented as views or aliases on the same underlying table

### Junk Dimension
A single dimension table containing a collection of low-cardinality flags and indicators.
- Used to keep the fact table narrow
- Example: is_first_order, is_promotional, is_gift_wrap, payment_method_type grouped into one dimension
- Cardinality = product of all unique combinations

### Bridge Table
Resolves many-to-many relationships between a fact table and a dimension.
- Example: a product belonging to multiple categories; a customer having multiple account managers
- Sits between the fact table and the dimension table

---

## Conformed Dimensions

A dimension that is built once and shared across multiple fact tables.
- Enables drill-across queries (joining two fact tables via a shared dimension)
- Requires a single agreed business definition
- Must be negotiated across departments — this is where modelling is political, not technical

**The Bus Matrix** is the tool for managing conformed dimensions. If a dimension appears in multiple fact table rows of the bus matrix, it must be conformed.

**Test:** Can you join two fact tables via this dimension and get a meaningful answer? If yes, it must be conformed.

---

## Additivity

| Type | Definition | Example | Modelling rule |
|---|---|---|---|
| Fully additive | Can be summed across all dimensions | Revenue, units sold | Store directly |
| Semi-additive | Can be summed across some dimensions but not time | Inventory balance, account MRR | Store directly; flag in documentation; restrict aggregation in BI tool |
| Non-additive | Cannot be meaningfully summed across any dimension | ACOS, conversion rate, NRR | Store components; derive in BI layer |

**Golden rule:** When in doubt, store the components, not the derived value. Components are always more flexible.

---

## Star Schema vs Snowflake Schema

**Star schema:** Dimension tables are fully denormalised. One join from fact to dimension.
- Recommended for most analytical use cases
- Simpler queries, better performance
- Kimball's default recommendation

**Snowflake schema:** Dimension tables are normalised into sub-dimensions.
- More complex queries, additional joins
- Acceptable when dimension tables are very large and storage is a concern
- Avoid by default

---

## The Data Warehouse Bus

The collection of conformed dimensions and facts that define the overall data warehouse architecture.

- Fact tables are connected to each other through shared conformed dimensions
- New fact tables can be added incrementally without restructuring the bus
- Enables enterprise-wide consistency in reporting

**Bus Matrix** = the planning document that maps which processes use which conformed dimensions.