Power Map (GeoFlow) for Excel - 3D Geographic Data Visualisation

Update: Microsoft have done a renaming job on some of their products, so this blog needed a touch up. GeoFlow is now called Power Map!

So on we continue …

I have to say when I saw the Power Map (GeoFlow) Excel addin online I thought it looked fab and couldn’t wait to have a play – 3D data visualised onto Bing Maps!

All too often, with Microsoft products, there is a wealth of examples and comment with US or North American data, but the remainder of the world gets a bit ignored. Normally this doesn’t matter, but with regard to geographic data, this is especially important and the differences in data quality or effectiveness can be significant.

This being said, this blog is written from a UK perspective with mainly UK data, but also includes some EU data.

This is also not intended to be a walk through starting from the basics then moving onto higher concepts, it is a dip in to some things I found interesting. A thorough look at the functionality can be found here.

Requirements

You need to be using Excel 2013 on Windows 7, 8 or Server 2008 R2.

Power Map (GeoFlow) needs an internet connection as it needs a live connection to Bing maps.

The data that feeds Power Map needs to be in an Excel table or in a PowerPivot Data Model. It is best to use unaggregated data (in usual data warehousing methodology), or if you have large volumes of data then you may want to aggregate the data to the lowest grain that you want to investigate, that way you will not lose any detail in the data.

Geographic Aspect

To make any use of Power Map (it may seem obvious) but you need some geographic aspect to your data, otherwise, frankly there is no point. The following are able to be mapped to Bing Maps.

  • Latitude/Longitude (decimal formatted)
  • Street Address
  • City
  • County
  • State / Province
  • Zip Code / Post Code
  • Country

Data Preparation

So bearing in mind you can’t connect Power Map directly to remote sources, you need to collect your data by an appropriate SQL query to your data source, which can then be refreshed easily with the most recent data.

My test base was 300,000 transactions of fact data which I joined to customer and product dimensions. The geographic information was taken from the customer dimension and was their home address. A standard Excel (2013) file of the 300k records in a PowerPivot model was created. This took up about 25Mb on disk, not an optimal PowerPivot model by any means!

Viewing Your Data

Power Map stores it’s visualisations in tours, within which there are a number of scenes. You can control the transition between scenes, what happens within a scene while you are there and how long each scene lasts.

Initially you will have an empty tour and a default scene, you will then need to map some geographic variables.

GeoFlow GeoMapping

Once you choose ‘Map it’, Power Map will go ahead and map your data. Large datasets mapped to street or postcode level will take a while. My i5, 8Gb RAM laptop took about 10 minutes to display my data at postcode level. Once the data is mapped, then moving around using the arrow keys / zoom controls, then there is little or no delay.

Geocoding Alerts

This is probably an opportune time to mention the ‘geocoding alerts’ which are mapping alerts due to vagueness in your data. In the example above, there are 61% of country entries mapped with high confidence. If you also maps other geographic column data, it will use that to try and better improve the percentage of high confidence results.

Looking at my postcode data, I found a large number of occasions where in my data (which was mapped to a 97% confidence), the country was ‘UK’ and the postcode (zip code) was a reasonable UK postcode, but it wasn’t mapped. The less then helpful message ‘No Resolution for this address’ was shown. In the sample shown here, Google maps could resolve most of these just on postcode, so this may be a lack in Bing Maps.

GeoFlow Geocoding Alerts

I was interested to find data on my town, ‘Tunbridge Wells’, which Bing Maps seems to know as ‘Royal Tunbridge Wells’ so it wasn’t mapped, but once I changed the underlying data to include the ‘Royal’, then these were mapped also.

Data Charting Options

The three charting types are column, bubble and heat map.

GeoFlow UKSalesByPostcodeColumn

I tend to find the bubble a bit useless unless you are looking at fairly aggregated data, as if not then the bubbles overlap and you just get a big blob. A curiosity with heat map is as you zoom in and out, the heat map changes density, which I didn’t expect, rather, I expected to see the same view, but just more detail.

Power Map only does aggregations on sum, min, max, average, count, none. So if you want to do something more complex like median, mode on the value field, then it’s not possible. I guess creating a SQL query or a PowerPivot which contains the median or mode would get around that issue.

The category aspect is interesting. Power Map automatically converts bubble charts into pie charts when a category is added.

GeoFlow BubblesByGender

The most potentially useful of the category displays is the clustered column option. You need to be at quite high level data, but it is a case of using the correct display tool for the correct summary level of data.

GeoFlow ColumnsByGender

Time Visualisation

There is quite a neat time visualization tool that allows you to observe your data over time, which is pretty, but I’m not sure that it is a useful analytical tool. With the right type of data, I imagine that it could show some interesting things.

Saving Power Map

There is no save button in Power Map, but what you can do, is to save a ‘Scene’ which is a single view of Power Map data, and ‘Tours’ which is a number of Scenes. Click ‘Add Scene’ to add a scene to a tour. The concept is very Powerpoint-like, and you are able to change transitions (what happens between scenes) and Effects (what happens within a scene). You can add themes, which provides various backgrounds, legends and labels.

So, whatever the current ‘state’ of a particular Power Map tour is, will be the tour that is saved.

Summary

I have to say that Power Map / GeoFlow is an interesting visualisation tool for getting a feel for the shape of your data. You need to have fairly good quality geographic information to get the best value from your data. Overall it is an interesting offering from Microsoft; one which I hope is developed further.

Latest from this author