Tableau + Python + IoT

You’ve asked and it’s time to answer.  About a week ago I posted a teaser video on Twitter that showed how I integrated my home automation system with a Tableau dashboard.  The outcome of the video was dynamically changing three different lights in conjunction with KPI results.

So beyond the “hey this is really cool” factor of connecting lighting fixtures (or anything else for that matter) to a Tableau dashboard – there are some great practical reasons to do this.  A few great replies to the tweet were of the same sentiment of the thoughts coursing through my mind.  Use something like a light to easily indicate a status – it’s that step back from the KPI shape or the BAN (big ass number).  It’s something visible that you can glance at to immediately understand performance.

Next comes the technical components of how this gets put into place.  To achieve integration between my home automation system and Tableau I used Python.  It serves as a great integrator and took an idea to proof of concept quickly.  Python benefits this situation because several people have developed libraries and published them on GitHub to share.  And the other reason why Python was a great natural choice is because of TabPy.  TabPy allows you to connect to a Python server and run native Python code through calculated fields.  These calculated fields are served up on a Tableau sheet as a table calculations.  And in general when you’re using Python with Tableau it’s to get a number/string/boolean result out.

So just because you are supposed to use TabPy for something doesn’t mean you can’t extend it’s capabilities and use it in a completely different manner.  And that’s exactly what this integration project was about.

This project is also about taking a continuous project I have around home automation and folding in something new I learned (Python) with something I already love (Tableau).

My final caveat is this: if you’re looking to emulate what I did you’re going to need some hardware aka Philips Hue lights and a bridge.  In theory you can change this out with the home automation pieces of your choice.  If you’re unfamiliar with what home automation is – it basically works by having a controller (often called a bridge) and then different connected elements.  The controller is responsible for issuing commands to things like lightbulbs, locks, and your thermostat.  It is also the vehicle that takes how you interface with your home automation system (which is typically a mobile app) and translates those commands, routines, or programs to the elements in the system.

And specific to this project, I’m interfacing with my bridge (controller) using a Python library.  The library I chose to use was Phue, a Python library designed to fully interact with the Hue API.

First I downloaded and installed Phue into my Tableau-Python-Server environment (aka the one used for TabPy).  That allows me to call the library in my Python scripts that are passed through Tableau.  During the install process I also ran a simple script to connect the library to my bridge – a necessary dependency to get this all to work.  It makes sense that you’d have to be able to establish a link between the communication component and the lights you want to change.

The next parts are all reading through documentation, trial and error with Python, and fun with Tableau.  Here’s the final calculated fields that are driving results:

Profit Ratio KPI – this is something we’re all used to.  I’ve taken the basic Profit Ratio calculation and had it evaluate against a parameter.  Based on the evaluation the result will be either 0 or 25000.  The numbers 0 and 25000 correspond to the color hue input required for the lights.  0 is red, 25000 is green.

And here’s the calculated field that is pure Python.  In this scenario I’m not actually using Python to crunch anything and return a result, so you’ll notice that the return argument is a dummy number 1.

You will see it in the calculated field description in the editor within Tableau – to use SCRIPT_INT (or any of the SCRIPT functions) the format is: put your code in quotes and any inputs or arguments being used are called using _arg1, _arg2, _argN.  Those arguments follow the comma after the script.  In my example I have one argument, [Profit Ratio KPI] and that is being assigned to a in the code.

  • The basic outline of the code is as follows:
  • set [Profit Ratio KPI] to be a
  • import phue library so we can use it
  • use my bridge (that’s the bridge’s IP address on my internal network)
  • set the saturation of the lights to 254 – this is extra code, but ensures that the light has full saturation
  • set the light hue to the value of a
  • return 1 – just for fun!

If you’re extra interested, the functions from Phue require you to select which light you’re going to be controlling.  The Profit Ratio KPI light was number 25.

A trial and error moment here.  As I was testing this code I had a Jupyter notebook up and was entering the same code snippets to test it outside of Tableau.  Under normal circumstances I was able to assign a number to a without any issue, execute the code, and have the lights change colors.  Inside of the Tableau space I had to call the first index “[0]” of the argument.  I haven’t looked through the documentation for TabPy in enough detail, but it leads me to believe that arguments come in as lists instead of single variables.  So to isolate out the one result I wanted, I had to specify which element I wanted in my list of one variable.  So my KPI result is really like [25000] in Python notation and has to be called as _arg1[0] (non-Python folk: Python indexes start at 0).

Literally everything else about this was setup and build of the Tableau components.  Building out the BANs and adding parameters to dynamically input the goal variables.  Everything else was connected and working after the correct code was entered.

Here’s what one of the BAN sheets looks like:

The calculated field is present on detail, but not used anywhere else in the view.  (Which if you hovered over a default tooltip would show “1”).

Once the visualizations are set it’s just a matter of interacting with the dashboard to start seeing this integration take effect.  You’ll notice during the video that it’s not super fast – there is definitely an element of time.  And that makes sense – there is a bunch of overhead to take an argument from Tableau that has to be computed, push it through a Python server, and wait for a result to return.

This is a beautifully simple integration and demonstration of how to take the mantra of seeing and understanding data to the physical world.  With a few lines of code data-driven performance can exist in the physical world.

The home automation mindset

Home automation is something that I have been following for several years.  I think we all have seen automation on TV.  Bill Gates’ house with music and pictures that follow you around in the 90s.  For me, it is about getting to Star Trek level of voice control with automation that does what I want with minimal interaction.  Getting to that ideal state is one of the most thought intensive projects I have taken on.  To use a meme, one does not simply automate a house. It requires a mindset.

The first thing to keep in mind is that the home automation landscape is like any other technology ecosystem but even more complex.  The one thing that makes it more complex than gaming, or photography is that home automation is so open.  I know that I can ignore news on PlayStation because I don’t have that console and I know I won’t ever be able to use it.  I also know that gaming has a limited scope that won’t impact much outside of gaming.  With automation, anything can be made compatible with almost anything and it affects my entire life.  For example, I’m thinking about getting a wi-fi souve machine.  How well does that work with SmartThings or any of the other platforms I have?  If my fridge broke tomorrow, I would have to consider automation in my replacement decision.

This openness is great because it means that I can do anything I put my mind, and time, to.  Literally, I could write an app that uses the accelerometer in my iPhone to change light colors.  I could then paint with all the colors that Hue can produce, while I dance around the living room.  I don’t have the time to do that, but that is really all that is stopping me.  Thankfully, others have used their time productively to produce tools that are more useful. But, of course, that means searching and testing the work of other hobbyists.

With the help of talented coders around the world, I can get better and more effective automation.  But that brings up the next thing to keep in mind: what do I even want to happen?  When I come home, what lights should be on?  Should it be the same lights during the day and during the night?  When does “night” start inside a house?  Essentially, I am always trying to determine what I usually do with lights and fans and temperature, and thinking about how I can communicate that to the automation systems.

All of that is the basis for the mindset needed to do home automation.  It is always on my mind.  If I’m out, I am thinking about what the house should be doing.  Should I communicate my distance to home?  What would I want the house to do with that?  When I am home, I think about what could be better.  What lights are working? Should the house be adjusting to my actions? And what would those adjustments look like? While thinking about this stuff, I also have to keep in mind how much time any solution would take, is the problem already solved, and if it is worth it.

The worst part of all of this is that the biggest hurdle faced in home automation is getting past the automation vendors themselves.  They want to take this complex and involved mind set and make it one button.  That would be fine, but nobody is that good at it yet, and the least good are the people that make the products.

So why even bother? Because of the way it feels when it works.  The feeling of a giant complex machine that culminates in a fan turning off. It’s basically magic, with magical tools, and me as the magician.