Toan Hoang

Tableau + Python – Going Outside of the Box

Toan Hoang

Aidan Moore reached out to me a few months ago as he wanted to show me something interesting in Tableau, I am always up for seeing something interesting, but what Aidan showed me really got me thinking as to why this is technique is more widely used? why I have not seen this in more companies? as it is pretty powerful, as such, this blog was written by Aidan Moore to share with you, what he shared with me.

Why should you keep reading? Aidan talks about how he leverages the power of Python to go way, way outside of the box and do some very interesting things with Tableau. I personally hope this would spark your imagination.

Introduction

Author: Aidan Moore

Back in the mists of time when face-to-face events was a thing, I attended TCE19 in Berlin.  I had some free time where I was not sure which session to go to and whilst mulling over the options noticed a hoard of people all heading up some stairs. People power is persuasive so I followed and ended up in Rachel Bowes awesome session From ‘Hello World’ to saving the world: Advanced Analytics With Python.

This session got me thinking if could I jump into the Python part of the TabPy process to help me with a couple of business challenges like enabling data download requests without using up Tableau Server resources, or enabling users to request training documentation, offline forms etc whilst in the Tableau Server environment.

The thought sat at the back of my mind for a couple of years until I had reason to give it a try, and in the rest of this blog, I will walk through how I used TabPy to connect to a SQL Server Database and insert rows, so essentially, enabling my Tableau Dashboards to write to a database. Hopefully, this will inspire you and lead to some interesting ideas.

Tableau Writeback

For this example, I am using the sample Superstore Dashboard, open this dashboard and then start by creating a Parameter called Button SQL

Next, we want to create a Calculated Field called Python SQL

SCRIPT_REAL("
scores = []
scores.append(0)

if _arg3[0] == True :
    import pyodbc
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server=<YOUR SERVER NAME>;Database=<YOUR DATABASE>;UID=<YOUR USERNAME>;PWD=<YOUR PASSWORD>;Port=<YOUR PORT>')
    cursor = cnxn.cursor()

    sSQL = 'INSERT INTO [Tableau].[Person] ([Username],[DashboardName]) VALUES(' 
    str1 = ''.join(_arg1)
    sSQL = sSQL + '\'' + str1 + '\''
    sSQL = sSQL + ','
    str2 = ''.join(_arg2)
    sSQL = sSQL + '\'' + str2 + '\''
    sSQL = sSQL + ')'

    cursor.execute (sSQL)
    cnxn.commit()
return scores
 ",
ATTR(USERNAME()), 'Superstore - Overview', ATTR([Button SQL]))

Let me break down what is going on here:

SCRIPT_REAL

I am using SCRIPT_REAL here, it means Tableau is expecting to get a numerical value back from TabPy

scores = []
scores.append(0)

Here I am creating a Python list for the return value and appending 0 to the list so it will not return an empty list – remember Tableau is expecting to get a numerical value back from TabPy

 if _arg3[0] == True :

_arg3 is the third argument I am passing in the line

ATTR(USERNAME()), 'Superstore - Overview', ATTR([Button SQL]))

I only want the process to run when the parameter called Button SQL is set to True.  This ensures it will not fire off the process when the workbook is opened

import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server=<YOUR SERVER NAME>;Database=<YOUR DATABASE>;UID=<YOUR USERNAME>;PWD=<YOUR PASSWORD>;Port=<YOUR PORT>')
cursor = cnxn.cursor()

Here I am importing the pyodbc library and using this to create a cursor connection.  You will need to change the driver, server, database details if you are trying this for yourself

sSQL = 'INSERT INTO [<Schema>].[<table>] ([Username],[DashboardName]) VALUES(' 
str1 = ''.join(_arg1)
sSQL = sSQL + '\'' + str1 + '\''
sSQL = sSQL + ','
str2 = ''.join(_arg2)
sSQL = sSQL + '\'' + str2 + '\''
sSQL = sSQL + ')'

I am now creating a SQL insert statement, you will need to have a corresponding table set up in your database.

There are a couple of important bits in here:

str1 = ''.join(_arg1)

Tableau is passing the arguments as a list but Python wants to see a string here, so I am joining the list item onto a string to keep Python happy

sSQL = sSQL + '\'' + str1 + '\''

Some funny backslashes here – this is an escape character in Python and I need to add it or Python will interpret the single quote as a string terminator. I have to repeat this process for each value in my list of arguments

cursor.execute (sSQL)
cnxn.commit()

This is the bit in Python that executes and commits my SQL insert statement

return scores

Tableau is expecting to get a numeric value back from TabPy so I need to return the scores list I created at the top

ATTR(USERNAME()), 'Superstore - Overview', ATTR([Button SQL]))

This is the list of arguments I am passing to TabPy.  It includes my Username in Tableau, a string for the dashboard I am adding this process to and the Boolean value of the Button SQL parameter

Now that we have created our Calculated Field, we will now build our Dashboard

Dashboard

We will start by creating a worksheet in Tableau that will act as a dashboard button:

You should now see the something like the following:

Now add our worksheet to the Sample Superstore Dashboard:

Now we need to do a little more preparation. In one of the other worksheets in the dashboard, add a Calculated Field called False and add the text FALSE, and click OK

We will now need to add this Calculated Field to Detail Mark for every sheet on the dashboard EXCEPT the SQL Worksheet, for example:

Now we are going to bring it all together using Dashboard Actions.

I need two actions, one to set my Button SQL parameter to TRUE when I click my worksheet button and another to set it to FALSE when I click anywhere else.

Now, when you click the worksheet button it will change the Button SQL parameter to TRUE and run the TabPy process resulting in a line being added to the SQL table. Clicking on any other worksheet will change the Button SQL parameter back to FALSE

Summary

So, I now have a record in a SQL table that I can use to do several different things outside of Tableau, for example:

I know there will be many more ideas of how this process can be used within the Tableau Community and I look forward to hearing them. In my next blog post, I will be looking at how we can automatically send an email directly within the process.

If you want to know more, please feel free to connect with me on LinkedIn: https://www.linkedin.com/in/aidan-moore-36061a11/

Exit mobile version