How to Automate Data Refresh Operations to Tableau Server with Alteryx

<Edited on Dec 2015 to mention new Alteryx Macro to upload to Tableau Server and Online directly>

So you built a kick-ass visualization in Tableau, which reads from a tidy single TDE file you obtained, after you blended in Alteryx several data sources and performed complex calculations. You published that TDE along a relevant TWB workbook to your Tableau Server or Tableau Online.

Your users love the results and got addicted to the new insights they can now easily surface. You even implemented a time stamp to indicate how fresh the data is, and now, you fall victim of your success, as they ask you to update the viz every day, or even every hour…

What to do? Do you really have to knock on IT’s door to deploy an expensive and complex operations tool? As we are getting near Independence Day, how about opting for Analytic Independence as well? You could and should be in control of the whole data flow, from preparation to publishing.

You can certainly run the workflow as needed in Alteryx, then open the resulting TDE file in Tableau, and finally perform a Publish to Server:

Operations 1That works, but a bit tedious, especially on a hourly basis…

A much more appropriate approach, as of Alteryx 9, is to leverage the Alteryx Scheduler. The license for Scheduler is not included with the standard Alteryx Designer license, but it is a modest add on, way cheaper than upgrading to a Server. The new licensing option is fairly new, see http://www.alteryx.com/products/pricing for details under the Desktop Automation section. With that Scheduler feature, you can now schedule any workflow from within Alteryx Designer, to run hourly, daily and even email you if something goes wrong. It is recommended to run the Scheduler from a PC or Server sitting on your network, possibly always on, but it could also run great from a Amazon EC2 Windows Server, with no Tableau license required…

Should your desired operations run a set of complex Alteryx workflows in a specific sequence, I  highly recommend the set of Runner macros from Adam Riley. Those Macros are free and provide new tools, the Runner tools, that augment your Alteryx and let you visually design your operations:
RunnerChain
These runners macros each kick off a workflow, with a Success or Failure outcome. You can conveniently chain them, as well as additional tools, into an operations workflow.

You now have a TDE file that gets refreshed appropriately. But how will you get it to  Tableau Server automatically? Alteryx Designer does not provide an option out of the box, yet.

In the meantime, there are 3 ways to upload the results of an Alteryx workflow to Tableau Server using the Alteryx Scheduler, all at no cost:

  1. Easy: Tableau Server standard refresh
  2. Easier: Tableau TABCMD script
  3. Easiest: Upload Macro

1. Tableau Server Refresh Feature

A major disclaimer for this approach is that you need a database as a data gateway:
a.  Connect the Alteryx Output tool to your workflow and select the database you will use
Outputto dump the required data formatted for Tableau into a dedicated table. Alteryx supports a variety of database formats, from Amazon Redshift to Teradata, through Oracle, MySQL and MS SQL Server, details here.
b. Assuming you have proper access to the database, and have admin rights to the Tableau Server, you can setup from Tableau Server the scheduled refreshes which will read from the database, as indicated in Tableau’s help. Ensure the Tableau Server job starts only once the Alteryx job has completed its refresh…

If you are using Tableau Online and Tableau Desktop 9, note that Tableau has recently introduced a Sync Client that could help you skip the database step and ease the synchronization. More details available here.

These Tableau standard methods are feasible, but require control over quite a few moving parts, and you will most likely need to coordinate with IT. You are unlikely to get done in a snap…

2. TABCMD Script

This approach is easier, as it does not require setting up anything on the Tableau Server, doing away with the need for Admin rights: If your Tableau Server account has publishing rights, you are all set.
a. Write your Alteryx workflow result to a TDE file that will be stored on the PC or Server running Alteryx with Scheduler
b. Install TABCMD on the PC or Server running Alteryx with Scheduler, a command line utility that comes with Tableau Server.
<Edit September 2015: If you run Tableau Online or have only limited access to your Tableau Server on premise, you might have a hard time getting your hands on the TABCMD installer for  your desktop. Well, fortunately, here is where you can download the standalone TABCMD Installer. I recommend updating TABCMD with at least every major release of Tableau.>
c. Schedule a workflow of that type:

Which is using the Run Command Tool to run a .bat script of this type:

cd\
c:
cd C:\Program Files\Tableau\Tableau Server\hosted-main\extras\Command Line Utility

tabcmd login -s YourTableauServerAddress -t YourTableauServerInstance -u YourLogin -p YourPassword

tabcmd publish “Your Path\YourSassyTDE.tde” -o -n “YourDataSourceName” –project “YourTableauProjectName”

tabcmd logout

pause

Of course you can upload several TDEs in sequence. This approach is reliable, works even for Tableau Online. TABCMD can perform various other tasks, even though those tasks are more Tableau administration relevant than Analyst. I just don’t like that you have to include your password in an open script, and need to edit it manually if you update the password on the Server…

3. Upload Macro

This is my favorite method, as it is the easiest and fastest to implement, and is a breeze to maintain. It only requires publishing credentials to Tableau Server, which you most likely already have.

<Update: Since Dec 2015, the Information Lab upload macro described below is broken for Tableau Online and is not maintained anymore, since Alteryx provides and maintains since Version 10.1 the equivalent macro which is described in my subsequent post. Consider using that official version rather than the Information Lab’s>

The always great folks at Information Lab provide a free macro, that you insert in your workflow as the data output, instead of storing a TDE file. It uploads your workflow generated content directly to Tableau Server and/or Online. You can download the macro as well as additional tools and content from here, and the latest version is always found here:
http://www.theinformationlab.co.uk/att
att stands for Alteryx Tableau Tools, and upon installation, it will add a new tab to your Alteryx tools:
Tableau Tabwhich includes two new macro tools:
Upload Tools
to combine in a workflow:
One carries the credentials to Tableau Server, hidden from sight, while the other selects the relevant Project from your Tableau Server and lets you input the data source name. Combined with the Runner tools mentioned above, this makes arranging operations within Alteryx very easy, with modularity, thanks to the Copy and Paste enabled with those tools.

Note that, if you procured the macro from another source, such as from the Visual Analytics kit, it is possible that you got an older version which could be malfunctioning. The Macros have been maintained to accommodate the security architecture changes that took place with Tableau 9, ensure you use the latest.

 

This entry was posted in Alteryx, Automation, Tableau and tagged , , , . Bookmark the permalink.

One Response to How to Automate Data Refresh Operations to Tableau Server with Alteryx

  1. Pingback: Upload directly to Tableau Server from Alteryx | Insights Through Data

Leave a Reply

Your email address will not be published. Required fields are marked *