PL-300 Prepare the data Practice Question
You are designing a Power BI data model for a sales analysis. The source data has a table 'Orders' with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, UnitPrice. You also have a table 'Customers' with CustomerID, CustomerName, and 'Products' with ProductID, ProductName. You need to create a star schema. What should you do?
⚠ Common exam trap
Microsoft often tests the misconception that splitting fact tables by time (e.g., year) is beneficial, but the correct approach is to keep a single fact table with a date dimension for time-based analysis.
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
✓
Keep Customers and Products as separate dimension tables, and Orders as the fact table
In a star schema, a single fact table (Orders) stores quantitative measures (Quantity, UnitPrice) and foreign keys (CustomerID, ProductID) that link to dimension tables (Customers, Products) containing descriptive attributes. This design optimizes query performance by reducing joins and enabling efficient aggregation, which is a best practice for Power BI data modeling.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Split Orders into multiple fact tables by year
Why it's wrong here
Splitting Orders into multiple fact tables by year fragments the central fact table of your star schema, forcing every measure to aggregate across several tables and blocking seamless year-over-year comparisons. A date dimension with a Year attribute already provides time-based analysis, so annual partitioning only adds relationship complexity and unnecessary query overhead without removing any modeling benefit.
- ✗
Create a snowflake schema by normalizing Customers and Products further
Why it's wrong here
Creating a snowflake schema by normalizing Customers and Products introduces extra junction tables and additional join paths, which inflate the model's size and slow DAX queries because Power BI must traverse more relationships. Unlike row-store databases, Power BI's VertiPaq columnar engine rewards denormalized dimension tables, so the added normalization yields no storage or performance gain and only makes the star schema harder to maintain.
- ✓
Keep Customers and Products as separate dimension tables, and Orders as the fact table
Why this is correct
Keeping Customers and Products as separate dimension tables and Orders as the fact table correctly implements a star schema: the fact table holds numeric measures and foreign keys, while each dimension provides descriptive attributes linked by one-to-many relationships. This design lets users filter sales by any customer or product attribute without duplication or fan-out, and it lets DAX aggregate order measures directly and efficiently.
- ✗
Merge Customers and Products into a single dimension table
Why it's wrong here
Merging Customers and Products into a single dimension table combines two distinct business entities, creating a non-atomic dimension full of redundant attribute combinations and causing incorrect filtering when a customer is associated with many products. It also forces the fact table to store both CustomerID and ProductID but handle them as one key, which breaks the star schema's principle of one dimension per business entity and makes independent slicing by customer or product impossible.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 524-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.