Data Modeling Fundamentals

What You'll Learn

• What a data model is
• Star schema design
• Create relationships between tables
• Understand cardinality
• Best practices for modeling

What is a Data Model?

A data model is how your tables connect to each other.

Without relationships: Tables are isolated. Can't combine data easily.

With relationships: Tables connect logically. Power BI knows how they relate!

Example:

• Orders table links to Customers table
• Power BI knows which customer made each order
• Visuals automatically filter correctly!

Star Schema

The gold standard for Power BI models.

Structure

Center: Fact Table Contains measurements/transactions:

• Sales
• Orders
• Transactions

Around: Dimension Tables Contain descriptive attributes:

• Products
• Customers
• Dates
• Locations

Example Star Schema

Fact: Sales

• OrderID
• ProductID (links to Products)
• CustomerID (links to Customers)
• DateID (links to Calendar)
• Amount
• Quantity

Dimension: Products

• ProductID (primary key)
• ProductName
• Category
• Price

Dimension: Customers

• CustomerID (primary key)
• CustomerName
• City
• Country

Dimension: Calendar

• DateID (primary key)
• Date
• Year
• Month
• Quarter

Creating Relationships

Model View

1. Click Model View (left sidebar)
2. See all tables visually
3. Drag to arrange them

Auto-Detect

Power BI auto-creates relationships based on matching column names!

Checks for:

• Same column name
• Same data type
• Unique values on one side

Manual Creation

Create relationship:

1. In Model View
2. Drag field from one table to another

Example:

• Drag Sales[CustomerID] to Customers[CustomerID]

Relationship Settings

Right-click relationship line > Properties:

From table/column: The "many" side (fact table)

To table/column: The "one" side (dimension table)

Cardinality:

• Many-to-One (most common)
• One-to-One (rare)
• Many-to-Many (avoid!)

Cross filter direction:

• Single (one way)
• Both (bidirectional - use carefully!)

Understanding Cardinality

One-to-Many

Most common relationship type.

Example:

• One customer has many orders
• One product has many sales

Notation: 1:* or 1:n

Direction: Filters flow from "one" to "many"

Many-to-One

Same as One-to-Many, just reversed perspective.

Power BI shows as 1:*

One-to-One

Rare. Usually means bad design.

Example: Customer and CustomerDetails should be one table!

When valid: Security (separating sensitive data)

Many-to-Many

Avoid when possible!

Creates bridge tables or complicated logic.

Better solution: Create intermediate table

Primary and Foreign Keys

Primary Key

Unique identifier in dimension table:

• CustomerID in Customers
• ProductID in Products

Requirements:

• Unique (no duplicates)
• Never null/blank
• Doesn't change

Foreign Key

Reference to primary key in fact table:

• CustomerID in Sales (links to Customers)
• ProductID in Orders (links to Products)

Can have:

• Duplicates (many orders per customer)
• Nulls (if allowed)

Relationship Best Practices

Use Surrogate Keys

Instead of: "John Smith" as key Use: CustomerID = 1001

Why:

• Names can duplicate
• Names can change
• Numbers are faster

One Direction

Prefer single cross-filter direction.

Bidirectional can cause:

• Slow performance
• Ambiguous filtering
• Unexpected results

When to use both: Specific scenarios with many-to-many

Minimize Relationships

Don't over-connect tables.

Only create relationships you actually need.

Bad: Every table connected to every other table Good: Star schema with clear fact/dimension split

Date Table

Always create dedicated Calendar/Date table!

Benefits:

• Easy time intelligence
• Consistent date grouping
• Year/Quarter/Month hierarchy

How to create:

1. Modeling > New Table
2. Use DAX: Calendar = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))
3. Add Year/Month/Quarter columns
4. Link to fact tables

Managing Relationships

View Relationships

Model View: See all visually

Manage Relationships: Modeling > Manage Relationships See list of all relationships

Edit Relationship

1. Right-click relationship line
2. Properties
3. Change settings
4. OK

Delete Relationship

1. Click relationship line
2. Press Delete

Or:

1. Manage Relationships
2. Select relationship
3. Delete

Inactive Relationships

Mark relationship as inactive: Used only when explicitly referenced in DAX

Useful for: Multiple date relationships (OrderDate, ShipDate, etc.)

Try This Exercise

Build a simple model:

1. Create 3 tables in Excel:

Sales.xlsx:

• OrderID
• ProductID
• CustomerID
• Amount

Products.xlsx:

• ProductID
• ProductName
• Category

Customers.xlsx:

• CustomerID
• CustomerName
• City

2. Import all to Power BI
3. Go to Model View
4. Create relationships:
   • Sales[ProductID] to Products[ProductID]
   • Sales[CustomerID] to Customers[CustomerID]
5. Verify in Report View:
   • Add ProductName and Amount to visual
   • Should work automatically!

Common Modeling Mistakes

Circular dependencies: Tables connected in a loop. Power BI won't allow!

Wrong cardinality: Many-to-many when should be one-to-many

No primary keys: Relationships on non-unique columns

Too many bidirectional: Causes performance and logic issues

Next Module Preview

In Module 2, you'll learn DAX (Data Analysis Expressions) to create calculated columns and measures!

Congratulations!

You now understand data modeling - the foundation of good Power BI reports!

Tip: Good model design = 80% of report success. Spend time here!