#1 Brand New Tip of the Month from BI expert Peter Myers – Spatial Reporting with Power View

A new, and very welcome feature, in Power View is the map report. Available in both Excel 2013 and SharePoint 2013 (with SQL Server 2012 Reporting Services integration), Power View reports based on tabular data model can express analytic data as points on Bing Maps. By default, tooltips reveal context the and the numeric values plotted on the map – and this can be changed to add data labels adjacent to each point. Map backgrounds can also be configured to display road maps and aerial maps. Note that Bing Maps is a service hosted on the internet, so internet access is required to use this feature.

The fully exploit this capabilities the tabular data modeller will need to prepare, load and define the data model appropriately.

The simplest and fastest way to achieve this is to include a column (or columns) consisting of a geographic location. The location could be a continent, country, state or province, city or even a landmark. To render the location on the map, Bing Maps will perform a lookup in its database to retrieve the spatial coordinates. (If you have many data points that need to be plotted, you can often notice the incremental addition of each data point as it has been looked up and added to the map.) This lookup process could lead to inaccuracy when, for example, there are numerous cities of the same name. Think about the example of Richmond – is it the suburb of Melbourne? The regional town of New South Wales? Or the capital city of the US state of Virginia? Bing Maps is likely to resolve the spatial coordinates based on population or possibly even popularity based on search frequency. In the Richmond example, it does in fact resolve to the location of the city with that name – in the state of Virginia.

To ensure Bing Maps resolves the correct spatial coordinates it is a possible to include supplementary data, either in the column, or with additional columns. For example, the column value could be “Richmond, NSW”. Including additional columns is more ideal. It is achieved by adding columns that describe the location’s ancestors. In the Richmond example, this would be country and state. When a hierarchy is produced to support the navigation from country to state to city, and this hierarchy is used in the layout of the map, the ancestor members of the hierarchy are also passed to Bing Maps to help correctly resolve the spatial coordinates.

A new feature in tabular data model development allows for the categorization of columns. There are built-in categorizations to mark text columns as Continent, Country/Region, State or Province, County, City, Postal Code. These categorizations can also assist in improving accuracy of spatial coordinate lookups.

For absolute accuracy, the data modeller can include Latitude and Longitude columns consisting of numeric data, and more specifically the coordinates converted to decimal format. The values in these columns can be used to map locations with precision and do not require Bing Maps to resolve the spatial coordinates. This way the mapping accurate, and faster as no lookup is required. Of course, this will mean you will need to capture and store the coordinates of all of your locations. There are Latitude and Longitude categorizations that can be used to mark such columns.

An interesting additional benefit of storing latitude and longitude in the data model is that the coordinate values can be aggregated. It is therefore possible to use the average of latitude and longitude to plot a midpoint of numerous single locations, for example, all customers in a filtered group.

PowerView Mapping