Star schema explained: facts, dimensions and grain for business analysts

What a star schema is: facts in the middle, dimensions around them

A star schema is a simple data model designed for reporting rather than for running day-to-day transactions. It has two kinds of tables. When you draw it, one table sits in the middle and the others connect to it like the points of a star. That is where the name comes from.

Fact tables hold the measurements

A fact table records events from one business process: a sale, a payment, a support call, a delivery. Each row holds numbers you want to add up or average, called measures (quantity, amount, duration), plus keys that point to the dimension tables. Fact tables are usually long and narrow: many rows, few columns.

Dimension tables hold the context

A dimension table describes the who, what, where and when of each event. A product dimension holds product name, category and brand. A date dimension holds day, month, quarter and financial year. A store dimension holds store name, city and region. Dimension tables are usually short and wide: fewer rows, many descriptive columns.

The link between them is a key. Each dimension row has a unique key, and the fact table stores that key as a foreign key. In reports, dimensions become your filters, slicers and row labels. Facts become the numbers in the cells.

• If you would sum, count or average it, it probably belongs in a fact table.
• If you would filter, group or label by it, it probably belongs in a dimension.
• If you cannot decide, ask what one row of the fact table means. That question leads you to grain.

Grain: decide what one row means before anything else

The grain is a one-sentence statement of what a single row in a fact table represents. For example: one row per product line on a till receipt, or one row per store per day, or one row per support ticket status change. Everything else in the design depends on it.

Grain decides which questions the data can answer. If the grain is one row per product line on a receipt, you can report sales by product, by hour, by till and by store. If the grain is one row per store per day, you can never see which products sold, because that detail was summed away before it reached the table.

Grain also protects you from double counting. Every measure in a fact table must be true at the declared grain. If you put a monthly store target on every receipt line, a report that sums the target will multiply it by the number of lines. This is one of the most common reporting bugs, and it starts as a modelling decision, not a dashboard error.

A good rule: pick the lowest sensible grain the source data supports. You can always add detail up into totals. You cannot split a total back into detail.

Why a star schema helps reporting

Operational systems store data in many small, linked tables so that each fact is saved once and updates are safe. That is good for running the business, but a simple question like sales by region by month might need joins across many tables. A single flat spreadsheet-style table goes the other way: easy to read, but heavy, repetitive and easy to break when a description changes.

A star schema sits in between. The benefits for reporting are practical:

• Simpler queries. Each question needs one fact table and a few joins, which you can read and check yourself with basic SQL.
• Consistent numbers. Measures are defined once, so two dashboards that use the same fact table should agree.
• Better performance. BI tools such as Power BI are built to work well with this shape. Microsoft's star schema guidance explains why.
• Reusable dimensions. One date or product dimension can serve several fact tables, so filters behave the same everywhere.
• Easier conversations. Business people already think in numbers and descriptions, so the model maps directly to their questions.

How to define a star schema as a business analyst

You will rarely build the tables yourself. Your job is to give the BI or data team a clear, tested specification. These steps work whether the team uses Power BI, another BI tool or a data warehouse.

1. Start with the business questions. Collect the real questions the dashboard must answer, written in the stakeholder's words. Link each one to a KPI or a decision.
2. Name one business process per fact table. Sales, returns, deliveries and targets are different processes. Each usually gets its own fact table.
3. Declare the grain in one sentence. Write it at the top of the specification and ask the data team to confirm the source can deliver it.
4. List the dimensions. For each fact table, ask who, what, where, when and how. Note the descriptive columns people will filter or group by.
5. List the measures and how they add up. For each measure, say whether it can be summed across all dimensions, only some, or none (for example, a stock balance or a rate).
6. Write sample rows and test them against the questions. A few made-up rows expose missing dimensions and grain problems faster than any diagram.
7. Agree definitions and record decisions. Write down what each measure means and why the grain was chosen, for example in a decision log.

Worked example: a sales star schema at Marigold Grocers

Marigold Grocers is a fictional chain of neighbourhood grocery stores. Helena, the sponsor, wants a dashboard showing sales by store, product category and month. Nadia, the business analyst, writes the specification with you.

Business process: till sales. Grain: one row per product line on a till receipt. Dimensions: date, store, product. Measures: quantity and sales amount, both fully additive.

• fact_sales: receipt_number, date_key, store_key, product_key, quantity, sales_amount
• dim_date: date_key, full_date, month_name, quarter, financial_year
• dim_store: store_key, store_name, city, region
• dim_product: product_key, product_name, category, brand

Nadia writes three sample fact rows to test the design (shown with names instead of keys so they are easy to read):

receipt  date     store   product  qty  sales_amount
1001     3 March  Pune    Milk     2    ₹120
1001     3 March  Pune    Bread    1    ₹40
1002     3 March  Nagpur  Milk     1    ₹60

Total sales for the day are ₹220 (₹120 + ₹40 + ₹60). Pune sold ₹160 and Nagpur sold ₹60. Milk sold ₹180 across both stores, from 3 units. Grouping by any dimension gives a sensible answer, so the grain works for Helena's questions.

Then Helena asks for sales against target. Targets are set per store per month, so they do not match the receipt-line grain.

Common star schema modelling mistakes to catch early

Many reporting errors found in testing started as small modelling choices. These are the ones worth checking every time.

• Mixing grains in one fact table. Line-level sales and monthly targets in the same table cause inflated totals. Give each grain its own fact table.
• Never writing the grain down. If people describe one row differently, they will build different measures. Put the grain sentence at the top of the specification.
• Storing rates and ratios as measures. Averages, percentages and margins cannot be summed. Store the parts (sales, target, cost) and calculate the ratio in the report, for example as a measure in Power BI.
• Putting descriptions in the fact table. Product names or store cities on every fact row bloat the table and go out of date. Keep them in dimensions.
• Forgetting history in dimensions. If a store moves to a new region, should last year's sales follow it or stay in the old region? Ask the business and document the rule.
• Missing unknown members. If a fact row has no matching product, an inner join silently drops it. Ask for an unknown row in each dimension so totals still reconcile.
• Building one giant flat table. It feels simpler at first, but every new question adds columns and duplicated text.
• Splitting dimensions into many small tables. Over-normalising (often called snowflaking) brings back the join complexity the star was meant to remove.

Quick review checklist before you sign off a model

Use this list when the BI team shares a model or a draft dashboard. You do not need to read the code to ask these questions.

• Every fact table has a written grain sentence that the business understands.
• Each measure is valid at that grain and its summing rule is documented.
• Every business question maps to one fact table and named dimensions.
• Dimension columns use business-friendly names that match the project glossary.
• There is a plan for unknown or missing dimension values.
• Rules for dimension changes over time are agreed and recorded.
• Dashboard totals reconcile to a trusted source report for a sample period.

If you want to practise this end to end, the BA Lab includes a data and SQL stage and a dashboards stage where you define measures and check them against a fictional company's data.

Frequently asked questions