I’m a huge Iron Man/Tony Stark fan. Admittedly, I’ve been a fan since I was a young comic book collector, but I also love what was done with the movies. I find them as a great source of inspiration for how people could interact with technology and often have the movies playing in the background while I work on other things (if you haven’t had Iron Man 2 playing in the background while you cook, well then you don’t know what you are missing).
I’m especially fond of the visual interfaces that get presented in the movies. For example, in Iron Man 2, Tony has an interface that is shown on his mirror that essentially shows him his health in relation to poisoning that is occurring as a result of the arc reactor in his chest. I recognize there was a fair amount of information in that sentence that sounds like a foreign language, so I’ll share this image:
Obviously, he’s freaked out due to the level of palladium toxicity (83%). However, in the context of that particular movie, it was incredibly important that he monitor his health and the impact of that arc reactor in his chest. The safety and security of the world was tied to his health and served as a major driver of Tony’s decision making throughout the movie.
As a technology company, we offer products, consulting, and managed services. We are maniacal about customer success and as part of that, our Solutions Delivery division is constantly evaluating the health of our consulting engagements (similar to how Tony is monitoring his health).
Our Solutions Delivery division tracks everything from customer satisfaction to the financial model for every engagement. They use Airtable to store this data, and at any point, people can view the health of an engagement based upon the scoring algorithm used. For those that interact with Airtable on a daily basis, the default grid view in Airtable is likely sufficient. Otherwise, the view can get overwhelming.
To help focus and expedite efforts, we created a simple report in Power BI that highlights the engagements that need attention. Although this particular report is simple in nature, Power BI is capable of some impressive visualizations.
Power BI doesn’t have an Airtable connector available at the time of this writing, although a few workarounds do exist. We evaluated the most popular workarounds against a common set of criteria:
- Simplicity: Can connect to data in one minute or less with minimal work. More information about connecting to data sources in Power BI is available at https://docs.microsoft.com/en-us/power-bi/desktop-data-sources.
- Scheduled Refresh Support: Should support Power BI’s scheduled refresh. More information about Power BI’s data refresh is available at https://docs.microsoft.com/en-us/power-bi/refresh-data.
- Automation: Should allow for minimal backend interaction once visualization is built.
Approach 1: Export CSV From Airtable, Store In OneDrive, Import Into Power BI
The benefit of this approach is that Power BI (both the desktop and online versions) directly support working with files stored in OneDrive. After exporting a CSV file from Airtable, simply add it to your OneDrive and with a few clicks, the data is available in Power BI. More information about this approach is available at https://docs.microsoft.com/en-us/power-bi/refresh-csv-file-onedrive.
In our tests, this approach worked as intended and was simple to setup. Power BI supports scheduled refresh of data stored in One Drive. However, this would require a manual export of data out of Airtable, then uploading the new CSV file into OneDrive. Additionally, the exported CSV should be named the same as the original one used when creating the visualization so that it overwrites the file that exists in OneDrive. As a result, this approach did not pass the Automation criteria.
Approach 2: Connect To Airtable Via Web Connector In Power BI
This approach enables Power BI to connect directly to the Airtable API. With this connection type, scheduled refresh is supported and once setup, requires no interaction for the data refresh.
In our tests, this approach took a bit of experimentation to get the authentication correct. If you’re familiar working with third-party API’s, this is relatively straightforward. However, after successfully connecting to the Airtable API, we had to do some manipulation of the query in order to get the data we wanted. In fact, we spent more time formatting our data query than building the actual report. As a result, this approach did not pass the Simplicity criteria.
Recommended Approach: Connect To Airtable Directly With Power BI Via Conduit
This approach takes advantage of Conduit, a lightweight solution for enabling real-time connectivity to data sources and combining multiple data sources into a single source.
In our tests, this approach was simple to setup. After creating a Conduit connector to an Airtable OData feed, we used the Spark connector in Power BI to connect to Conduit. Like the second approach above, this connection type supports scheduled refresh and once setup, requires no interaction for the data refresh. This was the only approach that met all of the evaluation criteria.
It’s important to be able to take advantage of the data that gets collected and consolidate it into a visualization that helps inform decision making. The Airtable + Conduit + Power BI approach provides an enterprise with a powerful and flexible analytics solution so they can focus on the information that matters, such as monitoring the health of their projects and engagements.
Hey, maybe Tony Stark could use Conduit ;)