How do you publish to Tableau Server from Alteryx and maintain the definition of the Tableau Extract? This is important if you have things including calculated fields and default formats embedded in the Tableau Hyper file.
Within Alteryx one of the Output tool formats is Tableau a Hyper file. This is simple to use, a standard output tool and will save the file to a directory.
In addition, there’s a tool to publish a hyper file to Tableau Server from Alteryx.
However, that tool often isn’t ideal as it overwrites the entire data source, including default settings and published calculated fields.
Publishing calculated fields within the Hyper file optimises the calculations, making your Tableau quicker, and also gives the calculations to anyone using Tableau data source.
Obviously, it’s important to keep the extract definition of the data source already published on the Tableau Server. Removing calculations can have very negative consequences on published dashboards using these fields.
Fortunately, there is an alternative option without any of the aforementioned downsides.
Before anything can publish to Tableau Server, firstly create a hyper file.
How to create a Tableau Hyper file in Alteryx
The Alteryx Output data tool has the option to output to a Tableau Data Extract (TDE) or a Hyper file.
Just select your data, drag in an Output tool and set the file type to be Hyper or Tableau Data Extract (tde).
Alternatively enter a .tde / .hyper file extension into the ‘Write to File or Database’ input box.
This will default to a TDE / hyper File Format.
If you don’t have an extract file already created, Alteryx will create it.
Type the name and location you want for your Tableau Data Extract in the ‘Write to File or Database’ location.
Then Alteryx will create and put your extract file in that location.
In the Output Options you can “Overwrite Existing Extract File (Create if does not Exist)”.
For incremental data updates, “Append to an Extract File”.
When you have the hyper file, next is to publish it to Tableau Server. We want to maintain the extract definition so can’t use the built-in publish tool.
Use Tabcmd to maintain the Tableau extract structure
Tabcmd is a command line utility to interact with the Tableau Server.
Most tasks carried out on the Tableau Server interface can also be carried out via the command line by using tabcmd.
Tableau’s tabcmd, amongst other tasks, enables publishing to Tableau Server from the command line.
There are 2 ways to use Tabcmd in Alteryx to publish to Tableau Server.
- By using Run Command
- By using Alteryx Events
Both of these methods need a command line script to publish an extract.
Publish to Tableau Server using Tabcmd
An example will be the simplest way to show the layout of the script.
publish "\\NetworkLocation\TableauDataToPublish.tde" -s TableauServer -u UserName -p Password -t TableauSite -o -r "Data Sources" --replace
The structure of the tabcmd script should be:
a. Command to run on TabCmd – ‘publish’ in this example
b. TDE / Hyper file to publish to Tableau Server, wrapped in quotation marks
c. Options/arguments required to publish using TabCmd. To maintain the existing Hyper data source definition include “–replace” to the end of the tabcmd command line. You don’t want to overwrite the Tableau data source, you want to replace it. Wrap any arguments in double quotes when there’s a space, such as the “Data Sources” example above.
Important things to note about the string:
- publish = the tabcmd command
- -s = tells tabcmd which Tableau Server to use. This doesn’t need to be the “https://TableauSite” URL, it is better as the server name as entered to the Tableau Postgres database
- -u = the username to login to the Tableau Server (also needs to be set up on the Tableau Server)
- -p = the password to login to the Tableau Server
- -t = the name of the Tableau Site on the Tableau Server to use
- -r = the project name to publish to. It publishes to the Default project if not included.
- -o = Overwrite the existing tde file with the same name
- –replace = replace the extract, keeping the definition
Now we have a tabcmd command line to publish to Tableau Server, the next step is to use it
Publish to Tableau Server from alteryx using Run Command
Using Run Command means the publish to Tableau Server can happen anywhere within the Alteryx workflow.
This can be a big advantage compared to using Alteryx Events.
How to use Run Command to publish to Tableau Server from Alteryx
Run Command isn’t the easiest tool to understand. Once again, a simple example will help.
In this example the Hyper file / TDE file is already created and is on a directory.
- Add a Text Input tool and enter the filepath of the Tableau Data Extract (TDE) or Hyper file.
- Add a Formula tool and create an Output Field called “cmd” with your script to run in the Command prompt.
- Note – when using Run Command the file path to the Tabcmd.exe has to be included in the script – see the above image, where it specifies the location on the C drive of tabcmd.exe, followed by the tabcmd publish script.
- Use a Select tool and select only the Output Field, cmd, created in step 2. Only the Command script should be loaded into the Run Command.
5. Add a Run Command tool:
a. In the Write Source Output section identify where the batch file should be saved.
In the example I use the Alteryx temp directory (%temp%). Set it to be a csv File Format, no delimiter, no field names and no quoted output fields
b. Call the bat file just created in the Run External Program – Command area
The final workflow to publish to Tableau Server from Alteryx is as follows:
Often this would either become a macro or be added within the existing workflow which creates the Tableau extract file.
Publish to Tableau Server using Alteryx Events
We can publish to Tableau Server from Alteryx using the Events functionality.
A downside of this technique is the timing of the publish to Tableau – it either happens before or after the workflow has run.
However, if you’re happy with this constraint then Alteyx Events are a good option.
How to use Alteryx Events to publish to Tableau Server
In the example we have a simple workflow that ouputs a Tableau extract to a directory. Once the workflow is complete use the Alteryx workflow configuration Events to publish the extract to Tableau Server.
- Open the Alteryx workflow that creates the tde / hyper file. Alteryx creates Hyper files in the Output tool.
2. In the Events section of the Workflow – Configuration add a Run Command event. Also check ‘Enable Events’.
3. Choose “After Run Without Errors”
4. Enter the location of the tabcmd.exe in the Command box
5. Enter the command line to execute in the Command Arguments section. This is the Tabcmd publish script written earlier in this post.
6. Ensure the Timeout (in seconds) gives enough time for the TDE file to publish to the Tableau Server
Now it’s complete, run the workflow and you should no longer need to manually publish TDE / hyper files to Tableau Server.
Important notes when publishing to Tableau Server from Alteryx
- If using the Alteryx server, the Alteryx service account will need setting up on Tableau Server with a Publisher role.
- If tabcmd fails, use the tabcmd.log file to see the error message(s). Alteryx will only tell you it failed, the tabcmd.log will tell you why it has failed. For example, you could get tabcmd error code 16. This is copied from the tabcmd overview on the Tableau website, advising where to find the tabcmd.log: Status messages and logs. When a command is successful, tabcmd returns a status code of zero. A full error message for non-zero status codes prints to stderr. In addition, informative or progress messages may print to stdout. A full log named tabcmd.log that includes debugging, progress, and error messages is written to C:\Users\<username>\AppData\Local\Tableau.