Snowy Sports – Winter Olympics Dashboard

makingof

Hey viz fans.

I’m baaaaack. Thanks to everyone that send good wishes for my shoulder surgery last week. It was a frustrating few days so I thought I’d get vizzical as quickly as possible.

I’m gonna start this post with a caveat – Don’t treat this viz as authoritative! Please! I had some massive challenges with the data and tried a few things for the first time so I just can’t guarantee that it is as accurate as some would like. As with most of these vizzes, they are as much to showcase the capabilities of Tableau / Alteryx as they are intended to be informative. If you’ve got an issue with that then proceed no further.

I love sport. All sport. Especially the Olympics. And as you will know we are coming up to the start of the 22nd Winter Olympic Games in Sochi, Russia.  So I thought I’d viz up the medal history going back to the first competition in 1924.

And here it is!

sochidash

So how’d I do this?

1. Data

Now this was *almost* a great success story. Let me explain.
All the medal history was held at http://www.databaseolympics.com, see the Winter Olympics section. The trouble is, you have to navigate through a couple of levels to get to the sports you need, there’s no way to view all the results in one go. When you do get to the data it’s in a nice format for copying into Excel, see below.

data

If only I could get it all into one place. Then I had an idea.

First off I managed to get a list of all the base level URLs I needed to cover each sport for each games. There were about 90 of them. e.g. www.databaseolympics.com/sport/sportevent.htm?sp=ALP&enum=110

Then it was dadda!! – Alteryx time. Let’s see what you got eh. Erm… Well I was pretty confident that this could be done but I’m far too inexperienced with the tool to get anywhere. Luckily a good friend of us at VN is the current Alteryx Grand Prix Champion – Chris Love (@chrisluv) of The Information Lab. I thought I’d see if he could solve the issue.

This is what he sent back to me… Turned out to be fairly tricky.

lovemail

And here’s the Alteryx module that cracked it. Happy to send it to anyone if you want to take a closer look.

alteryx

Absolutely awesome. I’ll be honest, I don’t yet fully understand how it works but I’ll be looking into that in more detail. I’m a little disappointed that Alteryx doesn’t seem to do web queries a little easier, nor can it process html files (even though it’s ok with xml).

So there we are – almost. I now had good data for all competitions up to 2006. Trouble is there was no data on that website for the Vancouver 2010 event (argh!). I did manage to locate the information from Wikipedia but it was a totally different format and had a number of other variances from the other data.

Another caveat is that I’ve treated every team medal won by an individual as an individual medal. For example, the bronze medal won by the GBR Ice Hockey team in 1924 counts as 10 bronze medals – not strictly accurate I know.

I managed to use Alteryx again to transform some of the 2010 data into a better format but I couldn’t get away from the fact that I had to do a lot of manual crap to get the info into the format I needed. With that, there are sure to be some mistakes – hence the above caveats.

Anyway….

UPDATE: Due to some pretty bad data quality issues with the source detailed above (missing whole events etc), I switched to this alternative. It looks to be a lot more accurate but again I can’t vouch for the quality. Let me know if you see any issues.

http://www.olympic.org/medallists-results

2. Viz design

Timeline – Quite like this view. Simple plot using an Olympic Ring image as the mark, plotting the year of the competition.

saraOne thing I really wanted to do was to represent the official flyer of the competitions and have that change with user selection. I’ve been meaning to try out Shawn Wallwork’s tip on how to do this. http://community.tableausoftware.com/thread/119079.

In summary it’s a tip for creating a dynamic background map, based on user selection. Check it out. Very smart.

Filters – I then created a couple of worksheets to act as filters for Sport and Medal type. Simple enough, using shapes. Needed to make sure the medals were consistent in size and style.

pictoFor the sport selector I used the official pictograms for the Sochi games. I think they’re awesome and look perfect for this style of viz. I positioned them centrally to act as a central selection panel. Love the look and feel of the images themselves. Give it a bit of use as filter action and job done.

medalsMedal Table & Individual Records – These were simple enough. The only cool bit was the use of small medal shapes for the gold, silver and bronze medals. Looks smarter than a basic text or highlight table.

Final touch was to add an athlete wildcard search, and then a mouseover info sheet to highlight the caveats discussed in the post. I also added a country code custom list filter so you can compare how one nation has performed compared to another.

3. The Finished Product

sochidash

So there you go. A viz of the Winter Olympics. Fun to do that one and great to be back in vizzing action. Lots of ways this one could be improved but I didn’t have the time on this occasion.

Regards, Paul

Advertisements

2 minutes with… Kelly Martin of VizCandy

2 mins with title2

Hello everyone. I hope you’re doing well. Time for another episode in the top-rated, some say premier, some say foremost interview show on the web.

And this is a bit of an OMG moment for us here at Ninja Towers, for we are proud to welcome our first Tableau Zen Master to the show.

kellyIt’s the fabulous Kelly Martin (@vizcandykelly)! This is a real pleasure for us here. Kelly has been a tremendous help to me behind the scenes of this blog, offering continuous support and expertise as I’m sure she has been to many of you reading this. She was the original inspiration for me to begin expressing myself in the BI space, and I’m sure has done the same for numerous others.

 

VizNinja (VN): Thrilled to speak to you, how are you doing?

KM: Pretty good, although I could afford to lose a few pounds and I’m having a bad hair day.  Too much information?  Careful with those open-ended questions.

VN: How do you use Tableau at your organization?

KM: I am the whole organization @ VizCandy.  It’s just me and the cat, and she’s not much help.  I use Tableau all the time, exclusively.  I don’t think I’ve made an Excel chart in 3 years.  My focus is dashboard design, so that’s what I do for people – build them great dashboards in Tableau.

VN: What has the impact been on your business?

KM: I have been doing contract work off and on for 10 years while having a full-time position in different organizations.  Tableau has enabled me to go full-time contract – that’s the business side.  On the personal side, it has made me a happy analyst.

VN: Who do you learn from in the Tableau community?

KM: Wow.  That’s a long list and constantly growing.  There’s all the bloggers (pretty thorough list on Ramon’s site), then there’s the hundreds of tweeters,  Viz of the Day, and the Community Forums.  I check out the questions on the forums regularly.  I’m not very good at helping people in that format (I don’t usually understand their question or problem), but I learn a great deal from the help other people provide.  Sometimes I just go to Shawn Wallwork’s  activity to read through the questions and answers.

I will say that there was one blog post by Steve Wexler ‘Hey, Your Tableau Public Viz is Ugly *and* Confusing’, that had the most impact on my work.  When Steve first published this I thought he was directing it at me.  It felt personal because I was guilty of many of the bad actions he was describing.  After my internal defensive hissy, I worked at improving my design skills and I often present his post when talking to people about designing dashboards.

I love this blog as well, it’s fun and insightful.

VN: In your opinion what should we to be mindful of in the BI space going forward?

KM: In my experience, BI is usually a small team within IT which wrangles data sources into warehouses (or marts) and builds dashboards, which the majority of the organization uses to download data into Excel in order to conduct analysis relevant to their subject area or business segment/department.

IT used to be besieged with requests to ‘Just give me access to the data’ and the same thing is pretty common now with BI. Some organizations have put BI teams into Finance, Operations, or Marketing, etc., but the majority of analysis within those departments is still conducted by the Excel analysts not in the BI team.  High level KPI dashboards and scorecards built with complex stack BI solutions only prompt more questions about what is going on.  BI, reliant on those solutions, are not able to respond in a timely manner and nor should they.  They can’t possibly know the business or subject areas as the Excel analysts do.

kelly2With Tableau, I think BI could transform itself into a data service team, supporting Self-Service-BI throughout the whole organization.  I did a Prezi on this last year http://prezi.com/4bi24d44yid1/self-service-bi/.

VN: Could you give me an interesting non-work fact about yourself?

KM: You can take the girl out of the trailer park, but you can’t take the trailer park out of the girl.

VN: Thanks for your time, see you soon.

KM: You bet.  Thank-you, and I look forward to connecting at the next TCC!

Ok that’s it for this one. Delighted to have our first Zen Master in the can. Thanks also to all those that are still waiting to have their interviews featured and have shown interest in this series. We’ve got a couple of very vocal bloggers lined up, some faces that will be well known to many of you.

Until then stay safe, and if anyone needs a shoulder operation, I can recommend an excellent surgeon….

Regards, Paul

FIFA Ballon d’Or 2013 Dashboard

makingof

Hello there everyone!

Thanks to everyone for the great feedback on my recent Pinball viz. Was a lot of fun doing it, and glad it brought back some youthful memories for a number of people. I wasn’t intending to produce another viz so soon, but this one is fairly timely.

See I love football. I mean LOVE it. For some reason I’m still playing every week in the Surrey Sunday leagues, my 21st season. The old bones creak a bit these days and I’m not as quick as I once was, but the enjoyment is still there. So like many I watched with interest as the recent World Footballer of the Year awards were announced. Sorry I’d better say the FIFA World Footballer of the Year awards in case Mr Blatter is watching.

The actual results weren’t too much of a surprise. Ronaldo, Messi & Ribery making up the top 3. But I wanted to see how those votes broke down and see if I could pull out any trends using Tableau. Especially as there has been much chat about whether these votes are a bit of a farce or otherwise.

And here it is!

dordash

A viz of all the votes, showing geographical distribution, points breakdown per player and voter type (coach, captain or media).

So how did I do this?

1. Data

Data was pretty easy to get hold of. In a rare display of process transparency FIFA publish it here. Each voter names their top 3 players from a short list with 5 points given for first place, 3 for second and one point for third. Total points wins the trophy. Simple.

dordataWell sort of simple. See this format has the player name as a measure, I want it as a dimension. Copying this data into Excel for analysis wasn’t a goer, it just didn’t seem to be manageable with Tableau in that format.

reshaperSo this gave me the chance to try out this Excel Tableau “data reshaper”. I’ve heard other people talk about it but never used it myself. Simple enough to install and bingo – worked first time.

formatteddataMy data now looked like this, with the player name as a dimension. Worked fine when connected to Tableau. Perfect.

2. Viz design

I really wanted this viz to show the following

  • Geographical distribution of votes
  • Is there a trend of votes between the 3 voter types
  • Any oddities in voting?

With that I made the focus of the viz a map with pies. Now I know this looks a bit cluttered when all the marks are visible, but it does seem to work when the set is restricted with filters or even highlights.

The player filter allows me to easily see where each player’s points came from. The points calculation was done with a simple calculated field to assign either 5, 3 or 1 point to each player’s SUM(Number of Records).

I like highlight tables so I thought I’d provide a full points breakdown. This is more interesting when combined with a filter.

Regarding the regional grouping, that was manually done. Could have split it into ‘official’ FIFA regions I suppose but didn’t have time.

3. The Finished Product

FIFA Ballon d’Or 2013 Dashboard

dordash

What’s this telling me?

There are a few interesting trends that this viz allows me to infer.

mediaFirst off, although Ronaldo won the award, you can see that he got a large portion of his votes from captains and coaches. When I select media as the filter then you can see that Ribery easily won the vote of media representatives, except in the Americas region – kind of expected given that Messi is Argentinian. That’s an interesting finding as FIFA controversially extended the voting deadline to allow Ronaldo’s awesome performance against Sweden to be considered, a move that many felt cost Ribery the award. Ronaldo only topped the vote for one region, Europe.

It’s also easy to see that there was a lot of national favouritism. I.e. assigning points to the player of your own nationality even thought they are clearly not one of the favourites. Examples include – Zlatan Ibrahimovic, Gareth Bale, Yaya Toure, Radamel Falcao, Andrea Pirlo, Robert Lewandowski & Luis Suarez – all of whom received significant points from their home nations.

persie

Lotta love for Robin van Persie in the Carribean (see left).

It was also interesting to see that neither Ronaldo nor Messi voted each other in their top 3. Understandable I suppose, if a little childish.

I can also see a surprising amount of countries that didn’t bother to send in all their votes. I’m looking at YOU Turkmenistan.

So this voting does seem a little contrived and a bit like the Eurovision Song Contest at times. Clear favouritism & agendas at play.

Ok that’s it. As always feedback appreciated. And I know it’s not perfect, so don’t take it too seriously.

“FIFA – For the game. For the world.” – Yeah whatever Sepp.

Regards, Paul

That deaf dumb and blind kid…

makingof

#He’s a pinball wizard there has to be a twist. A pinball wizard, got such a supple wrist.. do do de do… How do you think he does it? What makes him so goood?#

Good morning all. I don’t start every post with a song but at the moment I can’t get that tune out of my head. And it’s no surprise given the subject of the post.

Peter Gilks recent viz about video games consoles got me reminiscing back to the heady days of 8-bit gaming and all those days I spent in dodgy arcades in the North-East of England. And I got thinking about all the great pinball tables I used to play, both back then and later at University. So as with most thoughts us BI folk tend to have, it gravitated into a Tableau viz!

And here it is.

pinballviz

A viz of all pinball tables where more than 1000 units were manufactured. The timeline shows release date, coloured by manufacturer. I’ve also pulled a table rating and the exact number of units produced.

So how did I do this?

1. Data

pinsearchData is obviously the key to any viz. And luckily the folks at The Internet Pinball Database have a detailed repository, easy to query and returns the results in a format that I can easily get into Excel. So step 1 was to use their search functionality andsearchresult pull out a list of all tables with >1000 units produced and copy that into Excel. Simple enough. I was hoping to locate a really detailed datasource and given the fact loads of people take their flippering very seriously it wasn’t a surprise to find this resource.

2. Viz design

Now I knew from experience that pinball had a “golden age”, was hit by the advent of video games and then made a bit of a comeback. But would that be reflected in the data? So my first view was a timeline by year, coloured by manufacturer to show the big players like Williams and Gottlieb etc.

That was simple enough to create but I wanted to give it a kind of 8-bit graphics feel, like you’d get on a table display, and that was done with a stacked bar chart, carefully choosing the colours and border around each cell. I really like the look of it, especially from a distance. Works great when using the manufacturer highlight also.

The other 2 views were self explanatory enough, highlighting the ratings and units produced. I had planned on limiting them to the top 20 tables so I didn’t have annoying scrollbars (I hate those things), but a couple of people I showed it to said they’d like to see more detail. Let me know what you think.

#Aint got no distractions. Can’t hear those buzzers and bells. Don’t see lights a flashing. Plays by sense of smell. Always gets a replay, never seen him faaalll… # Sorry – there it goes again… Be gone Daltrey!

3. Filtering

I wanted to highlight and filter according to manufacturer. Highlights look great on the timeline with all those little squares lit up like a video game, but it would also be nice to filter the whole sheet by manufacturer to tell their own story through the years and view their top / most produced tables. This was achieved by allowing the tables by manufacturer chart to double up as a dashboard filter.

I also added a filter for machine types, so you can go back the really old-school days of Electro-Mechanical tables, before they all became Solid-State.

4. Graphical Grief

I really wanted to get some more graphics into the viz, pictures of tables etc, but ran into two issues.

Firstly I did manage to find all the images on ipdb.org, but there were far too many to use as custom shapes or icons. So I figured that I’d be able to have a web page object that linked back to the image at source, displaying the table whenever it was selected by the user. That would be ace.

All I needed was the URL path for one image for each table. Now I’m sure as hell not gonna manually copy the URLs into the datasource so managed to achieve this with the help of a colleague (@GlenRobinson72) who knocked up this VBScript.

ipdbscriptIt works by taking a text file of pinball table names, and for each one it connects to ipdb.org, searches for the table, finds an image file and parses the URL, which it returns back to me. I then manually copy that into the datasource csv as a dimension. Dead easy. It doesn’t always get the best image for each table but that’s less of a concern.

Then the next step was to create a dashboard URL action, that referenced the new photo URL dimension.

And finally a web page object that dynamically changed URL destination based on what table was selected.

imageproblemThat works great. BUT – the web page object has no control over the size of the image returned. I’d wanted each image to resize into the web page object but what ended up happening was that only a tiny portion of the image was displayed with huge scrollbars. I was unable to find a workaround to this.

For example, see the image in the bottom right corner of the screenshot (left).

I did find a compromise. I removed the line in the script that stripped the “tn_” from the URL and this meant that the thumbnail image was returned. Too small to be of real use but more workable than the large image.

If anyone knows a way of getting around this then let me know. It would have been the icing on the cake for this viz. I suppose I could have downloaded the images locally, resized them manually and then used them. I wonder if Alteryx could be used to solve this problem?

5. If I’d had more time

I managed to find a data set of world’s best pinball players and their ranking points totals from 2013. That would have been good to represent, and maybe correlate with their favourite tables. The data is at http://www.ifpapinball.com/ if anyone is interested.

I also wanted to represent the World Record score for each table, but wasn’t able to find that data.

The Finished Product

I’m well pleased with this. In particular the way the colour matrix works for the timeline chart. Looks funky.

http://public.tableausoftware.com/views/PinballTables/PinballWizardry#1

And there are a surprising number of trends to infer from the data. You can see the golden age of pinball from the mid 60’s to late 70’s, when the impact of Space Invaders and Pac-Man led to a crash in table production. And then the mini-revival in the early 90’s, which is when I ploughed loads of my student grant into them. Stern are still producing tables to the present day but these are in lower numbers than 1000 so are not included in this dataset.

Looking at ratings you can see Gottlieb have by far the biggest presence at the top of the ratings chart, even though they didn’t produce individual tables in huge numbers. But they clearly have a cult following as the ratings suggest. Williams and Bally seemed to go for large numbers, consistently across the golden age, with Williams continuing into the 90’s revival. Of course none of that would have been possible without the early pioneers like Chicago Coin and Genco.

No surprise to see The Addams Family as the top produced table. That thing was everywhere. I never got on with the magnetic madness on that table although everyone else seemed to love it. I’ve called out my favourite table in an annotation. As far as I’m concerned it was directly responsible for me not getting a 1st in my undergraduate degree so has a lot to answer for.

So there it is. Hope you like it. Feedback invited as always. And don’t buy that stuff about the deaf dumb and blind kid. I’ve tried playing blindfolded….

Regards, Tommy – agh sorry!

2 minutes with… Peter Gilks of Slalom Consulting

2 mins with title2

Good morning all, I hope I find you well.

It seems that “2 minutes with…” is proving to be quite the premier interview show on the web. We’ve been literally inundated with requests to take part. I might have to employ some additional ninjas to deal with the admin.

We’re switching continents for this episode… He’s an alien. He’s a legal alien. He’s everyone’s favourite Englishman in New York…

It’s Peter Gilks (@pgilks) of Slalom Consulting in NYC.

250px-Slalom_ConsultingFounded in 2001, Slalom Consulting are an American business and technology consulting firm and were named as one of the best 50 companies to work for by Forbes in 2013.

Here at VN we are big fans of Peter’s work, in particular his cool Tableau Public vizzes. This recent one will roll back the years for you hardened video gamers. Peter is also a vocal tweeter and has been very helpful with his feedback about this blog and other topics.

IMG_0042VizNinja (VN): Hi Peter, how are you?

Peter Gilks (PG): I’m very well thanks, despite just getting back from seeing the Chicago Bulls lose at Madison Square Garden.

VN: So who are you then and what do you do?

PG: Peter Gilks, Data Visualization Consultant at Slalom Consulting, New York. Slalom are Tableau North American Alliance Partner of the Year 2013.

VN: How did you start using Tableau? And how are you using it now?

PG: I started using Tableau about 4 years ago when I was working as a customer insight analyst at Barclays. At first it was just a great a replacement for coding SQL and draw charts in Excel, but I soon realised the scope of the product and I was hooked!  Eventually the product grew within the company and we were lucky enough to get a Server which opened up the possibilities of pushing Tableau dashboards out to colleagues and customers. These days I’m spreading the love to other organisations, big and small, as a Tableau consultant at Slalom.

Tableau Public has also become something that I’m passionate about. I have my own blog which I run as a hobby and I try to do creative things with data. But I also love reading other folks blogs and get a kick out of seeing Tableau Public dashboards embedded in news sites. Where as I mostly make vizzes on fun topics, things like the recent dashboard showing fatal shootings of children in the US that was featured on NBC News really show how powerful data visualisations can be.

VN: How have you seen Tableau make an impact on businesses?

PG: There are a few big impacts I see Tableau making. One is the huge increase in efficiency for business and data analysts – which in turns leads to another that is the discovery of insights which is enabled by the ability to ask and answer questions quickly. Another major impact is the engagement with data from people who would otherwise be disengaged and making more ‘gut’ decisions. I think the aesthetics of Tableau really help draw people in, and enable them to make new discoveries and better decisions.

VN: Who do you learn from in the Tableau community?

PG: The Tableau Community is really one of the best-selling points of the product. It’s such a friendly, welcoming bunch and I enjoy reading various blogs, which are quite diverse in content. I also love the way that Tableau appreciates and encourages the culture of knowledge sharing through things like the Zen Master program. And the Tableau Customer Conference of course is the highlight of the year. Your blog has also been hugely helpful (and fun).

VN: In your opinion what should we be mindful of in the BI space going forward?

PG: Specifically in relation to Tableau we should as a user community be willing to help keep the company moving in the right direction through open discussion and constructive criticism. For the BI industry in general, I think expanding the field beyond IT and involving people with other skills and views is the key to success. Lets make BI an enabler for creativity, not a barrier.

VN: Could you give me an interesting non-work fact about yourself?

IMG_0438PG: I’m currently getting into home brewing – there is a dry hoppy IPA in the fermenter right now.

VN: Thanks for your time Peter, see you soon.

PG: No problem, enjoyed it.

Hope you enjoyed our latest episode of the interview show that’s taking the internet by storm. I’d recommend that NBC, BBC or Al-Jazeera get in touch quickly as our rates are going to rise.

Regards, Paul

Mmmm doughnuts…

Hi there,

Recently I saw this cool viz from the awesome @jeweloree.

dashSo I thought I’d have a go at my own food related dashboard. They say that in London you’re never more than 10 feet from a rat. Well in the investment banking arena that also applies to doughnuts. They’re everywhere. A trip to the coffee point can end up adding an inch to your waistline. Luckily I’m quite fussy when it comes to doughnuts so unless there’s an apple cinnamon one there I can easily walk on by.

So exactly what goes into one of those bad boys? Well Krispy Kreme have a pretty cool website where they declare a lot of this information. That’s good of them I think. No-one pretends doughnuts are good for you but at least they’re not hiding what goes into their products. I like that approach. Allows the consumer to make, and take responsibility for their own actions, rather than the people who trough loads of fatty food and then complain that they “didn’t know it was bad for them”.

So I grabbed the nutritional info for their Xmas menu from the link at the foot of this page. I had to chuck it into a spreadsheet and reformat it a little, but the resulting data was easy to read with Tableau. They also had nice thumbnail pics of each doughnut that instantly gave me the idea of a lollypop chart using a custom shapes palette. I also added a reference line to indicate the value of the secondary element of a nutrient component. Shame that only applies to carbs and fats though.

So in a little more detail…

I grabbed the data from the Krispy Kreme website here. I had to transfer the pdf to a local spreadsheet but given the limited data set it didn’t take long.

parameterOnce the xls was connected I decided what measures to plot. I wanted the user to be able to change the chart focus to whatever nutrient they wanted so to do that I used a parameter. It looked like this (left).

calcfieldThat parameter had a corresponding calculated field to allow the selection to be plotted on the Columns shelf. I typically use a case statement to allow the selection. Not sure if this is the best way to do it but it seems to work well. And of course I had to “Show Parameter Control@ to allow it to be effective.

That all allowed me to easily plot a simple bar chart for the nutrient in question, selectable using a parameter.

ScreenShot

In order to represent the secondary nutrient for Carbs and Fat I created another calc field and plotted that using a reference line.

Shame that the other main measures didn’t have a corresponding secondary nutrient. I would have liked this to be consistent for all the selectable measures but hey ho.

shapesNext step was to give the lolly its pop. First off I grabbed all the thumbnail images of the doughnuts and chucked them in a shapes folder. Then it was a case of assigning the correct image to each value for the “Donut” measure. Once that was done I duplicated the measure on Columns and made the chart dual axis, and the shape to be the doughnut image. To add extra effect I made the width of the original bar thinner.

The doughnut selector was achieved by a simple chart showing only the images.

doughnuts

And the rankings by using the nifty Rank table calculation feature in 8.1. This is ace and saves so much time over earlier versions.

Then I pulled the whole lot into a dashboard and used the main doughnut selector as a filter. I’m pleased with the result, especially the use of the images which give the whole dash an element of visual appeal.

I was gonna do a timeline of when each product was released but I couldn’t find that data anywhere. Boo.

And here’s the result. Hope you like it. That White Chocolate & Almond one is a total bad-ass. Cookie Crunch also packs a carbohydrate punch but at least you get a bit of fibre in there.

dash

Cheers, Paul