Analyticsadvanced
Dashboard Design & Performance Optimization
Explain how dashboard structure, page layout, and query design choices affect load performance and user experience at scale.
A dashboard that is fast with five test users can be unusable with five hundred. Most of the difference is design rather than hardware.
It's like ordering at a restaurant: asking the kitchen to prepare fifteen separate small dishes one order ticket at a time is slower than sending one consolidated order for the same total food, and it's slower still if the kitchen starts cooking dishes nobody at the table actually wanted yet.
Key Concepts
1
The biggest lever is the number of queries a page fires.
every analysis on the page = at least one logical SQL request
15 small analyses = 15 queries, in parallel, on every load
3 denser analyses = 3 queries2
Fifteen analyses each fast in isolation can be slower together, because they compete for connections and for
the database.
3
Views within one analysis are free. A table, a chart and a pivot on the same analysis share one result
set. Splitting them into three analyses triples the queries for the same content.
4
That is the single most useful structural rule: combine, then present multiple views.
5
Put content on more pages, not one long page. A page loads everything on it, including what the user
would have to scroll to reach. Three pages of five analyses beats one page of fifteen, because only the
opened page runs.
6
Prompts should narrow before the query runs. A dashboard defaulting to all regions and all time runs the
widest possible query for every user on every load.
7
default the prompt to the current month, and this user's region
-> the first query is small8
Users who want more can widen it.
9
Cache deliberately. The dashboard's main analyses are the right candidates for seeded cache — run them on
a schedule after the ETL, so the first user of the morning hits a warm cache rather than paying for it.
10
Check what the queries actually cost. Usage tracking gives elapsed time per analysis and how many
physical queries each generated.
11
An analysis generating eight physical queries is usually combining facts at different grains, or failing to
use an aggregate. That is a model problem surfacing as a dashboard problem, and no amount of layout work
fixes it.
12
The order to work in. Reduce the number of analyses. Narrow the default prompts. Then look at the
model — aggregates and content levels. Then consider caching. Adding hardware comes after all of those,
because a page firing fifteen unfiltered queries will consume whatever it is given.
13
What the interviewer is probing.1. "How do you make a dashboard load faster?" Probing: the method. Stalls: "A faster
server." Moves up: fewer analyses per page, default prompts that constrain the query, cached or
aggregate-backed sources, and deferred sections.
14
2. "What is a deferred section?" Probing: the technique. Stalls: "Nothing." Moves up:
content that does not run until the user expands it, so the initial load pays for only what is seen.
15
3. "Why do default prompt values matter so much?" Probing: the practical insight. Stalls:
"Convenience." Moves up: without them the first query is unfiltered across all history, which is
the single most common cause of a slow dashboard.
16
4. "How do you decide what belongs on the first page?" Probing: the design. Stalls:
"Everything." Moves up: the questions people actually ask daily, from usage tracking — detail
belongs one navigation step away.