Power BI DirectQuery Performance Features

DP-600 By islever Editorial Last updated

Short answer

AB Use user-defined aggregations and automatic aggregation to reduce query execution time by minimizing the data scanned from the source.

Question

You have a Fabric workspace that contains a DirectQuery semantic model. The model queries a data source that has 500 million rows. You have a Microsoft Power Bi report named Report1 that uses the model. Report1 contains visuals on multiple pages. You need to reduce the query execution time for the visuals on all the pages. What are two features that you can use? Each correct answer presents a complete solution, NOTE: Each correct answer is worth one point.
  1. user-defined aggregations Correct Answer
  2. automatic aggregation Correct Answer
  3. query caching
  4. OneLake integration

Key Takeaways

The exam tests knowledge of specific performance optimization tools for DirectQuery models, specifically distinguishing between user-defined and automatic aggregations versus caching or integration features.

This question addresses reducing query execution time for large DirectQuery datasets in Power BI. The correct solution involves using aggregation features to minimize data scanning.

Common mistake: Many candidates incorrectly select Query Caching (C), believing it is a primary feature for all visuals. However, caching is often limited to the first page load or specific scenarios, whereas aggregations actively reduce the data volume scanned by the engine for all queries.

Expert Analysis

Why the Answer Is Correct

Both User-defined aggregations (A) and Automatic aggregations (B) are designed to optimize DirectQuery performance by allowing the engine to scan smaller summary tables instead of the full 500 million row dataset. This directly reduces query execution time for visuals across all pages.

Why the Other Options Are Wrong

Query caching (C) primarily caches results for the initial page load and does not reliably accelerate subsequent interactions or new visual queries on other pages as effectively as aggregations. OneLake integration (D) refers to storage architecture (DirectLake mode) rather than a feature you 'use' to optimize an existing DirectQuery model's execution time in this context.

Community Comment Notes

Community consensus strongly favors AB, with users noting that aggregations prevent full scans. Some users argued for C, but the majority clarified that caching is insufficient for the broad scope of 'visuals on multiple pages' compared to the structural optimization provided by aggregations.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When asked about optimizing DirectQuery performance for large datasets, prioritize 'Aggregations' (both user-defined and automatic). Avoid 'Caching' unless the question specifically mentions repeated identical queries on the same page, as aggregations are the robust architectural solution for reducing scan volume.

Frequently Asked Questions

Why is Query Caching not the best answer?

Caching stores results for the first page load but does not consistently optimize queries for new visuals or pages as effectively as aggregations which reduce the underlying data volume.

What is the difference between User-defined and Automatic Aggregations?

User-defined aggregations require manual creation of summary tables, while Automatic aggregations are created by the service based on query patterns without manual intervention.

More DP-600 FAQ →

Community Votes

AB
100%

100% of anonymous learners picked answer AB. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 13 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 13

  1. sraakesh95 👍 19
    Agree with lengzhai's reference of the 2 links: A - Custom aggregations enables PBI to not perform a Full Scan of the underlying datasets. B - The AutoAggregations feature automatically creates aggregations on large datasets and based on query optimization determines the total number of rows that requires processing based on the generated query plan. Incorrect to this question context: C - Although caching helps improve performance on large datasets, it doesn't support DirectQuery (Important note in https://learn.microsoft.com/en-us/power-bi/connect-data/power-bi-query-caching) ; Also, it is a feature available in PBI Service that is automatic and needs no intervention from the user.
  2. Momoanwar 👍 11
    D: onelake integration not for Direct Query C: only at loading for first page So AV
  3. lengzhai 👍 5
    Agree with A B https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced https://learn.microsoft.com/en-us/power-bi/enterprise/aggregations-auto
  4. Fermd 👍 4
    A. User-defined aggregations (UDAs) allow you to pre-aggregate specific calculations directly in the semantic model. This reduces the amount of data that needs to be retrieved from the source each time a visual requires the calculation, significantly improving query execution time. C. Power BI Desktop enables query caching for DirectQuery models. This stores frequently used queries on the client machine, eliminating the need to re-send them to the source data for subsequent interactions.
  5. AshwiniVivek 👍 2
    User-defined aggregations (A): User-defined aggregations allow you to create pre-aggregated tables that summarize data at a higher level, reducing the volume of data that needs to be queried for specific visuals. This can significantly improve performance for common queries by avoiding the need to scan the entire dataset. Query caching (C): Query caching stores the results of previous queries for reuse. When users interact with visuals that require similar data, the cached results can be returned more quickly than querying the source again, thereby reducing execution time for those visuals.
Show 8 more comments
  1. Nicofr 👍 2
    https://learn.microsoft.com/en-us/power-bi/enterprise/aggregations-auto https://learn.microsoft.com/en-us/power-bi/enterprise/onelake-integration-overview
  2. estrelle2008 👍 2
    Agreed AB. Although query caching (C) will reduce query execution time too, you risk outdated cached results when working with real-time or dynamic data.
  3. 282b85d 👍 1
    A&B While query caching can be beneficial in certain scenarios, user-defined aggregations and automatic aggregations are typically more effective for improving query performance in Power BI reports with large datasets and complex queries. These methods reduce the volume of data processed in real-time queries, directly addressing the performance bottlenecks associated with querying large datasets.
  4. 6d1de25 👍 1
    B&D are correct. Direct Lakes are great for performance in the OneLake integration https://learn.microsoft.com/en-us/fabric/get-started/direct-lake-overview
  5. NRezgui 👍 1
    A. user-defined aggregations B. automatic aggregation
  6. Rakesh16 👍 2
    A & B is the answer
  7. stilferx 👍 1
    IMHO, A & B looks good
  8. Murtaza_007 👍 1
    CHATGPT saya AC

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Fabric Deployment Pipeline Folder Structure Behavior

S-grade

Accessing OneLake Shortcuts in Fabric Notebooks

S-grade

Direct Lake Fallback to DirectQuery with Row-Level Security

S-grade

Displaying All Rows in Power BI Python Visuals

S-grade

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide