While KNIME is an excellent tool for ETL uses, as well as data modeling and machine learning, many KNIME users like to use Tableau for visualization of their data. Since this is a common practice, we thought we should provide a short guide on how to connect KNIME with Tableau. There are a few methods you can use to do this, and the text below describes these methods in further detail, while walking through an example KNIME workflow.
Tableau Export Workflow Example
To get started, download the TableauTesting workflow from the example server. The workflow and associated dataset is available at EXAMPLES/05_Reporting/02_Tableau/01_Tableau_Integration_Example. Go ahead and execute the five nodes listed in the Usual Modeling Steps section. This is a workflow that reads in a file containing demographic and income information for a sample of adults working in the United States, and uses the decision tree node to predict and score income classification for the sample. Of more interest to us, the workflow also exports both the raw data and results of the decision tree model using the Tableau SDK in a few different ways, which we'll discuss in more detail below.
Tableau Online Setup
For the purpose of this exercise, we'll assume that you want to publish data to Tableau Online. However, most of what is discussed here also applies if you host your own Tableau server. If you don't already have a Tableau Online account, you can create a trial account here (https://www.tableau.com/products/cloud-bi). For the steps that follow, you'll need a few pieces of information:
- The URL of your Tableau server. Important note: within KNIME, you must use the re-directed specific URL (e.g. https://us-west-2b.online.tableau.com) and NOT the general URL (https://online.tableau.com). Also, take particular care to use the secure https:// protocol.
- Your username
- Your password
- Your Tableau site ID. For Tableau Online, this can be observed within the URL itself. Given the URL https://us-west-2b.online.tableau.com/#/site/ExampleSite/projects, the Site ID is ExampleSite.
You will also need to have a Tableau Project already in place on the server. By default, Tableau Online creates a default project for you, but it's a good idea to create one and give it an appropriate name. You can do this in Tableau Online by navigating to the projects section and clicking on the New Projects button. Let's call our new Project "Adult Income".
Let's set Tableau Online aside for now; we'll come back to it in a bit.
Tableau SDK Setup
Initially, we'll need to do some one-time setup to make sure that the Tableau nodes will function properly. As discussed in the Node Description, the Tableau nodes require the installation and setup of the Tableau SDK. This involves the following steps:
- Download the Tableau Java SDK that is appropriate for your system.
- Extract the SDK .zip archive to an easily accessible directory on your machine.
- Update your system PATH to point to the Tableau SDK /bin folder. (Windows users can find instructions here.)
- Tell KNIME where the SDK is located by opening the preferences dialog within KNIME and navigating to General → Workspace → Linked Resources. Click New, type in a name (for example, TableauSDK), and click Folder to browse to the SDK /bin folder. See the figure below.
- Finally, if needed, download and install the Visual Basic 2013 Redistributable.
Using the Tableau Writer (TDE) Node
Now that our Tableau Online account is in place and the SDK is configured, let's use the Tableau Writer (TDE) node to export a Tableau Data Extract file. Double click the node assigned to write out model scoring statistics to open its settings. The only thing we need to do here is to give a path and file name for the TDE extract:
Now, we can execute the node, Simple enough, right? But what do we actually do with TDE files? Files of this type can be imported into Tableau Desktop for further analysis, and from there, published to a Tableau Server. TDE files can also be published directly to Tableau server by using Tableau's Command Line Utility, tabcmd. We'll discuss each of these methods below.
Publishing TDE Files
TDE files exported from KNIME can be used as-is with a installation of Tableau Desktop. Double clicking on the TDE file will open the Tableau interface where you can begin construction of visualizations on the data right away. You can also publish the TDE for use in the Tableau Online environment via the desktop GUI. To do this, navigate to Server → Publish Data Source, assign a Project in the resulting dialog, and click Publish. This will make the data from the TDE available in the Tableau Online Environment.
Publishing TDE files can also be done without a GUI, using tabcmd, Tableau's Command Line Utility. Tabcmd allows for scripting of data management tasks in the server environment, and has a straightforward set of options and parameters. Logging into a Tableau server, for example, can be accomplished with the following command:
tabcmd login -s https://online.tableau.com -u YourUsername -p YourPassword
Publishing a TDE file to the server and then be done via the following. Let's use our generated TDE as an example, being sure to include the full path and Project name on the server:
tabcmd publish "c:\path\to\file\Scorestats.tde" -r "Adult Income"
The figure below presents the results of using tabcmd to publish a TDE using these commands. The tabcmd documentation, linked above, presents a more comprehensive list of available commands and options.
Using the Send to Tableau Server Node
Perhaps you would like to bypass the creation of TDE files altogether, and instead send your data directly to Tableau Online via the KNIME interface. This can be done using the Send to Tableau Server node. For this example, let's send the raw Adult data to our Tableau Online server for additional visualization. Double click the node to bring up the configuration dialog, and enter the required information Under the Tableau Server Settings tab. We'll be sending our file to the Adult Income project, and giving it a name of AdultRaw. Note the format of the URL carefully!
If we navigate to our project space in Tableau Online, we can now see three available data sources:
- Scorestats Extract, published via the Tableau Desktop GUI
- Scorestats, published via the command line using tabcmd
- AdultRaw, published directly from KNIME.
Creating a Visualization in Tableau Online
Once your data has been published to Tableau, you are free to create your visualizations in the usual way. Here are a couple of examples generated from the Adult dataset.The first presents a map of average ages for adults in the data set, by country of origin. The second is a stacked bar chart that shows, in descending order, the total hours adults worked, by gender, in each country. If you are familiar with Tableau, you know that there are many other visualizations you can easily produce - see what you can come up with!