---
name: kimball
description: Build dimensional data warehouse models using the Kimball methodology — bus matrix, grain statements, fact and dimension tables, stakeholder interviews. Use when designing a star schema, creating dimensional models for e-commerce or SaaS, conducting business process interviews, or generating Kimball artifacts like bus matrices and grain statements.
argument-hint: "[full|interview-only|live-interview|artifacts-only|reporting-only]"
context: fork
---

# Kimball Modelling Skill

## What this skill does

When invoked, this skill guides a consultant (or acts autonomously) through the complete Kimball modelling procedure for a business. It produces a full set of professional modelling artifacts:

1. **Business context brief** — extracted from website research and public sources
2. **Value chain diagram** — the end-to-end business process map
3. **Stakeholder interview guide** — tailored to the business type, with role-specific question sets
4. **Interview output synthesis** — structured notes from interviews into modelling inputs
5. **Glossary / definition register** — surfacing conflicting definitions across teams
6. **Bus matrix** — the master map of processes × dimensions
7. **Grain statements** — one per fact table, written in plain business language
8. **Dimension attribute lists** — per dimension, with SCD type recommendations
9. **Fact candidate tables** — with additivity flags and source system mapping
10. **Reporting validation checklist** — does the model support the key reports that run this business?

---

## How to invoke this skill

```
You are a senior Kimball data modelling consultant. Use the Kimball Modelling Skill.

Business: [company name or description]
Website: [URL if available]
Business type: [saas-b2b | ecommerce | hybrid]
Mode: $ARGUMENTS
Context: [any additional context the user provides]
```

Modes:
- `full` — run the complete end-to-end procedure
- `interview-only` — produce the stakeholder interview guide only
- `live-interview` — conduct a stakeholder interview in real time through this conversation; produces a Synthesis Report at the end
- `artifacts-only` — skip discovery, produce modelling artifacts from provided context
- `reporting-only` — validate an existing model against reporting requirements

---

## Procedure

### Step 0: Business Research

Before any modelling work, research the business using available tools.

**If a website URL is provided:**
- Fetch the website and key sub-pages (pricing, features, about, integrations)
- Search for the company on LinkedIn, Crunchbase, G2/Capterra (for SaaS), or Amazon Seller Central / Shopify references (for e-commerce)
- Look for third-party case studies that may reveal tech stack

**Extract and document:**
- Business model (subscription, transactional, marketplace, hybrid)
- Product or service catalogue (what they sell, how it's structured)
- Pricing model (flat fee, per-unit, commission, tiered, usage-based)
- Channel mix (direct, marketplace, retail, OTA, etc.)
- Customer segments (SMB, enterprise, B2C, B2B)
- Tech stack clues (payment processors, ERPs, CRMs, ad platforms)
- Scale indicators (headcount, revenue if public, number of products/customers)
- Brand structure (single brand vs. multi-brand)

Output this as a **Business Context Brief** (see `${CLAUDE_SKILL_DIR}/templates/business-context-brief.md`).

**Modelling value of research:**
- Populates a preliminary bus matrix before any interviews
- Enables sharper interview questions
- Surfaces dimension candidates (channels, brands, plans, tiers)
- Identifies likely source systems and data availability constraints

---

### Step 1: Value Chain Mapping

Draw the end-to-end business process as a horizontal chain of stages. Each stage is a candidate business process and potentially a separate fact table.

**For SaaS B2B:**
```
Lead Acquisition → Trial/Onboarding → Subscription → Feature Usage → Support → Renewal/Expansion → Churn
```

**For E-commerce:**
```
Brand/Product Setup → Inventory Procurement → Listing & Merchandising → Marketing & Advertising → Order & Fulfillment → Returns & Refunds → Customer Retention
```

**For each stage, capture:**
- Who owns this process (department/role)
- Key events or transactions that occur
- Volume and frequency (how many events per day/month)
- Systems involved
- Whether it generates measurable outcomes (→ fact table candidate)

Output: Value chain diagram in text or Mermaid format.

---

### Step 2: Stakeholder Interview Planning

Generate a tailored interview guide based on the business type and value chain.

**Role mapping — who to interview:**

| Role | Priority | Session length | Focus |
|------|----------|----------------|-------|
| Executive / Business Owner | High — interview first | 30–45 min | KPIs, strategic decisions, what they're accountable for |
| Business Analyst / Ops Manager | Highest value | 60–90 min | Real definitions, actual reports used, workarounds |
| Power User / Report Consumer | High | 45–60 min | Real grain, daily workflow, Excel workarounds |
| Data / Systems Owner | Essential after draft model | 45 min | Source system availability, data quality, grain confirmation |

**Interview don'ts:**
- Don't run group interviews in the first round — people self-censor
- Don't ask executives about data details
- Don't conflate "would be nice" with "business-critical"

**Core questions per Kimball step:**

**Process (→ fact table)**
- Walk me through what happens when [key transaction] occurs
- What's the start and end of this process?
- How many of these happen per day / week / month?

**Grain (→ fact table row definition)**
- What's the smallest thing you need to measure?
- Give me the last decision you made using data — what was the unit of analysis?
- If I showed you a report with one row per X, would that be useful?

**Dimensions (→ filtering and grouping)**
- How do you slice this analysis? By region? By product? By channel?
- What do you filter by most often?
- What attributes do you need to see alongside the numbers?

**Facts (→ measures)**
- What are your top 5 KPIs?
- How is [metric] calculated?
- Can you sum this across all regions / products / time periods? (→ additivity probe)
- Where does this number come from? (→ source system)

**Cross-cutting questions (ask in every session):**
- What reports do you use today, and what's wrong with them?
- What questions can you not answer today that you wish you could?
- How do you define [key term]? (especially: customer, revenue, churn, active user)
- If two people pulled this number independently, would they get the same answer?

Output: Interview guide document (see `${CLAUDE_SKILL_DIR}/templates/interview-guide.md`).

---

### Step 3: Interview Output Synthesis

After interviews are conducted, synthesise the outputs into structured modelling inputs.

For each interview, capture:
- Business processes identified (candidate fact tables)
- Dimensions mentioned, with attributes
- Facts/KPIs mentioned, with calculation notes
- Definition conflicts surfaced
- Source systems mentioned
- Open questions and risks

Resolve conflicts explicitly — do not silently pick a definition. Document the conflict and the agreed resolution in the glossary.

Output: Synthesis notes + populated glossary (see `${CLAUDE_SKILL_DIR}/templates/glossary.md`).

---

### Step 4: Bus Matrix

Build the bus matrix as the central artifact. It is the master plan of the data warehouse.

**Format:**

| Business Process | Date | [Dim 2] | [Dim 3] | [Dim N] |
|---|---|---|---|---|
| [Process 1] | ✓ | ✓ | | ✓ |
| [Process 2] | ✓ | ✓ | ✓ | |

**Rules:**
- Every fact table gets a row
- Every candidate dimension gets a column
- A tick means that dimension is used by that process
- Dimensions that appear in multiple rows are **conformed dimensions** — they must have a single agreed definition and implementation
- The Date dimension should appear in every row — it is always conformed

**Conformed dimension test:** Can you drill across two fact tables using this dimension and get meaningful results? If yes, it must be conformed.

**Bus matrix interpretation:**
- Columns with many ticks = high-value dimensions to build first
- Rows with many ticks = fact tables with broad analytical coverage
- Isolated ticks = dimension used by only one process, lower priority to conform

Output: Bus matrix (see `${CLAUDE_SKILL_DIR}/templates/bus-matrix.md`).

---

### Step 5: Grain Statements

Write one grain statement per fact table, in plain business language. Get explicit stakeholder sign-off.

**Format:**
> "One row in this fact table represents [one X] [for one Y] [at one Z] [on one date]."

**Examples:**
- "One row represents one product SKU sold on one order on one channel on one date."
- "One row represents one subscription invoice line item for one account in one billing period."
- "One row represents the inventory level of one SKU at one warehouse at the end of one day."

**Grain validation questions:**
- Would a row at this grain be meaningful to a business user?
- Is this grain consistent with how the source system records the event?
- Are all proposed facts consistent with this grain? (a revenue fact at order grain can't coexist with a campaign impression fact)

**Fact table type per grain:**
- **Transaction fact** — one row per discrete event (order line, booking, ticket)
- **Periodic snapshot** — one row per entity per time period (daily inventory, monthly MRR)
- **Accumulating snapshot** — one row per entity tracking lifecycle stages (subscription from trial → paid → churned)

Output: Grain statement register (see `${CLAUDE_SKILL_DIR}/templates/grain-statements.md`).

---

### Step 6: Dimension Design

For each dimension identified in the bus matrix, produce an attribute list.

**Per dimension, document:**
- Dimension name and business description
- Primary key
- All attributes with business label
- Cardinality estimate (tens / hundreds / thousands / millions)
- SCD type recommendation per attribute:
  - **Type 1** — overwrite (no history needed): name corrections, typo fixes
  - **Type 2** — new row (history needed): customer segment change, product category reclassification
  - **Type 3** — add column (limited history): rare, use sparingly
- Source system(s)
- Known data quality issues

**Common conformed dimensions across business types:**

*SaaS:* Date, Account, Subscription Plan, Feature, Support Channel, Sales Rep/CSM
*E-commerce:* Date, Product/SKU, Brand, Channel, Customer (DTC only), Campaign, Warehouse/Fulfillment Node, Promotion

**Special dimension cases:**
- **Degenerate dimension** — an attribute with no dimension table (e.g., order number stored directly on the fact row)
- **Role-playing dimension** — same dimension used in multiple contexts (e.g., Date as order date, ship date, return date)
- **Junk dimension** — a collection of low-cardinality flags grouped into one dimension to avoid fact table clutter
- **Bridge table** — for many-to-many relationships (e.g., a product belonging to multiple categories)

Output: Dimension attribute sheets (see `${CLAUDE_SKILL_DIR}/templates/dimension-sheet.md`).

---

### Step 7: Fact Table Design

For each fact table, document all candidate measures.

**Per fact, document:**
- Fact name (business label)
- Business definition / calculation
- Additivity:
  - **Fully additive** — can be summed across all dimensions (revenue, units)
  - **Semi-additive** — can be summed across some dimensions but not all (inventory balance — don't sum across time periods)
  - **Non-additive** — cannot be meaningfully summed (ratios, rates, percentages — store components instead)
- Data type and unit
- Source system and field reference
- Known calculation conflicts with other teams

**Derived facts:** Store components, not derived values, where possible. Margin is better stored as revenue + COGS separately than as a pre-calculated margin column — it stays flexible.

Output: Fact candidate table (see `${CLAUDE_SKILL_DIR}/templates/fact-candidate-table.md`).

---

### Step 8: Reporting Validation

Validate the model by checking whether it can produce the key reports that run the business.

**For SaaS businesses, the critical report suite is:**

| Report | Requires |
|---|---|
| MRR waterfall (new / expansion / contraction / churn) | Subscription fact + Account dim + Plan dim + Date dim |
| Net Revenue Retention (NRR) by cohort | Subscription fact + Account dim (with cohort date) |
| Trial-to-paid conversion funnel | Trial fact + Account dim + Plan dim |
| Churn by account age / plan / CSM | Subscription fact + Account dim + Plan dim |
| Feature adoption by plan tier | Feature usage fact + Feature dim + Plan dim |
| LTV / CAC by acquisition channel | Subscription fact + Account dim + Marketing spend fact |
| ARR bridge (period over period) | Subscription fact + Date dim |

**For E-commerce businesses, the critical report suite is:**

| Report | Requires |
|---|---|
| Contribution margin waterfall (gross → CM I → CM II) | Orders fact + Returns fact + COGS + Ads fact + Brand dim + Channel dim |
| Revenue and units by brand / channel / SKU | Orders fact + Product dim + Brand dim + Channel dim |
| ACOS / TACOS by campaign | Ads fact + Orders fact + Campaign dim + Product dim |
| Inventory health (days of cover, stockout rate) | Inventory snapshot fact + Product dim + Warehouse dim |
| Return rate by product / channel / reason | Returns fact + Product dim + Channel dim |
| Customer cohort LTV (DTC only) | Orders fact + Customer dim + Date dim |
| Repeat purchase rate by brand | Orders fact + Customer dim + Brand dim |

**Validation method:** For each report, list the required fact tables and dimensions. If any are missing from your model, flag the gap. If any conformed dimension is not fully conformed (e.g., two fact tables define "customer" differently), flag the conflict.

Output: Reporting validation checklist (see `${CLAUDE_SKILL_DIR}/templates/reporting-validation.md`).

---

## Business-type playbooks

### SaaS B2B Playbook

**Key modelling considerations:**

- **MRR is the north star metric.** Your subscription fact table must support the MRR waterfall natively — every subscription event (new, expansion, contraction, churn) must be capturable with a single consistent grain.
- **Accumulating snapshot for lifecycle tracking.** Track each account from trial through to churn in a single accumulating snapshot fact table. This enables cohort and lifecycle analysis.
- **Plan changes require SCD Type 2 on the Plan dimension.** When an account upgrades, you need to know what plan they were on before.
- **Commission vs. flat-fee revenue must be separated.** If your pricing model includes both (as in Smoobu), they require different recognition logic in the fact table.
- **Feature usage is a leading churn indicator.** Build the feature usage fact table early — accounts that stop using core features churn 60–90 days later.
- **Beware of different churn definitions.** Finance, CS, and Product often define churn differently (30-day window vs. 60-day, logo vs. revenue). Document and resolve in the glossary before building.

**Priority build order:** Date → Account → Subscription Plan → Subscription Revenue fact → Renewal/Churn accumulating snapshot → Feature Usage fact → Trial Conversion fact

---

### E-commerce / Multi-brand Playbook

**Key modelling considerations:**

- **Contribution margin is the north star metric.** Your model must connect revenue, platform fees, COGS, and ad spend through conformed dimensions — otherwise CM by brand/channel/SKU is impossible.
- **Channel drives everything.** The Channel dimension must be conformed across Orders, Advertising, and Inventory. Each channel has different fee structures, attribution models, and data availability.
- **Amazon anonymises customers.** A unified Customer dimension is structurally impossible for marketplace revenue. Plan for a degenerate customer key on Amazon order rows, with a full Customer dimension only for DTC channels.
- **ACOS and TACOS are non-additive.** Store ad spend and attributed revenue separately. Never store ACOS as a fact — it can't be summed across campaigns.
- **Inventory is a periodic snapshot.** Store end-of-day inventory levels, not transactions. Days of cover is derived (units on hand ÷ avg daily sales), not stored.
- **Multi-brand requires a Brand dimension conformed early.** Every fact table must join to the same Brand dimension — otherwise cross-brand analysis and consolidated P&Ls are impossible.
- **Returns need their own fact table.** Don't net returns against orders — you lose the ability to analyse return rates, reasons, and margin impact separately.

**Priority build order:** Date → Product → Brand → Channel → Orders fact → Returns fact → Advertising fact → Inventory snapshot fact → Customer fact (DTC only)

---

## Quality checks

Before declaring a model draft complete, verify:

- [ ] Every fact table has an explicit grain statement
- [ ] Every dimension used by more than one fact table is marked as conformed
- [ ] Every conformed dimension has a single agreed business definition in the glossary
- [ ] No non-additive fact is stored as a pre-calculated ratio (store components instead)
- [ ] Semi-additive facts are flagged and the restricted aggregation dimension is documented
- [ ] SCD type is specified for every dimension attribute that could change over time
- [ ] Every fact table can be linked to at least one report in the reporting validation checklist
- [ ] All definition conflicts from interviews are resolved and documented in the glossary
- [ ] Source system is identified for every fact and every dimension

---

## Output format

All artifacts are produced as structured markdown documents. Each artifact has a template in the `${CLAUDE_SKILL_DIR}/templates/` directory. Produce them in order and collect them into a final deliverable package.

The final deliverable is a `MODEL_REPORT.md` that links to or embeds all artifacts, providing a complete picture of the data model for handoff to engineering or BI teams.

---

## Agent orchestration

This skill uses five specialist agents. Each has a distinct role and a defined handoff chain. They can be run sequentially by a single model or spawned as parallel subagents where the environment supports it.

```
Researcher ──► Interviewer ──► Kimball Expert ──► Designer ──► Copywriter
     │               │               │                               │
     │               │               └──► (review loop back         │
     │               └──► (gap queries back to Interviewer)         │
     └──► (vocabulary register to Copywriter at start)              │
                                                             MODEL_REPORT
```

| Agent | File | Responsibility | Primary inputs | Primary outputs |
|---|---|---|---|---|
| **Researcher** | `${CLAUDE_SKILL_DIR}/agents/kimball-researcher.md` | Business intelligence from public sources | Website URL, company name | Business Context Brief, Vocabulary Register, Open Questions |
| **Interviewer** | `${CLAUDE_SKILL_DIR}/agents/kimball-interviewer.md` | Stakeholder interview design and synthesis | Business Context Brief, interview notes/transcripts | Interview Guides (per role), Synthesis Reports, Cross-Interview Conflict Log |
| **Kimball Expert** | `${CLAUDE_SKILL_DIR}/agents/kimball-expert.md` | Technical model design and rigorous review | Interview Synthesis, Business Context Brief | Bus Matrix, Grain Statements, Dimension Sheets, Fact Tables, Glossary — all reviewed against quality checklist |
| **Designer** | `${CLAUDE_SKILL_DIR}/agents/kimball-designer.md` | Artifact structure and visual presentation | Kimball Expert-approved artifacts | Fully structured, formatted artifact templates ready for prose |
| **Copywriter** | `${CLAUDE_SKILL_DIR}/agents/kimball-copywriter.md` | Plain-language prose for all written content | Designed artifacts, Vocabulary Register | Complete MODEL_REPORT with all prose written in the business's own language |

### Handoff rules

- Nothing leaves the **Kimball Expert** without passing the full quality checklist. If an artifact fails a check, it goes back — not forward.
- The **Researcher** sends the Vocabulary Register to the **Copywriter** at the start of the engagement, not the end — the Copywriter needs it before writing begins.
- The **Interviewer** sends the Cross-Interview Conflict Log to the **Kimball Expert** before any fact tables or dimension sheets are finalised.
- The **Designer** does not change technical content — only structure and format. Any structural change that implies a content change must be flagged back to the Kimball Expert.
- The **Copywriter** does not change definitions or technical decisions — only language and clarity. Any language change that could alter meaning must be flagged back to the Kimball Expert.

---

## References

- `${CLAUDE_SKILL_DIR}/references/kimball-concepts.md` — core Kimball terminology and concepts
- `${CLAUDE_SKILL_DIR}/references/saas-metrics.md` — SaaS reporting metrics and definitions
- `${CLAUDE_SKILL_DIR}/references/ecommerce-metrics.md` — E-commerce reporting metrics and definitions
- `${CLAUDE_SKILL_DIR}/agents/kimball-researcher.md` — business intelligence research agent
- `${CLAUDE_SKILL_DIR}/agents/kimball-interviewer.md` — stakeholder interview design and synthesis agent
- `${CLAUDE_SKILL_DIR}/agents/kimball-expert.md` — technical modelling and review agent (the technical authority)
- `${CLAUDE_SKILL_DIR}/agents/kimball-designer.md` — artifact structure and visual presentation agent
- `${CLAUDE_SKILL_DIR}/agents/kimball-copywriter.md` — plain-language prose and business communication agent