Share via

Self-Service Geographical Analysis using Excel 2013

  • Article

Power View was first introduced as part of SharePoint 2010 to work with SSAS Tabular Model (SQL Server 2012). It offers easy, interactive data exploration and visualization for self-service BI analysis. There are quite a number of articles about Power View including from the Technet website and also a video from Microsoft BI. This post summarizes the advantages of using Power View in Excel 2013 as I see them.

 

Affordable

As a consultant, I have been asked a few times if there is a possibility that Power View could live outside SharePoint. The good news began when Excel 2013 Preview was announced and Power View has also been included as part of Excel! In addition, the Map feature is now available in Power View via Excel 2013. 

 

Easy to Use

Power View in Excel 2013 understands geographical categories, as listed below. These geographical categories are available in Excel 2013 PowerPivot, as well as the SQL Server 2012 SP1 Tabular Model:

  • Address
  • City
  • Continent
  • Country/Region
  • County
  • Postal Code
  • State or Province
  • Place
  • Latitude – Longitude

The main advantage of the Power View map capability is that you can define drilldown levels for the interactive analysis. When you're mapping geographical data, Power View offers two field types: Location and Longitude/Latitude. There is no restriction or error checking on what you put in the Location field. When using PowerPivot in Excel 2013 or the SQL Server 2012 SP1 Tabular Model, ideally you would put geography columns in the Location field (except for Longitude and Latitude columns). The Longitude and Latitude columns can go to Power View's Longitude and Latitude fields, respectively. For example, if you have complete Country, State and City details on your dataset, you can add these three columns. The map will aggregate the information on the Country level first, which you can then drilldown to the State and then the City level.

Map fields/options in Power View

 

Flexible

When using Power View with PowerPivot as the data connection, you can connect to any data source that PowerPivot offers. Any Excel tables can also be added into the Data Model in the workbook, which in turn imports the data into PowerPivot. When the geographical columns are not accurate but the Longitude and Latitude columns are available, it would be best to not categorize the geographical column. In this scenario, I find that it's best to add the geographical columns to the Location field and also supply the Longitude and Latitude columns on the Longitude and Latitude fields. In my Earthquake example, I have a column called Area which could contain a mixture of landmark, city, state, region or country names. Because I have Longitude and Latitude values for each earthquake incident, I am able to map the Location column to the Location field and the Longitude - Latitude columns to the Longitude and Latitude fields. The workbook can be downloaded from here. Bing Mapswould interpret Area as best as it could as a generic location, but backed with the Longitude and Latitude details supplied.

 

Multi-purpose

Excel 2013 makes it easier to perform self-service activities. A lot of people are familiar with it and data analysts know Excel quite well. This in turn makes Power View a self-service BI tool and allows for easy prototyping. Self-service BI does not replace corporate BI. Quoting Chris Webb, " Even when self-service BI tools are used it’s widely accepted (even by Rob Collie) that you’ll only get good results if you have clean, well-modeled data – and that usually means some kind of data warehouse ." This is true for Power View.  From my experience in creating prototypes and having productionized Power View reports for my clients, I quickly agree with Chris's argument. Having good quality data sets does make a difference for creating good reports--Power View included. If your environment is corporate BI and has high quality data, Power View will shine in this environment also. If you're a one man (or woman) team, as I can be from time to time, Power View enables great data exploration and analysis as much as those with mature infrastructure.

 

Further reading

Power View Map for Excel 2013 Preview

Power View for Geographical Analysis with Excel 2013 (contains sample files)