PL-300 Model the data Practice Question
You are designing a data model in Power BI that includes a fact table called 'Sales' and dimension tables 'Customer', 'Product', and 'Date'. The 'Sales' table contains columns: 'SalesID', 'CustomerID', 'ProductID', 'DateKey', 'Quantity', and 'Amount'. You need to ensure that the model follows star schema best practices and that filters from the 'Customer' table propagate correctly to the 'Sales' table. What should you do?
⚠ Common exam trap
Test-takers frequently think bidirectional cross-filtering (Option B) is needed for filter propagation, but in a star schema, unidirectional filtering from dimension to fact is the correct and efficient approach.
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
✓
Create a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID.
In a star schema, the dimension table (Customer) should have a one-to-many relationship to the fact table (Sales) based on the common key (CustomerID). This ensures that filters applied to the Customer table propagate correctly to the Sales table, maintaining referential integrity and enabling efficient query performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Merge the Customer and Sales tables into a single flat table.
Why it's wrong here
Merging the Customer and Sales tables into a single flat table destroys the star schema's separation between dimensions and facts. This denormalization causes redundant attributes to repeat on every sales row, increasing storage and making updates to customer information more complex. It also loses the natural grain of the fact table, making it harder to enforce referential integrity and requiring manual maintenance to avoid inconsistencies.
- ✗
Set the cross-filter direction to Both on the relationship between Customer and Sales.
Why it's wrong here
Setting the cross-filter direction to Both on the Customer–Sales relationship is unnecessary in a classic star schema because filter propagation from the dimension (Customer) to the fact (Sales) is the only direction needed for typical aggregations and slicers. Bidirectional filtering introduces ambiguity when multiple relationships exist, as it can create unintentional filter paths that lead to unexpected results and slower query performance due to additional filter crossing. In this scenario, the extra direction provides no benefit while adding risk.
- ✗
Create a many-to-many relationship between Customer and Sales using SalesID.
Why it's wrong here
Creating a many-to-many relationship between Customer and Sales using SalesID is invalid because SalesID is a unique fact key, and using it as the join key would attempt to match multiple customers to multiple sales rows without a clear logical basis. A true many-to-many relationship would require a bridge table to disambiguate the association, and it would unnecessarily complicate the model, often causing duplication of sales amounts or incorrect filtering. This pattern violates the star schema principle that dimensions have a one-to-many relationship with fact tables, not many-to-many.
- ✓
Create a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID.
Why this is correct
Creating a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID is the correct star schema pattern. CustomerID is the unique primary key in the Customer dimension table, and it appears as a foreign key in the Sales fact table, allowing each customer to link to multiple sales transactions. This direction supports intuitive filter propagation from the dimension to the fact table, enabling reliable aggregations like total sales per customer while preserving the granularity of the fact table and maintaining a clean, normalized model.
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.