Model one business process as a star schema

I have a table full of columns and no idea what should be a fact and what should be a dimension.

BeginnerRead 5 minDoing: about an hour7 steps

▶ Start

What you'll have at the end

A star schema for one process: the grain written as one sentence, plus its fact and dimension tables

Before you start

You need

One wide table, such as an export or a query result, holding every column for one business process with no split into descriptive attributes and measured numbers yet.

Not covered

Sorting several flag or status columns into one shared dimension, which needs its own procedure once a table has more than a couple of them.

Leans on

Sort the wide table's own columns first

Name the business process and grain
Sort every column into three buckets
Test each numeric column against its use

Build the tables and check the load

Turn descriptive columns into dimension tables
Label each measure additive or not
Load the fact table, check the total

Query it and prove the split holds

Query the star and prove it works

Where to go next

Collapse scattered order flags into one junk dimension

Come back here once the wide table has several flag or status columns instead of one identifier and a handful of descriptive ones.

Preserve customer history with a Type 2 slowly changing dimension

Use this once a dimension attribute built here, like a customer's tier, actually changes over time and the old value needs keeping.

Checked 25 Aug 2026

Part of the Kimball Data Modeling cookbook