Tutorial: Using Apitite to Power your Geckoboard (30 mins)

No comments
Figure 1. A line chart in Geckoboard


In this tutorial, I will show you how to hook your database directly into Geckoboards using Apitite to create custom dashboards. It doesn't matter if your database is relational or NoSQL, Apitite makes it possible! (Time: ~30 mins)

Geckoboard - Build Powerful Custom Business Dashboards

I recently stumbled across Geckoboard, a powerful tool for creating custom dashboards for your business. In a nutshell, you build your dashboard by adding widgets that "transform your raw data into simple visualizations". Visualizations include gauges, meters, leaderboards, and charts of all kinds. I'm blown away by Geckboard because of its flexibility and how sleek it looks. While working at IBM Research I spent two years developing analytics dashboards, so I understand the importance of having actionable insights at your finger tips.

A really powerful aspect about Geckoboard is how you can integrate existing services your business uses, such as Twitter, Google Analytics, and Hubspot, to create useful dashboard visualizations. If you browse Geckoboard's integrations page you will see there are more integration points than you can shake a stick at.

Visualizing Custom Data with Geckoboard

Geckoboard can also visualize custom analytics that you create as well. All you need to do is point your widget at a URL that returns some JSON or XML and you're ready to rock. However, suppose you want to create a custom visualization from data that is stored in your database. The natural answer would be to create a web app that accesses your database and serves up the data as an API endpoint. This approach requires coding up a web app, deploying it on a server, and maintaining this server.

Some databases also offer a REST interface to query them over HTTP. The issue with this approach is that your database layout must match the Geckoboard widget JSON structure exactly. Figure 2 shows the Geckoboard line chart documentation along with the JSON structure it expects.

Figure 2. 

It is impossible for a traditional relational database (e.g. MySQL and SQL Server) to output JSON like this because relational database tables are organized as rows and columns and JSON objects very often contain nested objects and arrays.

Using Apitite to Create Custom Analytics

Great news! You can use Apitite to create your custom analytics and with the announcement of the Apitite JavaScript Transformation Code feature you can translate the data returned by an endpoint into any JSON format. This means you can create an API endpoint that hooks your database directly into Geckoboard and without having to build an application to translate the data! The best part is that it doesn't matter if your database is relational (e.g. MySQL or PostgreSQL) or NoSQL (e.g. MongoDB).


In an older post, I created a Toronto Raptors API from a dataset of historical player totals. I will be using the same dataset and API for this tutorial to create a line chart that plots the average total player points per season. The dataset is stored in a MySQL database, which means out of the box the data returned from an endpoint will not be in the JSON format Geckoboard expects. Figure 3 shows some sample data from this dataset. As you can see, the data is stored in the standard relational format of rows and columns.

Figure 3. A sample of the Toronto Raptors player total dataset. 

Step 1 - Create a New Custom SQL Endpoint

To get started, I created a new Custom SQL Endpoint for my Toronto Raptors API called Average Player Points per Season and added some descriptive information about the endpoint. A Custom SQL Endpoint allows you to interact with your data source (e.g. select, insert, update, and delete data) by providing a simple SQL query.

Figure 4. Creating a new endpoint for my Geckoboard visualization
Figure 3 displays how I created my Custom SQL endpoint. The important thing to note is the URL Root: apitite.net/api/raptors/avg-player-pts. This is the URL that Geckboboard will use to poll my API. You can view the actual endpoint here and play around with it.

Step 2 - Create Custom SQL Query

Figure 5. SQL query used to calculate average payer totals per year
I want my Geckoboard line chart to plot the average player totals per season. So I create a SQL query (shown in Figure 5) that computes this and enter it in my endpoint's SQL Query text field.

Figure 2 above shows the JSON layout the Geckoboard line chart expects. However, if you access your new endpoint at this time, the data will be flat because it is coming from a relational table, as shown in Figure 6. All the data is there, but it's frustratingly not in the correct JSON format. Apitite JavaScript Transformation Code to the rescue!

Figure 6. The data returned from my endpoint without data transformation

Step 3 - Transform you Data with the Apitite JavaScript Transformation Code

The JavaScript Transformation Code feature allows you to have Apitite run a piece of JavaScript on the data returned from a endpoint, which is extremely handy for performing transformations on data (e.g. rounding values) or translating it into a particular JSON structure. To use this feature, go the Manage Endpoint page for your Custom SQL endpoint, scroll to the bottom, and twist open the JavaScript Transformation Code box. There you will find a text field with a JavaScript stub function and instructions on how you can build your own transformation code. The parameter passed to the stub function is the JSON data returned from your endpoint.

Figure 7. Apitite JavaScript Sandbox code used to translate rows and columns into JSON Geckoboard can use.

Figure 7 shows the function I created to transform the data in Figure 6 to JSON that Geckoboard can recognize. Since the Geckoboard line chart expects Strings as labels, I had to convert the year returned from my endpoint into a string (see line 7 in Figure 7). Also, I wanted to make the average point stats nice and round, so I used the Math.round(...) function on line 8 of Figure 7. With the Apitite JavaScript Sandbox I was able to make these transformations without having to modify my underlying database. Now, if you look at Figure 8 below, the data returned from our endpoint is all ready for Geckoboard! Click here to check out the endpoint for yourself.
Figure 8. Data output from my endpoint after data transformation

Step 4 - Hooking Geckoboard to Apitite

We're almost done! Next, all I needed to do was log into Geckoboard and add a Line Chart widget.

Figure 9. Connecting my Geckoboard Line Chart to my Apitite endpoint
Figure 9 shows the options I entered for my line chart. The most important step is specifying the URL for your endpoint in the URL data feed and setting the line chart to Polling. This means that Geckoboard will periodically poll your endpoint for data to populate your line chart. Another thing to note is that the endpoint URL you enter must have /json appended to it because it will be a programatic request. For example, the URL I specified is https://www.apitite.net/api/raptors/avg-player-pts/json. Try it out! It works.

Step 5 - That's it! Kick back and enjoy your new visualization!

Navigate back to your dashboard and check out your new visualization! Figure 10 shows the finished product. Before you start commenting on why the Raptors did not score anything in the year 2014, it turns out that my dataset has an error in it. The season_end column is really the season_start column and 2014-2015 is only half over, which explains why only an average of 224 points have been scored.

Figure 10. The finished product! Go Raps Go!


Geckoboard is great for creating custom dashboards, but you need to put in some legwork to feed your Geckoboard custom analytics from your database, especially if your data is in a relational database. However, with Apitite you can create an API that feeds Geckoboards directly, regardless of whether you are running a traditional relational or a NoSQL database.

No comments :

Post a Comment