A common problem that report users often face is slow dashboards, which we – as a data consultancy – know can be very frustrating. Performance is a multifaceted problem and goes well beyond just technical best practices. This blogpost is the final blogpost of a series of three that provides you with guidelines to keep in mind when working in Tableau, and more specifically, when creating data sources, calculations, and designing workbooks. Each blog post also provides a flow chart that can help simplify the process of determining where to focus your optimization efforts. This blogpost will focus on best practices when designing workbooks.
You can find a troubleshooting flow chart at the end of this article where you can also download an extensive white paper from Tableau that has inspired this series.
Worksheets and Dashboards
Much of the advice in this blogpost applies to both worksheets and dashboards, as dashboards are the collection of worksheets. Filters, actions, and more are all possible on both the worksheet and dashboard levels. Thus, the design of your worksheets will be the main driver of overall performance. Overall dashboard design can be leveraged to get the most out of your visualizations and help speed delivery of insight to your users, but it is important to note that performance on the dashboard level is really the aggregation of everything else. To get the most out of the dashboard, remove clutter from your workbooks, such as unused calculations, worksheets, dashboards, and data sources.
Worksheet best practices
Worksheets are where we can control much of the rendering that Tableau will need to accomplish. The number of marks and complexity can quickly add up across multiple visualizations. Thoughtfully reducing complexity is the key to outstanding performance. Hence, minimizing and focusing your visualisations is the central recommendation. Also keep in mind that filtering visualizations have the added benefit of reducing both the number of marks to render as well as the size of query results. When building your worksheet, you should consider the following:
Tip no. 1: Visuals are better than text. Tableau has been optimized to do visualizations. Yet, it is not uncommon to see dashboards that are just a table of raw data with a long list of filters on the side. These views struggle with performance as it loads lots of unseen data and includes more details than your users probably need. Additionally, from a user-experience perspective, this often indicates a lack of understanding of end-user needs, as it is a “one dashboard to rule them all” that provides everything to catch all possible analysis. Hence, it expects the user to do the hard work to find their insights. You will get better performance as well as a better user-experience if you give the end-users a way to step through and drill down to the data that answers their questions.
Tip no. 2: Avoid high mark counts. A mark is any point, plot, or symbol on your visualizations. They can be the bars in your bar graph, and in large text tables each text element is considered a mark. The more marks, the more work Tableau is performing rendering your visualization. When considering performance, it is important to ask if it is necessary to convey what you want from the visualization. In most cases you can likely aggregate to a higher level or use some additional tricks to limit the number of marks. Look to filter the marks or break out the results into different worksheets or views for better visibility into the data.
Tip no. 3: Use text formatting instead of calculations and shapes. Sometimes you need to convert a numeric value to a KPI indicator, such as ▲(up) or ▼(down). A trick is to use Tableau’s built in custom formatting to display these marks and you get a performance boost by skipping another calculated field and assigning it a shape.
Tip no. 4: Minimize the size of images. Like any type of mark, using lots of images can mean more work for Tableau. For images, however, there is an added issue of the file size. Our suggestion is to keep images below 50kb when possible with a 32×32 pixel count.
Tip no. 5: Use transparent background PNGs instead of JPGs. Another way to ensure smaller file sizes for your custom shapes and images is simply to use PNGs. PNGs support transparent backgrounds, which means less to store. It also can make the images more useful since they will work on multiple background colors without introducing a box around your shape.
Tip no. 6: Tooltips. Tooltips are a great way to provide details on demand, allowing the user to hover over a mark and pull up additional details. This streamlines your visualizations and means less to render on the initial visualization.
Dashboard best practices
Dashboards are where it all comes together. If you have followed best practices with your data, calculations, and worksheet design, you will be in a strong position to create well performing dashboards. The primary way we control performance on the dashboard level is by limiting the number of elements (worksheets, filters, containers, images, etc). Dashboards perform best when they are designed for a clear purpose, with focused content. A visualization may perform fast on its own but combined with many others can result in a slower dashboard. Hence, if you have a complicated chart with many marks, it may be best to keep that the primary focus of a dashboard. When building your worksheet, you should consider the following:
Tip no. 7: fixed sized dashboards. When you choose fixed dimensions, Tableau is able to better cache the results since it is a predictable size that does not have to automatically fit the end user’s screen.
Tip no. 8: Remove unneeded device specific dashboards. Tableau gives you the ability to create device specific dashboards so that you can better serve your users on phones or tablets. Tableau will even automatically generate a mobile version of your dashboard by default. If these device specific dashboards are not required, removing them will create a smaller TWB file and help your dashboard load faster.
Tip no. 9: Reduce the number of views per dashboard. Every visualization you add to your dashboard increases the number of queries to execute and marks to render. It can be tempting to create a single dashboard with all your visualizations but breaking them up across multiple dashboards will result in better performance. If you have more than 5 visualizations on your dashboard, begin to consider how to break apart your analysis.
Tip no. 10: Reduce the number of filters. Adding filters increases rendering and query complexity. This is especially true if they have a high cardinality (a large number of unique values) or are set to Show Only relevant Values. A good target is 3-5 filters. If you have more, consider the actual needs of your users and alternative ways to deliver them the functionality they desire. Also, if your filters are applied to multiple worksheets, each change will trigger multiple queries as all the affected worksheets that are visible will update
Tip no. 11: Additional guidance on efficient filters
- Limit use of “Show only relevant values”. When using it each filter control will apply the other filters. Thus, Tableau will query the data each time a filter is applied to regenerate the list of values.
- Beware filters with many options. Filtering on a large list of items can result in a lot of extra information being passed to your data source. Use a higher-level dimension if possible, i.e., region rather than city.
- Avoid filtering on the result of an aggregation. Using filters that incorporate things like Top N or LOD’s can generate multiple queries. E.g., filtering top 10 products by sales. If you also have a filter for region, then Tableau first has to query the data to find the top 10 products, before running another query for the visualization filtered to region.
- Include the filter dimensions in the visualization. Filtering by a dimension that is not in the view can be more performance expensive to evaluate whilst limiting Tableau’s ability to do in-browser filtering. When you include the dimension you filter by in the view, Tableau has all the data already needed and can perform the filter without issuing another query.
- Parameters can be useful for filtering. When static, they don’t result in additional queries to the database. When dynamic, they will issue a single query when the workbook is open.
- User filters are effectively not cached. Since these often relate to user security, Tableau cannot cache the results. Consider aggregating to a higher lever to eliminate the need for user filters.
- Replace filters based on LOD calculations. with Sets based on formulas.
- Zooming does not filter. All the marks are still there when zooming on a map. Consider applying a filter for the area you want to see to speed up your view.
Tip no. 12: Add targets to level of detail when designing action filters. If possible, add the Target Field of the action filter to your target view. When the field is used in an action filter, the information needed to do the filter is available in the view and Tableau can do the filtering right in the browser. Consider the dashboard below. If we create a filter action that targets the bar chart of cities from the map of States, we can speed up the performance by adding state to the level of detail of the bar chart. If State is not part of the level of detail of the bar chart, then Tableau will have to query the data to find out which cities belong to the State you are filtering to.
Tip no. 13: Leverage “exclude all values” on selection clear. When you create a filter action, you can control what happens when the selection is cleared. By default, Show all the values is selected, which removes the filter from the sheet, displaying all the previous data. Another option available is Exclude all values. When cleared, this will cause the target sheets to go blank. To see this, once you have set it up, you will need to activate and clear the action at least once. This will provide a very natural way to create a drilldown. Take for example the dashboard below where we want to create a drilldown from State to City to Postal Code. With the actions set to Exclude all values in the clearing selection, you will only provide the users with a barchart once a State is selected, showing the respective cities in the given state. If they then select a city, a new barchart about the Postal Codes in that city will appear. This is great for performance. On the initial dashboard load, only a single view is being loaded and a single query being executed. Further details are only requested once the user has made a decision. Providing these details only on demand keeps us from loading all the data on every initial load and instead delivering only what our users need as they need it.
Tip no. 14: Drill into detail and Hidden Containers. Many dashboards suffer from long load times because of the amount of data that needs to be loaded due to granular views that oftentimes never end up being used by the end-user. Tableau will only render and query what is needed to load the dashboard. Thus, a good way to achieve performance is to limit the initial dashboard to summary information and only provide details when the user requests them. There are many different ways to achieve this, including actions, global filters, or putting these detailed views in their own purpose built dashboard. Break apart your dashboards into consumable chunks. Multiple load times (within reason) are better than a single long load time.Thoughtfully group content and use drill-downs to quickly bring up additional details. Another common way is to simply hide your detailed views in a container. When you float a container in Tableau, you have the ability to add a “show/hide” button. When the container is hidden, it’s contents will not be rendered.
Tip no. 15: Clean-up containers. Containers in Tableau help us align and group content in our dashboard. Often during the process of design and iteration, numerous nested containers end up in our dashboard. Cleaning up the design will uncomplicate the rendering and calculation that Tableau has to do when loading your dashboard.
Tip no. 16: Publish to the server without tabs. When tabs are enabled, Tableau will load elements of the other dashboards and worksheets. When you publish a workbook, you have the choice between publishing with tabs and without tabs. One known issue in Tableau is that performance is less efficient when tabs are displayed, as Tableau is loading more information at once. Consider alternative forms of navigation such as navigation buttons or URL actions which do not require tabs to be present.
Eager to Learn More?
This blogpost extracts the key takeaways from the white paper Designing Efficient Production Dashboards by Ben Bausili. The white paper is built upon the experience and expertise of consultants across the globe and is addressing the likely areas you will achieve large improvements with performance in Tableau. For more detailed advice and insights, download the white paper here.