As Tableau licensing usually is out of reach for smaller companies, there is a way to have free Tableau implementation with the little trick I describe in this post.

Please note that this concept might be understood by Tableau as EULA infringement. You must read all the license agreements for the software before implementing.

How it’s done?

Method described here is fairly simple, however requires basic programming understanding and ETL/Data processing background to run smoothly.

I use Tableau Public to create a .twbx file, refresh it using the Python script and then open it with Tableau Reader. Simple and clean method for free Tableau visualizations.

Required software/accounts

To connect all the pieces in one working mechanism you must have:

  • Tableau Public account
  • Tableau Reader Software
  • Windows server (this is optional – you can ran it locally but remote machine makes it more convenient)
  • Python 2.7+ installed
  • Tableau Extract API
  • MS Access Database (different data source can be used here but this one is most versatile)

All those components are free except for MS Access Database. However, most companies use Office Package so I assume that you have it already. This can be replaced by free alternative as MySQL though..

Tableau Public

Tableau Public is a free edition of the famous Tableau Online which is basically free Tableau Server in the cloud. It allows you to publish you own visualizations based on selected data connectors. You could call it a perfect solution for small business but there’s a catch. As name implies, data presented there is shown to the public. However, uploaded viz can be downloaded as .twbx file (ha!). Just go to the public.tableau.com, create account and download software.

Tableau Reader

Tableau Reader is a lite version of free Tableau Desktop designed for read-only operations on .twbx files. This should be used by end-users like Sales representatives, who should only interact with the existing dashboards and not create ones.

Python 2.7+

Python is a widely recognized programming language which is getting more and more attention lately due to it’s flexibility and ability to perform on different tasks. It’s relatively easy to understand and write so we will use it in our example. Python interpreter must be installed on the machine to run .py scripts. It can be downloaded here https://www.python.org/downloads/windows/. Note that my script uses python 2.x not 3.x. Although, it should work properly in 3.x there are few syntax differences between second and third Python iterations so you might need to rework the .py file on your own.

Tableau Extract API

You can easily download free Tableau Extract API package that allows you to perform number of extract operations on Tableau files. One of them is fetching data from extract which I will be using in this example. Package can be downloaded from here: https://www.tableau.com/data-extract-api

Joining pieces together

Installing Python

Let’s start with the most important and most tricky part – installing Python. Run the downloaded installer and follow the steps . Make sure that path where you install Python is relatively simple – it will make your life easier later on. If you are asked to alter PATH definition then definitely do it. In theory everything should work out of the box (but usually doesn’t). To check if python is working properly bring up command line and type:

cd c:\your_path_to_python\Python(yourversion)\

Free Tableau with offline refresh

when in the installation folder type python.

Free Tableau with offline refresh

If you see the Python interpreter then you can safely skip to the next point.

However, in most of the cases instead of interpreter you will get python is not recognized as an internal or external command error. It means that python command cannot be mapped to any program. To fix it,open you start Menu, right click on the Computer and select Properties

Free Tableau with offline refresh

Then go to Advanced System Settings and click Environment Variables at the Bottom

Free Tableau with offline refresh

Find PATH record on the list in the bottom box and just add your Python installation location after semicolon

Free Tableau with offline refresh

After that, you should be able to enter interpreter using python command as above. If it still doesn’t work, try typing py instead of python

Installing Tableau Extract API

Unzip downloaded package and copy it’s content (with the whole main folder) to c:\yourPythonInstallation\modules\. If there’s no modules folder – create it. Now bring up the command line and navigate to the folder with dataextract api and type py setup.py install to install this package for Python to use

Free Tableau with offline refresh

Installing Python ODBC connector

To refresh our data we will need to install ODBC connector that Python could use to access and fetch data in extract. This is nothing new to Tableau as it mostly relies on ODBC connectors for main data source as MS Excel or MS Access. There is a chance that proper connector is already installed by we need to make sure. Bring up the command line, navigate to python folder and type:

pip.exe install --upgrade pyodbc
py
import pyodbc
[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]

Free Tableau with offline refresh

if there’s proper driver on the list then no action is required. Otherwise you have to install ODBC driver to access data. Follow instructions here [click]

 

Create Data source for Viz

I’ve created super simple data source for my viz based on sample MS Access table and saved the whole database as testu1.mdb.

Free Tableau with offline refresh

Note that file format (mdb/accdb) defines which odbc connector you would need to access the data. Please refer to the previous point where we installed pyodbc to make sure your connectivity is setup properly. I’ve made random viz on my newly created datasource and I am ready to start refreshing.

Running the script

Phew! it seems that we have everything setup to start the real thing – running the script! Credits for the original code goes to Jason Lopez. My modifications included Tableau path changes and one small loop which scans for the .tde file so you don’t have hard code the name and couple more readability changes.

#-------------------------------------------------------------------------------
# Purpose:      refresh Tableau packaged workbook file's underlying dataextract file from SQL
# Author:      jasonlopez01
# Modified by: mlazecki (meowbi.com)
# Created:     05/08/2016
# Modified:    28/02/2017
# Note:       could also load in from csv, Excel, etc.
#-------------------------------------------------------------------------------

import pyodbc, os, shutil, zipfile, sys
import dataextract as tde
import datetime, time
import glob

t0 = time.time()

#helper dictionary for tde API data types
tdeTypes = {
    "<type 'int'>": 7, #integer
    "<type 'float'>": 10, #double
    "<type 'bool'>": 11, #boolean
    "<type 'datetime.date'>": 12, #date
    "<type 'datetime.datetime'>":13, #datetime
    "<type 'str'>" : 15, #char string
    "<type 'unicode'>":16 #unicode string
}

def add_row(r_ob, DType,col_i,v): #row, data type, col index, value
    if isinstance(v,int):
        r_ob.setInteger(col_i,v)
    elif isinstance(v,float):
        r_ob.setDouble(col_i,v)
    elif isinstance(v,datetime.datetime):
        r_ob.setDateTime(col_i,v.year, v.month, v.day, v.hour, v.minute, v.second, 0) #zero is milliseconds
    elif isinstance(v,datetime.date):
        r_ob.setDate(col_i,v.year, v.month, v.day)
    elif isinstance(v,unicode):
        r_ob.setString(col_i,v)
    elif isinstance(v,str):
        r_ob.setCharString(col_i,v)
    else:
        print("data type in add row function issue!" + str(type(v)))

#Change variables below to math correct path, filenames, etc.
path = 'C:/dev/'
dashboard_file = 'refresh_test.twbx'
dashboard_file_twb = 'refresh_test.twb'
temp_ext = 'tableau_files' #folder to unzip to

sqlQuery="SELECT [Sales_table].[Date_field] AS [Date_field],  [Sales_table].[ID] AS [ID],  [Sales_table].[Sales_value] AS [Sales_value] FROM [Sales_table]"

updated_dashboard = dashboard_file[:-5] + str(datetime.datetime.fromtimestamp(time.time()).strftime('%Y%m%d%H%M%S')) + '.twbx'


#pull data from warehouse
print("connect to mdb")

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\dev\testu1.mdb;'
    )
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()

crsr.execute(sqlQuery)

#unzip Packaged Workbook file to new folder
zip = zipfile.ZipFile(path + dashboard_file)
zip.extractall(path + temp_ext)
zip.close()


#get tde filename
print("looking for tde in: "+path + temp_ext+"/Data/"+dashboard_file_twb +' Files/')
tde_files = glob.glob(path + temp_ext+"/Data/"+dashboard_file_twb +' Files/*.tde')
tdeName_temp = [os.path.basename(x) for x in tde_files]
tdeName= tdeName_temp[0]


#remove existing extract file
os.remove(path + temp_ext + '/Data/' +dashboard_file_twb +' Files/' + tdeName) #always in /Data/Datasources/extract_name.tde

#create new tableau extract w/ orignal name
print("create tableau extract")
os.chdir(path + temp_ext + '/Data/' +dashboard_file_twb +' Files/')
tdefile = tde.Extract(tdeName) #create tde file
tableDef = tde.TableDefinition() #create a new table def


for column in crsr.description:
    tableDef.addColumn(column[0],tdeTypes[str(column[1])])

tdetable = tdefile.addTable("Extract",tableDef) #has to be Extract for some reason

print("adding rows")
for row in crsr.fetchall():
    i = 0
    newrow = tde.Row(tableDef)
    while i < len(row):
        val = row[i]
        t = type(row[i])
        if val == 'None' or val is None:
            newrow.setNull(i)
        else:
            add_row(newrow,t,i,val)
        i += 1
    tdetable.insert(newrow) #insert row into TDE table
    newrow.close()

#close SQL connection and extract file
tdefile.close()
cnxn.close()

#rezip all files and folders, maintains folder structure in zipped workbook
def zip_dir(zipname, dir_to_zip):
    dir_to_zip_len = len(dir_to_zip.rstrip(os.sep)) + 1
    with zipfile.ZipFile(zipname, mode='w', compression=zipfile.ZIP_DEFLATED) as zf:
        for dirname, subdirs, files in os.walk(dir_to_zip):
            for filename in files:
                path = os.path.join(dirname, filename)
                entry = path[dir_to_zip_len:]
                zf.write(path, entry)

print("repackaging Tableau workbook")
zip_dir(path + updated_dashboard, path + temp_ext)
os.chdir(path)
shutil.rmtree(path + temp_ext)
print(round((time.time() - t0)/60,2), ' min process time')

you can also download the whole file here [icon name=”file-text” class=”” unprefixed_class=””]

There are few things going on here.

First of all, file paths are added to the variables.

Make sure you folder / file structure is properly described and defined:

path = 'C:/dev/'
dashboard_file = 'refresh_test.twbx'
dashboard_file_twb = 'refresh_test.twb'

Secondly, the query is being executed:

sqlQuery="SELECT [Sales_table].[Date_field] AS [Date_field],  [Sales_table].[ID] AS [ID],  [Sales_table].[Sales_value] AS [Sales_value] FROM [Sales_table]"

This query can be accessed via Tableau Public in edit Datasource tab > Data > Convert to Custom SQL

Free Tableau with offline refresh

And lastly, there’s a connection string that will fetch new data:

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\dev\testu1.mdb;'
    )

You need to pay attention here. Driver variable needs to be setup carefully depending on the data source. This is the same name you should see in the pyodbc.drivers() function print.

Make sure you copy the script to c:\yourpythoninstallation\scripts\ folder

Let’s run it!

Bring up the command prompt and navigate to python\scripts folder and type

 py twbx_refresh_meowbi_com.py

where twbx_refresh_meowbi_com.py is the filename for the script above. If everything is ok, you should see newly created .twbx file with current timestamp and refreshed data. And this is how free Tableau data refresh works!

Free Tableau with offline refresh

Next actions

This is just the framework but with proper scheduling and .twbx files distribution you can have free Tableau access. This method has it’s limitations. You have to remember to remove initial viz from Public as soon as possible (or design viz on empty database) and you are limited only to few connectors. On top of that, freshness of your data relies on how often you run the script. However, it’s a great start to convince supervisor to invest from free Tableau workaround to full-blown Tableau solution.