Partitioning, Prescriptions & Dashboard Design

by Ravi Mistry

The importance of a data dashboard is hard to misinterpret. After spending time on data preparation (which, in my last post, can take much longer if your mind isn’t poised on what you want the data to be), and creating the views which you want within Tableau, it comes to the all important dashboard. On Tableau Public, there are some insane examples of what proper dashboard formatting can lead to (check out people like Kelly Martin or Anya A’hearn amongst others). This in mind, the effort that goes into beautifying visualisations is paramount, as the end user will see this immediately.

Over my weeks at the data school there have been some amazing visualisations done by my colleagues – Have a look at their profiles too; and one key running theme on the ones that I have found the ‘coolest ‘ is the effort put into making them look pretty. So, as we went into prescriptions & SQL week, I was determined to make my viz more appealing to the eye. The data we worked on was huge – Some 517M rows long and quite a fair few fields wide. This is where Craig Bloodworth came in, and giving us our brief, our data and leaving us with this weeks key idea – Work with the data in the most efficient way possible with a key story or question that you are looking to answer. So thus, the project begun – Working in Exasol to explore samples of the data, making database calls using SQL (something completely alien to the economist in the room) and either the Exasol database live from Tableau or (as I did, the cheap easy way out) creating an extract to make loading and response times faster. More on that shortly.


It was also table calculations week! I’ve got a blog post in the pipeline where I will attempt to tackle addressing and partitioning as a concept – It was something I found particularly hard to get my head around, so I will do my best to explain it. As you may recall, our second day was a ‘Tableau Zen Masterclass’ with Andy and Craig, and that was our first real look at table calculations. So our session on Monday 27th was our first real look into not only how table calculations worked, but the order of operations within Tableau.


Tableau calculations are an odd concept to get your head around – With Craig, we took breaks often and it was good to digest the parts of how table calcs operated and how they fitted in with the wider Tableau processes. I think that this will become a really good knowledge set to have, because when we start to work with clients, knowing what will happen before we start clicking will be important. This again links into our objective for the week – To look at being efficient with, not only our data, but out actions within Tableau – Avoiding the ‘Tableau shuffle’ as Craig called it; messing around with Tableau until it showed what you wanted in a lot of clicks. We’re getting better at it, I think.

Laszlo Zsom came in as well to talk about dashboard design. This has a lot more intricacies than I would imagine, but I guess this refers back to my opening paragraph. Aesthetic choices and the way a dashboard behaves is paramount to making a solid first impression. If it passes the eye test, then the user feels more welcome to click in and find the data they want. This is where stuff like knowing your audience, having clearly defined aims and developing a story or narrative into it becomes really useful. I’m really bad at cleaning up my tooltips, and usually for our projects haven’t left myself enough time to do proper formatting. This is where Laszlo’s class was excellent at explaining why this is a good skill to have to create a ‘wow’ factor with the clients.

My work with Laszlo can be found by clicking the image below – It’s on my Tableau Public profile. The tabs represent the different views, and the arrangement is to the liking of LZ – It was intriguing to learn the choices of dashboard design which links into the likes of photography. Something I wouldn’t have immediately connected. The use of colour is important, but more important is where to use which types of colour – Stepped over fading and the correct pallets.


Now; about my project.

The prescriptions data was big. Like huge. Like nothing I’d ever worked with before. So making calls to EXAsol and figuring out the syntax from a background where I’d never ever coded before was a real challenge. But after a few masterclasses with Robin Kennedy and Andy, we got better at writing SQL. I think moving forward, this could a key skill – I initially asked Craig what the point was to transform data within the Exasol console, and why we weren’t just extracting a sample of the data to Alteryx. Where firms don’t have or cannot afford Alteryx, he said, they may still have a big data environment. So leaving them with something they can actually work with and replicate is better than leaving things in a format they can’t easily use.

This is a good approach I think. There’s a lot to be gained from understanding what happens ‘under the hood’ in programs like Tableau – And following our table calculations exercise and a primer on how Tableau runs a process as a software gives perspective to the clicks we are making.

My first idea was to keep things easy – Following last weeks debacle of spending too long on the data, I felt that choosing a theme early was a good plan. To this respect, I chose to do an exploratory dashboard, than focussing on a story to tell; this meant taking the data for just one year and my original idea was to map each body part to each relevant drug. But the problem with this, I found, was that most drugs don’t affect one part of the body – so for instance the eyes, or ears or legs; they impact organs and systems. So quickly, I changed track and looked to just use the pre-defined headers and see the changes within both the actual cost and quantity.

I then assigned images to the headers to be used as filters on the dashboard, followed by a dynamic case statement (helped here by Nicco Cirone) in order to have changing text on the dashboard it self. Check out the visualisation below and let me know what you think!



In terms of beautification, I think I could have done more. The dashboard itself is really clean and simple, and the graphs are as well. In terms of trends, it can be argued that a longer time period than simple one year would have been better, but I chose this out of trying to make sure the data size was smaller. There’s definitely scope for a minor makeover here, especially to tell a better story.