Analytics & BI Tools · Chapter 13 of 18
Making a dashboard fast, and keeping it that way
A diagnostic order that works on any tool, the four places time actually goes, and the design decisions that beat any amount of tuning.
Dashboard performance work is done badly almost everywhere, for one reason: people optimise the layer they are comfortable with. Analysts tune calculations, data engineers tune the warehouse, and neither measures first. This chapter is a diagnostic order that works regardless of tool.
The four places time goes
Every slow dashboard is one or more of these, and they need completely different responses:
| Where | Symptom | Typical cause | Fix |
|---|---|---|---|
| 1 · The source query | One visual is slow; the query itself is slow when run directly | Missing pruning, a bad plan, an unaggregated scan | Fix it in the warehouse — index, cluster, pre-aggregate. Not in the BI tool |
| 2 · The engine | Queries fast at the source, slow in the tool | Row-by-row evaluation, huge intermediate results, an oversized model | Restructure the calculation, or move the work upstream |
| 3 · Rendering | Queries fast, screen slow | Too many marks, too many visuals on one page | Aggregate, bin, or split the dashboard |
| 4 · Concurrency | Fast alone, slow at 9am | Every user issuing live queries at once | Extract, cache, or scale the capacity — and check whether freshness was ever needed |
The order matters. Measure which of the four it is before changing anything, because the four fixes are disjoint and three of them will do nothing. Every serious tool has a performance-diagnostic view that separates query time from rendering time, and reading it takes five minutes.
The design decisions that beat tuning
Reduce the work rather than speeding it up
Aggregate at the right grain. A dashboard showing monthly revenue by region does not need a row-level fact table. A pre-aggregated table with a thousandth of the rows answers instantly and costs almost nothing to maintain. This single move solves more dashboard performance problems than everything else in this chapter combined, and it is skipped because it feels like cheating.
Default to a narrow view. A dashboard that loads three years of data because the date filter defaults to ‘all’ is doing enormous work for a user who wanted this month. Default the filter to the common case and let people widen it deliberately.
Cut the visual count. Every worksheet is at least one query. A dashboard with fourteen visuals and six filters is issuing a great deal of work per page view. Splitting it into two focused dashboards is usually both faster and better design — nobody reads fourteen visuals at once anyway.
Cut the mark count. A scatter plot with hundreds of thousands of points is hundreds of thousands of objects to lay out and draw, and it is illegible. Binning or aggregating makes it faster and more readable, which is the rare case where performance and design pull the same way.
Remove what nobody uses. Usage telemetry will show that a third of your dashboards have not been opened in ninety days, and each one still refreshes on a schedule and still consumes capacity.
Refresh, and the failure that goes unnoticed
Refresh failures are the most under-monitored thing in most BI estates, and the reason is specific: when a refresh fails, the dashboard does not break. It quietly serves yesterday's data, then the day before's, and nobody notices until someone spots a number that has not moved.
- Alert on refresh failure, and alert to a human who will act. A failure email into a shared mailbox nobody reads is not monitoring.
- Alert on staleness, not only on failure. A refresh that succeeds but pulls no new rows because an upstream job failed is the same outcome for the user and produces no error at all. Put the maximum source timestamp on the dashboard itself — a small ‘data as of’ label is the cheapest data-quality control there is, and it makes staleness visible to the person best placed to notice.
- Watch the refresh duration trend, not just its success. A refresh growing five percent a month will breach its window at a predictable date, and knowing that date in advance turns an incident into a planned piece of work.
- Stagger schedules. Forty models refreshing at 06:00 is a self-inflicted capacity spike, and it is usually the default because nobody chose a time.
Governing the sprawl
Every self-service estate accumulates hundreds of near-duplicate assets. The instinct is to restrict creation, which kills adoption and produces a shadow estate in spreadsheets. The better approach is a tier system that makes the difference visible:
| Tier | What it means | What it requires |
|---|---|---|
| Certified | Numbers you can take to a board meeting | Owned, on the shared semantic model, monitored, reviewed, with a named owner who answers questions |
| Shared | Useful, published, not guaranteed | An owner and a description. That is all |
| Personal | Someone's own analysis | Nothing. Do not govern this — it is where exploration happens |
The point is not the labels; it is that the cost of governance should be proportional to the consequence of being wrong. Applying certification standards to personal exploration stops the exploration. Applying no standard to the board pack produces the meeting from the previous chapter.
And a retirement policy matters more than a creation policy: anything untouched for six months gets archived, with a one-click restore. That removes the maintenance burden and the capacity cost without anyone having to make a judgement about whether a dashboard is good.
Check yourself