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.

http://www.guardian.co.uk/sport/series/london-2012-olympics-data


Datasets (with excel sheet names):

“Olympics Events Calendar” (Page | Google Spreadsheet)
Events

“Olympic medal winners: every one since 1896 as open data” (Page | Google Spreadsheet)
MedalistsHistory
IOCCountryCodes

“London 2012 Olympic sponsors list: who are they and what have they paid?” (Page | Google Spreadsheet)
Sponsors

“London 2012 Olympic athletes: the full list” (Page | Google Spreadsheet)
Athletes
CountryCodes
Sports

“World Population” (Page | Google Spreadsheet)
Population


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!

Olympics PowerView 1

-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.

Advertisements

About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on August 9, 2012, in Business Intelligence and tagged , , . Bookmark the permalink. 3 Comments.

  1. Hey the link to your template doesn’t work for me. Do you have another copy of this template somewhere? It sounds great!

    Like

  1. Pingback: Rugby World Cup with Power BI | Karl Beran's BI mumble

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: