Scheduling Extracts in Tableau Server programmatically (very unsupported)

Parental Advisory: This is really cool but of course you shouldn’t do it ever because it is unsupported and dangerous and opening up the Repository is dangerous and Tableau Support won’t help you if you delete your whole repository.

You can publish with the Tableau REST API but currently you can’t set a published extract or workbook with extracts to refresh any way  (neither REST API nor tabcmd) except through Desktop or the Server UI. I did some digging and extract scheduling is actually implemented in a rather simple way in the Tableau Repository.

There is a table called “tasks”, and if you do the following:


SELECT * 
FROM tasks
WHERE type IN ('RefreshExtractTask', 'IncrementExtractTask')

 

You’ll see all of the scheduled extract refreshes in the Server. There is an obj_type field which is set either to “Datasource” for published data sources, or “Workbook” for extracts contained within a workbook. There is only one entry per workbook, regardless of the number of extracts contained within.

obj_id is the database ID for the given data source or workbook. You can find these by querying the “_datasources” view or the “_workbooks” view. The id field of either of those views is what goes in the obj_id field in the “tasks” table. The site_id field will comes from the “_sites” view.

There is also a schedule_id field which comes from the “_schedules” view. There’s actually a lot in that view, so to get down to just the real possible extract schedules, the following query works:


SELECT *
FROM _schedules
WHERE scheduled_action_type = 'Refresh Extracts'
AND hidden = false

So now that you’ve got all the IDs in place, you’ll need to do an INSERT statement to put the extract in place. Obviously, if you’re doing an INSERT into the repository, you need a higher level of access. This is where I’m not allowed to say exactly how this works, but there is a configuration somewhere that can give you the password for this level of access.

Here’s the INSERT statement:


INSERT INTO tasks
VALUES(
DEFAULT -- id will auto-increment if you pass DEFAULT
, --schedule_id from _schedules
, 'RefreshExtractTask' -- or 'IncrementExtractTask' for incremental
,1 --priority, can be lower if you want. Workbooks seem to default to 50
, --obj_id from _datasources or _workbooks
,NOW() --created_at
,NOW() --created_at
, --site_id
, 'Datasource' -- or 'Workbook'
,NULL --luid will autogenerate correctly when NULL, based on trigger function
, 0 -- this starts as 0
)

Now I hate that this is unsupported and scary, so please tell Tableau Product Management how important this feature is to have implemented in a secure way.

Advertisements

One comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s