33.3× Faster Dashboards

A dashboard is an interaction, not a query.

What humans feel is the whole trip from click to useful chart. That trip may include a browser calculation, a request from the BI tool, time waiting for a warehouse, SQL execution, data transfer, and chart drawing. Tuning the SQL can help, but it will not fix a queue, ten duplicate requests, or a large calculation left sitting in the browser.

Start with a trace, not a guess.

Open a page from a clean session and record what actually runs. Count warehouse queries and browser calculations. Note which widgets launch them, which data sources they read, whether the result came from cache, and how long each stage took. One slow query is easy to see. Ten medium queries running together can be harder to spot, even when their combined work is the real problem.

In the legacy workbook, one slow request merged logic from eleven workbook elements. That was a useful clue. The page was not merely reading a reporting table. It was rebuilding relationships and business rules while the user waited.

Parallel work is useful until it repeats the same thinking.

Parallel requests aren't automatically bad. Independent charts can load at the same time and shorten the total wait. Trouble starts when each request scans similar data, repeats the same joins, or calculates a slightly different version of the same metric. Then you pay twice: once in wait time, and again when two charts on the same page disagree. Those requests compete for warehouse slots, network bandwidth, browser memory, and the reader's attention. A page can look busy while still making little useful progress.

Think of ten cooks preparing ten plates. Parallel work helps if each cook has a separate task and the ingredients are ready. It hurts if every cook first walks to the store, makes the same stock, and writes a new recipe. The dashboard had the second pattern. Several widgets performed their own setup work before they could draw anything.

Concurrency also changes the shape of a benchmark. A single SQL statement tested alone may be quick, while the same statement slows down beside nine other requests. Measure the full page first. Then test the costly queries alone to learn whether the problem is the query itself, competition between queries, or both.

Widgets should present an answer, not rebuild it.

A widget-level calculation feels harmless when a dashboard is still small. A chart needs a rate, so the author adds a formula. Another chart needs a related rate, so it adds a second formula and a new join. Filters, exceptions, and campaign rules follow. Over time, each widget grows its own private route to an answer.

That design creates three costs. First, repeated joins and formulas run whenever the page loads. Second, two widgets can give different answers to the same question — not because the business changed, but because each path encoded the rule a little differently. Third, every change has to be made in several places at once. The dashboard becomes both the display layer and the data model, which makes it slow and hard to trust.

The shared-model rule

If several widgets use the same relationship or metric, define it once before the dashboard request. Give every widget the same prepared source. Leave only true presentation choices, such as the selected date range or grouping, in the dashboard.

In everyday terms, that is precomputation: do stable, reusable work ahead of time. The warehouse refreshes that work on a schedule or after its inputs change. A reader then pays for the small final step, not the full history of how the answer was made.

Move rules to the lowest useful shared layer.

Not every formula belongs in the final reporting table. Put a rule at the lowest layer where it is both correct and reusable. Clean source fields close to the source. Resolve stable identities in a shared dimension. Store many-to-many membership in a bridge. Calculate common reporting measures in a serving mart. Keep a temporary display choice, such as sorting the visible top ten, in the widget.

Placement matters because a rule placed too early may lose detail you will need later. A rule placed too late gets repeated. The right layer preserves the raw fact while giving common readers a prepared answer.

Why one model is not the same as one giant table

The goal is one trusted route to each kind of answer. It is not a table with every field and every grain mixed together. A giant table can repeat rows, inflate totals, waste storage, and become slow again. Good shared models have clear jobs. A history table may keep one row per post and day. A serving table may keep only the latest row per post. A bridge may keep one row for each valid campaign and post relationship.

Set the row meaning before you tune the query.

The grain of a table is what one row means. This is the first question in dashboard design because joins can silently change it. Suppose one post belongs to two campaigns. Joining post performance directly to campaigns may produce two copies of the post. If a widget sums views after that join, the page can become fast and wrong.

The rebuilt path separated these jobs. The page did not scan the full post-by-day history when it only needed the latest state. Campaign membership lived in its own bridge. The reporting mart expanded that relationship at a known grain and exposed the metrics the widgets shared.

Prepared data has a price.

Moving work out of the click path does not make the work disappear. It moves the cost to refresh time. The team now owns storage, refresh order, freshness targets, and failure recovery. That is usually a good trade when many readers reuse the result, but it should be said out loud. In this system, prepared tables refreshed through a controlled warehouse path. A latest-state table used a twelve-hour freshness target because the page did not need second-by-second change.

Choose the smallest table that can answer the question.

A time-series chart needs history. A campaign scorecard may only need the latest accepted value for each post. Reading the history table for both is safe but wasteful. It scans many observations that the scorecard will throw away. A latest-state fact removes those unused rows before the click. A campaign-post serving mart goes one step further by preparing the relationship the campaign widgets use.

Smaller does not only mean fewer rows. It can mean fewer columns, simpler joins, and less work to find the newest record. The best serving table follows the question. It contains enough detail to filter and explain the answer, but not every piece of source history.

Watch for fanout.

Fanout happens when one row joins to several rows and a measure gets copied. For example, one post with 1,000 views may belong to two campaigns. A campaign-post result needs two relationship rows, but a portfolio total must not blindly sum both copies and report 2,000 unique views. The model needs a declared rule for the level at which a metric may be added.

This is why grain is also a performance tool. Once each table has one clear row meaning, the warehouse can prepare the right result without defensive widget formulas, distinct counts, or repeated deduplication. Correct structure removes work.

Use two tests because they answer two different questions.

A performance study can easily compare unlike things. This project used two views of speed, and they need to stay separate.

Read a distribution, not one average.

The median, also called P50, is the middle observed time. P90 is the time that 90 percent of observations beat. P95 and P99 move farther into the slow tail. P90 is useful, but it is not the worst case. A page can have a good median and still punish some users with very long waits.

Always show the sample size beside a percentile. A percentile from three runs is a quick check, not a stable service target. Also record failures. A fast query that fails is not a good user experience.

Treat cache as part of the experiment.

A cache returns work that was already done. Warm-cache tests can describe repeat use. Cold-cache tests can describe first use. Neither is wrong, but mixing them hides the cause of a speed difference. In a fresh trace from this study, 24 of 30 test-workbook warehouse queries were cached, compared with one of three legacy-workbook queries. That makes cache a real part of the observed experience — and it confounds any attempt to pin the gain on one cause.

A benchmark needs a written protocol.

Write the steps before seeing the result. Name the saved view, account, filters, time window, warehouse size, and completion signal. Decide whether the cache should be warm or cold. Alternate the order of the two versions so the first one does not always receive a different system state. Repeat enough times to see normal variation, and keep every valid run instead of dropping slow ones because they look unusual.

Capture at least three clocks: the user's page-load time, the BI tool's processing time, and warehouse execution time. If the page is slow while warehouse time is low, the bottleneck may be browser work, request coordination, or data transfer. If warehouse time dominates, inspect scans, joins, grouping, and queue pressure.

Speed is a failed project if the number changes meaning.

Moving rules into a shared model changes joins, row selection, and metric timing. Each of those changes can alter the answer. So performance testing needs a separate correctness test. Do not use matching numbers as a speed sample, and do not use fast load times as proof of accuracy.

The correctness study compared 336 known campaign deliverables across three campaigns. The new model matched 333 of 336 campaign, link, creator, and platform records. That is a 99.1 percent match for the tested identity fields. It did not validate every engagement metric, and it did not turn the three campaigns into a performance benchmark.

A practical semantic contract

• Write down what one row means in every prepared table.
• Name the source of each metric and the time at which it is valid.
• Test joins for missing rows and multiplied rows.
• Compare known records at the level a user can inspect.
• Keep unresolved differences visible instead of forcing a match.

A short playbook for a slow dashboard

1. Define the user action.Choose the exact page, filters, and sign that tells you loading is done.
2. Trace the whole request.Count widget requests, warehouse queries, browser work, queue time, fetch time, and cache hits.
3. Group repeated logic.Find joins, identity rules, latest-row rules, and metrics rebuilt by several widgets.
4. Set the grain.State what one row means before combining data. Protect measures from join fanout.
5. Prepare shared work upstream.Build small facts, bridges, latest-state tables, and serving marts for work many readers reuse.
6. Keep widgets thin.Let them filter, group, and display. Do not let each one invent the business answer.
7. Benchmark fairly.Repeat matched loads. Separate cold and warm cache. Report sample size, percentiles, and failures.
8. Check the meaning.Reconcile known records and metrics. A quicker wrong answer is still wrong.

When not to precompute

Keep work at request time when the question is rare, the result must be live to the second, or the possible filter combinations are too wide to prepare well. Precompute when the same logic serves many widgets or users, when source scans are large, and when a small delay in freshness is acceptable. Most useful systems end up mixing both approaches.

What stuck

The biggest win was not a clever SQL tweak. Define shared business logic once, calculate the reusable metrics once, and let every widget consume the same trusted result. It worked because the page stopped behaving like ten separate data products.

Speed was the headline. Consistency was the quieter secondary effect. When every widget stopped inventing its own joins and metrics, the numbers on the page had one place to come from — so they stopped drifting apart for accidental reasons. A dashboard feels fast when someone gets a trustworthy answer before they lose the thread of the question. That takes more than SQL tuning. It needs a clear model, a short request path, and evidence that speed did not change the truth of the numbers.