Optimizing Dashboards in Tableau
Dashboards are used in data analysis presentations as a way for visually communicating complex data from various sources. Instead of talking in numbers which is hard to understand, they are used to quickly see Key Performance Indicators(KPIs), patterns, performance insights and areas of improvement. The main goal of dashboards is to save time and effort to sift through complex data.
So, it is very important that dashboards don't take longer times to load. Also, while presenting, all the filters and actions should respond quickly to show the changes in charts.
Ever wondered how long it takes for dashboards to load?
First time when a dashboard is loading, it takes around 15 - 18 seconds. From the next time, dashboards load in less time because of caching.
Dashboards take long time to load as a result of various factors like
Connecting to live data sources
More worksheets in a dashboard
Using more filters thinking of making an interactive presentation
Using more data points in worksheets
Joining data from various tables, running nested queries, more calculated fields after data is imported to Tableau
Not cleaning up unused worksheets, fields
So, let’s talk about improving the performance of dashboards.
Dashboard optimization in Tableau should start from even before data is imported into Tableau.
Optimization of dashboards can be achieved at three stages as shown in picture below:
#1. Extract Filter: Extract Filter is applied during data extraction to minimize the amount of data entering Tableau. This is done in two ways
Data is extracted from a live connection to the DB, or
A static snapshot of the database is created from the live connection, which means, data is available offline and any queries executed will run much faster. Data can be refreshed many times to maintain consistency.
Extract option in Data Connection selections in TABLEAU By selecting the Extract radio button(which is only available in Tableau Desktop), a local copy is created in Tableau.
#2. Data Source Filter: Data Source filters come between Tableau data source and Tableau dashboard. These are post condition filters applied on Tableau data source used to restrict data available for creating visualizations thus reducing the size of data entering Tableau dashboard. These are like SQL queries using WHERE clause.
Data Source Filter in Tableau When ‘Add’ in the filters section is selected, the window below pops-up.
Selecting ‘Add’ in the ‘Edit Data Source Filters’, lets us choose fields which we want to have in our data subset. If ‘country/region’ is selected, data related to the selected country is only made available for creating visualizations in worksheets.
#3. Context Filters: In the order of Operations of Tableau, context filters apply after Extract filters and Data Source filters at worksheet level whereas Extract filters and Data Source filters apply at data source level.
To apply a context filter in the worksheet, drag a dimension or measure into the Filters shelf and select ‘Add to Context’. Doing so will let Tableau evaluate this filter before any subsequent dimension or measure filters.
After these initial filtering stages, create a dashboard for presentation.
When the dashboard is prepared and ready to be published or shared, it’s a good idea to check if it is taking a little longer than it should take to load. This can be done by recording performance in Tableau Desktop. (this feature is not available in Tableau public)
To record and analyze processing times in a Tableau dashboard, use the Performance Recording feature within Tableau Desktop or Tableau Server. This feature allows you to capture performance data while interacting with your dashboard, which can then be analyzed to identify bottlenecks and areas for improvement.
Here's how to record performance:
1.Start Performance Recording:
In Tableau Desktop, go to Help > Settings and Performance > Start Performance Recording.
In Tableau Server, you can enable performance recording for a site through the site settings.
2. Interact with the Dashboard:
While recording, interact with the dashboard as you normally would, including filtering, selecting data points, and refreshing data.
3. Stop Performance Recording:
In Tableau Desktop, go to Help > Settings and Performance > Stop Performance Recording.
In Tableau Server, performance recording can be stopped by moving to a different page or removing :record_performance=yes from the URL.
Analyze performance:
A workbook is created which has performance information about key events as you interact with different filters, parameters in charts .
Different events that are known to affect performance include:
Query execution
Compiling query
Geocoding
Connections to data sources
Layout computations
Extract generation
Blending data
Server rendering (Tableau Server only)
The performance recording workbook contains two main dashboards:
Performance Summary : provides high-level overview of the most time-consuming events
Detailed views : provides lot more detail and to be used by advanced users and is visible only when opened in Tableau Desktop
The Performance Summary dashboard contains three views: Timeline, Events, and Query.
The middle view in a performance summary dashboard shows the events, sorted by duration (greatest to least). Events with longer durations can help you identify where to look first if you want to speed up your workbook.
Once we know what is slowing down dashboard performance and responsiveness, we can start working on fixing those.
Below are some of the points to remember while preparing views:
Limit marks
Limit data points
Limit using quick filters in views because each filter has to run a query to update the chart
Avoid high-cardinality filters i.e filters on columns in a data set that have numerous values like Customer name, customer id..
Use ‘all values in database’ filter instead of ‘only relevant values’
Always use ‘show Apply button’ for filters
Avoid ‘Exclude’ and use ‘include’ filter
Use Continuous date filter instead of Discrete date filter
Use more numeric data types than date data type, string data types in filters
Use Tableau functions and calculations instead of creating new ones
Use action filter chart
Filtering is a more powerful tool in Tableau, but it’s the most common reason for bad performance.
Using more filters can slow down loading and responsiveness of dashboards. Every time a filter is used, a query is run to fetch data to update the chart. The more the filters are used, the more processing time is required for running more queries resulting in slow responsiveness.
Summary:
Data that Tableau consumes should be already cleaned, meaning only relevant and required data should be presented as tables.
This can be achieved
by using Python or SQL which facilitate data preparation, optimized calculations, data aggregation, and the use of Hyper files. Hyper is Tableau's new in-memory data engine technology, designed for fast data ingest and analytical query processing on large or complex data sets.
By using Extract Filter In Tableau Desktop or Tableau server, data imported to Tableau is greatly filtered.
After the data source is connected to data for preparing visualizations, various filters can be used at worksheet level to improve performance. Few house-keeping rules like deleting duplicate copies of worksheets and unused calculated fields go a long way in faster loading of dashboards.
Slow dashboards can be frustrating, but several strategies can improve performance. Optimizing SQL queries, using real-time databases, and pre-aggregating data are key steps.
Hope you learned some new things. Happy Learning !!
