DP-900 Describe an analytics workload on Azure Practice Question
Your company has a Power BI dashboard that uses a data model with a single large fact table and several dimension tables. The dashboard loads slowly when users filter by multiple dimensions. Which design change would MOST improve performance?
⚠ Common exam trap
A common mix-up: candidates confuse UI-level filter scoping (page-level vs. report-level) with actual query performance optimization, or they mistakenly believe that aggregating data or switching to DirectQuery will always improve speed, ignoring the fundamental importance of star schema design for in-memory analytics.
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
✓
Ensure the fact table and dimension tables follow a star schema design with proper relationships.
A star schema design with proper relationships between the fact table and dimension tables is the foundational best practice for optimizing Power BI data models. This design minimizes the cardinality of relationships, reduces the size of the data model, and enables efficient query folding and storage engine compression, which directly improves filter performance across multiple dimensions.
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 page-level filters instead of report-level filters.
Why it's wrong here
Changing filters from report-level to page-level only alters where the filter is applied in the visual hierarchy; it does not change how the query engine processes or caches data. The VertiPaq storage engine still scans the same column segments regardless of filter scope, so this adjustment has negligible performance impact. Performance gains require optimizing relationships, cardinality, and query patterns, not filter placement.
- ✗
Create a calculated table that aggregates the fact table at a higher granularity.
Why it's wrong here
A calculated table that aggregates a fact table to a higher granularity will reduce per-row detail but does not automatically improve filter propagation across dimension tables. Because calculated tables are stored in memory and evaluated during refresh, they can increase model size and may still inherit inefficient filter paths if relationships are not properly set. Pre-aggregation can help only if the aggregated grain aligns with the dimensions being filtered, which is rarely the case for generic filter-heavy workloads.
- ✓
Ensure the fact table and dimension tables follow a star schema design with proper relationships.
Why this is correct
A star schema with separate dimension tables and a single fact table ensures that filters propagate through one-to-many relationships efficiently, allowing the VertiPaq storage engine to compress dimension keys and iterate over only the relevant fact rows. Proper relationship cardinality reduces ambiguity and prevents row multiplication, which directly accelerates filter operations and DAX queries. This design is specifically optimized for analytical workloads and is the foundational best practice for Power BI data models.
- ✗
Convert the data model to a composite model using DirectQuery for some tables.
Why it's wrong here
Converting to a composite model with DirectQuery for some tables does not inherently improve performance; it offloads queries to the underlying source, which must have efficient indexing, query folding, and low network latency to deliver gains. If the source query is poorly formed or lacks appropriate indexes, DirectQuery can be slower than an optimized import model. Additionally, mixing storage modes introduces complexity in relationship handling and may cause circular dependencies or unexpected behavior, making it a suboptimal solution for general filtering performance.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Schema
A schema is a blueprint or logical structure that defines how data is organized, stored, and accessed in a database or information system.
Key term
Data model
A data model is a blueprint that defines how data is organized, stored, and accessed in a database or data system.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 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 DP-900 exam.