How to Build a Basic Tableau Web Data Connector

The most important step when using Tableau is to bring in data for analysis. But what if your data lives somewhere on the web and not in a database? One way to bring that web-based data into Tableau is with a Web Data Connector. In this tutorial, I'll show you the basics of setting up a simple web data connecter to help get you started!

The data

In this tutorial, I'll be using data from the Open Brewery DB. The Open Brewery DB is a free data set and API on breweries, cideries, brewpubs, and bottleshops. The API doesn't have any CORS restrictions or authentication and includes simple pagination making it a perfect example for getting started with WDCs. The final WDC will allow you to select a US state and get all the breweries in that state.

Prerequisites

Local environment

If you'd like to follow along locally you can set up a simple http-server to serve your WDC:

Install node.
Open the terminal or command prompt and install http-server globally with the command npm install --global http-server.
Download and unzip this starter template.
Within that unzipped folder run the following command to start up the http server: http-server -p 8888.
Visit http://localhost:8888 in your browser and you should see "Hello World!" if everything is set up correctly!

Glitch environment

If you don't want to bother with running things locally you can quickly get started by remixing the starter template on Glitch. Glitch is a free tool for developing and hosting apps collaboratively. It's also where you can find the finished code for this and many of my tutorials. I recommend creating an account with Glitch so you can save your project but it is not necessary to follow along with this tutorial. Once you've remixed the starter template visit https://<your-project-name-here>.glitch.me and if you see "Hello World" everything is good to go.

Tableau Desktop 2019.4

This tutorial will use modern javascript methods and therefore requires a Tableau Desktop version of 2019.4 or higher.

Set up the UI

When you create a WDC you are able to show an interactive view where your users can input variables and preferences before the data is fetched. Let's set up a simple page with just a dropdown of US states for our end users to select and a button to submit their selection. Replace the contents of the <body></body> tags in the index.html page with the following:

<p>Select a state:</p>
<select id="state">
  <option value="Alabama">Alabama</option>
  <option value="Alaska">Alaska</option>
  <option value="Arizona">Arizona</option>
  <option value="Arkansas">Arkansas</option>
  <option value="California">California</option>
  <option value="Colorado">Colorado</option>
  <option value="Connecticut">Connecticut</option>
  <option value="Delaware">Delaware</option>
  <option value="District of Columbia">District of Columbia</option>
  <option value="Florida">Florida</option>
  <option value="Georgia">Georgia</option>
  <option value="Hawaii">Hawaii</option>
  <option value="Idaho">Idaho</option>
  <option value="Illinois">Illinois</option>
  <option value="Indiana">Indiana</option>
  <option value="Iowa">Iowa</option>
  <option value="Kansas">Kansas</option>
  <option value="Kentucky">Kentucky</option>
  <option value="Louisiana">Louisiana</option>
  <option value="Maine">Maine</option>
  <option value="Maryland">Maryland</option>
  <option value="Massachusetts">Massachusetts</option>
  <option value="Michigan">Michigan</option>
  <option value="Minnesota">Minnesota</option>
  <option value="Mississippi">Mississippi</option>
  <option value="Missouri">Missouri</option>
  <option value="Montana">Montana</option>
  <option value="Nebraska">Nebraska</option>
  <option value="Nevada">Nevada</option>
  <option value="New Hampshire">New Hampshire</option>
  <option value="New Jersey">New Jersey</option>
  <option value="New Mexico">New Mexico</option>
  <option value="New York">New York</option>
  <option value="North Carolina">North Carolina</option>
  <option value="North Dakota">North Dakota</option>
  <option value="Ohio">Ohio</option>
  <option value="Oklahoma">Oklahoma</option>
  <option value="Oregon">Oregon</option>
  <option value="Pennsylvania">Pennsylvania</option>
  <option value="Rhode Island">Rhode Island</option>
  <option value="South Carolina">South Carolina</option>
  <option value="South Dakota">South Dakota</option>
  <option value="Tennessee">Tennessee</option>
  <option value="Texas">Texas</option>
  <option value="Utah">Utah</option>
  <option value="Vermont">Vermont</option>
  <option value="Virginia">Virginia</option>
  <option value="Washington">Washington</option>
  <option value="West Virginia">West Virginia</option>
  <option value="Wisconsin">Wisconsin</option>
  <option value="Wyoming">Wyoming</option>
</select>
<button onclick="submit()">Submit</button>

Here we've added

Some text to direct end-users to what to do.
A <select> tag with the id state that has a list of all the US states from the Open Brewery DB.
A <button> with an onclick action to a function called submit() that we haven't created yet.

Test in Tableau Desktop

Starting the debugger

Tableau Desktop has a debugging tool built in which can help us to troubleshoot any problems we come across while developing our WDC. First, close any instances of Tableau Desktop then follow the instructions here to start Tableau Desktop with the debugger running. Note: If you are using 2021.1 or higher you no longer need to use an old version of Chrome.

Once you've started Tableau Desktop with the debugger go to http://localhost:8696 (or whichever port you chose) and you should see a list of "Inspectable Pages" and the "Discover Pane". Keep this open so you can debug as we go along.

Test what we have

Let's take a moment to test out our WDC in Tableau Desktop. Open up Desktop and make a new connection to "Web Data Connector". Enter the URL for your WDC. If you used glitch it will be https://<your-project-name-here>.glitch.me. If you are using a local server (make sure it's running!) the URL will be http://localhost:8888. Hit enter and you should see your state drop-down and submit button. Note: I'm not including anything about CSS in this tutorial but feel free to style as you wish! Then refresh the debugger page in your browser and click on your WDC. Next, click on the console tab and you should see the Hello 🌎 message. As you build out this WDC feel free to send messages to this console tab using console.log("your message or data here") to see how things work or troubleshoot an error.

Add the javascript

Now that we have our interactive page set up let's get to the meat of the WDC, the javascript! Within the script.js file start by replacing the contents of the file with the following scaffolding:

let myConnector = tableau.makeConnector();

myConnector.getSchema = function (schemaCallback) {
  // Add code here
};

myConnector.getData = function (table, doneCallback) {
  // Add code here
};

tableau.registerConnector(myConnector);

function submit() {
  // Add code here
}

Here's what we've done:

Created a connector and assigned its functions to call to create the schema (myConnector.getSchema) and to get data (myConnector.getData) and then registered the created connector with Tableau.
Note that the getSchema function has an argument for a schemaCallback function that we will use once we've finished setting up the schema.
Similarly, note that the getData function has table and doneCallback arguments. The table is where we will append the data once we've collected it and the doneCallback is how we let Tableau know we're done.
Finally we have a function that will get called when the user clicks the submit button.

When you load up the WDC in Tableau Desktop the UI will be displayed allowing the end-user to select a state. Once the submit button is hit Tableau will run the getSchema function. Then when it's time to get data (such as when creating/refreshing the extract or hitting "Update Now") it will call getData.

Set up the submit button

When the end-user clicks the submit button we want to grab the state they selected and store it so we can use it to retrieve breweries from that state later. To do this we'll store the selection in tableau.connectionData. This is a string value that can be used even after the interactive window has been closed. For more complex WDCs you can stringify a JSON object to store more data but for now, we only need to save one string, the selected state. Add the following to the submit() function:

tableau.connectionData = document.getElementById('state').value;
tableau.connectionName = 'Open Brewery DB';
tableau.submit();

Here's what we did:

Set tableau.connectionData to equal the selected state by using the ID on the drop-down to get its value.
Giving the connection a name.
Telling Tableau we are ready to move to the next phase by calling tableau.submit().

Create the schema

The first thing we need to do is tell Tableau the structure of the tables we want to bring in. To start we describe the columns in an array. For each column, we need an id and a data type. You can optionally include an alias for a more human-readable name as well as several other options. So for example to add a column for brewery name it would look like this:

{ id: "name", alias: "Name", dataType: tableau.dataTypeEnum.string }

To see all the possible data types see the WDC reference page.

We'll put together a list of these columns into an array and call it columns, add this code within your getSchema function:

let columns = [
  { id: 'id', alias: 'ID', dataType: tableau.dataTypeEnum.int },
  { id: 'name', alias: 'Name', dataType: tableau.dataTypeEnum.string },
  { id: 'brewery_type', alias: 'Brewery Type', dataType: tableau.dataTypeEnum.string },
  { id: 'street', alias: 'Street', dataType: tableau.dataTypeEnum.string },
  { id: 'city', alias: 'City', dataType: tableau.dataTypeEnum.string },
  { id: 'state', alias: 'State', dataType: tableau.dataTypeEnum.string },
  { id: 'postal_code', alias: 'Postal Code', dataType: tableau.dataTypeEnum.string },
  { id: 'country', alias: 'Country', dataType: tableau.dataTypeEnum.string },
  { id: 'latitude', alias: 'Latitude', dataType: tableau.dataTypeEnum.string },
  { id: 'longitude', alias: 'Longitude', dataType: tableau.dataTypeEnum.string },
  { id: 'phone', alias: 'Phone Number', dataType: tableau.dataTypeEnum.string },
  { id: 'website_url', alias: 'Website URL', dataType: tableau.dataTypeEnum.string },
  { id: 'updated_at', alias: 'Last Updated At', dataType: tableau.dataTypeEnum.string },
];

Next, create an object with the information for our table and add that below the columns array:

let tableSchema = {
  id: 'OpenBreweryDB',
  alias: tableau.connectionData + ' Breweries',
  columns: columns,
};

Here we are just setting the metadata for the table and adding the columns we set up. We're also using tableau.connectionData to pull the state that the end-user selected, which we set when the user clicked the submit button. Remember that once set, tableau.connectionData is available for all phases of the WDC, even when refreshing later.

Finally, pass the table we just created to the schemaCallback function to fully register the table schema:

schemaCallback([tableSchema]);

Get data

Now we're done with the getSchema function so let's move on to actually getting the data from the Open Brewery DB. The API doesn't return all the data at once, so we'll need to use pagination to get all the breweries. We can go through each page using a while loop and some variables to keep track of our place. Within your getData function add the following:

let tableData = [];
let state = tableau.connectionData;
let morePages = true;
let page = 1;

while (morePages) {
  // Add fetch code here
}

Here's what we've done:

Set up a variable tableData to store all the brewery data as we get it from the API.
Pulled the state that was selected by the end-user from tableau.connectionData.
Set up morePages and page variables to use with our while loop. While morePages is set to true we'll loop through and increment pages to get all the data from the API.

Fetch the data

Take a look at the API documentation for the Open Brewery DB and you'll see a list endpoint. Try it out! This endpoint uses pagination and allows you to filter the list by different properties. The documentation tells us that we can filter by state by using by_state and passing the state name. It also shows us that we can use page and per_page (max of 50) to go through all the data. If we reach a page with no data the API returns an empty array. We'll use this in our loop to determine when to finish requesting data. First, let's get the first page of data using the Fetch API by adding this inside the while loop:

let url = `https://api.openbrewerydb.org/breweries?page=${page}&per_page=50&by_state=${state}`;
let response = await fetch(url);
let data = response.json();

Notice that I'm using a template string to pass in the page (starting at 1) and the state the user selected to the URL. I'm also using the async/await promise pattern with the Fetch API to get data from that URL

Next, we need to check if we actually received any data. We'll use this to determine if we should keep going with the while loop or end it.

if (data.length > 0) {
  // Add push code here
  page++;
} else {
  morePages = false;
}

Here we are checking the length of the array returned by the API, if there were breweries returned (data.length > 0) then increase the page number (page++) so the next time we go through the loop we'll collect the next page of data. Otherwise, flip our morePages variable to false and therefore end the while loop.

Lastly, we need to push the data we're getting from the API into our tableData variable which will eventually get passed to Tableau. To do this we need to push in an item for each row where each property matches the properties we set in the schema. Add this inside the first part of the if statement in the while loop:

for (let brewery of data) {
  tableData.push({
    id: brewery.id,
    name: brewery.name,
    brewery_type: brewery.brewery_type,
    street: brewery.street,
    city: brewery.city,
    state: brewery.state,
    postal_code: brewery.postal_code,
    country: brewery.country,
    longitude: brewery.longitude,
    latitude: brewery.latitude,
    phone: brewery.phone,
    website_url: brewery.website_url,
    updated_at: brewery.updated_at,
  });
}

Here we are looping through each brewery returned by the API, creating an object that matches up the properties of the brewery with the column names we set in getSchema and pushing that object into our tableData storage variable.

As we loop through each page, more and more breweries will get added to tableData then finally once we've found no more breweries (the else statement) we need to add tableData to the Tableau table and let Tableau know we're finished. Outside of the while loop, but still within getData add the following:

table.appendRows(tableData);
doneCallback();

At this point your WDC is complete! Refresh the WDC page in Tableau Desktop, select a state from the drop-down, and click submit. You should then see the Tableau connection pane with the column names we created within getSchema. Next, click "Update Now" to run the getData function and pull the data in! Success!

Below you can find the live code version of this tutorial (with some formatting) feel free to inspect it or remix a copy of your own! Learned something new? Subscribe and never miss a new post!

🌟 Load live code sample! 🖱️

How to Build a Basic Tableau Web Data Connector

April 30, 2021 - Tableau, Web Data Connectors

The data

Prerequisites

Local environment

Glitch environment

Tableau Desktop 2019.4

Set up the UI

Test in Tableau Desktop

Starting the debugger

Test what we have

Add the javascript

Set up the submit button

Create the schema

Get data

Fetch the data

Subscribe

Leave a comment

About Me

Subscribe

Categories

Popular Posts

A Quick Tableau Data Source Mapper

Automatically Update a Google Sheet From a Hosted CSV

Quick Copy Extension

Topic Request