Designing BI for Speed: Modeling Patterns that Cut Query Time by 30–60%

Dashboards feel fast during development and crawl at month-end. The gap usually comes from three issues. First, models mirror OLTP structures: wide transactional tables, nested relationships, and ad-hoc joins that multiply row counts. Second, expensive calculations run on the fly instead of being summarized for the questions people ask most. Third, predicates block the engine from pruning data (functions on filter columns, mismatched types, nonselective joins), so each click scans far more than it should.

Typical symptoms look familiar: a promo dashboard that scans a year of order lines per view; filters wrapped in functions that disable partition pruning; DISTINCTs added late to undo row explosion; window functions stacked on unindexed text columns; and freshness that slips under load because everything recomputes. The fix starts in the model, not the hardware.

Shape data for reading, not for writes. A star/snowflake layout keeps fact tables lean and dimensions tidy. That alone reduces accidental duplication and makes joins predictable.

Pre-aggregate heavy views. Build summary tables or materialized views for the slices people read every day: by day, by SKU/store, by line/shift. Keep them small enough to refresh often (hourly or daily) and broad enough to cover 80% of questions. Your transactional facts still exist; you just don’t recompute everything at click time.

Partition by date and choose an ordering strategy that matches access. If your queries filter the last 30–90 days, date partitioning allows the engine to skip cold data. Add sort/cluster keys (for warehouses that support them) on the fields you filter or group by most—often date, SKU, store, or account. This keeps scans tight and improves compression.

Model for selective filters. Provide helper columns (e.g., pre-parsed date parts, normalized search fields) so filters don’t wrap raw columns in functions. That enables predicate pushdown and index/zone-map use. If text search matters, add a pre-tokenized column designed for that purpose and keep it narrow.

Plan the grain. Decide whether the question is per order, per item, per session, per line/shift, or per day—and keep that grain consistent in the fact or summary. Mixed grains cause duplicates and DISTINCTs downstream.

Avoid functions on columns used in WHERE/JOIN. Instead of WHERE DATE(order_ts) = '2025-08-01', pre-store a order_date column and filter on it. Instead of LOWER(email) = LOWER(:email), store a normalized email_norm. This preserves pruning and index use.

Aggregate before joining when possible. Joining two large raw sets and then grouping is slower than aggregating each side to the needed grain and joining the smaller results. If you need distinct counts across big joins, consider sketching (e.g., HyperLogLog) where accuracy trade-offs are acceptable.

Trim SELECT lists and CTE bloat. Keep only the columns you need; drop staging CTEs that materialize huge intermediates. Many engines will still process them, even if the final output is small.

Use window functions deliberately. They are powerful, but over unfiltered ranges they read a lot. Precompute running totals or rankings into summaries if they appear in every dashboard query.

Profile with real workloads. Explain plans on developer-sized samples can be misleading. Validate on the time ranges and concurrency levels you see at month-end and during campaigns, then fix the biggest offenders first.

Week 1

Inventory the slowest views and their questions. For each, record the intended grain, filters, and groupings. Add two quick profiling checks: largest scans and most time-consuming joins. Identify functions used on filter columns and mismatched types.

Week 2

Introduce summaries for the heaviest views. Start with one to three summary tables or materialized views (e.g., daily_sku_store, returns_by_reason_day, oee_line_shift_day). Add date partitioning and an appropriate sort/cluster key. Rewrite the dashboard queries to use these summaries first; fall back to the base fact only when detail is explicitly requested.

Week 3

Tighten predicates and joins. Add helper columns so filters stop wrapping raw fields; align join keys by type and case; cut unnecessary DISTINCTs by fixing grain issues at the source. Where you rely on large joins, aggregate before joining and cache intermediate results if they are reused.

Week 4

Validate under load and set guardrails. Test with month-end ranges and typical concurrency. Add simple SLOs for key dashboards (e.g., “p50 under 5s; p95 under 10s” for the last 90 days). Monitor query runtime and scanned data size. Keep a short runbook: what to roll back, which summaries to refresh first, and who owns each dashboard.

This plan keeps changes small and visible, so stakeholders feel the improvement quickly and you avoid risky rewrites.

Track what users feel. Record median and p95 load times for top dashboards by week; track scanned data per query; and watch Excel exports. A drop in exports usually means the dashboard actually answers the question. For teams with large reader populations (executives, managers on the floor), consider a consumption model where authors keep full access but readers use session-based viewing. That keeps cost aligned with bursty usage while your modeling work holds load times steady.

Close the loop. When performance improves, adoption rises. When adoption rises, requests become more specific, which helps you target the next summary or model change. Publish short release notes so users know what changed and why the dashboard feels faster this week.

If you want a one-page checklist (questions to ask, patterns to apply, and a small query-profile template), send two slow examples. We’ll reply with options you can try in your next sprint.