Let me set the scene. like many children I grew up visiting my grandparents at weekends, they owned a paper shop and as luck would have it, this paper shop was in Finsbury Park, North London. Now in those days whilst football players were stars, they also smoked ciggies and read newspapers and so the players from the local team were patrons of my grandparent’s shop… and so began my life-long obsession. In time, I grew up, got a job, left home, got married all the usual stuff, but through it all my obsession remained.
As with my choice of football team, I got lucky when it came to work, I was leaving school at a time when the computer industry was burgeoning, I got a job as a trainee programmer and that began another obsession, data; I became a geek!
I’ve forged myself a career working with data and over time I came to realise that I’d also become a data hoarder. As I’m not very good at remembering statistics, I collect data; if I play a game I note down my progress and use it to plan getting to the next level, reaching the next target, etc. Well I confessed to being a geek, didn’t I? Unsurprisingly this means I have accumulated a record of all my football team’s results stretching back to the 1980/1 season.
So where does Agile Business Intelligence come in to this I hear you ask?
Until recently mention “agile” in computing terms and you would have been talking about Agile Project Management, Kanban walls, burndown charts and scrum masters… These days however, “agile” when related to Business Intelligence means the ability to quickly wrangle (cleanse) and mash (join) together data, without having to go through lengthy and formal Extract, Transform, Load (ETL) iterations. Now my “bread and butter” is helping clients design and develop exactly the formal ETL that this new Agile BI says you can do without, so I thought that I should see what the hype was all about.
First things first, I needed to select an Agile BI toolset, there are plenty out there: Tableau, QlikView etc. but I plumbed for Microsoft’s Power BI, mostly because I’m already reasonably familiar with Microsoft technologies. So I kicked off the free download and started thinking about what data to play with… Well learning a new tool is always easier and more fun if you know the data you are using well and especially if it’s something that’s interesting to you… Oh, wait, I have all these years and years of football results, I wonder what they can tell me?
Wrangling the data
Being a self-confessed data hoarder and general geek my dataset was already in pretty good shape; for each fixture I had the date, opposition team, competition (Premiership, FA Cup, Champions League etc.), goals for and against and whether the game was played at home, away or at a neutral ground. With a little bit of hunting around on Google I was quickly able to add to this the city and country of all the various grounds and so I set to work.
I needed to wrangle my data to annotate competitions in order to categorise each as “domestic” or “European” and I needed to work out a formula to calculate the football “season” for any given date, but neither of those proved a problem.
Next I gave some thoughts to how often the team had won or lost games, this need to be calculated dynamically so that if I filtered data or summarised it I saw the correct value, Power BI’s DAX language easily allowed me to add calculated measures for win, loss and draw percentages.
And so the fruits of my labours…
So what does it tell me?
Well, my team wins more than half the games they have played and of the remainder they draw more often than they lose – which is good.
These stats aren’t quite so good in European matches as they are in Domestic ones, but the win percentage remains above 50%.
When I created the win, loss and draw percentages I created some calculated measure columns using the built-in DAX language, don’t let that worry you either. If you’ve created any formulas in Excel, you already know the basics of DAX.
As you can see I also managed to plot my team’s games on a map, but more on that next time where I’ll cover quite how easy that was, barring an odd assumption on my part and a “gotcha” from the Bing mapping engine. I’ll also include a link to the interactive report itself.
Mark Fulgoni is a Principal Consultant in Red Olive’s Data Practice.