Forcing Tableau to Re-Query without using Refresh

This question was brought by my fantastisch colleague Timo Tautenhahn, who runs tableaufans.com. He was seeing some performance issues with using the Refresh commands in the JS and Extensions API, but not encountering the issues when changing a filter, which also was causing a new query to the database

In essence, whatever happens when the Refresh command fires off involves more steps than just re-querying. So the question is: what can force a re-query without the Refresh commands?

Solution: Add an Integer Tableau Parameter to a Custom SQL query that doesn’t actually affect the logic of the Query

You can have your own fun coming up with logical statements that are always true, but you don’t need anything more complex than:

SELECT * FROM table t WHERE [Tableau Parameter] = [Tableau Parameter]

As long as you change the value of the parameter to something you haven’t used yet within the session, each change should force a query refresh without using the Refresh action. Simply incrementing up from zero is enough to make it work.

How does it work?

Tableau doesn’t recommend using Custom SQL too often, because it can have performance implications. But if you are using a very simple query, with a very simple set of WHERE clauses, it can actually make things perform better by forcing the database’s query analyzer to apply a WHERE clause directly on a base table (among other edge cases).

Tableau uses the user-defined Custom SQL statement directly as the inner-most FROM clause of any query that the VizQL process comes up with.

If you set up you Custom SQL statement as:

SELECT * FROM table t WHERE t.field_1 = 'a value'

Then bring out several fields from the Tableau in the Tableau UI, the query Tableau sends to the database will be something like:

SELECT field_1, field_2, field_3
FROM (SELECT * FROM table t WHERE t.field_1 = 'a value') AS custom_sql_1
WHERE ...

Custom SQL statements in Tableau can be dynamically modified by adding Tableau Parameters in the statement (there is a Parameters menu in the Custom SQL dialog). When the value of the Parameter changes, Tableau sees that the base form of the Custom SQL statement has changed, and immediately re-queries using the new statement.

Since we’re doing something logical that doesn’t actually affect the result set, you get a pure data refresh from Tableau without changing the query.

Both the Tableau JavaScript API and Extensions API can set a parameter value, and this gives technique for forcing a query to update faster than their RefreshAsync methods (you need to test to make sure it is actually faster, potentially there are edge cases where Refresh would be better). Since you are simply incrementing an integer, the actual code to implement will be fairly trivial in either API.

When is this useful?

The two most obvious use case are (1) constantly updating underlying data, where the Tableau display needs to get as close to “real-time” as possible and (2) more complex data workflows or integrations, where actions may be taking place to affect the underlying data (write-back to database, triggering a reprocessing of an ML analysis, etc.)

You will need to use either the JS or Extensions API, and which one you choose will depend on your particular use case.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s