Have you heard of Airtable? We’ve been using it at Operation Fistula for several months now and are in love with it. Airtable is a collaborative online spreadsheet tool, that allows people who don’t normally work with data to create simple data bases that are easy to make, maintain and navigate. We now use it for various purposes such as:
- Planning out apps before they are put into CommCare
- Creating task lists
- Tracking time on tasks
- Submitting expenses
- Keeping track of dashboard request
- Mapping our filing system
Airtable is a fast and flexible way to create tables to keep track of anything. On your mobile device, your records are presented as tap-friendly cards. On the web, you can edit your records using a simple spreadsheet interface. Our Airtable Connector delivers metadata information based on established standards that allow Tableau to identify data fields as text, numerical, location, date/time data, and more, to help BI tools generate meaningful charts and reports. Utilize server-side functionality and intelligent row-scanning to. Integrate Everything with SSIS: Cloud Apps, Databases, Data Warehouses, & More. Leverage SQL Server to do more with the most comprehensive and advanced set of SQL Server Integration Services components. Airtable works like a spreadsheet but gives you the power of a database to organize anything. Welcome to Airtable Templates, where you can quickly get started using Airtable by selecting the template that was built just for you.
These are just a few examples, and you will have to try it yourself to really understand the value that Airtable adds and how flexible of a tool it is. This post explains a little more what we do with our Airtable data.
Getting data out of Airtable
Collecting data is super easy in Airtable, and much of that information is happy to continue to live there as well. But at Operation Fistula we like to visualise our data in Tableau to make sure that we are getting the most use from it.
Manual downloads from Airtable are easy, but there is currently no way of connecting it directly to Tableau for a live connection. There are a few attempts at a Web Data Connector floating about, but none of these work at the moment and development of a WDC will be difficult until Airtable releases a metdata API.
Airtable does however have very extensive and clear API documentation, and I use Alteryx to extract data and then send it straight to our Tableau Online environment, so that the rest of the team can use the data they entered in Airtable to build visualisations that can help them with their work.
Airtable And Tableau
From Airtable to Alteryx
Using the macro
I created a macro, which you can find in the Alteryx gallery. The interface allows you to enter the API for the table you would like to access and your unique API key. You can find both of these in your Airtable API documentation.
The table reference is outlined in yellow in the image below. The API is outlined in orange. In order to view this, make sure to tick the box in the top right corner, which will reveal your key.
How it works
The outer shell macro serves the sole purpose of reading in those two bits of information, then sending them through the iterative macro that is nested inside of it.
This iterative macro is needed because because Airtable only retrieves 20 records at a time, so an offset is required for pagination (Thanks to Peter, who taught me how to do this part). So the first part of the macro sends the right information through the download tool and assesses if another iteration is needed, and what information should be passed back for the next loop.
Airtable Tableau Software
The second part of the iterative macro parses out the json into an orderly table. I have used this macro with a number of tables now and have so far not run into any issues, but it is possible that the setup of the workflow encounters problems with certain field types that we haven’t used in any of the tables that I am accessing. If you come across any issues please let me know. I will endeavour to keep the macro updated.
Airtable Tableau Connection
From Alteryx to Tableau
Once through the macro, I typically still have a few steps of processing that I do in Alteryx. Often times this is limited to selecting only those fields that I want to import, but if you have multi-select fields in your table these might need to be split out to be used in analysis.
Airtable Vs Tableau
I then use the ‘Publish to Tableau Server‘ tool in order to set up a new data source on our Tableau Online environment. As we don’t have a way of scheduling Alteryx workflows at the moment I have all of my Airtables within one app, so that I only need to run the app to refresh all of my Airtable data on Tableau Online.