Tableau Performance Tips #3 : Avoid small list of values to be in the context

The issue of using list of values that are context's linked

Let’s begin with a definition of what a list of values is. A list of values is linked to a filter. The filter can be in the context or not.

Hum… May i should define the context too…

The context of a view is an ensemble of filters that restrict one or several views. To put a filter in the context, you right click on it and choose « Add to context ». The filter then become grey instead of blue.

In this exemple we have 4 contextual filters (in grey). And 2 lists of values.

To determine if the list of values (LoV) is in the context, you click on the down arrow at le right side of the LoV header.

This is the case for our 2 LoV (cd_super_marque and cd_marque)

Having define the List of Values (LoV) and the context, we can now understand what means having a list of values IN the context : the LoV must respect all filters in the context.

Here we have a filter on the time that restrict data to one month (a sales month of course). That will have an important impact on the queries generated to retrieve the list of values.

Let’s have a look on the model of the data used here and the generated queries used to retrieved the 2 lists of values in our exemple :

Using a logical model with a sales fact table of 30M rows linked to small dimensions tables (the largest one have 20k rows) with a M-1 relationship with integer FK-PK columns and strict referential integrity

With a simple view that have the context of 4 filters, 2 lists of values and one metric as key figure data, we will record the performance using the performance recorder.

Performance record with LoV in the context

3 queries are generated for this simple view : one for the data (blue arrow) and 2 for the LoV (red arrows).

As you can see the queries to retrieve the list of values lead to a multiple joins to respect the context (a one month filter)

In some cases the effort to retrieve the list of values in the context may seem disproportionate, especially when the number of values are reduced. Indeed when the context is « complex » tableau will need to generate joins with several tables.

In theses cases, it would be beneficial to switch the list of values retrieval mode to « all values in databases » or even better to « all values in hierarchy ». You will have much simpler queries on much smaller tables.

The resulted list of values may not be contextually « exact » but it will reduce a lot the database pressure : it up to you !

List of values : All values in hierarchy

the 2 queries to retrieved list of values are much simpler and touched only small dimensions tables

And another performance improvement you will find with this change is that you can also benefit from tableau cache : indeed, because the list of values are no more context dependent, any change in the context (changing the filtering on the time for exemple) won’t have effect on the list of values.