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.
To reduce the query execution time for visuals on multiple pages in a Power BI report that uses a DirectQuery semantic model with a data source containing 500 million rows, you can use user-defined aggregations and automatic aggregations. User-defined aggregations allow you to pre-aggregate specific calculations directly in the semantic model, which reduces the amount of data that needs to be retrieved from the source each time a visual requires the calculation. Automatic aggregations, on the other hand, automatically create aggregations on large datasets and optimize query performance by determining the optimal number of rows to process based on the generated query plan. Together, these features can significantly improve query performance by reducing the volume of data that needs to be processed in real-time queries.
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.
D: onelake integration not for Direct Query C: only at loading for first page So AV
I mean AB*
Agreed to AB. Both UDA's and AA optimize direct query performance. One just requires more manual work and in depth knowledge data modelling and query optimization techniques (UDA), whereas the other makes simplifies this process through the use of ML algorithms (AA).
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
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.
Question is about Fabric workspace... not Power BI Desktop !
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.
https://learn.microsoft.com/en-us/power-bi/enterprise/aggregations-auto https://learn.microsoft.com/en-us/power-bi/enterprise/onelake-integration-overview
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
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.
CHATGPT saya AC
IMHO, A & B looks good