Public Preview GeoFlow for Excel 2013

If you love geospatial data or geographical data, you should check the Public Preview of GeoFlow. It is an add in to Excel 2013 which renders 3D visualization of geographical and temporal data. The Beta version was announced late last year and I provided a review here. The Public Preview version is a much improved version, from performance, user interface, new features (such as chart) and many others. The Public Preview of GeoFlow was announced this morning and demoed at PASS BA Conference.

Without further ado, the download link is here:

http://www.microsoft.com/en-us/download/details.aspx?id=38395

Here is a teaser from me.

Stay tuned for more reviews from me!

If you are attending PASS BA Conference, check out Jason Thomas’ session (Geospatial Analysis Using Microsoft BI) on Friday (Apr 12, 2013). There may be a demo of GeoFlow there too.

I will also be doing Geospatial visualization talks at SQL Saturday 211 in Chicago on Apr 13, 2013 and SQL Saturday 201 in Orange County on Apr 20, 2013. I will show you how GeoFlow works live!

Official Links from Microsoft
GeoFlow Forum: Post and answer questions for the product team and community
GeoFlow Facebook Page: Share datasets, tours, and blogs
GeoFlow Product Page on Office.com: Download requirements, directs to Web Download page below
GeoFlow Download Page: Download the bits on Microsoft Download Center

Further Reading / Research

GeoFlow Beta - Untapping 3D Visualization by Julie Koesmarno
Analytic Cartography: Master Geospatial Reporting session at SQL Saturday 211 in Chicago
From Impaired To Insightful Geospatial Reporting session at SQL Saturday 201 in Orange County

 

Power View for Geographical Analysis with Excel 2013

Earlier this month, I have presented “Power View for Geographical Analysis” as part of my Pacific Northwest US tour. My apologies for posting this fairly late in the month, following the presentations, as I have a few other commitments earlier this month.

Without further ado, below are the sample files:

1. Earthquake Last 30 Days

Download Excel 2013 file.

The data is retrieved from the USGS website. This particular workbook was manually prepared (i.e. data copied and pasted from the webstite). The workbook has been cleansed and manipulated using Excel flash fill functionality and contains from 11 September 2012 to 11 October 2012.

The workbook does not retrieve the data from the USGS website automatically.

Earthquake Analysis - About 1000 miles radius of Portland

Earthquake Analysis - About 1000 miles radius of Portland

 

Earthquake Analysis - by the hour

Earthquake Analysis - by the hour

 

2. PASS Chapter and SQL Saturday Growth

Download Excel 2013 file.

Taking the data from 2 different sources, Dan English’s SQL Saturday workbook and Karla Lundrum’s report from PASS HQ, this demo shows how to combine them together.

There is also a section here that shows implications of incomplete Location hierarchy. In this example, Australia has complete Country – StateProv – City definition, whereby Singapore is missing StateProv information.

PASS Chapter and SQL Saturday Growth

PASS Chapter and SQL Saturday Growth

 

 

Data Integrity - Incomplete StateProv Level

Data Integrity - Incomplete StateProv Level

 

3. Internet Sales by Customer Occupation

Download Excel 2013 file.

A simple Adventure Works sample which shows well structured data model with good quality data. In this case we analyse Internet Sales by an attribute that doesn’t directly link to the sales, such as Customer Occupation.

Internet Sales by Customer Occupation

Internet Sales by Customer Occupation

 

Wrap up

These sample files contain static data – i.e they do not retrieve data from data sources auotomatically. Hence the data is as at the time of preparation. The aim of the sample files is to provide an overview of Geographical Analysis techniques using Power View in Excel 2013. As I have mentioned in my sessions, having good data model and data quality is important to build good interactive analysis using Power View. However, in real life, we may be far from a perfect data quality as shown in the Earthquake example and the PASS Growth example. However, with creativity we can generate something meaningful for analysis.

Hope you enjoy the sample files.

Note: the workbook should work with Office 2013 Preview, if it does not, please let me know.

My version of Excel 2013 is: Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.

 

 

Power View Map in Excel 2013 Preview

With Office 2013 preview launched in mid July 2012, there have been ubiquitous posts on the new Power View feature in Excel 2013. This is a very welcomed feature and I would sense that it will win Business adoption as an easy to use and “hopefully” affordable Self Service BI.  Here’s my thought on a fairly early version of Power View feature in Excel 2013 Preview.

Power View

Since the first time Power View introduced in SQL Server 2012 and made available in SharePoint 2010, I’ve always been fan of it. With most tools if you have analytical mind and are great with visualization / design, most reports you’ll create will be useful and used – and in my opinion – Power View is definitely one of these tools.

Using the Australia Broadband Guarantee data set provided from http://data.gov.au/dataset/australian-broadband-guarantee/, I am able to quickly and painlessly create an interesting Power View Map in Excel 2013 that I can analyze the investment the Australian government has made, at country level and down to specific Suburbs / Postcodes. The map feature is currently not available in SQL Server 2012 RTM with SharePoint 2010. I do hope that it will be introduced as a Service Pack 1 to SQL Server 2012.

Sample Data and Map

The Australian Broadband Guarantee (ABG) was an Australian Government initiative designed to help residential and small business premises access high-quality broadband services regardless of where they were located. The program targeted premises unable to access commercial metro-comparable services, particularly those living in remote parts of Australia

More information on the project: http://www.dbcde.gov.au/__data/assets/pdf_file/0017/128204/Australian-Broadband-Guarantee-2010-11-Guidelines-July-2010.pdf

The dataset I’m using has a very straight forward data structure. My motive here is to see which areas have the funding gone to? Does the amount of funding yield many connections? Is there any isolated area that gets funding?

Zoomed at the Nation level:

Australia Broadband Guarantee Map - Nation Level

 

Zoomed at a State level, with funding > $1 million filter: (hovering on one of the bubble gives me a descriptive tooltip)

Australia Broadband Guarantee in NSW

 

I can zoomed in further to the actual postcode as the granularity of the location is postcode, as shown below:

Australia Broadband Funding in Aarons Pass NSW

 

Advantages and Disadvantages of Using PowerView Map

Although it’s still at its early stage as Office 2013 is only released in a Preview version, I consider Power View Map feature has the following advantages and disadvantages.

Advantages of PowerView in Office 2013 preview as at the time this post is written:

1. It integrates with Excel. So it allows for rapid prototyping and development.

2. No knowledge of Power Pivot is required. Power View can be created based on a table on a worksheet and it automatically creates Power Pivot data based on the table. Knowing Power Pivot and being able to configure relationship and data categories, will help for performance (so it seems based on my testing).

3. The map feature recognises longitude and latitude pairs which are easily attainable. It can also use information such as Suburb and State.

4. It uses Bing Map as the map layer, so there is no need to acquire ESRI shape files or converting to Spatial data type.

5. Performance of the Power View map feature is amazing. When plotting over 2000 locations, I could barely notice the wait time.

6. Most importantly, the ability to interactively zoom in/out the map – from world to road level – is fantastic. This is something that is not available in Report Builder or SSRS yet.

 

Disadvantages of PowerView in Office 2013 preview as of the time this post is written:

1. No support for Spatial Data type. So there is no user defined zone that can be layered on top of the map.

2. Location, Longitude and Latitude values are all needed. Location could be PostCode, City/Suburb or Country.

3. The Location category is US Centric. This meaning that if State is used as the Power View Map Location, and there is a State with the same name or abbreviation in US, it would point to US. As an example, “WA” is Western Australia in Australia (http://binged.it/QzFaDn), but it is also Washington in US (http://binged.it/QzF7HG); which is over 9,000 miles away or over 14,000 kilometers.

4. Unable to connect directly to the Analysis Services; i.e. it is required to import the data into Power Pivot first or Excel table, then create a Power View map based on the imported data.

There are a few areas that still don’t work properly. The application crashed a few times on me when converting the initial pre-built table created by Power View to a map, as well as when the column detects text (larger size) instead of numbers for aggregation. I think in time, these issues will be ironed out.

Wrap Up

Office 2013 seems to offer a number of nice features, especially for self-service BI solutions using Excel 2013. I have no doubt that small businesses will start adopting Excel 2013 more and more in the near future, for data analytics using the new readily available Power View feature in Excel.

Further Reading

Excel 2013 Preview with PowerPivot and Power View by Kasper De Jonge

Microsoft Business Intelligence in Excel 2013, SharePoint 2013 and SQL Server 2012 SP1 by Microsoft BI Team

Building a Simple BI Solution in Excel 2013, Part 1 by Chris Webb

Shark Week Special: Mapping Shark Attacks (Drillable Maps and Hyperlinks) by Sean Boon