Star Schema
Also called: Dimensional model, Star model
A star schema is a data model with one central fact table of events and numbers, joined to dimension tables that describe them, so reports can slice measures by date, product or region.
How a star schema works
Picture one table in the middle and several smaller tables around it. The middle table, the fact table, holds one row for each thing that happened (an order line, a support ticket, a page view) with the numbers to add up, such as Quantity and Revenue. Each surrounding dimension table describes one angle on those events: Date, Product, Region, Customer.
Draw the relationships and the shape is a star. Each dimension joins to the fact table through a key, and filtering a dimension filters the facts. Pick North America in a Region slicer and only the fact rows with that region’s key stay in play, so Revenue falls from $2.80M to $1.12M.
Why it beats one big table
Many reports start as a single wide export: every order line with the product category, customer segment and region typed out on each row. That works until you need to rename a category, add a second source or count customers instead of orders.
- Descriptions live in one place. Fix a product’s category once in the Product table, not on 200,000 rows.
- Slicers get clean lists. A slicer on Region reads a four-row table, not a column with every order’s value.
- Measures stay simple. With one fact table, a measure like Revenue is a plain sum, and DAX only has to follow one route from dimension to fact.
- Several fact tables can share dimensions. Sales and Budget can both join to the same Date and Region tables, so one slicer filters both.
Star vs. snowflake
A snowflake schema normalizes the dimensions further: Product joins to Subcategory, which joins to Category. It stores each value once, but report authors then have to follow longer chains of relationships. For analytics models, flatten the chain into a single Product table unless you have a strong reason not to.
In Power BI vs. Tableau
| Power BI | Tableau | |
|---|---|---|
| Where you model it | Model view in Power BI Desktop, saved in the semantic model | Relationships canvas in the data source tab |
| Typical advice | Build a star schema, hide the key columns | Relate fact and dimension tables; one wide table also works |
Best practices
- Keep facts narrow and dimensions wide. Facts hold keys and numbers; dimensions hold the text people filter by.
- Use a dedicated date table. One row per day, related to every fact table’s date column.
- Join on a single key column. Avoid composite keys; add a simple key column if needed.
- Don’t connect dimensions to each other. If two dimensions need a link, route it through the fact table.
- Check the grain. Every fact row should mean the same thing, for example one order line.
Mocking up a star schema
Every filter and chart on a mockup becomes a dimension or measure in the model, so sketch the page before the tables. A dashboard that shows revenue by region, product and month tells you the dimensions you need. See why to mock up Power BI reports before you build.
