Tech It Out: Scripting Automated Emails of Extracts with Tableau
December 2nd, 2015 by
Businesses now more than ever need effective, data-driven metrics to drive solid, evidence-based workflows. More and more small- and medium-sized companies are realizing an increased demand for data visualization to be able to quickly and clearly communicate exactly what is happening at every level in their business. A Stanford-born company arose to fill this need: Tableau. Tableau has more than 35,000 customer accounts, so chances are, if you need data analytics, you’re using Tableau.
Our particular needs were a little different and required a little bit of innovation. Tableau has user accounts, but we weren’t satisfied with the level of user account access control to the app, and we couldn’t give everyone access to all of the data. So how could we report metrics to different departments while keeping the irrelevant inner workings of other departments cleanly separated?
The solution lies in having meaningful tags on workbooks and automatically emailing out extracts. Here’s the reference for commands we’ll be using.
What you’ll need:
– A scripting language you’re comfortable with
– An already running Tableau Server
– The username and password to that server to log in and perform general admin tasks
– The site ID that you’re interested in reporting on
1. Create a command to log in to the Tableau Server.
The first thing you’ll want to do is write a command in your script to log in to your already running Tableau server. It should have the basic form:
tabcmd login -s SiteURL -u Username -p Password
2. Focus on either emailing PDFs or workbooks and plan accordingly.
You can either export PDFs of views or the actual workbooks themselves. The workbooks are more time-consuming to export, but they contain the interactive parts of the workbooks, and the dimensions don’t have to be known beforehand. Either way, you’ll need to perform a GET request.
If you want to get info on the workbooks:
your_site_uri + ‘api/2.0/sites’ + site_id + ‘/users/’ + user_id + ‘/workbooks
If you want to get info on the views:
your_site_uri + ‘api/2.0/sites’ + site_id + ‘/workbooks/’ + workbook_id + ‘/views’
3. Parse the workbook info.
Each workbook and view comes back with a smattering of XML info related to the workbook. The important bits are as follows:
- ID
- Title
- Project
- User-defined tags
- Frequency
- Recipients
- Disabled
The ID, title, and project are all related to the default Tableau structure. In our case, I have user-defined tags which represent the frequency that the workbooks are emailed out (daily/weekly/monthly), a tag with the email address of each intended recipient, and a tag indicating if the emailing function should be disabled for that particular workbook.
With some simple string substitution, maybe a regex pattern here or there, you should be able to pull out all of the relevant information and create your own class to store the tag information for each workbook.
4. Refresh your extracts.
Unless you want stale data getting sent out, you should refresh each extract related to each workbook. Every call should include information about the workbook title, the project, and the data source. It should also have the synchronous flag to ensure stale data isn’t mailed out before the extract completes refreshing. It should have the form:
tabcmd refreshextracts –workbook WorkBook –project Project –datasource DataSource –synchronous
5. Export the workbooks.
Exporting the workbooks command should look something like this:
tabcmd get ‘/workbooks/’ + title + ‘.twb -f ‘ + title + ‘.twbx’
6. Email
Use the tags pulled from step three to create the list of recipients. Use your favorite email class (I used Ruby’s built-in Mail class to do this) to email out each email if the particular report is to be sent out that day.
And that’s it—you’re done! You’ve defeated automated reporting!
