Power BI Data Modeling with Star Schemas

Declare fact-table grain

Grain states exactly what one row represents: one order line, one support ticket, or one account-day snapshot. Every measure column and foreign key must make sense at that grain. Mixing order headers and lines duplicates shipping charges or order counts. Split processes into separate fact tables when they naturally occur at different grains.

Choose transaction, periodic snapshot, or accumulating snapshot patterns based on the process. Transactions record events, periodic snapshots record state at intervals, and accumulating snapshots update milestones across a workflow. Preserve source identifiers for reconciliation. Do not aggregate away detail merely to make the first report smaller if later analysis requires it.

Design conformed dimensions

Dimensions contain names, categories, dates, geography, customer attributes, and other descriptors used to group or filter facts. Use one row per dimension key and include a controlled unknown member for missing or late keys. A dedicated date dimension supports fiscal periods, weekdays, and consistent time intelligence across facts.

Conformed dimensions are reused across business processes. The same product dimension can filter sales and returns when definitions align. If historical reporting must preserve prior customer region or product category, use a documented slowly changing dimension approach. Otherwise current attributes may rewrite the interpretation of old transactions.

Create predictable relationships

The normal relationship connects a unique dimension key on the one side to repeated foreign keys in a fact on the many side. Single-direction filtering from dimension to fact is easier to reason about. Ambiguous paths and routine bidirectional filtering can produce surprising totals and make troubleshooting difficult.

Many-to-many situations need explicit modeling. A bridge table can represent multiple salespeople assigned to an account or products carrying multiple tags. Define allocation when summing additive values across the bridge; otherwise the same revenue may appear under several members and overstate totals. Test totals with and without each dimension filter.

Validate the semantic model

Create measures for governed calculations instead of relying on implicit aggregation of visible columns. Hide technical keys and raw fields that report authors should not use. Apply clear names, descriptions, formats, folders, and default summarization. Keep sensitive attributes out of broadly distributed models or protect them with appropriate access rules.

Reconcile row counts and core totals to sources before building polished visuals. Test blank keys, duplicate dimension keys, inactive date relationships, and filters across multiple facts. Microsoft documents star-schema principles specifically for Power BI; use that guidance while adapting refresh, storage, and security choices to the actual data volume and service constraints.

Model sales and returns without double counting

A retailer imported one wide table that repeats order totals on every line and joins returns directly by product, producing inflated sales.

1. Define Sales fact grain as one order line and Returns fact grain as one returned line event.
2. Create shared Date, Product, Customer, and Store dimensions with unique surrogate keys and unknown members.
3. Relate each dimension one-to-many to both facts, using role-playing date handling for order and return dates.
4. Create explicit sales, return, and net-sales measures from line-level amounts rather than repeated order headers.
5. Reconcile daily totals and test product, store, customer, and date filters against source-system reports.

Star schema design record

Complete this record for each model before report development begins.

• Business process and fact: source event, row grain, fact type, identifiers, measures, and refresh cadence.
• Dimensions: key, unique-row rule, descriptive attributes, unknown member, history policy, and owner.
• Relationships: cardinality, filter direction, active role, bridge logic, allocation, and ambiguity check.
• Semantic layer: explicit measures, formats, descriptions, hidden technical fields, hierarchy, and security.
• Validation: source row counts, total reconciliation, orphan keys, duplicate keys, filter tests, and sign-off.

Common mistakes

• Joining tables before defining row grain and then repairing duplicated totals with arbitrary distinct counts.
• Using bidirectional filters everywhere because they make one visual work while creating ambiguous paths elsewhere.
• Placing descriptive text repeatedly in large fact tables instead of using governed dimensions.

Try one

Sources

• Microsoft Power BI star schema guidanceOfficial Microsoft guidance for fact tables, dimension tables, relationships, and model grain.
• Microsoft Power BI measures documentationOfficial Microsoft documentation for creating and using explicit model measures.