The Slow Dashboard: Optimizing Performance for a Large Dataset
A data analyst has built a dashboard in Tableau Desktop that connects live to a Snowflake database. The dashboard contains four worksheets: a detailed table with 50,000 rows, a bar chart of sales by product category, a line chart of monthly sales, and a map of sales by country. The dashboard takes over 2 minutes to load and often times out for users on Tableau Server. The underlying data has 10 million rows. The analyst has already created an extract, but the extract is set to full refresh daily and is 2 GB in size. Users complain that the dashboard is too slow, especially when applying filters. The analyst needs to improve performance without sacrificing the ability to see detailed data.
1. What steps should the analyst take to diagnose and improve dashboard performance?
Read the worked answer
To diagnose and improve performance:
- Use Tableau's Performance Recording: Record the dashboard performance to identify slow queries or rendering issues. Analyze the timeline to see which worksheets take the longest.
- Optimize the extract: Instead of a full extract, use an incremental refresh if possible. Also, consider filtering the extract to only necessary data (e.g., last 2 years) to reduce size. Use 'Hide Unused Fields' to reduce extract size.
- Reduce the detailed table rows: The table with 50,000 rows is likely a major bottleneck. Instead of showing all rows, limit to top 1000 or use a summary table. Alternatively, move the detailed table to a separate dashboard or use a parameter to allow users to drill down.
- Optimize calculations: Replace any row-level calculations with aggregate calculations or precompute in the data source. Use Boolean or integer calculations instead of strings.
- Use context filters: If filters are slow, set the most restrictive filter as a context filter to reduce the data set before other filters are applied.
- Reduce the number of worksheets: Combine or remove unnecessary worksheets. The map and line chart might be redundant; consider consolidating.
- Use a live connection vs. extract: Since the extract is large, consider using a live connection with aggregate queries, or use a smaller extract with only aggregated data for the charts and a separate detailed extract for the table.
- Enable 'Aggregate Measures': For the bar chart and line chart, ensure aggregate measures are used to reduce data points.
- Check network latency: If connecting live, network issues can cause slowness. Test with a local extract.
- Use Tableau Server performance tools: Check server logs and use the Admin views to see query times.
After implementing these, re-record performance to measure improvement.
2. How can the analyst balance the need for detailed data with performance constraints?
Read the worked answer
Balancing detail and performance:
- Use a two-tier approach: Provide a summary dashboard with aggregated views for quick insights, and a separate detailed dashboard for drill-down. Use actions to navigate from summary to detail, passing filter context.
- Implement data drill-down: Instead of showing all 50,000 rows at once, use a hierarchical structure or set actions to reveal detail on demand. For example, start with aggregated rows and allow users to expand.
- Use parameters for row limits: Allow users to select how many rows to display (e.g., top 100, 500, 1000) via a parameter, and use a calculated field to filter.
- Optimize the detailed table: Use 'Page' shelf to break the table into pages, or use a 'Show Me' table with only essential columns. Avoid excessive mark colors or sizes.
- Leverage extracts with aggregation: Create an extract that is aggregated to the level needed for the charts, and a separate extract for detailed data that is only loaded when needed (e.g., via a separate data source).
- Use Tableau Prep to pre-aggregate: If the detailed data is rarely used, pre-aggregate in Tableau Prep and provide a separate detailed data source for power users.
- Educate users: Train users to use filters effectively and understand that large detailed tables are inherently slower.
By providing options and optimizing each component, the analyst can meet both needs without compromising performance.