Cafe Natalia: Creating a Spatial Intelligence Tool in PowerBI

This dashboard is a story of dead ends and frustration. It may be the natural focus The Data School puts on Tableau, which after today’s experience with PowerBI, I love far more; but, putting together this dashboard was a struggle.

We started the day being provided with a dataset published by the City of Melbourne; a 10-year dataset of available seating for thousands of hospitality venues over time.

At first glance the data looked pretty good and I had a plan for how I wanted to approach this brief; and that is roughly the point it all went pear-shaped.

Here’s a brief outline of the initial plan:

  1. Bring the data into Alteryx and start exploring
  2. Pull a list of unique hospitality venues
  3. Enrich the data using Google’s Places API to pull reviews, price level, and ratings to estimate popularity
  4. Create a series of visualisations to allow a user to identify gaps in the market; city blocks where the average rating for a particular price point is low.
  5. Finish the day with time to spare so I can take care of the other responsibilities this job comes with.

Now, you might think that I was crazy to believe that it would all go that smoothly; and you’re right, because it didn’t.

After a relatively successful first day of dashboard week I was pretty confident going into today. I was going to improve my Alteryx workflow documentation, and nurture my Python skills by writing a script to hit the Google API.

However, at about 4pm I realised that my plan needed to be heavily revised, and after I went home at 5:30pm I had to essentially start from scratch with a revised plan.

My story had completely disappeared and I needed to find a new one, this is what my revised approach looked like:

  1. Create a new Alteryx workflow ensuring that I properly understood the grain of the data. In my first attempt I didn’t realise that each trader had a line each for indoor and outdoor seating per census year.
  2. Use fuzzy matching to iron out inconsistencies in the trading names of the venues.
  3. Use the normalised names to aggregate the venue data to estimate the age of the venues by finding the first and last census years the venue appears.
  4. Using the opening and closing years of the venues, create a visualisation that gives a user insight into when and where cafes and restaurants begin or fail.

Lessons To Learn

In my relatively short career I have been reminded many times that there are good days, and there are bad days. Unfortunately, this was not one of the good ones.

However, what helps me sleep at night (although it is quite late that I’m writing this) is that there are no failures, only learning opportunities.

So this is what I’m going to take from this experience:

  1. When using unfamiliar technologies, such as PowerBI in this case; actually, whenever you’re using PowerBI, allow much more time to put a viz together, especially if you want it to look good.
  2. Time management is vital. This has always been a struggle of mine, and I have to say I indulged in a lot of exploration at the beginning of the day and I paid for that in the end.
  3. 10 Minutes of Planning is worth an hour at least. My exploration process was pretty aimless, I was just waiting for the analysis to come to me without a plan for how to find it. This led me in circles and down dead-ends. I wish that I’d just planned a rough outline of what I wanted to do which would have saved a lot of time in Alteryx.
  4. Fuzzy matching is hard, and it’s an art rather than a science. We haven’t strictly learned fuzzy matching, but I am familiar with its concepts from my own personal learning. It’s not exact (of course not, it’s in the name after all) and it’s very difficult to manage and tune. Allow more time for that to work.
  5. Spend more time looking at the make-up of the data. I made a mistake pretty early by not identifying the grain of the data at the start. This led to a huge amount of duplicate rows, and a lot of pain.
  6. Don’t get too cute. What I mean is, don’t boil the ocean and don’t overcomplicate things. I had so many grand ideas at 10:30am that I wasted time pursuing, and now at 12:54am I kind of wished I’d just focussed on creating something good, not revolutionary. We’re not here to reinvent the wheel.

With that being said, today was a reminder that dashboard week is not to be underestimated. Having an entire freeform day to pursue an analytics process is fun, and the mornings are relatively chill, but as soon as the clock strikes lunch and you’ve got sweet-nothing, that’s when the stress kicks in.

I learned a lot about myself and my approach today, I’m honestly just hoping that I can redeem myself tomorrow and create something awesome once again.


PS. If you noticed that I used Comic Sans in my PowerBI dashboard that choice was half irony and half admitting defeat.

Daniel Lawson
Author: Daniel Lawson

Right off the bat I can tell you that I’m not your average data analyst. I’ve spent most of my career running my own business as a photographer and videographer, with a sprinkling of Web Development and SEO work as well. My approach to life and work is very T-shaped, in that I have a small set of specific skills complemented by a very broad range of interests; I like to think of myself as a dedicated non-specialist. Data Analytics, and Programming, started as a hobby that quickly grew into a passion. The more I learned the more I looked for opportunities to pull, manipulate, and join data from disparate sources in my life. I learned to interact with REST APIs for services I used, personal data from services I use like Spotify, and health data captured by my devices. I learned SQL to create and query databases, as well as analyse SQLite files containing my iMessages and Photos data on my Mac. Every technique I learned opened up more possibilities; now I’m hooked and there’s no turning back. Learn More About Me: