PL-300 Model the data Practice Question
You are designing a data model for a report that shows sales by region and product category. The source data includes a table 'Sales' with columns: Region, Category, SalesAmount. You also have separate tables 'Regions' and 'Categories' that contain additional attributes. You need to create a star schema. What should you do with the 'Region' and 'Category' columns in the 'Sales' table?
⚠ Common exam trap
Many candidates think keeping attributes in the fact table is simpler and faster, not realizing that a normalized star schema with foreign keys actually improves performance and scalability in Power BI.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Remove them from the Sales table and use foreign keys to link to the dimension tables
In a star schema, dimension tables (Regions, Categories) contain descriptive attributes, and the fact table (Sales) stores foreign keys referencing those dimensions. Removing the Region and Category columns from the Sales table and replacing them with foreign keys (e.g., RegionID, CategoryID) normalizes the model, reduces data redundancy, and enables efficient filtering and slicing by region and category attributes. This approach aligns with best practices for Power BI data modeling, ensuring optimal query performance and maintainability.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Remove them from the Sales table and use foreign keys to link to the dimension tables
Why this is correct
This is the correct star schema approach. Remove region and category descriptive text from the Sales table and replace them with foreign key columns (e.g., RegionID, CategoryID) that reference the primary keys of dedicated dimension tables. This normalizes the fact table, eliminates redundant string storage, and enables DAX filter and slicer operations to traverse relationships efficiently. It also simplifies future updates to dimension attributes without rewriting historical sales rows.
- ✗
Keep them in the Sales table as attributes for simplicity
Why it's wrong here
Keeping these attributes directly in the Sales table produces a wide, denormalized fact table that repeats the same region and category names on every sales row. This inflates storage, slows scan and aggregation performance, and creates a high risk of update anomalies when a category or region is renamed. A proper star schema separates these slowly changing descriptive attributes into dimensions to keep the fact table lean and consistent.
- ✗
Merge the Regions and Categories tables into the Sales table
Why it's wrong here
Merging the Regions and Categories tables into the Sales table does not create a dimension model; it collapses the entire model into a single monolithic table. This approach destroys the ability to browse hierarchies, forces every sales change to carry full descriptive context, and makes filtering for reporting more expensive. It also eliminates the referential integrity benefits of having separate keyed dimension tables.
- ✗
Mark the Sales table as a date table
Why it's wrong here
Marking the Sales table as a date table is conceptually invalid because a date table must contain a contiguous set of unique date values with no gaps, not sales transactions. Region and category are not date columns, and designating the Sales table as a date table will cause DAX time intelligence functions to return incorrect results or fail. Even if the table contained sales dates, it would still violate the requirement that the date column be unique and cover the full calendar period needed.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 217 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.