The image is taken from the Snowflake Website
It’s Dashboard Week Day 3 and again, Andy has shoved us right out of our comfort zone. He has tasked us to use Alteryx to download data via an API from World Bank, clean and upload that data to Snowflake as a table (which we have no experience doing) and visualize the data within tableau. I was pretty excited about this challenge as Snowflake is something I’ve been planning to delve into for a few weeks now but we knew it was going to be tough.
Firstly, the API documentation on the World Bank website is extensive – it wasn’t easy to find exactly what I was looking for. It also took me a while to understand what the codes at the end of the API meant but I found a useful excel file that told me pretty much all I needed to know under the “Data and Resources” tab here.
An Example API looked like the below for Male Life Expectancy at Birth.
The 5 part code towards the end is the code for this specific metric, so in order to download multiple metrics, I would need multiple API calls.
I used the excel document to choose the metrics I was going to use (all to do with World Male Populations. My first step in Alteryx was to just use one of the population metrics to create a standard macro that cleans up the data. This macro could then be used on each of my other metrics quickly and easily.
My workflow image for the macro is seen below, as is my final workflow:
I used 5 metrics (they could have each been brought in within a single macro) but by this point my mind was beginning to fry at the thought of using Snowflake, so I did what came to me first.
Now for the Snowflake part – I encountered a number of issues when trying to configure my Alteryx to Snowflake connection. I’m not going to go into much detail within this blog post since I have a blog coming soon to talk you through a step by step guide to creating a table in Snowflake via Alteryx. I will update this blog with the link to that when it is published.
Finally uploading my table to Snowflake brought about a big cheer from myself within my home office, I could see my created table on my Snowflake platform!
So what I have I done so far? I have downloaded data via an API, created a macro to clean the API so that I could call numerous APIs at once, cleaned that data in Alteryx, created an Alteryx to Snowflake ODBC connection and uploaded my cleansed data to Snowflake to create a table within my Snowflake database… Wow, that was a mouthful, and if you told me I’d be doing that within 3 months of Data School training I would have laughed out loud at the idea – but here I am feeling a bit like a wizard.
Finally... Oh yeah… I have a dashboard to create – another thing to add to the list above! Connecting to Snowflake via Tableau was incredibly easy – you simply connect to Snowflake via the server option in tableau, input your username, password and server as seen below and voila!
My final dashboard intends to be an exploratory dashboard, hence the number of filters. I only had about half an hour at the end to create it so I’m pretty proud of what I managed. I’ve also left the dashboard as I had it at 3pm so I can keep it as a reference to what I managed for today's project.
View it on my tableau public here.