Tailwind Traders: A DirectQuery Report at 130% Capacity
Tailwind Traders runs a Power BI Premium P1 capacity and a Synapse dedicated SQL pool at DWU1000c. The Sales Performance dataset uses DirectQuery and pages carry up to 40 visuals; average page load is 22 seconds and the 95th percentile is 70 seconds. The Capacity Metrics app shows that between 10:00 and 11:00 the capacity sustains 130% of its 30-minute CU limit, and a single query contributes 38% of that CPU. The dataset has 65 measures, several nesting IFERROR and FILTER over the 400-million-row fact table, and 22 calculated columns that no visual uses. Synapse shows many identical Power BI queries with high compile time. Of 300 users, about 150 open the mobile app and only look at three cards; the rest use detailed pages.
1. Describe the diagnostic sequence you would follow before touching capacity, and what evidence would justify scaling instead.
Read the worked answer
Sequence
- Open the Capacity Metrics app at the 10:00-11:00 timepoint. Read top items and timepoint detail, separating interactive from background. If a refresh or subscription overlaps the hour, that is the cheapest fix and it must be ruled out first.
- Identify the item responsible for the 38% query. Confirm whether it is a user action, a pinned dashboard tile, or an automated subscription. A pinned tile refreshing on open can look like a capacity problem.
- Pull query detail from the diagnostic logs exported to Azure Monitor or Log Analytics, and reproduce the slow visual in Performance Analyzer and DAX Studio.
- In DAX Studio, read Server Timings to separate storage engine from formula engine time. Long storage engine time points at Synapse or folding; long formula engine time points at DAX.
- Check throttling indicators: interactive delay means users are already being slowed, interactive rejection means they are being refused.
What justifies scaling
Scaling is justified only when the top consumers are legitimate, well-modelled, and still exceed the SKU after remediation. If one badly written measure accounts for 38%, P2 simply runs the bad query faster and costs more.
Evidence to collect
Before-and-after CU consumption for the same report, and Synapse DWU utilization to show whether the database or the capacity is the bottleneck.
2. Rewrite the problematic DAX and model patterns, and explain the performance effect of each change.
Read the worked answer
DAX changes
- Replace nested IFERROR with DIVIDE for ratios. DIVIDE handles divide-by-zero natively; IFERROR forces extra evaluation and silently converts real errors into blanks, which hides bugs.
- Replace FILTER(FactGift, ...) over 400 million rows with CALCULATE and direct column predicates, or KEEPFILTERS on dimension columns. FILTER materializes a table and pushes work into the formula engine.
- Use variables so a subexpression is evaluated once rather than once per row or per visual context.
- Audit all 65 measures rather than fixing the two that look suspicious; unused and duplicate measures still cost maintenance and invite accidental use.
Model changes
- Drop the 22 unused calculated columns. In DirectQuery they can be pushed into the generated SQL and inflate every query.
- Reduce visuals from 40 to about 12. Each visual issues at least one query; a page with 40 visuals multiplies concurrency, which is exactly what saturates the capacity.
- Turn off auto date/time and hide unused columns to shrink the metadata surface.
Structural change
Add user-defined aggregations in import mode over the DirectQuery fact so common visuals hit a small table. This is the single highest-leverage change for a DirectQuery model at this scale.
Measure the effect
Use Performance Analyzer for per-visual timings before and after, and DAX Studio Server Timings to confirm fewer storage engine queries.
3. Synapse shows repeated identical queries with high compile time. What do you change in the database and in the report delivery model?
Read the worked answer
In Synapse
- Enable result set caching on the dedicated SQL pool. Identical repeated queries are the ideal case, and it removes both execution and much of the repeated work.
- Create materialized views for the aggregates the dashboards use, with automatic refresh, and keep statistics current; stale statistics explain high compile time and poor plans.
- Check DWU utilization and queuing during 10:00-11:00. If the pool is queuing, raise DWU for the window or use workload management to give Power BI traffic a classifier with reserved resources.
- Use the application name in the connection string so Power BI queries are identifiable in DMVs; otherwise you cannot attribute load.
In the delivery model
- The 150 mobile users looking at three cards should not touch DirectQuery at all. Build a small import-mode dataset with those three cards, refreshed hourly, published as a separate app. That removes roughly half the user population and all mobile page loads from the DirectQuery path.
- For the detailed pages, set a sensible query timeout and reduce visuals per page so a single report cannot open 40 concurrent connections.
- Consider a composite model where the aggregate is import and detail stays DirectQuery, so common views never reach Synapse.
Expected outcome
Most of the capacity relief comes from removing users and visuals, not from tuning the database; the Synapse changes protect what remains.