Khizar Ali
All posts

Why your dashboard takes 40 seconds to load

· 5 min read Business intelligence

The complaint always arrives the same way. Someone forwards a screenshot of a spinner with the subject line "is this normal?" — and by the time it reaches you, three people have already decided the warehouse needs more hardware.

It rarely does. In eighteen years of untangling slow reports, the database was the real bottleneck maybe one time in ten. The other nine, the problem was a modeling decision made months earlier by someone who was, reasonably, in a hurry.

1. You're aggregating at run time what you could aggregate once

A dashboard showing monthly sales by region should not be summing millions of transaction rows every time someone opens it. It does that because the metric was pointed at the fact table, which was the fastest thing to build on day one and has been quietly costing thirty seconds a view ever since.

The fix is unglamorous: pre-aggregate. Build the summary table, or the cube, or the materialized view — whatever your platform calls it — and point the metric there. In MicroStrategy this is what intelligent cubes are for, and on the projects where I've done it the difference has not been subtle. Same rows, read a smarter way.

2. Every visual is asking its own question

Open your dashboard and count the panels. Now count the queries it fires. If those two numbers match, each visual is making its own round trip, and your load time is the sum of all of them rather than the longest one.

Panels that share a grain should share a dataset. One query, sliced several ways in memory, beats eight queries racing each other for connections every time.

3. The join is fanning out and nobody noticed

This is the one that hides longest, because the report isn't wrong — it's just doing far more work than it looks like it should. A many-to-many relationship somewhere upstream turns a modest result set into a vast intermediate one that gets collapsed again before display.

Pull the generated SQL and read it. Not the tool's summary — the actual SQL. Then run it with a row count at each stage: 

-- if this returns far more than the report displays,
-- your grain is broken somewhere upstream
SELECT COUNT(*)
FROM   fact_sales      f
JOIN   dim_store       s ON s.store_key = f.store_key
JOIN   dim_promotion   p ON p.promo_key = f.promo_key
WHERE  f.business_date >= CURRENT_DATE - 30;

More often than not the culprit is a dimension that isn't as unique as its name suggests.

4. Nobody set the VLDB properties

Every BI platform ships with defaults chosen to be safe on any database rather than fast on yours. In MicroStrategy those are the VLDB settings, and the defaults will happily generate a temp table where your warehouse would rather have a derived table, or a GROUP BY where an analytic function would do.

This is the cheapest win on the list: no remodeling, no new tables, just telling the tool what kind of database it's actually talking to. Adjusting how intermediate results get materialised has taken dashboard suites I've worked on from painful to unremarkable in an afternoon.

The data was fine. The questions we were asking it weren't.

The order to work in

When a slow dashboard lands on my desk, I go in this order, because it runs cheapest-fix first:

  • Read the generated SQL. Not the tool's summary of it.
  • Count queries per page load. One per visual is a smell.
  • Check row counts at each join. Look for the fan-out.
  • Review VLDB settings against the actual warehouse.
  • Then consider pre-aggregation — the biggest win, and the most work.

What I have never once needed to do is buy a bigger box.