Familiar. Collaborative. Managed.
The Data Mashup contest deadline is being extended to October 5th. In addition, we are opening up the contest data sources to allow you to pull anything from the Windows Azure Marketplace that is free. Need help getting started? We put this blog post together to assist you in pulling the data as well as running you through an example submission.
There are many free sources you can pull from in Windows Azure Marketplace. Feel free to choose any data you find interesting! For this example, we’re going to use some data from Wolfram Alpha. You can see examples of some of the datasets Wolfram offers at http://www.wolframalpha.com/examples/. The example below will walk you through pulling the data with the Windows Azure Add In for Excel.
Some of the many options Wolfram Alpha offers include:
· Historical weather by city & year
· Unemployment rates
· Salary information
· Home value data
· Population estimates
· Crime data
· Nutritional information
For our example, we are hypothesizing that over time, an increase in unemployment will correlate to an increase in crime. We are going to pull crime rates and unemployment rates from 10 major US cities and the 2010 US population to compare against. Let’s get started.
1. Click the Data tab in Excel, then find and click the “Import data from DataMarket” button at the top right of the screen.
2. You will need to sign in with your Live account. If you don’t have one it will walk you through creating one.
3. Once you allow access from DataMarket, on the right hand side of the screen, find the little shopping cart icon and click that to browse datasets. This will take you to the Windows Azure Marketplace web portal.
4. Use the search box to find Wolfram Alpha.
5. Click on the link for Wolfram Alpha and subscribe to the data. Once you do this, head back to Excel.
6. Find the refresh button next to the shopping cart and click that. Your Wolfram data should now show up under Subscribed datasets.
7. Click the Import Data option to bring up the Query Builder.
8. In the query builder, type your query in the “equals” field. In this case we’re looking for the 2005 unemployment rate for Atlanta.
9. Click on “Import Data” once you enter in your query and it will pull the data into Excel.
10. We are going to use the mean unemployment rate from this data pull. Next we will do the same inquiry for Atlanta in 2009, then move on to the other cities until we have all the data we need. Next we pulled it into the table below.
11. Moving on to crime, we do an inquiry in the same manner until we were able to populate the table below.
12. Lastly, we pull the 2010 population for each city.
13. Repeat steps 7-9 until you have all the data you wish to query.
14. When we have all the appropriate data points, we calculate the changes from 2005-2009 for each category and organized them into tables.
15. Now we create a table in PowerPivot. Highlight the data in Excel and click “Create Linked Table” in the PowerPivot option in the ribbon.
16. This will open the PowerPivot window with the linked table.
17. Using PowerPivot, we created graphs by selecting the PivotTable option and building them out.
18. After refining, we generated the graphs below on the left. We also utilized Excel to create an additional visualization on the right side below.
Although this is a very basic example, we hope you found this walk-through to be helpful. Remember the contest has been extended to October 5 and is now open to any free data in the Windows Azure Marketplace, so start exploring and start visualizing!
Not available in your market.