PL-300 Model the data Practice Question
You are creating a Power BI report for a small business. The data source is a Microsoft Access database with tables: Customers (CustomerID, CompanyName, City), Orders (OrderID, CustomerID, OrderDate, Amount). You need to model the data to analyze total orders by customer and by month. What is the most 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
✓
Import both tables into Power BI and create a relationship between CustomerID columns.
Importing both tables and creating a relationship between the CustomerID columns builds a proper star-schema-style model in Power BI, letting the Customers dimension filter the Orders fact table so totals by customer and by month can be computed efficiently with DAX. This approach leverages Power BI's in-memory VertiPaq engine and relationship-based filter propagation, which is the recommended modeling pattern for this kind of analysis. Option A does not fit because DirectQuery is not supported for Microsoft Access as a data source in Power BI, and even where DirectQuery applies it would not be more efficient here. Option B is less flexible because pre-joining the tables in Power Query flattens the model and complicates month-level aggregation and reuse. Option C is unnecessary overhead for a small business scenario and adds migration effort without modeling benefit.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use DirectQuery on the Access database to avoid importing.
Why it's wrong here
DirectQuery against Access sends live queries to the Jet/ACE engine for every visual interaction, which lacks the query optimizer, columnstore compression, and indexing of SQL Server — causing severe latency and blocking for even modest datasets. Access is a file-based database with row/page-level locking and no server-side caching, so concurrent report users will further degrade performance. Since the scenario is a small business with a small dataset, importing into memory is vastly faster and avoids these live-query bottlenecks.
- ✗
Create a single query in Power Query that joins the tables and import the result.
Why it's wrong here
Merging the tables in Power Query creates a de-normalized flat table that repeats customer attributes (name, address, etc.) on every order row, bloating the model and inflating storage. This forces measures to rely on defensive DAX like DISTINCTCOUNT for customer counts instead of simple aggregations over a properly modeled fact table, and it prevents the automatic filter propagation that a star schema relationship provides. The duplicated data also increases refresh time and memory footprint, contradicting the goal of an efficient, compact model.
- ✗
Migrate the Access database to SQL Server and then import.
Why it's wrong here
Migrating Access to SQL Server introduces substantial infrastructure overhead—provisioning a server, designing schemas, handling authentication, and building an ETL process—which is unjustified for a small business with a small Access dataset. Power BI's Access connector already extracts table data efficiently, so the extra hop adds complexity and maintenance cost without improving query performance because the data would still be imported into the same in-memory engine. This approach is only worth considering if the data exceeds memory limits or requires near-real-time refresh, neither of which applies here.
- ✓
Import both tables into Power BI and create a relationship between CustomerID columns.
Why this is correct
Importing both tables into Power BI loads the data into the VertiPaq in-memory columnar store, which compresses values and delivers sub-second DAX aggregations even without external infrastructure. Creating a one-to-many relationship on CustomerID between the Customers dimension table and the Orders fact table enables standard star-schema filtering—measures like 'Total Revenue by Customer' work automatically via row context and filter propagation, with no need for explicit joins. This design keeps the model compact, avoids data duplication, and is the recommended pattern for small datasets in Power BI.
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.