So continuing on from last time, mapping the data was a bit more interesting. Initially I wasted some time on a silly assumption that I would need latitude and longitude details for all the football grounds, I should have realised that this new breed of tools would be more intelligent than that. I have the city field in my data, it turns out that all I really needed to do was tell the Power BI model that this was a “city”. The Model (data set) editor contains a whole set of geographical categories such as city, country, postal code etc. that you can tag columns with to enable mapping your data).
However, I quickly hit a little “gotcha” Athens in was appearing in North America, but the online help enabled me to resolve this, creating a calculated column to concatenate the city and country together put Athens firmly back as the birth place of modern democracy in Greece. Don’t you just love the US-centric view of the world…?
Next, I quickly realised that I needed to exclude home matches from the mapping otherwise the stats were so heavily biased towards London. It wasn’t hard to figure out how to apply a filter just to the map, rather than to all the graphs and tables. Even so London is still the city with the most fixtures, but given the number of London based teams in the top flight and the fact that cup semi-finals and finals are most often played there that’s no real surprise.
Mapping the data
What was more of a surprise was the top city when I filtered by European games:
Thankfully I’d got Bing to resolve Athens in Greece, otherwise it would have looked like we had been on some serious transatlantic journeys!
So, other than having a bit of fun with two of my obsessions, football and data, what did I learn?
Well Microsoft’s Power BI is quick and easy to use; DAX is nothing to be frightened by, if you know Excel you are most of the way there and the online help is also pretty good.
It wasn’t hard to pull in data and join it together, nor was it difficult to add new data manually, however Power BI doesn’t have some of the advanced data wrangling capabilities that some tools I’m currently investigating do have (for example MicroStrategy 10).
I also found it easy to calculate additional measures, such as the win, loss and draw percentages which need to be calculated on the fly at the appropriate totalling level.
And, all joking aside it was fun!
Next time I’ll look at and discuss the implications of Agile BI and the traditional Data Warehouse.
Take a look for yourself, the link below should take you to a public version of my report. However, whilst I keep my own stats up to date, I can’t promise that I’ll keep publishing updates.
Mark Fulgoni is a Principal Consultant in Red Olive’s Data Practice.