Dynamic loading of multiple tables in Tableau Web Data Connector

Riffyn Tableau.png

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:

(function() {

var myConnector = tableau.makeConnector(); 

myConnector.getSchema = function(schemaCallback) {

// Get multiple identifiers from user
var userInfo = $(“#userInfo”).val();

// Split Identifiers into an array
var AllIds = getUserInfoArray(userInfo); 

// Loop through each item in the array
AllIds.foreach {
// Get the schema for this individual item
var thisdata = $.ajax()...

// Determine the data type of each schema col
var thisID = getID(thisData);
var dType = getDataType(thisData); 

// Push cols of schema to cols array
cols.push({id:thisID,dataType: dType}); 

                // Create Schema
                tableSchema.push({
          id: exp,
                     alias: "Riffyn Experiment: "+exp,
                     columns: cols
                });
}

// tableSchema is an array of tables rather than a
// single table. 
// schemaCallback sends all table schemas to Tableau
schemaCallback(tableSchema);  
 
};

myConnector.getData = function(table, doneCallback) {

// Tableau will getData individually for each table
// getData need only acquire data from a given “id”

      table.appendRows(csvJSON); 
      doneCallback();     

};

$(document).ready(function() {


        $("#submitButton").click(function() {
            
           // Pass Variables
           var expObj = {
             experiment: expid
           };                            
           // Submit to Tableau
           tableau.connectionData = JSON.stringify(expObj);
           tableau.connectionName = "Riffyn WDC"; 
           tableau.submit(); 
          
            
        });

}); // document

})();
 

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.

 
Riffyn Tableau.png
 

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.

cols.push({id:thisID,dataType: dType});

// Create Schema
tableSchema.push({
    id: exp,
    alias: "Riffyn Experiment: "+exp,
    columns: cols
});

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.  

schemaCallback(tableSchema);  

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).

So that’s it. It’s not very hard to implement, but it can be challenging for new Tableau / JavaScript developers. Many of JavaScript’s foibles with arrays come into play. Our hope is that this reference will serve to smooth out any points of confusion.
 

References

https://github.com/tableau/webdataconnector/blob/master/Examples/js/earthquakeUSGS.js

http://tableau.github.io/webdataconnector/docs/api_ref.html#webdataconnectorapi.columninfo.datatype

http://tableau.github.io/webdataconnector/docs/

http://tableau.github.io/webdataconnector/docs/wdc_multi_table_tutorial

Douglas Williams