I85PREP
⌘ K
DP-500 study materials
DP-500 study resources

DP-500: Monitor and optimize enterprise-scale analytics solutions

DP-500 · Work through your exam, one topic at a time.

All guide sections

Monitor and optimize enterprise-scale analytics solutions

Domain 5 - Monitor and optimize enterprise-scale analytics solutions

This domain tests finding and fixing slow reports, expensive queries, and capacity pressure across Power BI and Synapse.

Diagnosing report performance

  • Performance analyzer in Power BI Desktop breaks each visual into DAX query time, rendering, and waiting.
  • DAX Studio shows server timings and the query plan; a large gap between total and SE (storage engine) time means formula engine work, usually iterators or complex filters.
  • VertiPaq Analyzer reports column cardinality and size; the largest columns are usually the fix.

Model and query tuning

  • Remove unused columns, reduce precision on decimals and datetimes, and replace calculated columns with measures.
  • Keep Power Query steps foldable; check "View Native Query" to confirm folding. Non-folding steps force full loads.
  • For DirectQuery, reduce visuals per page, avoid DAX patterns the source cannot translate, and add aggregations.
  • In Synapse, inspect sys.dm_pdw_exec_requests and sys.dm_pdw_request_steps, create statistics on external tables, and use result set caching and materialized views for repeated heavy queries.

Monitoring the service

  • Azure Monitor diagnostic settings send Synapse and Data Factory logs to Log Analytics.
  • The Premium capacity metrics app shows CPU and memory utilization, throttling, and refresh duration by workspace and item.
  • Use the Power BI activity log for audit trails, refresh history for failures, and usage metrics reports for adoption.

Worked example

A matrix visual takes 12 seconds. Performance analyzer attributes 10 seconds to the DAX query; DAX Studio shows high FE time with a nested SUMX over a 50-million-row fact table. Rewrite the measure as CALCULATE(SUM(Sales[Amount]), filter conditions) and move row-level logic into a precomputed column only if needed. Reasoning: the storage engine can aggregate the column directly, avoiding a row-by-row scan in the formula engine.

Traps

  • Blaming the visual when the delay is actually a slow source query.
  • Adding aggregations but querying at a grain they do not cover.
  • Ignoring capacity throttling during peak refresh windows.
  • Scheduling refreshes and heavy interactive queries at the same time.