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.
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.