Star schema concept in Power BI - Step-by-Step Guide

Introduction

A star schema organizes data into a central fact table connected to several dimension tables. This layout helps you analyze data easily and quickly by simplifying relationships.

When you want to build a sales report that shows total sales by product, region, and time.

When you need to analyze customer behavior using different categories like age, location, and purchase history.

When your data has clear facts (numbers) and descriptive details (categories) that relate to those facts.

When you want to improve report performance by reducing complex joins.

When you want to create a simple and understandable data model for business users.

Steps

Step 1: Import your main data table

- Power BI Desktop > Home tab > Get Data

The fact table with numeric data appears in the Fields pane

💡 The fact table usually contains sales amounts, quantities, or counts

Step 2: Import dimension tables

- Power BI Desktop > Home tab > Get Data

Dimension tables with descriptive data appear in the Fields pane

💡 Dimension tables include product names, dates, or customer info

Step 3: Create relationships between fact and dimension tables

- Model view > Drag the key column from dimension table to matching column in fact table

Lines appear connecting the fact table to each dimension table

💡 Use one-to-many relationships with the dimension table on the 'one' side

Step 4: Verify relationship directions and cardinality

- Model view > Select relationship line > Properties pane

Relationships show single direction and correct cardinality

💡 Single direction filtering improves performance and avoids ambiguity

Step 5: Use fields from dimension tables to slice and filter your reports

- Report view > Fields pane > Drag dimension fields to Filters or Slicers

Report visuals update based on selected dimension filters

💡 This shows how star schema supports easy data exploration

Before vs After

Before

Fact and dimension tables are imported but not connected; report shows all data without filtering

After

Fact and dimension tables are connected in a star schema; report filters and slices work correctly and efficiently

Settings Reference

Cardinality

📍 Model view > Relationship properties

Defines how tables relate to each other, usually dimension to fact is one to many

Default: One to many

Cross filter direction

📍 Model view > Relationship properties

Controls how filters flow between tables; single is best for star schema

Default: Single

Active relationship

📍 Model view > Relationship properties

Determines which relationship Power BI uses by default in calculations

Default: Active

Common Mistakes

Creating many-to-many relationships between fact and dimension tables

This causes ambiguous filters and slows down reports

Ensure dimension tables have unique keys and use one-to-many relationships

Using bidirectional cross filtering in star schema

It can cause unexpected filter behavior and performance issues

Use single direction filtering from dimension to fact table

Summary

Star schema organizes data with one fact table connected to multiple dimension tables.

It simplifies data relationships and improves report performance.

Use one-to-many relationships with single direction filtering for best results.