Some time ago Data Explorer, an add-in for Excel 2010 or 2013, was released in Public Preview. In short, it is a self-service ETL tool which is in my opinion a great tool for quickly searching data sets.

Recently, I had to create a demo for showcasing PowerPivot and Power View in Office 2013. As with all demo’s, the hardest part is to figure out which compelling story you will tell. I had downloaded a publicly available Excel file with Olympic data from 1900 until 2008.

This is very useful for a demo, but I wanted to create some maps in Power View, so I also needed a list of countries and their location. And this is where Data Explorer came into the picture. One of the niftiest features of the product is to search online for datasets. In reality a lot of results come from Wikipedia, but we’re not picky.

As you can see in the first screenshot, countries are identified by their NOC code (National Olympic Committee) so the first thing we need to do is find a list of countries and the corresponding NOC codes. To make future matching a bit easier, I also include ISO codes.

With one click on the USE button, the entire dataset is imported into a new Excel sheet:

Next I import similar data sets containing list of countries with their location and also some lists with unemployment rates, population and gross national income. All I need to do some serious analyses on what are the key contributing factors in winning a gold medal at the Olympics.

After I imported everything into PowerPivot, I did some rudimentary data cleansing. The hardest part was to match countries from different lists. For example, is it North Korea or Democratic People's Republic of Korea? Finally I have the following model:

Now I can build some nice Power View reports directly in Excel 2013 and my demo is ready:

The point of this blog post is that it’s fairly easy to create a demo data set using Data Explorer; it took me a bit more than one hour to create this model. However, Data Explorer is more than just an online search tool for data sets, so if you want to learn more about this tool check out the following resources:

Data Explorer hits full preview
Exploring Data Explorer
Access the Windows Azure Marketplace from Data Explorer
Installing Data Explorer Preview & Demo with IMDB Data
Calling A Web Service From Data Explorer, Part 1
Finding Shakespeare’s Favourite Words With Data Explorer