Tableau Performance Tip #4 : Avoid using a big datasource to display semi-constant informations

Tableau performance and constant calculated field

More than often you will want to display semi-constant information like the current timestamp, the Tableau Username, a chosen currency or a simple single information. For that the easy way is to just create a new calculated field with a formula using tableau function like NOW(), TODAY(), USERNAME().

Not a so good idea depending on your datasource, let’s see why :

Tableau logical model sample

Let’s imagine we have a datasource that use only a database (mssql here) with a classic fact table surrounded by its dimension tables.

Here the fact tables have 29M rows

We create a calculated using the tableau function NOW(). Any calculation must be linked to a datasource, as we have only one datasource (AggSales), our calculated field is then linked to this datasource.

We put the field in a view, that’s all

As we can see on the performance recording dashboard , Tableau generate a query against the datasource that use a SELECT GETDATE() (nothing special here) but it use also the fact table in the from clause (it may use a much more complex query in some cases depending on context filters ).

The call to the fact table will impact the performance while we just want a simple information.

The database is a mssql 2019 but I intentionally change the COMPATIBILTY_LEVEL of the database to 130 (2016). It means the mssql optimizer won’t use optimizations available in 2017 and 2019 version.
The elapsed time is +8s and we can is the execution plan that 29M rows are scanned

We can have a magic solution in our case : without changing anything in tableau I can have i instant response just by changing the compatibility level of my mssql database to 140 or 150

mssql change compatibility level

Elapsed time fall to 5ms and the execution plan show that only 1 row will be extracted from the fact table.

This is far better BUT…this is not always possible as easily as I did :

  • May be you have mssql but your version is 2016 and you cannot increase the compatibility level
  • May be your database is not mssql and your database haven’t such optimization
  • May be you use row level security in your database and this will slow down the query even more.
  • May be you use a datalake and you scan terabyte of data for nothing 🙁

The real good alternative

The real good one alternative is just to add a new datasource with one row. You can use your favorite database or even a file for that one row datasource.

Single row mssql datasource using custom SQL

In our exemple I create a one row table using a custom query :

SELECT X FROM (VALUES('X')) AS DUAL(X)

The calculated field « CurrentTime » that use the Tableau function NOW() is based on the new custom datasource

Performance recording tableau with NOW() using dual datasource

The query is based on the 1 row virtual table and return is 10ms to tableau. (800x faster than initially).

You can adapt the query for your database (using dual for oracle or sys.dummy for HANA or DB2) or even a simple csv file with one row, one column.

Happy tuning 🙂