TECH **Power BI Star Schema Design & Relationship Cardinality: Zero-Fluff Study Guide**

Power BI Star Schema Design & Relationship Cardinality: Zero-Fluff Study Guide

(For real projects, PL-300 exam prep, and production troubleshooting)

1. What This Is & Why It Matters

You’re building a Power BI report for a retail client. Sales data is in one table, products in another, and stores in a third. If you just dump everything into a single flat table, your report will: - Crash when filtering large datasets.
- Lie to users (e.g., showing duplicate sales because of incorrect joins).
- Be slow (DAX measures will take 30+ seconds to calculate).

A star schema fixes this by organizing data into fact tables (transactions) and dimension tables (descriptive attributes). Relationship cardinality (1-to-many, many-to-many) determines how these tables connect—and if you get it wrong, your entire model breaks.

Real-world scenario:
You inherit a Power BI report where sales data is duplicated when filtering by product category. The root cause? A many-to-many relationship was used where a 1-to-many was needed. Fixing this reduces report load time from 45 seconds to 2 seconds and eliminates incorrect aggregations.

2. Core Concepts & Components

? Star Schema

• Definition: A data model with a central fact table (e.g., sales) connected to dimension tables (e.g., products, stores) in a star-like pattern.
• Production insight: Star schemas are 10x faster for Power BI than flat tables because they minimize data duplication and optimize DAX calculations.

? Fact Table

• Definition: A table containing quantitative data (e.g., sales amount, quantity) and foreign keys to dimension tables.
• Production insight: Fact tables should only contain numeric metrics and keys—no descriptive text. Example: Sales[SaleID], Sales[ProductID], Sales[Amount].

? Dimension Table

• Definition: A table containing descriptive attributes (e.g., product name, store location) linked to a fact table via a foreign key.
• Production insight: Dimension tables should be denormalized (no snowflaking) for performance. Example: Products[ProductID], Products[Name], Products[Category].

? Relationship Cardinality

• Definition: How rows in one table relate to rows in another. Power BI supports:
• 1-to-many (1:*) (most common)
• Many-to-many (M:M) (use sparingly)
• 1-to-1 (1:1) (rare, usually a sign of poor design)
• Production insight: 90% of relationships should be 1-to-many. Many-to-many relationships require a bridge table and can cause performance issues.

? Filter Flow

• Definition: How filters propagate between tables in a star schema.
• Production insight: In a 1-to-many relationship, filters flow from the "1" side to the "many" side. Example: Filtering Products[Category] filters Sales[ProductID].

? Bridge Table (for M:M relationships)

• Definition: A table that resolves many-to-many relationships by linking two dimension tables.
• Production insight: Bridge tables should only contain keys (no metrics). Example: ProductStore[ProductID], ProductStore[StoreID].

? Active vs. Inactive Relationships

• Definition: Only one relationship between two tables can be active at a time. Others must be marked as inactive.
• Production insight: Use USERELATIONSHIP() in DAX to switch between inactive relationships.

? Role-Playing Dimensions

• Definition: A dimension table used multiple times in a fact table (e.g., OrderDate and ShipDate both referencing a Dates table).
• Production insight: Use inactive relationships and USERELATIONSHIP() to handle role-playing dimensions.

3. Step-by-Step: Building a Star Schema in Power BI

Prerequisites

• Power BI Desktop installed.
• Sample data (download AdventureWorks or use this simplified CSV).

Task: Build a Star Schema for Sales Analysis

We’ll model: - Fact table: Sales (transactions) - Dimension tables: Products, Stores, Dates

Step 1: Import Data

1. Open Power BI Desktop.
2. Click Home > Get Data > Text/CSV.
3. Load:
4. Sales.csv (fact table)
5. Products.csv (dimension)
6. Stores.csv (dimension)
7. Dates.csv (dimension)

Step 2: Clean & Prepare Tables

1. Fact table (Sales):
2. Remove unnecessary columns (e.g., CustomerName if not needed).
3. Ensure numeric columns (e.g., Amount) are set to Decimal Number.

4. Example:
   powerquery
= Table.SelectColumns(Sales, {"SaleID", "ProductID", "StoreID", "DateID", "Amount"})

5. Dimension tables (Products, Stores, Dates):

6. Remove duplicates (e.g., Products[ProductID] should be unique).
7. Example (in Power Query):
   powerquery
= Table.Distinct(Products, {"ProductID"})

Step 3: Create Relationships

1. Go to Model view.
2. Drag Sales[ProductID] to Products[ProductID] → 1-to-many (Power BI auto-detects this).
3. Drag Sales[StoreID] to Stores[StoreID] → 1-to-many.
4. Drag Sales[DateID] to Dates[DateID] → 1-to-many.

⚠️ Critical:
- Ensure the "1" side is the dimension table (e.g., Products).
- If Power BI gets it wrong, right-click the relationship > Properties > Cardinality.

Step 4: Verify Filter Flow

1. Create a simple table visual:
2. Rows: Products[Category]
3. Values: Sales[Amount] (sum)
4. Filter by Stores[Region] → Sales should update correctly (filter flows from Stores → Sales).

Step 5: Handle Many-to-Many (Example: Product Promotions)

Scenario: A product can be in multiple promotions, and a promotion can apply to multiple products.

1. Create a bridge table ProductPromotion:
   csv
ProductID,PromotionID
1,101
1,102
2,101
2. Import Promotions.csv (dimension table).
3. Create relationships:
4. Products[ProductID] → ProductPromotion[ProductID] (1-to-many)
5. Promotions[PromotionID] → ProductPromotion[PromotionID] (1-to-many)
6. Sales[ProductID] → ProductPromotion[ProductID] (many-to-many, mark as inactive)
7. Use USERELATIONSHIP() in DAX to switch relationships:
   dax
SalesWithPromo =
CALCULATE(
SUM(Sales[Amount]),
USERELATIONSHIP(Sales[ProductID], ProductPromotion[ProductID])
)

4. ? Production-Ready Best Practices

? Security

• Hide fact table keys from report view (right-click column > Hide in report view).
• Use row-level security (RLS) on dimension tables (e.g., restrict Stores[Region] by user).

? Cost Optimization

• Avoid calculated columns in fact tables (use DAX measures instead).
• Use DirectQuery sparingly (import mode is faster for star schemas).

? Reliability & Maintainability

• Name relationships clearly (e.g., Sales_Products_1toMany).
• Document your schema (use Power BI’s Data Dictionary or a separate doc).
• Avoid bidirectional filtering (can cause circular dependencies).

? Observability

• Monitor model size (File > Options > Data Load > View model size).
• Use Performance Analyzer to identify slow DAX measures.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. "Which relationship cardinality should you use?"
2. Trick: They’ll show a scenario where a many-to-many seems correct but a 1-to-many is better.

3. Answer: 90% of the time, 1-to-many.

4. "Why is your report slow?"

5. Trick: They’ll describe a flat table or bidirectional filtering.

6. Answer: "The model isn’t a star schema" or "Bidirectional filtering is enabled."

7. "How do you handle a product that belongs to multiple categories?"

8. Trick: They’ll suggest a many-to-many relationship without a bridge table.
9. Answer: "Create a bridge table between Products and Categories."

Key ⚠️ Trap Distinctions

• 1-to-many vs. many-to-many:
• 1-to-many: One product → many sales.
• Many-to-many: One product → many promotions, one promotion → many products.
• Active vs. inactive relationships:
• Only one active relationship between two tables. Use USERELATIONSHIP() for others.

7. ? Hands-On Challenge (with Solution)

Challenge

You have: - A Sales table with SaleID, ProductID, StoreID, Amount.
- A Products table with ProductID, Name, Category.
- A Stores table with StoreID, Name, Region.

Problem: When you filter by Stores[Region], sales for all products disappear.

Why? The relationship between Sales and Stores is many-to-many (incorrect).

Solution

1. Go to Model view.
2. Right-click the relationship between Sales and Stores > Properties.
3. Change Cardinality to 1-to-many (1 on Stores side).
4. Verify filter flow works.

Why it works: Stores[StoreID] is unique (1), so it should filter Sales[StoreID] (many).

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Microsoft Docs: Star Schema in Power BI
2. SQLBI: Relationships in DAX
3. Guy in a Cube: Power BI Star Schema Tutorial (YouTube)
4. PL-300 Exam Guide (Microsoft)