Examining Geo Data with Excel 2013 Power View

I was first exposed to data visualization on maps while working on an anti-piracy project for a software company.  Our efforts included layering multiple levels of information both automated and manually collected onto Bing maps.  Seeing the collaboration of data points on the same map gave insight to the intensity and impact of digital piracy on both region/sub-region levels where KPIs could be tracked, but also the details at street level where piracy targets were operating.  This work was insightful, but also complicated- requiring a team of developers and analysts to make the custom code, Bing Maps service, and SSRS reports interact.

What was previously limited to custom development projects or expensive software packages is now included in Office 2013 Excel Power View. Geo visualization of data is an amazing way to find insights and meaning from information. These features are an awesome new tool in the “self-service” BI tool set available to the Office 2013 user.

In this blog posting I hope to share with you the basics of Power View Maps in Office 2013 and give you a kick-start to performing your own geo-data analysis.

For this exercise I had some goals regarding the data: first, I wanted to use data that was readily available to everyone, second it should already include LAT/Long data (Power View Maps can integrate with Bing web services to produce lat/long info for you, but that is not covered here), and finally it should include some meta-information in order to highlight the grouping and filtering capabilities in the Power View maps.  I settled on Topical Gazetters data from the US Board on Geographic Names located here.  I selected the Concise Features file as it is easy to work with and has plenty of detail for this example.

After downloading this file go ahead and import it into Excel.  I chose to import all columns and used the default ‘general’ formatting for each.  After importing, I deleted certain columns I didn’t want – my end result spreadsheet included the following columns:

FEATURE_ID

FEATURE_NAME

FEATURE_CLASS

STATE_ALPHA

COUNTY_NAME

PRIM_LAT_DEC

PRIM_LONG_DEC

ELEVATION

MAP_NAME

Next, you’ll need to determine and select the data you want to include in your Power View analysis.  The source data worksheet includes some 39 thousand rows. Knowing the limits of the RAM (4GB) on my laptop (Power View is dependent on your machine’s ram for performance) I selected the 9 columns and the Alaska rows only – So my range was (1892R X 9C).

Now with my range Selected, I chose the ‘INSERT’ tab in excel and click the ‘Power View’ button.

This will load a new Power View report page with your selected columns visible in a default report in the body of the page.

I like to add my maps to the report body as a separate report allowing you to have interaction between the report body and the Map.  So to add a separate map report, I’m going to drag some fields into the report area.

So I grab the FEATURE_NAME field and drag it on to the body of the report page.

I now have a report box with FEATURE_NAMES listed in the report body.

Next step is to turn this list report into a Map visualization. To do so, with the new list report selected, I click the Map icon from the DESIGN tab.

This will transform the list report into a Map.  However there is an obvious need for some report resizing so we can see more of the map on the page.  To adjust, I grab the bottom edge of the top list report and move it up, shrinking it to about an inch tall.  Next , I grab the corners of my new map report and enlarge it on my report body area.  The end results look like this –

Now we’ll focus on plotting our points correctly on our map. To do so, we’ll need to utilize the report field controls on the right hand side of your screen.  With the Map report selected (your ‘Active’ report will dictate what fields and controls you have available), move the FEATURE_NAME field into the ‘LOCATIONS’ area, and PRIM_LONG_DEC into ‘LONGITUDE’ and PRIM_LAT_DEC into ‘LATITUDE’.  I also removed FEATURE_NAME from the ‘Color’ field as I want to use something else there later.

Plotted map locations should now appear in Alaska.  Navigate on your map with your mouse and zoom in using the plus and minus icons in the right corner of the map (or your mouse scroll wheel).

Notice when you click a marker (circle) on your map, it selects the associated data in the top report.  The Map body and top report can interrelate.  This is powerful feature to highlight – as you can have multiple reports and charts directly relating to the content on your map that update in coordination with the details on your map.

Meta data allows you to group filter and define how your data points appear on your map.  One column of metadata in our set is FEATURE_CLASS which defines the type (summit, glacier, river, populated area etc,) that we are looking at.  Let’s display these different feature classes by color. To do so, simply pull the FEATURE_CLASS field into the ‘COLOR’ field.

Next let’s add a filter to reduce the plotted points on our map.

I drag the FEATURE_CLASS field into the Filters area and check ‘Glacier’ and ‘Summit’.  Note I’m using the VIEW area as I want my filter to update all reports on my page not just the Map.

Here are my filtered results-

Now back to that metadata. Next I pull the ELEVATION field into my filter area.  Power View interprets the data type and automatically adds the filter as a slider.  Now manipulate the slider, and note how both the map and the associated top report update as I move the ELEVATION slider.

Now to see my plotted points sized relatively by Elevation, I add the ‘ELEVATION’ field to the SIZE field.

So the map is looking good, but now I want to adjust the top report to make it more useful.  I click to select and make ‘active’ the top report body.  Now on the POWER VIEW FIELDS on the left I check and un-check fields I want shown in the top report- I finish showing only FEATURE_NAME and ELEVATION.

My results appear pretty plain, but we want to add some additional visualization, so I go back to the DESIGN tab and chose ‘Column Chart’.

Now in the Columns Chart, I want to sort by Elevation instead of Alphabetical. To do so I hover in the upper left corner and select ELEVATION in the down arrow.

I do a bit more re-sizing, title clean up, and change the map background and here are my results.

As you can see, in just a few minutes with Office 2013 a user can create compelling Map visualizations providing geo insight to their basic data.

This is a simple example using elevation information, but translated to the business world you can easily use the same methods to visualize your critical business metrics- whether they be sales per store, activations per city, piracy counts per country, or advertising impressions.

Extending this example, some other visualizations you can try include using count of FEATURE_CLASS in the SIZE field to get aggregates by type. In this case, you’ll get count of features by County.

As you can see, the features in Power View Maps allow for a high degree of experimentation all without coding!  I hope this unlocks some of the geo data within your organizations and allows you to visualize things in a whole new way.

West Monroe Partners can help you optimize your BI strategy, create a plan for managing your data, and develop the user tools and data infrastructure that will drive higher levels of business performance. Click here to contact us today.

Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons