Deeper into the Tableau Repository Part 6: Cache how you want to

WARNING 

This is out of the box, non-standard, and not supported. Proceed at your own risk. Make sure you have a backup. Don't try this at home (more likely work). 

UPDATE 11/12/2020: I added some more details and comments about the code

Tableau server only allows you to set the cache for everything server wide. I have our server set at 12 hours. This is a pretty good compromise for things that update monthly, weekly, or daily. Newer versions of Tableau will dump the cache if an extract refreshes or the workbook is republished but a dashboard with a live database connection also gets that 12 hour cache. Mark Wu has a great article about how to manage this. I chatted with Mark last week at TC19 and got some really good insights. His solution is to add 1 millisecond to the last_published_at value for the workbook. His blog doesn't include code so below is what I came up with. Since the workbook thinks it has been updated it will dump the cache. 

There are comments in the code, but there are some things I am checking for:

1. Who tagged it? Unless the tagger was the owner, site admin, or server admin I will ignore the tag.
2. Does the workbook have an extract? This is intended for live database connections where the data is constantly changing. Extracts refreshes trigger a cache reload automatically so we want to use the cache. 

I run it every 5 minutes, but there is no reason you couldn't extend the idea to using multiple tags and having a more customizable cache. Something like a '1h-cache' tag to update it hourly and '15m-cache' that runs every 15 minutes. 

/*
The idea here is to refresh the cache ala http://enterprisetableau.com/cache3/ 
How it works:
This update statement will increment the published date for a workbook view tagged 'no-cache' by 1 millisecond
This forces the cache to be ignored and requery the database. 
*/

update workbooks w
set last_published_at = last_published_at + interval '1 millisecond'
FROM views v
,taggings ts
,tags t
,users u
,system_users su
where 
v.workbook_id = w.id
AND ts.user_id=u.id
AND su.id=u.system_user_id
AND v.id=ts.taggable_id
AND ts.tag_id=t.id
AND t.name='no-cache' --the tag we are looking for
AND (w.owner_id=ts.user_id --owner tagged the workbook
    OR su.admin_level=10 --OR a server admin tagged it
    OR u.site_role_id=0  --OR a site admin tagged it
    ) 
-- we are going to ignore it if it the workbook has an extract
AND (w.refreshable_extracts=false
    OR 
    w.incrementable_extracts=false);
 

For some reason the if I tag a workbook it shows as the view being tagged in the repository. I also set it to only do this for tags that were created by an admin (server or site) or the workbook owner.