Proper Use of Context Filters

Subject Area: Architecture

Level of complexity: Intermediate

Approximate Time to complete: 30 Minutes

Companion Workbook (twbx): n/a

Additional Resources on the web:

If you are applying filters to a large data source, you may be able to improve performance by setting up context filters. The "Add to Context" feature works by creating a TEMP table in the database (which may or may not always succeed, depending on the database and your permissions - we are assuming that TEMP tables succeed).

For Excel, Access, and text data sources, the temporary table is created as an Access table. For SQL Server, MySQL, and Oracle data sources, you must have permission to create a temporary table on the server. For multidimensional data sources, temporary tables are not created and context filters only define which filters are independent and dependent.

Consider a table of data with 100 million rows. The proper use of "add to context" is to filter down to 800k rows, perhaps for one segment, customer, line of service, or whatever dimension you want. The improper use of "add to context" is to select everything in the filter. This has the effect of creating a new, second 100 million row table. If the originating data model was a multiple table join, the resulting context table is a single, denormalized, fully indexed Tableau TEMP table. A nice denormalizing effect, but largely useless if you are simply recreating the original 100 million rows. Now imagine you have Tableau Server thrown into the mix: each time an end user interacts with this sheet or dashboard, a new 100 million row TEMP table will be created. This is unnecessary and defeats the purpose of using the feature in the first place.

If your data source has hundreds of columns of data, you can improve performance of the "Add to Context" filter by hiding unused dimensions and measures. Let's continue to use the 100 million row data source example, but this time it contains 160 dimensions and 65 measurements - 225 columns in total. The "add to context" feature will dutifully create a TEMP table with all of these columns, and indexes on each column. This could take a long time to generate, before you even get to the analysis itself. If you do not need them, you can hide fields prior to using "add to context". Once hidden, they will not be included in the TEMP table output. This can have a huge performance impact on your use of "add to context".

Here are some best practices to keep in mind when making use of context filters:

Using a single context filter that significantly reduces the size of the data set is much better than applying many context filters. In fact, if a filter does not reduce the size of the data set by one-tenth or more, it is actually worse to add it to the context because of the performance cost of computing the context.

If you do have multiple filters to add to the context it is better to create all of the filters first and then create a context that includes them all. To create a context that includes them all, select Analysis > Set Context and then add the multiple filters to the context all at once. Using the standard Add to Context command in the context menus of each filter will force Tableau to compute the context once per filter which can degrade performance. Complete all of your data structuring before creating a context filter. Changes in the data structure such as converting dimensions to measures, require recomputation of the context.

Set the necessary filters for the context and apply the context filter before adding fields to the shelves and the view. Doing this work first makes the queries that are run when you drop fields on other shelves much faster.