6 Ways to Visualize Location-based Information from the Windows Azure Marketplace in Excel

Windows Azure is many things. It spans over Infrastructure as a Service (IaaS) and Platform as a Service (PaaS) and provides a variety of components to enable both developers and IT-Pros in the public cloud as well as in a hybrid scenario where you connect securely to on-premise databases, individual machines or entire networks. But Windows Azure also provides a Marketplacewhich allows you to trade applications, services and data. If you have data that you want to sell or share for free, the Windows Azure Marketplace is something you should consider and if you are in need for data for your research or analysis, chances are, that you find them in the Marketplace.

Note:you do not have to have a Windows Azure subscription to access data from the Windows Azure Marketplace. You can sign-up for free and check out some of the free data sets without any cost. All you need is a Microsoft Account (formerly known as Windows Live ID).

image

While it is often a developer who integrates an application with the Windows Azure Marketplace, Information Worker can also use familiar tools such as Excel to access information, pivot them around and visualize them in a variety of ways. In this overview we have a look at some of the options to visualize data from the Windows Azure Marketplace in a location context.

Office Apps

With Office 2013 and the new Office Appsit is now easier than ever to access data from the Windows Azure Marketplace. Bing Maps has been implemented as such an Office App and it snuggles nicely into Outlook and Excel 2013. In Outlook 2013 it will identify address strings and allow you to map the location directly in your email.

image

But back to the topic: to visualize data from the Windows Azure in Excel 2013, start by identifying a data set that you are interested in. For the first example we use a free offering from the European Environment Agency that provides insights into Greenhouse Gas Emissions.

image

Once you subscribed to the offering you can view the data directly in the marketplace by clicking the link use.

On this page you can manipulate the query and identify the Service Root URL as well as the Primary Account Keythat is assigned to your Windows Azure Marketplace account. You will need both of these information to access the data from Excel.

image

In Excel 2013 go to the Ribbon Data and select Get External Data => From Other Sources => From Windows Azure Marketplace.

image

In the Data Connection Wizard enter the Service Root URL which you identified in the Windows Azure Marketplace under Location of the data feed and the Primary Account Key under Log on credentials. Then click Next.

image

Select the data set you are interested in – in this example there is only one – and click Nextagain.

image

Now save the data connection on your local machine by clicking Finish.

image

You have several options to import the data. In this case we might want to pivot the data and therefore we select PivotTable Report.

image

Once you have pivoted the data into a structure that has both a location and at least one column with values that you want to visualize, select the Ribbon Insert and then Apps for Office. In the screenshot below you see that there are already 2 Apps for Office installed. If you have not yet installed the Bing Maps App for Office click on See All.

image

This will show a dialog with Apps for Office that you have registered for – no matter if they are already installed on your machine or not. If you have not yet registered the Bing Maps App for Office click on Find more apps in the Office Store.

image

Then browse the Office Storeand install the free Bing Maps App for Office.

image

Now that you have the Bing Maps App for Office installed you can select the data you want to visualize, Bing Maps will then geocode them in the background and show them as sized circles on the map.

image

The Bing Maps App for Office does provide other visualization options such as pie charts as well but it wouldn’t allow you to color code geographic areas or create heat- or density-maps. If you are specifically interested in color-coding US States, there is another free app in the Office Store that you could use. For this example we select the Crime Statisticsfor the US that Data.gov makes available through the Windows Azure Marketplace. As in the previous example we load this data set as a PivotTable report into Excel.

image

The data provides statistics for different types of crime on a city level.

image

For the purpose of this example we want to aggregate the data by state and sum up the total number of crimes in relation to the population. While we can easily pivot the data in a way that aggregates fields by state, the second requirement, i.e. a computed column that sums up the total number of crimes from the different categories and relates it to the population, goes beyond simple pivoting. There are certainly other ways to accomplish this but for the purpose of this example we’re going to do this directly in the data model. Excel 2013 intentionally hides the data model underneath the Pivot-Tables to reduce complexity but if you feel the need to change the data model manually you can do that too. To activate the tools that let you access the data model, click on File and then select Options.

image

In the Options dialog select Add-Ins and at the bottom select COM Add-ins from the drop-down list Manage. Then click Go.

image

Now activate the check-box for Microsoft Office PowerPivot for Excel 2013to enable an additional Ribbon that gives you access to the data model.

image

Once you confirmed your changes and exited the dialog, you have an additional Ribbon for the PivotTable Tools. Click on Manageto open a window that allows you to manipulate the data model.

image

Now we scroll to the right and add a formula that adds up the different categories, divides it by the population and multiplies by 1,000. The result is the total number of crimes per thousand population.

image

This new computed column appears now in your spreadsheet and you can pivot the data around until you have a flat structure that has one column for the state and another one for the total number of crimes per thousand population. Then activate the Insert Ribbon and select the Geographic Heat Map (developed by Keyur Patel) from the Apps for Office. If you haven’t installed it yet, refer to the previous example to learn how to get to the Office Store and install this free app.

image

The Geographic Heat Map adds initially a placeholder. Click on the icon in the top left corner to open the settings.

image

Now select the data and the color schema and set a title.

image

Once you click save, you will see the corresponding map.

image

PowerView

One of the powerful new features in Excel 2013 is PowerView. It has been around as a server-side component for a while and now makes its appearance on the desktop, loaded with a lot of features – including Bing Maps for the visualization of data in a location context. To show just how powerful it really is, we use an appropriately large data set from the Windows Azure Marketplace: more than 2.4 Million records of flight delays provided by OakLeaf Systems.

image

The data set is large but fairly simple. It contains the airport code as the geographic information as well as the various flights of US carriers with origin, destination, arrival- and departure-delay on a daily basis.

image

As in the previous examples we import the data into Excel as a PivotTable Report but instead of pivoting the data first, like we did in the previous examples, we can now insert a PowerView Reportand start creating our charts right there.

image

You will be amazed to see how powerful and responsive the report is despite the size of the data set.

image

MapCite

If you are not yet blessed with Excel 2013, you don’t have the option to use Office Apps or PowerView and access to the Windows Azure Marketplace does not come out-of-the-box either but there are still options. First you need to download the Excel Plug-In for the Windows Azure Marketplace. You will find the download link on the Windows Azure Marketplace by selecting Learn and then under Find and Discover.

image

Once installed you will notice an additional button in the DataRibbon. Sign in with your Microsoft Account to explore the data sets you have already subscribed to.

image

In this case we select Places.SG– a free data set with various categories of points of interest in Singapore – and for the purpose of this example we want to identify areas with the highest density of bars and pubs.

image

For the mapping we choose a free Excel Add-On that has been developed by MapCiteand uses Bing Maps for the mapping. The data in Places.SG already comes with geocodes (latitudes and longitudes) and is grouped into categories so we can easily pivot or just filter the data in a way that we show only those categories we are interested in.

MapCite comes with its own Ribbon. Click on Add Datato open a wizard that will guide you through the process of mapping your spreadsheet.

image

In this wizard you assign columns for latitudes and longitudes, labels, icons and optionally other properties.

image

Once added to the map you can change the visualization from individual points to Heatmapand you will easily identify the areas with the highest density of bars and pubs. Prost!

image

Layerscape

Layerscape provides an Excel Add-In to visualize your spreadsheets on Microsoft Research’s WorldWide Telescope. It supports time series and 2D as well as 3D visualizations for point data and polygons. For this example we use the free data set World Population Prospectsprovided by the United Nations. In order to use Layerscape with Excel you must install both the WorldWide Telescope and the Excel Plugin.

image

After importing the data into Excel – similar to the previous example – we pivot the data around, transpose columns to rows and merge with country-polygons as provided in the samples that come with the Excel Add-In. In the WorldWide Telescope Ribbon you can then open the Layer Manager in order to map the columns for the polygons, the time and the value you want to visualize to the objects that WorldWide Telescope expects. Once you mapped the columns click on View in WWT(make sure that WorldWide Telescope is already running in the background).

image

Now you can use the Time Scrubberin WorldWide Telescope to visualize the change of population over the time axis.

image

Esri Maps for Office

Esri Maps for Office is a commercial offering with a free 30 day trial. It integrates nicely into Excel and PowerPoint, provides a variety of base-maps and allows you to publish the data to ArcGIS Online (leveraging Windows Azure). From there you can publish the maps further to the web, integrate into SharePoint or view them in the free apps for your desktop or mobile devices.

For this example we’re using the Environmental Hazard Rank provided by Environmental Data Resources(EDR) through the Windows Azure Marketplace.

image

After loading the data from the Windows Azure Marketplace, we can map the Environmental Hazard Index or -Rank on a zip-code level with Esri Maps for Office and then publish from there into ArcGIS Online.

image

Once published, you can use ArcGIS Online as the hub and publish from there to the web, integrate into SharePoint, build your own applications around it or use the free apps for Windows, Windows Phone, Android (incl. Kindle Fire) or iOS to access the maps from any device.

image

Happy Mapping!

This entry was posted in Bing Maps, ESRI, Excel, MapCite, Marketplace, Office Apps, PowerView, Windows Azure and tagged , , , , , , , , , , , . Bookmark the permalink.

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