Creating my first Tableau web data connector – the highs and the lows: part 1

After having successfully (enough) completed my introduction to creating a real live Tableau web data connector, I wanted to follow the lead of one of the inspirations for this otherwise unnecessary effort – Chris Love’s Persiscope on the subject of sharing Tableau-related failure as well as unbridled success –  and document something about the less smooth aspects of the journey it took. If nothing else, they’ll be useful notes if I have to do this again for real in the future.

First of all, I gave myself an unnecessarily harsh setup 🙂 . I was working on my company computer (shhhhh) as it has Tableau Desktop installed, and I figured that’d be important.  The significance of that is that I don’t have the admin rights needed to install any new software on it. And I don’t do web development as part of the day job, so I was restricted to using only very generic tools. In practice, this meant Windows Notepad for the most part, and Google Chrome for testing.

I had downloaded the Tableau Data Connector software development kit  I was a little worried at claims that I’d need to install a web server to use it, which I’m not sure our work IT security overlords would appreciate – but in the end, I learned that you can successfully create a Tableau web data connector without having:

  • the web data connector SDK
  • a web server
  • admin rights
  • …Tableau (yes, seriously).

What I did need:

  • a web browser (I used Chrome)
  • a text editor (I used Window notepad, because, well, what more could you want?)

I had also decided to try and work it out without asking anyone anything. The Tableau user community is pretty amazing in terms of generously giving up answers, and I’m sure someone would have solved any problem I encountered so fast all the fun would have gone.

My approach then was to follow the Tableau Web Data Connector tutorial. In this tutorial, they build a web data connector to a financial website to retrieve stock data. I figured for a first try I’d just follow along and just put the details of the BoardGameGeek API I wanted to use in instead of the Yahoo API.

I’m not going to go through the tutorial line by line, as you might as well just read the original. Instead I’ll just intend to highlight some points where I had problems, and what I had to do to overcome them.

The first thing I’d note in hindsight is that the tutorial is not actually “how to build the simplest possible web data connector” as I had imagined it would be from its description as “basic”. It surely is a relatively simple one (no authentication needed etc.) but it does contain at least 3 functions that are only necessary because of the specific API they were connecting to. These are:

  • buildUri
  • getFormattedDate
  • makeTwoDigits

Pretty obvious when you see what they do – but it would be a mistake to imagine you should just copy the function structure of the basic connector and replace it with your own code. You won’t need the above 3 functions for the most part. I felt the tutorial could make that clearer, especially for someone even more novice than I, who had never touched web programming (I’ve done some in the distant past).

But anyway, begin at the beginning. I raced happily through parts 1 and 2 of the tutorial, copying and pasting their base code. Nothing important to change there unless you want to give your version a more appropriate title.

Part 3 instructed me how to create a form for the user to enter information about their data on. Again, I can imagine some data connectors won’t need to prompt the analyst using it for any information, in which case you could also disregard this section.  However I did actually need that feature, as I wanted it to let me enter a BoardGameGeek username in and only retrieve board games that person had in their collection.

In the mean time, I had discovered – joyfully – that there’s a hosted Tableau Web Data Connector simulator on Github, thank you so much! The reason it made me happy is that it meant that I didn’t need to install a web server on my own computer, or do anything with the SDK that might have involved needing to beg for admin rights over my laptop.

Once I developed a connector in Notepad, I could just upload it somewhere (I chose Github Pages myself) and use the hosted simulator to see if it works – which is the reason you don’t actually need a copy of Tableau in the first place.  I’m sure it would have been far quicker to iterate if I had installed it locally, but it works just fine in a hosted version – as long as you beware the cache.

Lesson learned: It seems like there’s a lot of caching going on somewhere between or within the hosted Tableau Web Data Connector simulator and my web host (Github). Oftentimes I’d make changes to the code, reupload, but it didn’t seem to change the result as shown in the simulator. Sometimes closing browsers, deleting cookies etc. helped – but not always. I’m sure there is some science to this that one could work out, but given I didn’t need to make a lot of edits in developing a relatively simple data connector, I basically settled for renaming my file “version 1.html”, “version 2.html” etc. whenever I made a modification, which guaranteed a lack of annoying caching action.

But anyway, defining the user interface per the tutorial went smoothly. I entered the web address where I’d uploaded my connector: “amedcalf.github.io/BGGConnector.html”, unticked the “automatically continue to data phase” box (as the data phase is something I defined later, in line with the tutorial), and ran the “interactive phase”.

Up popped my bare-bones form asking for a BoardGameGeek username, I filled it in and pressed OK, and it took me back to the simulator and showed me a form that indicated that it had indeed recognised the information I gave it. Success!

Capture

No data was returned just yet – but it clearly recognised what I had entered into the form just fine.

Lesson learned: Although this wasn’t a problem for me in this stage, I later noticed that if there’s anything wrong in your code then you do not always get an error message per se. Sometimes the simulator just doesn’t close your UI, or it does but doesn’t return you any information – i.e. acts like a faulty web page. In my experience that tends to be when I’d made a minor mistake in my code – a missing semicolon or bracket or similar. Once that was fixed, the simulator worked nicely again (if you want to go wild and use something other than notepad that does syntax highlighting, you’ll probably not suffer this much.) So you should assume that it’s your code, not the simulator, that is broken in such instances 🙂

It became clear the user interface is defined in normal HTML – so if you are a nifty web designer/coder, or want to learn to be one, you can make it as beautiful as you like. That wasn’t the point of the exercise for me though, so I just made a plain default font and default box to type information into for my effort.

So, that was the user interface “designed” and tested, which takes us up to part 4 of the Tableau tutorial.

Next up: time to actually access the data I wanted. This proved to be a little more difficult first time through though.

#VisualizeNoMalaria: Let’s all help build an anti-Malaria dataset

As well as just being plain old fun, data can also be an enabler for “good” in the world. Several organisations are clearly aware of this; both Tableau and Alteryx now have wings specifically for doing good. There are whole organisations set up to promote beneficial uses of data, such as DataKind, and a bunch of people write reports on the topic – for example Nesta’s report “Data for good“.

And it’s not hard to get involved. Here’s a simple task you can do in a few minutes (or a few weeks if you have the time) from the comfort of your home, thanks to a collaboration between Tableau, PATH and the Zambian government: Help them map Zambian buildings.

Whyso? For the cause of eliminating of the scourge of malaria from Zambia. In order to effectively target resources at malaria hotspots (and in future to predict where the disease might flare up); they’re

developing maps that improve our understanding of the existing topology—both the natural and man-made structures that are hospitable to malaria. The team can use this information to respond quickly with medicine to follow up and treat individual malaria cases. The team can also deploy resources such as indoor spraying and bed nets to effectively protect families living in the immediate vicinity.

Zambia isn’t like Manhattan. There’s no nice straightforward grid of streets that even a crazy tourist could understand with minimal training. There’s no 3d-Google-Earth-building level type resource available. The task at hand is therefore establishing, from satellite photos, a detailed map of where buildings and hence people are. One day no doubt an AI will be employed for this job, but right now it remains one for us humans.

Full instructions are in the Tableau blog post, but honestly, it’s pretty easy:

  • If you don’t already have an OpenStreetMap user account, make a free one here.
  • Go to http://tasks.hotosm.org/project/1985 and log in with the OpenStreetMap account
  • Click a square of map, “edit in iD editor”, scan around the map looking for buildings and have fun drawing a box on top of them.

It may not be a particularly fascinating activity for you to do over the long term, but it’s more fun than a game of Threes – and you’ll be helping to build a dataset that may one day save a serious amount of lives, amongst other potential uses.

Well done to all concerned for making it so easy! And if you’ve never poked around the fantastic collaborative project that is OpenStreetMap itself, there’s a bunch of interesting stuff available there for the geographically-inclined data analyst.

 

Journey complete: a BoardGameGeek collection Tableau web data connector

Several months (!) after setting myself the challenge of becoming familiar with how to create a Tableau Web Data Connector, the good news is…it’s done! Well, done enough that I now understand how to create web data connectors should I ever need one in reality anyway.

As planned, its a connection via the BoardGameGeek API to your very own board game collection, if you have catalogued one at that site. For the uninitiated, BoardGameGeek is an amazing site if you’re the sort of person that likes boardgames, and, yes, cataloguing.

So, for starters, if you have indeed got a collection catalogued, or want to analyse someone else’s collection, then you can now point your Tableau Desktop software to a web connector at http://amedcalf.github.io/BGGConnector.html

 

Important / annoying note (explained below): BEFORE you do this, go and visit the below link in your web browser to make sure your BGG collection is not going to be stuck in an API queue.

https://boardgamegeek.com/xmlapi2/collection?username=Adam121

replacing the “Adam121” with the username you’re interested in. If you get a message about queues, caches or other errors, wait a few seconds and try again, until you end up with some unpleasant looking XML code that looks something like a whole string of this.

Capture

 Yes, this is not user friendly and would not be acceptable in a “production” environment, but this is just for fun; I’ll explain more below.

Once you’ve done that, go back into Tableau.

Once you have entered http://amedcalf.github.io/BGGConnector.html into the web data connection datasource part of Tableau (see Tableau’s instructions here), you should get a basic prompt to enter your BoardGameGeek username. You can test it with “Adam121” if you like, without the quotes. Do that, and up should pop the relevant collection ready for usage in Tableau.

Here is a description of the fields you’ll be presented with:

  • Boardgame Name: Textual name of your board game
  • Image Thumbnail URL: text URL of a small image relating to the boardgame, usually its cover.
  • Image URL: same as above, but a big version.
  • Last Modified: date the record was last changed.
  • Year Published: the year the game was published.
  • Plays: how many times you logged that you played this game.

Then there’s a series of flags that are set to either 0 (false) or 1 (true). These are:

  • For Trade
  • Owned
  • Preordered
  • Previously Owned
  • Want
  • Want To Buy
  • Want To Play
  • Wishlist

and are all attributes you can input yourself per game with the BGG collections feature.

Whilst it is based on the BoardGameGeek API, I should also to give maximum thanks to the BoardGameGeek user “Strathmeyer”, whose past repurposing of the BGG API into CORS-enabled JSON is what I ended up using as my source, rather than the API directly. When I come to document my journey, I’ll go through why.

One thing to note: if you try it on a username and it doesn’t work (perhaps giving a “typerror”) then this is probably down to the caching of the API on the server. BoardGameGeek queues API calls for collections that haven’t been requested in a while, and the JSON source seems to cache the “in a queue” response meaning that no data appears.

You can see if this is why you’re having problems if you go to this link, putting the BoardGameGeek user ID you’re after after the last equals sign of the link:

http://bgg-api.herokuapp.com/api/v1/collection?username=YOURUSERNAME

If it returns the message “Your request for this collection has been accepted and will be processed. Please try again later for access” then you’re in the queue. And it seems to cache the fact you’re in the queue, which is why you should go visit https://boardgamegeek.com/xmlapi2/collection?username=YOURUSERNAME before you do anything else, even visit the herokuapp link.

I haven’t taken the time to come up with a nice solution for this, but this is what worked for me. Obviously it’s a very unsuitable requirement should this be the mainstay of someone’s production system, but I’m afraid I was just playing about to see what works 🙂

So what can you do with this, except poke around for fun?

Well, ever since the great-yet-terrible event that was the downsizing of my infeasibly oversized board games collection, I don’t really have more than would fit on a single screen of BGG to be honest, and I can’t usually find many people to play them with me anyway (insert sympathy sound effect). But if you had a bigger collection, you might find it useful to answer such questions as “How many board games do I own?”, “Which board game have I played the most?”, “How many board games do I want to buy?” and so on. If so, please enjoy!

Just to prove to myself that it works, here’s an example very simple, quite pointless, dashboard that displays my collection and allows you to click on the name of a game in it, and see what the box looks like. Feel free to click through and play with it.

Capture

What’s next? Well, the point of the exercise was not really to find a way to list the remnants of my board games collection – but rather to learn something new, and document something of the journey I went through doing so, warts and all. With that in mind, I’ll be writing a few notes as to the trials and tribulations I went through in creating this, for future reference if nothing else.