The dashboard is taking ages to load. Rubbish!
– Joe “End-user” Bloggs
Heard this before? I know I have. So how do we speed up our dashboards? In this blog, I’ll share a few tips on how you can optimise your Tableau dashboards for speed and performance. Some of these tips, I’ve picked up from experience, colleagues or through Google searches. One particularly useful resource has been this whitepaper. I recommend it as further reading.
Data Optimisation
1. To extract, or not to extract?
In some scenarios, it would be more performant to extract your data, while in others it would be better to keep a live connection.
In these situations, an extract would be better:
- If your existing live query is slow, then it’s a good idea to try switching over to extract. The live query could be slow because Tableau is taking long to query the database. Perhaps your data warehouse is located halfway around the world; it would take longer to query this data than if it were in the next room, or, in the form of an extract on your local machine.
- If you would like to do offline building, you would need to extract your data. This may allow you to analyse your data faster and then you can switch back to a live connection when you’re done.
- If your data doesn’t require a complete refresh, for example, where existing rows do not change but new rows are added periodically, it may be preferable to use an extract and incremental refresh schedule. This would be less load on your server than doing a full refresh every time.
In these situations, a live connection would be better:
- When you need access to real-time data. For example, if you want a transaction to appear as soon as it happens, then a live connection would be better. With extracts, a refresh schedule would have to be set-up for at most, every 15 minutes. You cannot set a refresh more often than this, so if you require data refreshes more often, they a live connection is better.
- If your data has many, many rows (millions or billions), then a live connection is better. Firstly, it would take a very long time to generate the extract. Secondly, it would be a greater load on Tableau.
2. Tableau joins > Custom SQL joins
While Custom SQL queries can be very powerful, the queries generated by Tableau’s joins will often be more efficient that writing your own join scripts.
3. Initial SQL > Custom SQL
When using a Custom SQL query, Tableau re-queries the database each time a change is made to the visualisation. However, with Initial SQL, the database is only queried once when the workbook is opened. This temporary table can then be referenced using a custom SQL query.
Efficient Calculations
1. All data types are not created equal
Different data types have different speeds to process.
Fastest
Boolean
Number
String
Slowest
As booleans are fastest, where possible, write calculations that generate booleans. Here is an example of the same calculation but producing different data types:
[Sales]=[Target] will return a T/F boolean
IF [Sales]=[Target] THEN 1 ELSE 0 END will return numbers
IF [Sales]=[Target] THEN ‘Yes’ ELSE ‘No’ END will return strings
This applies to parameters too! It is better to use numbers as the ‘Value’ and then configure the ‘Display As’ option for the string you actually want. This way, Tableau will use the faster number to compute.
2. What IF there’s a better way?
There are a surprising number of ways you can write better IF statements. First, instead of nesting IF statements in other IF statements, utilise ELSEIF.
For example:
Nested: IF [Sales]=[Target] THEN ‘Yes’ ELSE (IF [Sales]<[Target] THEN ‘No’ END) END
Not nested: IF [Sales]=[Target] THEN ‘Yes’ ELSEIF [Sales]<[Target] THEN ‘No’ END
You know what’s better than an IF statement? A CASE statement.
CASE [Category]
WHEN ‘Furniture’ THEN 1
WHEN ‘Office Supplies’ THEN 2
WHEN ‘Technology’ THEN 3
END
So why is this faster? Well if you were to write this same calculation as an IF statement, Tableau would have to call the field ‘Category’ multiple times, whereas with a CASE statement, ‘Category’ is only called once.
You can optimise this CASE statement even further! Firstly, avoid redundant conditions. As you may have noticed, I didn’t need to write the last ‘Technology’ line. This is because Sample – Superstore only has 3 categories. So if it’s not ‘Furniture’ or ‘Office Supplies’, then it’s obviously ‘Technology’. This applies to IF statements too.
CASE [Category]
WHEN ‘Furniture’ THEN 1
WHEN ‘Office Supplies’ THEN 2
ELSE 3
END
Even better still, sort your CASE statement by the number of rows each condition meets. Because Tableau runs all rows through the first condition first, then whatever doesn’t meet that condition, passes through the next condition, and so on. So we want to reduce the number of rows that passes onto the next condition, because it’ll be less work for Tableau. In our example, we sell mostly ‘Office Supplies’, so we should place that condition first. Then, ‘Furniture’, then ‘Technology.
CASE [Category]
WHEN ‘Office Supplies’ THEN 1
WHEN ‘Furniture’ THEN 2
ELSE 3
END
3. Don’t reinvent the wheel! Use Tableau’s native features
Most times, it is more performant to use Tableau’s native features instead of using calculated fields to recreate them. The features I mean are aliases, groups, sets, bins, etc… If you don’t need a calculated field, use the native features.
4. COUNTD is hella slow
If COUNT would suffice, use COUNT. COUNTD is one of the slowest aggregations for Tableau, so don’t use it unless you have to.
5. Limit the number of calculated fields
Every field is a field Tableau has to query and calculate. Remember to remove all calculations that aren’t being used or using the Hide Unused Fields feature to hide fields that aren’t being used. This does not remove it from your workbook but hides them so that Tableau doesn’t query it. Try to keep calculated fields under 100 per workbook.
6. Compute row-level calculations in the database
Tableau is great at aggregations. Row-level calculations that don’t need to be performed in Tableau, should be pre-calculated in the database or in an ETL process if performance is a concern. This takes some load off of Tableau and lets Tableau do what it does best.
Fast Filters
1. Context filters
Filters on the filters shelf are independent of each other. When a filter is added to context, all other filters are dependent on what data comes out of the context filter. This means that Tableau has to compute the context filter, create a temporary table and then query that table for the other filters. This can be quite intensive so try not to use context filters excessively. If context filters could be a data source filter instead, then use that.
2. Data source filters
As previously, if a context filter could be a data source filter, use a data source filter. Data source filters generate subqueries, rather than temporary tables, so they are more efficient than context filters. Another reason to use a data source filter could be to reduce the number of rows that Tableau has to query in the visualisation and therefore reduce the load.
3. Quick filters aren’t quick!
Despite the misleading name, quick filters aren’t necessarily quick. Tableau pre-computes all potential values of all quick filters when a dashboard is opened. This could mean it takes ages to load. So if your dashboard has tonnes of quick filters, try to use actions instead. Drill-downs or filter actions are a great option to both speed up your dashboard, but to also encourage your user to interact. Alternatively use a parameter as your selection interface and apply a filter for that parameter.
4. (Don’t) Show only relevant values
This feature can be very useful for preventing users from selecting options that they shouldn’t. However, they’re not very efficient. This is because they rely on other filters on the dashboard. Every time another filter is changed, the ‘only relevant values’ filter must be queried again to change the values that appear.
Lastly, the Performance Recorder
My final tip is to use the Performance Recorder. This tool allows you to identify which actions and processes are the most intensive, i.e. contributes to the slowness.
Help > Settings and Performance > Start Performance Recorder
After hitting the start button, use the dashboard as usual then when you’re ready, stop recording. A new workbook will open with visualisations to help you figure out what’s slow.
I hope this was helpful!
Louise 🙂
Feed Me Data 