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

  • Set Data Type to Boolean
  • Set Current Value to False

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:

  • Create a New Worksheet
  • Add the Python SQL calculation to Detail
  • Create a Calculated Field called SQL with the ‘SQL‘; this is used as a label.
    • Drag this Calculated Field onto the Text Mark
  • Add a Boolean Value TRUE to Detail Mark by double-clicking in the Marks Card and typing TRUE (this is an example of inline Calculations). This is going to be used in our Dashboard Action!
  • Format the text and colour to suit your dashboard 

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.

  • Go to Dashboard > Actions > Add Action > Change Parameter
  • Give the Action a name – I used Button SQL TRUE
  • In Run action on ensure Select is selected
  • In Source Sheets ensure only the worksheet set up as a button is selected
  • In Target Parameter select Button SQL
  • In Field select TRUE, this is the Boolean added to Detail in the worksheet
  • Click OK
  • Go to Dashboard > Actions > Add Action > Change Parameter
  • Give the Action a name – I used Button SQL FALSE
  • In Run action on ensure Select is selected
  • In Source Sheets ensure only the worksheet set up as a button is NOT selected
  • In Target Parameter select Button SQL
  • In Field select FALSE, this is the Boolean added to Detail in the worksheets
  • Click OK

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:

  • Send an email to the button clicker with the data from the dashboard attached
  • Run an offline process that sends QBR or Customer Usage reports for customers

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/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.