Using Alteryx to Create Tableau Filled Maps
Why would you want to create your own filled maps in Tableau?
- Mapping plays a fundamental part in your company’s analysis and you want to make it as good as it can be, or
- You’re getting to the end of your nerve with multiple blended connections, or
- You’re a Tableau fanboy like me and just want to be able to do anything
What do you need to do it?
If you don’t have or want to buy/trial Alteryx then take a look at the most popular post ever in the Tableau community from Richard Leeke. I would also like to take this opportunity to extend my thanks to Richard for his continued service in improving Tableau’s mapping capabilities. By studying his solution I was able to better understand what Tableau was up to and ultimately create a solution in Alteryx.
I’ve got Alteryx, tell me more!
So lets go into some theory on how Tableau’s filled maps work. Tableau geocoding is dependent on a Firebird database installed by default into your Tableau program files folder, the exact location is [Installation Directory]/Local/data/GEOCODING.FBD. That database is a pretty big deal, as in it takes up a lot of hard drive space (from a 600MB install it’s 160MB or 25%). For those who hate that Tableau doesn’t come with their favourite boundaries out of the box you can understand why, mapping takes up a LOT of space.
So do I just get straight in there and edit that Firebird db?
Well you could, but that wouldn’t be very good practice. For a start you’d be overriding Tableau’s work, meaning that you’re not going to easily get the defaults back, and you’re going to have to do it on every single machine which will open your workbook including your server. So if you’ve got the time and the keys to say Tableau Public it might just be the solution for you. Otherwise let’s look at a better way of getting started. And anyway, should you upgrade Tableau Desktop or Server you will have to do it all over again.
A better method is to replicate how Tableau Desktop actually works when you import custom geocoding. When you hit Map -> Geocoding -> Import Custom Geocoding and point Tableau at your CSVs what Tableau does is replicate this out-the-box GEOCODING database and its tds connection files to a ‘Local Data’ folder in your ‘My Tableau Repository’ and inject your custom geocoding into it. The presence of the ‘Local Data’ folder is what tells Tableau desktop to overwrite the out-the-box geocoding and use your custom data instead.
It’s this method that we’re going to recreate using Alteryx. Only instead of replicating the out-the-box data we’re going to start from scratch. My theory is if you’re not happy with the capabilities provided by Tableau you’re not going to want them clogging up your system. You see when that Local Data folder is in place it gets copied into every packaged workbook you create with geocoded data in play. So making it smaller than 130MB might be a good idea.
So what have you created Craig?
If you’ve got Alteryx and a bunch of shapefiles you want to use as a custom ‘filled map’ in Tableau then I have the macro for you. You can download it here, add it to your workflow and answer 3 easy-ish questions.
- A path to the shapefiles you want to inject
- A name for each of those shapefiles
- The path to your ‘My Tableau Repository’
Then run the workflow, restart Tableau Desktop and enjoy your new filled maps.
Sounds great! Can you give me a step by step guide?
Sure thing although keep in mind you’ll have local admin rights on your machine. Here we go:
- Download the installer and run, remembering the directory you choose to install to (default is My Documents -> My Alteryx Macros) . If you need a copy of Alteryx you can download a 14 day trial
- Navigate to the installation directory from step 1 and open ‘config.ini’ with notepad (or right click & edit). Make sure the second variable is configured to point at your ‘My Tableau Repository’
- Run the ‘Create Firebird DB.bat’ file (you’ll need local admin rights). You will see a SQL error in the ouput screen, just ignore it. If successful you’ll now have a ‘Local Data’ folder in your My Tableau Repository with a file called GEOCODING.FDB located in it.
- Open the ‘Run Through Shapefiles to Firebird’ Alteryx workflow and set your ‘My Tableau Repository’ location in the macro.
- Edit the text input tool to include the file path of all the shapefiles you wish to use along with the name you want to give each (Hint! A directory tool could come in handy here, check out the alternative workflow disabled by default). For instance:
- Run the workflow. If all’s well it should complete without error.
- In order for Tableau to access the Firebird database it must be locked into ‘read only’ mode. To do this run ‘Lock Firebird DB.bat’
- Open Tableau Desktop and admire your handywork. Your new geographic objects can be assigned to dimensions with a right click -> Geographic Role -> Select the correct role
How do I maintain this and share the love?
You can keep adding more shapefiles to the database, however keep in mind the more you add the bigger the Firebird database gets. And every time you use it the WHOLE database needs to be packaged with your workbook or uploaded to Tableau Public/Online/Server.
If you create a customised database for your organisation you can pass it around. Simply copy the ‘Local Data’ folder from your My Tableau Repository and paste it in your colleague’s Tableau Repository. Restart Tableau Desktop and they’ll benefit from your hard work too! (They’re going to love you for it).
If at any point you want to go back to the Tableau geocoding defaults without losing your work simply move the ‘Local Data’ folder from your My Tableau Repository to another directory or rename it. Then when you want to use it again put it back or rename it back to ‘Local Data’.
As a famous bunny likes to say “That’s all folks!” Enjoy