Power BI star schema: a practical data model checklist

The grain is what one row in a fact table represents: one order line, one invoice, one stock movement, or one support ticket. Write it down in a single sentence before opening Power BI. If the sentence needs an 'and', the table probably holds more than one grain.

Grain determines which dimensions are valid and which measures can be summed. Combining order headers and order lines in one table double-counts header-level values such as delivery charges. Keep separate fact tables when the events differ, then relate them through shared dimensions. A header fact can hold delivery charges and discounts once per order, while a line fact holds quantity and line value. Relating both to the same dimensions lets each measure aggregate at its own level without double counting.

A fact table holds foreign keys and numeric measures that can be aggregated. A dimension holds the descriptive attributes used to filter and group those measures: customer segment, product category, store region, account owner.

Resist the temptation to put descriptions in the fact table or measures in a dimension. That shortcut makes the model harder to extend and usually produces inconsistent totals. Keep dimensions narrow, conformed, and reusable across facts. A conformed dimension, such as a shared customer or date table, is what allows separate facts to be compared. A degenerate dimension, such as the order number stored on the fact table, is acceptable when it is needed for drill-through.

Create a dedicated date table with one row per day and no gaps. Add year, quarter, month, month name, week, and an is-working-day flag. Mark it as the date table in Power BI so time intelligence behaves predictably. Do not rely on the date column inside a fact table as the date table; it will miss days with no transactions and break comparisons across periods.

A business often has several dates on one fact table: order date, invoice date, due date, and payment date. One active relationship per date role is the safe default. Use separate role-playing date tables or USERELATIONSHIP in specific measures rather than stacking active relationships between the same tables.

Use one-to-many relationships from the dimension side to the fact side, with single-direction filtering. Match data types exactly, hide foreign keys from report users, and give keys stable, meaningless values where possible. Natural keys can be reused or changed by source systems.

Bidirectional filtering is occasionally necessary, but it creates ambiguous filter paths and unexpected totals. Use it only for a defined many-to-many requirement, then test the result carefully. An unknown-member row in each dimension is better than blank keys that silently drop transactions from reports. After building the model, test a matrix with a known total and a filter on every dimension. A blank row or a sudden drop usually points to a missing key or a filter direction problem.

Compare the model with the source at the lowest grain and at the totals users will see. Check row counts, duplicate dimension members, unmatched foreign keys, and date coverage. A report that is fast but unexplainable is not finished.

Document the grain, sources, refresh schedule, and owner. Name measures in business language and apply consistent format strings. Remove unused columns, because every column adds size and maintenance, then review the model with someone who will use it. Set a refresh failure notification and record the last successful refresh where users can see it.