TABLEAU REST API: TABLEAU-API-LIB TUTORIALS

Query your Tableau view data like a boss using Python and Pandas

How to get the Tableau data you need in a format you want

Elliott Stam
Mar 24 · 8 min read
Image for post
Image for post
Photo by Stephen Dawson on Unsplash

Tableau worksheets (views) are the building blocks of all Tableau dashboards. These sheets can really make your data shine, but it can be a chore to extract the underlying data if you need it.

When you manually download the data from a Tableau view using the Tableau Server interface, you’ll get a CSV with every row / column that contributes to the sheet, including any fields sitting in the sheet’s marks card.

The result? Rough column names, unformatted numerical values, and the need to prep the output to make it presentable for other eyes.

An alternative is to ditch the manual data munging in favor of code. With a little Python you can fetch data from a Tableau view, store and process it in a Pandas DataFrame, and output the results to a CSV file or a database. As you’ll see, we can even control the sheet filter and parameter values.

This tutorial walks through using the Python tableau-api-lib package and is part of a series on how to tap Tableau Server like a keg, giving you control over Tableau Server’s REST API.

These tutorials assume you have Python 3 installed already. If you do not have Python 3 yet, this will get you started: guide to install Python.

Setting the Stage

The stars of the show here are Pandas and tableau-api-lib: two Python packages. Pandas gives us a multitude of convenient tools for processing the data, and tableau-api-lib provides customized and flexible control over Tableau Server’s REST API endpoints.

Consolidated code is provided at the end of the article in the form of a GitHub gist and is meant to be a starter template for your convenience if you adapt these methods into your own workflows.

Step 1: make sure you have tableau-api-lib installed

pip install --upgrade tableau-api-lib

New to this Python stuff? Don’t sweat it, you’ll catch on quick. Follow this getting started tutorial. That tutorial walks you through getting connected to Tableau Server using tableau-api-lib.

Step 2: tap into your Tableau Server environment

from tableau_api_lib import TableauServerConnection
from tableau_api_lib.utils.querying import get_views_dataframe, get_view_data_dataframe

tableau_server_config = {
'my_env': {
'server': 'https://YourTableauServer.com',
'api_version': '<YOUR_API_VERSION>',
'username': '<YOUR_USERNAME>',
'password': '<YOUR_PASSWORD>',
'site_name': '<YOUR_SITE_NAME>',
'site_url': '<YOUR_SITE_CONTENT_URL>'
}
}
conn = TableauServerConnection(tableau_server_config, env='my_env')
conn.sign_in()

Fun fact: you can also use personal access tokens, assuming you are on Tableau Server 2019.4 or newer. If you’re all about the access tokens, check out my article for details on how to use them.

Step 3: get the ID of the view housing the data you’re chasing

views_df = get_views_dataframe(conn)

The function returns a Pandas DataFrame that will look similar to this:

Image for post
Image for post

Equipped with these view details, we can determine the ID of the view whose data we want to query.

Step 4: (basic) query the view’s data

view_data_df = get_view_data_dataframe(conn, view_id='<YOUR_VIEW_ID>')

Using Tableau’s well-known Superstore data, I built a basic sheet we’ll use throughout this tutorial.

Here is what my sheet looks like in Tableau:

Image for post
Image for post
On the left we see the marks card, at the top we see the rows / columns shelves, and on the right we see filters

Take a good look at the visual itself. When we query the view data, we are going to get a table that represents everything we see in the visual. We will also get values such as ‘Quantity’, ‘Discount’, and ‘Category’ which we see in the marks card but do not contribute to any visual aspect of the sheet.

Let’s take a look at the view_data_df we defined when querying the view:

Image for post
Image for post
Any field that is present in your view will appear in the data you query from the view

Before we move forward with processing this table using Pandas, let’s walk through the steps to pre-filter the data you download.

Step 5: (advanced) set filter and parameter values for your query

In my sheet, I created a parameter whose value determines whether the ‘Superstore Sales | Dynamic Columns’ is populated with the order year or with customer segments. The entire visual changes depending on the value of this parameter.

Let’s make things interesting and query data for the filter and parameter values listed below. Note from the earlier screenshot of the visual that we have filters for ‘Region’ and ‘Ship Mode’, but these are not elements of the visuals and therefore are not included in the downloaded table. If we want to filter these values, we’re going to have to do specify that while downloading the data. Let’s get it done!

Region: we will keep ‘East’ and ‘West’.

Ship Mode: we will keep ‘First Class’ and ‘Standard Class’

Superstore Sales | Column Parameter: we will set this to ‘Segment’ instead of ‘Order Year’, which will modify the field referenced in the ‘Superstore Sales | Dynamic Columns’ calculated field.

Now let’s define some URL parameters that will impact the view data our REST API calls query from the Tableau view. The idea is to modify the URL as seen in Tableau Server’s documentation of the ‘Query View Data’ endpoint:

Image for post
Image for post
This image is taken directly from Tableau Server’s REST API documentation

For more details on what’s going on here with our filtering, check out Tableau’s documentation on implementing filters in REST API calls.

Here are the filter and parameter definitions I use to define my rules for ‘Region’, ‘Ship Mode’, and ‘Superstore Sales | Column Parameter’:

custom_params = {
'region': 'vf_Region=East,West',
'ship_mode': 'vf_Ship Mode=First Class,Standard Class',
'param': 'vf_Superstore Sales | Column Parameter=Segment'
}

Now one thing I’ll share with you so you don’t hit your head against a wall for hours troubleshooting is this: the URL text will choke on the spaces and the ‘|’ pipe character seen in my values defined above. To save everyone a nasty migraine, let’s clean the contents of our custom_params dict.

def replace_special_characters(text):
text = text.replace(' ', '%20')
text = text.replace('|', '%7C')
return text
def get_encoded_params(param_dict):
if isinstance(param_dict, dict):
encoded_dict = {}
for key in param_dict.keys():
encoded_dict[key] = replace_special_characters(str(param_dict[key]))
return encoded_dict
else:
raise Exception(f"This function expects a dict but received a {type(param_dict)} instead.")

Okay, with those two functions defined we can build a URL-friendly custom parameter dict:

param_dict = get_encoded_params(custom_params)

My output looks like this:

Image for post
Image for post
The keys in the dict don’t really matter; it’s all about the values

Note that my replace_special_characters() function only replaces white space and the pipe character. If you have dirtier text values, please add additional logic to replace your characters. Here’s a good resource for URL ASCII encoding.

Step 6: (advanced) execute the query using your custom filters

view_data_df = get_view_data_dataframe(
conn,
view_id='YOUR_VIEW_ID',
parameter_dict=param_dict)

My resulting view_data_df now looks like this:

Image for post
Image for post

You might notice that my query successfully altered the ‘Superstore Sales | Dynamic Columns’ value to reflect customer segments instead of the order year.

Step 7: (optional) supplement the data with new calculations

Let’s calculate a profit ratio, which divides our profits by sales. To do this successfully, I’m going to convert both columns to numerical values rather than their string representation. When Tableau returned our view data, it did so with ‘Profit’ and ‘Sales’ represented as strings of text.

modified_view_data_df = view_data_df.copy()currency_columns = ['Sales', 'Profit']modified_view_data_df[currency_columns] = modified_view_data_df[currency_columns].\
apply(lambda x: x.str.replace('$', '')).\
apply(lambda x: x.str.replace(',', '')).\
astype(float)

Now that our modified_view_data_df has ‘Profit’ and ‘Sales’ stored as numerical columns, we can compute the profit ratio:

modified_view_data_df['profit_ratio'] = modified_view_data_df['Profit'] / modified_view_data_df['Sales']

And here are our results:

Image for post
Image for post

Step 8: tidy things up by renaming columns

new_column_names = [
'Sub-Category', 'Segment', 'Category', 'Discount', 'Profit', 'Quantity', 'Sales', 'Profit Ratio']
modified_view_data_df.columns = new_column_names

Here are my results:

Image for post
Image for post

Step 9: output your data to a CSV file

modified_view_data_df.to_csv('your_view_data.csv', index=False)

Wrapping it up

Hopefully this tutorial has helped you automate one tedious aspects of the Tableau ecosystem out of your life!

If you have any questions about Tableau Server automation using the REST API, reach out.

Consolidated code

Click here to navigate to the GitHub gist.

Devyx

Delivering insights into Tableau Server automation and data…

Elliott Stam

Written by

Authoring technical tutorials and building modern analytics solutions. Occasionally seen falling down mountains with a snowboard strapped to my feet.

Devyx

Devyx

Delivering insights into Tableau Server automation and data visualization.

Elliott Stam

Written by

Authoring technical tutorials and building modern analytics solutions. Occasionally seen falling down mountains with a snowboard strapped to my feet.

Devyx

Devyx

Delivering insights into Tableau Server automation and data visualization.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store