Note: This article is also posted on our Developer Blog.
Tableau is fast becoming commonplace in life science circles. It is able to pull together data to share in a nicely formatted dashboard display for everyone to share.
To make loading data from Riffyn in Tableau seamless, we’ve created our own Web Data Connector. All users should be able to open any individual or group of data sets from Riffyn right inside of Tableau. This feat was a bit harder to accomplish and we wanted to share a few of our findings.
Tableau’s official documentation for opening multiple data sets shows a manual, hard-coded approach to loading a data schema (in the getSchema section of the WDC JS file). A short (or long) Google search doesn’t show any more dynamic alternatives. For Riffyn, we have been able to accomplish a more dynamic loading and are sharing this method for other Tableau developers.
Below is a basic framework for the WDC JS file:
The key for loading multiple tables lay in how to structure the getSchema section (highlighted in yellow). There are three building blocks to follow
1. Get user input that will allow you to determine the sets that you would like to load
The first step is to allow the user to provide input that references - either directly or indirectly - the data sets they would like to import. This could be something like a date range (i.e. enter 1/1/2018 to 1/7/2018 to get all of the sales tables between those dates).
For Riffyn, all data from Experiments are saved into flattened statistical data frames. These data frames are what we want the user to load into Tableau. Users, however, interact with Riffyn’s web interface and do not have a direct means of referencing the underlying data frames.
For our needs, we allow users to enter Experiment IDs for individual Experiments, Process IDs to get all of the Experiments for a given Process, a Process ID with a selection of Experiment numbers, and more.
2. Create an Array of references
From this user input, create an array of references that indicate the data that you want to load. For Riffyn, this is an array of Experiment IDs. Experiment IDs can be found in the URL bar for any Experiment. They’re very convenient to copy/paste into the Tableau WDC as-is.
AllIds = [Lc8AnQiZ2L3Hvsy3Q, m3oCXJ5pc9SJfYkt9];
The task in developing the Riffyn web data connector was to provide the user with a variety of options for inputs and then extrapolating those inputs into this array of Experiment IDs.
3. Build-up Columns of each
With this Array of references, loop through each, import the desired data via $.ajax(), and build up a table schema for them.
var thisdata = $.ajax()...
var thisID = getID(thisdata);
var dType = getDataType(thisdata);
There are a few important notes when constructing your tableSchema.
First, your ids must only contain letters, numbers, or underscores. That’s it. No spaces. No pipes (|) or other symbols. This was a bit of an issue for Riffyn as we name column headers to match Process designs.
Second, each column requires a data type associated with it. Tableau supports several data types - bool, data, datetime, float, geometry, int, string. This could be a challenge if your imported data doesn’t provide you with a means of easily determining what data type each column should be.
// Create Schema
alias: "Riffyn Experiment: "+exp,
Lastly, each entry into the tableSchema array must have a unique id that also only contains alphanumeric and underscore characters. Alias is options, but is very helpful. As a best practice, your alias should be also be unique.
4. Push the built up columns it to the table schema
After you’ve built up your tableSchema, you can set the callback. The tableSchema array is actually an Array of Arrays.
The important observation here is that tableSchema does not have brackets around it unlike with a single table import. This is because tableSchema, as we’ve constructed it, is already an Array containing individual Arrays (Compare with Line 29 in this Example).