Excel 2013 PowerView Olympics
It’s been quite a while since I’ve written here as I’ve had my head buried in a few projects from the red team.
I did an internal webinar not long after PowerPivot was first released. As the world cup was on at the time all the sample data including player stats, match results, schedules etc directly fed from html tables on the fifa site.
Now that the release of Excel 2013 has coincided with the Olympics and I’m living within spitting distance of one of the venues, I thought it would be a good opportunity to play with some of the data in Excel 2013, and in particular the new embedded PowerView capability. I also got a little excited on the weekend when New Zealand spanked Australia in medal table and then tried to cover it up in their press, so wanted to investigate some Olympics data a little more in depth. I’ve had to eat some humble pie since then, but we are still second on the Medal count by population table as you’ll see in the PowerView worksheet.
Whilst Jen Underwood (blog|twitter) has already jumped the gun with this post and done an excellent job and Sean Boon (blog|twitter) from Microsoft has put together a great series with a new post for each day of the Olympics.
However I thought it could be extended a little with some data from the Guardian and a local perspective.
The Guardian data sets are saved to Google docs. For simplicity I have simply downloaded as .xls and combined in a single workbook. However, it is possible to use Google docs to publish as an Atom feed which can then be consumed in PowerPivot as a data source.
Some of the data is a little unstructured so I have done a little tidying up, but nothing complex.
Here is the link to my workbook.
Datasets (with excel sheet names):
What I’ve done in the PowerPivot model:
-When adding an excel table to PowerPivot I’ve given the excel table a name as this is what PowerView uses in it’s folders, simply renaming the table in PowerPivot doesn’t flow through. This is quite frustrating if you simply have a list of data not formatted as an excel table and use the “Add to Data Model” button in the PowerPivot ribbon
-Created relationships where appropriate. The IOC uses it’s own country codes not ISO ones, so be careful with that.
-Created a table with a sort order for Medals, hid it from client tools, then a relationship based on Gold/Silver/Bronze name string, then a new column called MedalSort in MedalistsHistory, hid it from client tools and changed the “Sort by Column” for Medal to MedalSort. As this is only three values I tried writing an IF statement, but after changing the Sort by Column, it threw a circular reference error.
-Hid a few other unnecessary columns.
-Added column MedalCount to MedalistsHistory and changed the “Summarize by” (should be Summarise for those that speak proper english!) option to “Sum”. This is quite important as the number will behave like a textual attribute if you don’t do this, rather than an aggregatable measure (this was applied to counts on other tables as well). The one issue with this is that your counts will be a count of medalists not a count of medal events. So team events like the rowing eights or the football (which shouldn’t be an olympic sport, the world cup is big enough) will add an extra 7 or 10+ to your results. This can be fixed using a DAX distinct count type formula, but I haven’t had time to get to it this week due to too much time in front of the TV!
-The venues table (sorry I can’t remember where I sourced it) I have added Latitude and Longitude to using GPSVisualizer which can batch geocode multiple addresses using yahoo. It didn’t actually work that well so I had to look some of them up manually on Bing maps. This step isn’t actually necessary, as the map visualisation in PowerView links directly with Bing maps and will geocode any text string in your PowerPivot model on the fly, but it is a little more accurate.
-Added Duration(hrs) to the Events table using =ROUND([Duration]*24,2) as it came through in a date format. I also changed the Summarize by to “SUM”.
I thought about adding graphics as Sean did here but with country flags. He has covered it comprehensively so I will leave it to you to have a play with.
One thing I have plagiarised from Sean’s blog is his use of the “Medal Tracker” Office App. Mainly as I couldn’t find a reliable live data feed without screen scraping. As this data source includes things like Athlete IDs it’s alot more robust than some of the sources I’ve used, but I wasn’t aware of it when I put this together.
Anyway, I hope it’s something fun to poke around with after Sunday when you’ll have a lot of spare time on your hands that has been eaten up as a spectator over the past two weeks.
Again here is the link to my workbook.