This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Create Fact Tables and Dimension Tables

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

---

Creating fact tables and dimension tables is a foundational step in preparing data for analysis in Power BI. For the PL-300: Microsoft Power BI Data Analyst exam, this topic tests your understanding of data modeling principles, especially how to structure data into a star schema using Power Query before loading it into the data model.

Microsoft emphasizes not just what fact and dimension tables are, but how and when to create them during data preparation.

---

Why Fact and Dimension Tables Matter

Well-designed fact and dimension tables:

• Improve model performance
• Simplify DAX measures
• Enable accurate relationships
• Support consistent filtering and slicing
• Reduce ambiguity and calculation errors

Exam insight: Many PL-300 questions test whether you recognize when raw data should be split into facts and dimensions instead of remaining as a single flat table.

---

What Is a Fact Table?

A fact table stores quantitative, measurable data that you want to analyze.

Common Characteristics

• Contains numeric measures (Sales Amount, Quantity, Cost)
• Includes foreign keys to dimension tables
• Has many rows (high granularity)
• Represents business events (sales, orders, transactions)

Examples

• Sales transactions
• Inventory movements
• Website visits
• Financial postings

---

What Is a Dimension Table?

A dimension table stores descriptive attributes used to filter, group, and label facts.

Common Characteristics

• Contains textual or categorical data
• Has unique values per key
• Fewer rows than fact tables
• Provides business context

Examples

• Customer
• Product
• Date
• Geography
• Employee

---

Star Schema (Exam Favorite)

The recommended modeling approach in Power BI is the star schema:

• One central fact table
• Multiple surrounding dimension tables
• One-to-many relationships from dimensions to facts
• Single-direction filtering (typically)

Exam insight: If a question asks how to optimize performance or simplify DAX, the answer is often “create a star schema.”

---

Creating Fact and Dimension Tables in Power Query

Starting Point: Raw or Flat Data

Many data sources arrive as a single wide table containing both measures and descriptive columns.

Typical Transformation Approach

1. Identify measures
   • Numeric columns that should remain in the fact table
2. Identify dimensions
   • Descriptive attributes (Product Name, Category, Customer City)
3. Create dimension tables
   • Reference the original query
   • Remove non-relevant columns
   • Remove duplicates
   • Rename columns clearly
   • Ensure a unique key
4. Create the fact table
   • Keep foreign keys and measures
   • Remove descriptive text fields now handled by dimensions

---

Keys and Relationships

Dimension Keys

• Primary key in the dimension table
• Must be unique and non-null

Fact Table Keys

• Foreign keys referencing dimension tables
• May repeat many times

Exam insight: PL-300 questions often test your understanding of cardinality (one-to-many) and correct relationship direction.

---

Common Dimension Types

Date Dimension

• Often created separately
• Supports time intelligence
• Includes Year, Quarter, Month, Day, etc.

Role-Playing Dimensions

• Same dimension used multiple times (e.g., Order Date, Ship Date)
• Requires separate relationships

---

Impact on the Data Model

Creating proper fact and dimension tables results in:

• Cleaner Fields pane
• Easier measure creation
• Improved query performance
• Predictable filter behavior

Poorly designed models (single flat tables or snowflake schemas) can lead to:

• Complex DAX
• Ambiguous relationships
• Slower performance
• Incorrect results

---

Common Mistakes (Often Tested)

❌ Leaving Data in a Single Flat Table

This often leads to duplicated descriptive data and poor performance.

---

❌ Creating Dimensions Without Removing Duplicates

Dimension tables must contain unique keys.

---

❌ Including Measures in Dimension Tables

Measures belong in fact tables, not dimensions.

---

❌ Using Bi-Directional Filtering Unnecessarily

Often used to compensate for poor model design.

---

Best Practices for PL-300 Candidates

• Design with a star schema mindset
• Keep fact tables narrow and tall
• Keep dimension tables descriptive
• Use Power Query to shape tables before loading
• Rename tables and columns clearly
• Know when not to split (very small or static datasets)

Know when not to over-model: If the dataset is extremely small or used for a simple report, splitting into facts and dimensions may not add value.

---

How This Appears on the PL-300 Exam

Expect scenario-based questions such as:

• A dataset contains sales values and product details — how should it be structured?
• Which table should store numeric measures?
• Why should descriptive columns be moved to dimension tables?
• What relationship should exist between fact and dimension tables?

These questions test modeling decisions, not just terminology.

---

Quick Comparison

Fact Table	Dimension Table
Stores measurements	Stores descriptive attributes
Many rows	Fewer rows
Contains foreign keys	Contains primary keys
Central table	Surrounding tables
Used for aggregation	Used for filtering

---

Final Exam Takeaways

• Fact and dimension tables are essential for scalable Power BI models
• Create them during data preparation, not after modeling
• The PL-300 exam emphasizes model clarity, performance, and correctness
• Star schema design is a recurring exam theme

---

Practice Questions

Go to the Practice Exam Questions for this topic.