Hello and welcome to the first instalment of this new series on improving dashboard performance inspired by Emanuele Farotti’s great class. I am starting off this series with a well-known performance killer: filters. Hopefully, this blog post will have something for everyone to take away from it.
There are multiple ways to enact a filter in Tableau and I will merely demonstrate one method for each.
6 Different Filters in Tableau
In tableau there are 6 different types of filters:
1. Extract filters
2. Datasource filters
3. Context filters
4. Dimension filters
5. Measure filters
*6. Table calculation filters
“Table calculation filters” are the only filters included on this list which are not “true” filters. Table calculations perform on top of the view and therefore cannot filter anything (this would be circular logic). Data is not filtered out anywhere but rather it is hidden in the view. Table calculations filters can, be taxing on performance as they still allow all records into the view. If possible, another filter should be swapped for a table calculation filter.
To create a table calculation filter (create a table calculation and drag onto the Filters shelf):
The order given at the start for the filters is also the order Tableau enacts each one. It thus goes to stand that extract filters are the most performant filters as they stop data from entering the extract at all. Data source filters are also extremely performant as they impact the query sent to the database to bring the data into Tableau. This means less data is brought into Tableau.
To create an extract filter (in the data source pane click Edit):
To create a data source filter (in the data source pane click Add):
Context filters, when overused/misused, can be among the worst for performance. Context filters are filters applied after the data has come into Tableau and generate a temporary table from which all subsequent filters would be applied to. Context filters can actually improve performance if they cut down the data by over half and are not changed too often i.e. a country filter. There are also cases, such as creating a top 10, where a context filter maybe be necessary.
To create a context filter (right click a filter):
Dimension filters, whilst not as taxing as a context filter, are still less performant than measure filters. Dimension filters should be replaced with data source/extract filters if the value is never changed, i.e. you are always only interested in the “United States”.
SQL query example:
WHERE States IN (‘United States‘, ‘UK’)
Measure filters (unaggregated) may be less performant that data source and extract filters but they are faster than any other remaining filters. This is down to the fact that the comparison the query statement makes for a numeric field computes faster than for a string/dimension.
SQL query example:
WHERE Sales Between 0 AND 100
Aggregated measure filters, however, are not as good for performance because behind the scenes Tableau is having to group by fields/dimensions, retrieve the aggregated value and then compute the filter. In SQL terms this is the difference between using the WHERE and the HAVING keywords. WHERE can be used for row level filtering, HAVING is used for the aggregate filtering and is fundamentally slower.
SQL query example:
GROUP BY States
To create a dimension filter (drag dimension onto Filters shelf):
To create a measure filter (drag measure onto Filters shelf):
Date fields – Both a Dimension and a Measure
Date fields can be filtered as either a dimension or measure filter. If the date field on filter is set to a discrete option (blue) then the filter is treated as a dimension filter. If date field on filter is set to continuous (green) then the filter is treated as a measure filter. As aforementioned, it is more performant to use a measure filter so continuous (measure) date filters are preferable to using discrete (dimension) date filters. Another method to improve performance would be to filter the date would be to create a custom date field and extract the data so this field manifests in the extract. This would also be extremely performant as a filter.
Filter Options and Settings to Improve Performance
Try to use the “Only Relevant Values” setting sparingly as this requires a new query whenever any other filters are applied (to get the new list of values). Utilising action filters in the correct manner on a dashboard is a great way to get the same functionality of a filter with better performance times.
Following on from actions, use the hover action sparingly, as this is querying every hover action.
Try to utilise the “Show Apply Button” setting to prevent individual queries on multi-selection.
Filtering on a condition is also taxing on the performance. This is a case where either a measure filter should replace this filter or a field should be made and materialised in the extract to improve performance. Aggregate measure filters will also be worse on performance than a simple measure filter (since it must evaluate the aggregation first then filter).
I hope you are able to solve some of your performance issues using the above knowledge. If not, hang tight for the next instalment in improving dashboard performance.