Why this matters
As a Data Analyst, your dashboards must feel fast and trustworthy. Slow pages cause stakeholders to abandon insights and question data quality. Performance optimization ensures your dashboards load quickly, handle more data, and remain stable during peak usage.
- Real task: Reduce a sales dashboard page load from 8s to under 2.5s.
- Real task: Aggregate 100M transaction rows into daily summaries for trends.
- Real task: Make filters and slicers responsive without overloading the source.
Who this is for
- Data Analysts building or maintaining BI dashboards (Power BI, Tableau, Looker, etc.).
- Anyone preparing dashboards for exec and self-serve users.
Prerequisites
- Basic SQL (SELECT, WHERE, GROUP BY).
- Familiarity with your BI tool’s data model and visuals.
- Comfort with simple data types and date functions.
Concept explained simply
Speed comes from doing less work early and smartly. Limit data, shape it before visuals, and render only what matters.
Mental model: The 3-layer speed stack
- Source/Storage: Pull only needed rows and columns; add indexes/materialized summaries where possible.
- Model/Transforms: Use a clean star schema, pre-aggregate heavy calcs, reduce cardinality.
- Visuals/UX: Fewer visuals per page, simpler slicers, staged filters (cascading), and on-demand details.
If any layer is slow, the whole page feels slow. Fix from the bottom up.
Quick wins and rules of thumb
- Filter to a practical time window (e.g., last 13 months) and offer a drill to history.
- Return only necessary columns; avoid SELECT * in custom queries.
- Pre-aggregate: daily or weekly summaries for trends; keep a separate detail view for drill-through.
- Keep visuals per page to around 6–8; avoid multiple heavy maps.
- Prefer low-cardinality slicers; use cascading filters instead of a single huge list.
- Use numeric and correct data types; trim text, correct dates, and reduce precision where possible.
- Cache or schedule refresh off-peak; incremental refresh for large tables.
Worked examples
Example 1: Pre-aggregate to cut query time
Before: 120M transaction rows scanned to plot a 12-month revenue trend.
- Create a daily revenue summary table by product and region.
- Point visuals to the summary table; keep detail table only for drill-through.
- Result: Trend renders in under 1s; drill-through loads details on demand.
Example 2: Reduce visual count and complexity
Before: 14 visuals (3 maps, 5 cards, 4 tables, 2 slicers) taking 9s to render.
- Consolidate cards into 1 KPI bar with small multiples.
- Remove 2 maps; keep 1 if geography is essential.
- Replace 2 wide tables with a single summary table + drill-through.
- Result: 8 visuals; load time drops to ~2–3s.
Example 3: Trim data at the source
Before: Custom SQL pulls all columns for 3 years.
- Select only date, product, region, and revenue fields.
- Apply WHERE date >= current_date - interval '13 months'.
- Group by date granularity used in visuals (e.g., day).
- Result: Data volume falls by 80%+; visuals refresh faster.
Exercises
Do these to internalize the concepts. The quick test is available to everyone; only logged-in users get saved progress.
Exercise 1 — Make a heavy query lightweight
Goal: Return only the fields and rows needed for a 13-month daily revenue trend by product and region.
- Source table: sales_transactions with columns like trans_id, trans_dt, product_id, region, qty, unit_price, discount, currency, plus others.
- Task: Write a SQL query that filters the period, selects minimal columns, calculates revenue, and aggregates by day/product/region.
Exercise 2 — Trim visuals to meet a 2.5s target
Goal: Redesign a slow page with 14 visuals to 8 efficient visuals, using aggregation, cascading slicers, and a detail drill-through.
- List exactly which visuals you will remove or merge.
- Specify default time window and slicer behavior.
- State your target page render budget and how you'll measure it.
Exercise checklist
- Your SQL excludes unused columns and limits date range.
- Your visuals count ≤ 8 per page.
- At least one summary table is used for trends.
- High-cardinality slicers replaced with cascading filters.
- Drill-through retained for detailed investigation.
Common mistakes and how to self-check
- Mistake: SELECT * in production queries. Fix: Explicitly list required columns.
- Mistake: Using detailed tables for all visuals. Fix: Use summary tables for trends; detail for drill-through.
- Mistake: Too many visuals per page. Fix: Stay around 6–8 visuals; combine cards and limit maps.
- Mistake: Large, unconstrained slicers. Fix: Cascading filters and default time windows.
- Mistake: Complex row-level calculations done on the fly. Fix: Pre-calc in ETL or create materialized summaries.
Self-check mini audit (5 minutes)
- Can you articulate the time window default and why?
- Is each visual essential to the decision? Remove nice-to-have.
- Is there a summary vs. detail separation?
- Are there any visuals running on the raw transaction table?
- Is the long-running step in source, model, or visuals? Measure each layer.
Practical projects
- Speed-up Sprint: Take one slow dashboard page, cut visuals to ≤ 8, add a summary table, and document before/after load times.
- Aggregation Layer: Build a daily sales summary table and re-point trend visuals. Add drill-through to transaction details.
- Filter Redesign: Replace a 50k-customer slicer with Region → Customer cascading filters; record the change in responsiveness.
Learning path
- Start: Master filtering and column minimization in source queries.
- Next: Create summary/aggregate tables and adopt a star schema.
- Then: Optimize visuals (counts, types) and slicers (cascading, defaults).
- Finally: Schedule refresh, caching, and incremental refresh for big data.
Mini challenge
Pick one slow page. In 60 minutes, ship a version with: ≤ 8 visuals, default time window set, and at least one summary table. Measure load time improvement and write a 3-bullet changelog.
Next steps
- Adopt a performance budget (e.g., target < 2.5s render per page).
- Use your BI tool’s performance analyzer to find slow visuals/measures.
- Plan incremental refresh or partitioning for very large tables.
Quick test
The quick test is available to everyone. Only logged-in users get saved progress and streaks.