PL-300 Prepare the data Practice Question
You are a data analyst at a retail company. You are building a Power BI report to analyze sales performance across multiple stores. The source data comes from an Azure SQL Database that contains a table 'Sales' with columns: StoreID, ProductID, SaleDate, Quantity, and Amount. The database also has a 'Stores' table with StoreID and StoreName, and a 'Products' table with ProductID, ProductName, and Category. You need to create a data model that supports filtering by store, product category, and date, and also allows calculation of year-over-year sales growth. You want to minimize the model size and ensure optimal performance. The data volume is large (millions of rows). You must design the data model. What should you do?
⚠ Common exam trap
A common mix-up: candidates choose Option A (flat table) thinking it simplifies the model, not realizing that star schema design is essential for performance and compression in large datasets, and that Power BI's query folding can handle joins efficiently without merging.
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
✓
Import Sales, Stores, and Products tables, create a separate date table using CALENDAR, and establish relationships between Sales and dimension tables.
It follows the star schema best practice: importing dimension tables (Stores, Products, a dedicated Date table) and the fact table (Sales) separately, then creating relationships. This minimizes model size by avoiding data duplication and enables efficient filtering by store, product category, and date. The separate date table is essential for accurate year-over-year calculations using DAX time intelligence functions like SAMEPERIODLASTYEAR, which require a continuous date range.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Import all tables as they are and create a single flat table by merging Sales, Stores, and Products in Power Query.
Why it's wrong here
Importing all tables and merging them into a single flat table in Power Query denormalizes the data, causing every Sales row to carry repeated dimension attributes and expanding the physical model size significantly. This approach degrades refresh performance and compression because columnar storage loses its ability to efficiently compress repeated values, and it prevents the query engine from leveraging star schema optimizations. It also makes updates to Store or Product information require a full reprocess of the entire table rather than simply updating a dimension.
- ✓
Import Sales, Stores, and Products tables, create a separate date table using CALENDAR, and establish relationships between Sales and dimension tables.
Why this is correct
Creating a star schema by importing Sales as a fact table along with Stores, Products, and a separate date table generated via CALENDAR is the optimal design. The date table must be marked as a date table in Power BI to enable time-intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR to work predictably across fiscal and calendar periods. Relationships between Sales and the dimension tables filter facts efficiently, reduce model size through dimension normalization, and improve DAX query performance, making this the correct approach.
- ✗
Import Sales table only and create calculated columns for StoreName and ProductName using RELATED.
Why it's wrong here
Importing only the Sales table and attempting to create calculated columns for StoreName and ProductName with RELATED fails because RELATED can only reference fields from tables that already exist in the model and have an active relationship—Stores and Products aren't imported, so the formula will error. Even if you temporarily import those tables, the resultant calculated columns store duplicated text values in the fact table, expanding model memory and degrading performance, while still lacking a dedicated date table for time intelligence.
- ✗
Import Sales table and use the auto date/time feature for time intelligence.
Why it's wrong here
The auto date/time feature silently creates hidden date tables for every date column, adding many extra hierarchy columns (Year, Quarter, Month, Day) that inflate model size and reduce clarity. These hidden tables are inflexible—they can't be customized for fiscal years, holiday calendars, or week-based calculations, and their behavior changes unpredictably when date columns are used in relationships. In contrast, a single explicit date table built with CALENDAR and properly marked gives you full control over time hierarchies and ensures consistent time-intelligence calculations.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-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.