PL-300 Model the data Practice Question
You need to design a data model for a sales analysis that includes measures for total sales, sales by product, and sales by customer. The source data has a 'Transactions' table with columns: TransactionID, Date, CustomerID, ProductID, Quantity, Amount. What is the recommended star schema design?
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 fact table and separate dimensions for Date, Customer, and Product
Option B is correct because a star schema centers on a single fact table (here, Transactions with Quantity and Amount as measures) surrounded by separate dimension tables for Date, Customer, and Product, which lets you aggregate total sales and slice by product or customer efficiently. Keeping each dimension distinct preserves clean grain, supports conformed attributes, and enables the required sales-by-product and sales-by-customer analysis. Option A is wrong because splitting into two fact tables fragments the same transaction grain and complicates cross-measure analysis. Option C is wrong because a single denormalized table is not a star schema and loses dimensional modeling benefits. Option D is wrong because combining Customer and Product into one dimension creates a snowflake-like or junk dimension that prevents independent analysis by each attribute.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create two fact tables: one for sales and one for customers
Why it's wrong here
Customers are a descriptive business entity, not a set of measurable events; placing them in a fact table violates the fundamental fact/dimension distinction. A fact table should store additive numeric measures at the transactional grain, while a customer dimension holds attributes like name, segment, and region. Using two fact tables also forces unnatural relationships and risks fan traps, making customer-level aggregations and DAX calculations unnecessarily complex.
- ✓
Create a fact table and separate dimensions for Date, Customer, and Product
Why this is correct
This is the canonical star schema: a single sales fact table contains additive measures (quantity, revenue) plus foreign keys to separate Date, Customer, and Product dimension tables. Each dimension is at its own grain and contains only descriptive attribute columns, enabling users to slice and filter sales independently by any combination of date, customer, and product attributes. This design minimizes redundancy, supports fast aggregations, and produces unambiguous relationships, making it the correct choice for Power BI performance and maintainability.
- ✗
Create a single table with all columns
Why it's wrong here
A single denormalized table with every column would repeat customer and product attributes on every sales row, causing massive data redundancy and bloated storage. Attribute changes (e.g., a customer's region) must be updated in every row, making refresh slow and error-prone. Furthermore, Power BI's tabular engine performs best when descriptive attributes live in small dimension tables that filter large fact tables; the flat-table approach destroys that architecture and degrades query performance.
- ✗
Create a fact table and one dimension containing Customer and Product
Why it's wrong here
A combined Customer–Product dimension mixes two unrelated grain levels: one customer has many products and one product has many customers, so the table would either duplicate customer attributes for every product or vice versa. There is no single natural key that uniquely identifies a 'customer-product' entity, so relationships with the fact table become ambiguous and filters behave unpredictably. Each business entity needs its own standalone dimension table to preserve the correct one-to-many relationship to sales.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 524 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.