Caching in Tableau Server and Row Level Security

Tableau Server, particularly since the 9.0 release has  fantastic caching mechanism. Once a view has been loaded into the cache, any subsequent view using the same data will load extremely quickly. This is why you may notice that a first view in the morning takes some amount of time to load, but every other view is much quicker. Some Tableau customers even “warm” the cache on some of their views by scheduling an e-mail or pinging the Tableau Server for a request of a PDF early in the morning, before any of the regular viewer come in. You can even force a refresh using the “warming” technique by appending the :refresh parameter to the end of your request.

Once something is cached, how long does it stay? There are three different settings you can choose from in the Tableau Server Configuration application, on the Data Connections tab:

cachingThe default, Refresh Less Often, is basically for the cache to last until Tableau thinks it should have changed. Just trust us! Balanced makes sense when you have an idea of how quickly your underlying data refreshes and want to ensure that Tableau is refreshing relatively frequently. Refresh more often basically turns caching off (although it is different from getting rid of the cache server processes)

Forcing a refresh

Regardless of the setting, you can force a refresh through:

  • Appending the :refresh parameter onto a view’s URL
  • Pressing the “Refresh” button in the Toolbar
  • Calling the Workbook.refreshDataAsync() method of the JavaScript API
  • IF THE PREVIOUS THREE OPTIONS ARE TOO SLOW: Changing any Parameter that is used within a Custom SQL query to a value that has not yet been used will cause a re-query without rebuilding any connecting details.

Obviously, these are all more useful and easier to control when embedding Tableau Server views, rather than when a user comes in via the standard Tableau Server UI. If you have a use case where some views should be cached for the standard amount of time, but others need to be refreshed at each viewing, I’d recommend choosing “Balanced” but appending the :refresh parameter when loading any view that needs totally up-to-date data.

To get a similar behavior when not embedding your Tableau Server views, you might instead move to Extracts (or extremely optimized Live Connections) for any of the “static” and set the Server to “Refresh More Often”.

Row Level Security and Caching

The more “unique” a view is, the less likely the data will match any previously retrieved view and you will get a “cache hit”. With any type of Row Level Security (including Initial SQL methods), any new user who comes will require a new set of queries to the database. The more queries, the more work the DB has to do (and a cached view will always be faster than rendering a new view, even if the database or extract is extremely optimized).

What can you do in this situation? The recommendations for optimizing performance with Tableau Server are the same as always, but are even more important when serving up individualized views. Optimize your worksheets and dashboards in Desktop, then look at the queries Tableau is sending and optimize your databases to return those queries as efficiently as possible.

One comment

Leave a Reply

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

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

Facebook photo

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

Connecting to %s