On the first day of my first professional internship, I attended a Tableau Conference in London. I had no prior experience with Tableau, but my employer figured it would be a great way to learn about the tool I would be using that summer. I was instantly hooked on the software after watching people tell stories with data via intricate dashboards. Many of the views I saw that day looked more like art than dashboards. I attended a few sessions there and I learned the basics. Anyone who has used Tableau knows it is intuitive and easy to pick up. It wasn’t until I needed to use Tableau to automate reporting with branding guidelines in my corporate job that I learned to format well. Formatting in Tableau has a learning curve, but a few tips from YouTube and forums go a long way. I am always happy to share tricks, so if you have Tableau questions please reach out.
I use SQL and Tableau daily to analyze data at a financial institution. I wanted to leverage these skills to learn more about DeFi and NFTs, as well as use this as an opportunity to be more creative with Tableau. Building in Tableau off of spreadsheets is not the best experience, particularly if you need to edit or update your data. I began searching for tools to solve my need and I quickly came across Flipside Crypto’s free SQL editor and API functionality.
Flipside Crypto
Flipside Crypto allows you to use SQL to query data from certain blockchains. They have several schemas for popular blockchains such as Ethereum, Terra, and Polygon. They also have a few schemas for blue-chip DeFi applications like Aave, Compound, and Uniswap. I found the tables within each of the schemas easy to understand, and when I did have questions about the tables there are resources to help. What I really found interesting about Flipside’s tool was the ability to generate a unique API endpoint from your query results. From conversations with colleagues, I knew there was a way to connect Tableau to web data, but I had no prior experience working with data in this format or from these types of sources. Luckily, after a few hours of googling and trial and error, I was able to successfully connect Tableau Desktop to my Flipside query results via the API.
Here is how to set it up for yourself.
Disclaimer: This was my first time working with JavaScript and HTML. I cannot explain what every line of the code does. The code came from this tutorial, and I simply repurposed it to work with the Flipside API. Please refer to the tutorial documentation for more detailed information about the code and setting up the Web Data Connectors.
Web Data Connectors
A Web Data Connector (WDC) allows you to connect to a web data source from Tableau Desktop. They are HTML pages with JavaScript code that does three things:
- Connects to web data
- Coverts the data to a JSON format
- Passes that data to Tableau
There are five steps in the process of setting up your first connector. Once you have the first WDC working correctly, it is simple to repurpose your code to point to a different endpoint. You will need a specific .html and .js file for each WDC.
- Install/confirm prerequisite packages
- Get the Web Data Connector SDK
- Run the simulator
- Build your HTML file
- Build your JavaScript file
Instructions
1 — Prerequisites
Install the following packages. Refer to their documentation on how to do so.
2 — Get the WDC SDK
Start by opening a terminal. Make sure you are in the directory where you what to download the WDC software development kit (SDK).
Step 1: Run the following command:
git clone https://github.com/tableau/webdataconnector.gitStep 2: Change the directory by running the following command:
cd webdataconnector3 — Run the simulator
Step 1: Run the following command to install dependencies:
npm install — productionStep 2: Start the test web server by running the following code:
npm startStep 3: Open a browser and navigate to this URL:
http://localhost:8888/Simulator/index.htmlYou should now see the Web Data Connector Simulator
At this point, you are able to try the sample WDCs. If you would like to do so please refer to this documentation.
Building your files
I used Visual Studio Code to build my HTML and JavaScript files. Other code editors will work too. The important thing to note here is that both files need to be saved in the webdataconnector repository. You installed this directory in the ‘Get the WDC SDK’ step above. You can create a new folder in the repository for your files if you want.
4 — The HTML file
The HTML code here imports some files for styling and formatting, imports the JavaScript file you’ll create in the next step, and builds a button for you to load the data. The code for both files is too long to include here. For that reason, I will only include the excerpts that need to be changed to repurpose this. Refer to this link for my sample files. There are only two things here you need to change in your own file:
Step 1: Change the title to something describing your data (change “Token Price Feed”) — you will need this title in the .js file, too.
<title>Token Price Feed</title>Step 2: Change the source to your JavaScript file (change “sample.js” )
<script src=”sample.js” type=”text/javascript”></script>5 — The JavaScript file
While the HTML code is what the simulator interacts with, the JavaScript file is what gets the data from the endpoint. The JavaScript file here pulls data from one of my Flipside queries for hourly token price data. Again, the code is too long to post here so I will only include what needs to be changed. To repurpose this file to work with your API endpoint change the following:
Step 1: Edit the ‘cols’ variable to match the columns you want to return from your query (change id names and data types)
- Make sure the data type is correct for your columns
- See here for possible data types
var cols = [{id: “symbol”,dataType: tableau.dataTypeEnum.string}, {id: “hour”,dataType: tableau.dataTypeEnum.string}, {id: “price”,dataType: tableau.dataTypeEnum.float}];
Step 2: Edit the ‘tableSchema’ variable to something specific to your data
- This is optional, but I recommend this if you’ll be setting up more than one connection
var tableSchema = {id: “tokenFeed”,alias: “defi tokens”,columns: cols}
Step 3: Edit the API variable to your link
var apiCall = “https://api.flipsidecrypto.com/api/v2/queries/77a363ca-4096-4414-82e3-9c5c7f7e6b56/data/latest";Step 4: Add your columns to the ‘tableData.push’ step (these should match your names in step 2)
tableData.push({“symbol”: response[i].symbol,“hour”: response[i].hour,“price”: response[i].price});
Step 5: Make sure your connection name matches the title in step 1 of building the HTML file
tableau.connectionName = “Token Price Feed”Loading your data
Your code should be good to go at this point and we can head to the simulator we loaded in our browser earlier. If you closed out of the browser you can easily load it again with the steps below. These are the same steps you will follow any time you want to test a new connector or load your data to Tableau Desktop.
Step 1: Open terminal
Step 2: Change your directory to the WDC — run the following:
cd webdataconnectorStep 3: Start the simulator — run the following:
npm startStep 4: Navigate to the link:
http://localhost:8888/Simulator/index.htmlOnce the simulator is up and running, follow these steps to load your data:
- Edit the WDC URL field to match your HTML file path
- Click the ‘Start Interactive Phase’ button
- Click the ‘Get Data’ button
- Click the ‘Fetch Table Data’ button to finally download and display your data
Using your Connector in Tableau Desktop
- On the start page in Tableau Desktop click on ‘Web Data Connector’
- Enter the URL of your WDC & press Enter
- Click the ‘Get Data’ button
- That is it! You should now be connected to your data in Tableau Desktop
Other notes/tips
If you want to update your data you will need to run the simulator and go through the four steps of loading your data. Make sure your query is refreshed before you run the simulator. Additionally, if you are going to publish these on Tableau Public, your data will be embedded into your views as an extract. This means it will not update in real-time/when you refresh your query. I am exploring ways to make the update process easier, but right now it takes me about 60 seconds per WDC to refresh the data, and another 60 seconds to re-save the view to Tableau Public. If you an idea on how to help me with this please let me know. There are also more complicated things you can do with the WDCs like working with multiple tables, filtering and adding authentication. The Tableau documentation walks you through how to set these up if you are curious.
If you have any questions on how to set this up for yourself or have any feedback on this tutorial please do not hesitate to reach out — twitter @0xTableau
My Tableau Public page where I will be posting what I make with this.
