Speaking at PASS Summit 2013

I am delighted and honored to be selected as a Community speaker for a Regular Session at PASS Summit 2013. My session is “From Impaired to Insightful: Analysis with Geospatial Data“. This year this summit will be held in Charlotte, NC on Oct 15-18, 2013.

I look forward to meeting PASS Summit alumni, speakers and First Timers too! Is this your first visit to PASS Summit outside Seattle? Mine too! Good news, Melissa Coates (@SQLChick) has posted tips on how to get around Charlotte. Last but not least, congratulations to the other selected speakers at PASS Summit! Also special thank you to volunteers and organizers of PASS Summit!

PASS Summit 2013


Check out Coupon Code that may be available from your User Group. Malibu SQL Server User Group members should use the code mentioned here.

See you in October!

Insightful Geospatial Reporting

Thank you for those who attended my “From Impaired to Insightful Geospatial Reporting” session at beautiful Orange County SQL Saturday 201. We started early at 8:30AM on April 20, 2013 which did not deter a lot of you to come in and learn more about Geospatial Reporting.

Below are brief details on my session:

From Impaired to Insightful Geospatial Reporting

Slide Deck


It is expected that business reports be insightful and engaging. Region based reporting can be transformed from boring grids to visually interactive maps. This session will describe how to embark upon self-service and mobile BI solutions that are available within the Microsoft BI stack for analytical geospatial reporting. You will learn the basics of geographical data, including GIS, Longitude-Latitude coordinates and free form text. Using these different data types, we will discuss SSRS 2012, Power View and Excel 2013 tools that can leverage insightful analysis. Finally and most importantly, attending this talk will get you started in learning the essential techniques to deliver geospatial reports that tell meaningful stories for users.

Geospatial Reporting Tools by Maturity

From left to right: GeoFlow Public Preview for Excel 2013, Geographic Heat Map app for Excel 2013, Bing Map app for Excel 2013, Power View and SSRS.


Feel free to leave comments. This session is a brief summary of my view of the Geospatial reporting tools that are available on Microsoft platform.

Slide decks for other SQL Saturday 201 sessions can be downloaded here: http://www.sqlsaturday.com/201/schedule.aspx. The sessions marked with asterisk (*) on the schedule are those with downloadable materials.


SQL Saturday 211 – Geospatial Reporting

Thank you to those who attended my session at SQL Saturday Chicago (#SQLSat211) in Chicago on the weekend. I really appreciate the feedback from you.

The complete demo files are located here:

SQLSat211 – Analytic Cartography – Julie Koesmarno

In this presentation, I discussed from the mature Traditional BI tool such as SSRS to self service BI using Power View and GeoFlow. to mobile BI solution in a small scale such as Excel Apps: Heat Map and Bing Map. Using Earthquake incidents we learned what tools were best at, from data exploration to visually captivating view. Coupling the correct tool with a good story line, we can achieve effective geospatial reporting.

Special thank you to SQL Saturday 201 Organizers, Volunteers and attendees for making it happen!


Further Reading

Power View Map in Excel 2013 Preview  by Julie Koesmarno

Plan a Map Report (Report Builder and SSRS) by Technet Microsoft

Introduction to GIS by University of Missouri – St. Louis

Public Preview GeoFlow for Excel 2013 by Julie Koesmarno

Shape2SQL by Morten Nielsen

Spatial Data Series (SSRS) by Jeffrey Verheul

ArcGIS Tectonic Plate Lines by ArcGIS

SSRS Tips / Tricks by Jason Thomas

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:


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


GeoFlow Beta – Untapping 3D Visualization

At SQL Saturday 198, in Vancouver (Canada), I presented a Lightning Talk on GeoFlow. This is an add-in to Excel 2013 that is still in beta. In this session, I showcased a short tour of Earthquake incident data using Column Chart and  Heatmap with different location and zoom level.

The demo I used at SQL Saturday 198 is shown below.


A week later, at SQL Saturday 177 in Mountain View (USA), I decided to take up a challenge to the next level. I compared a few different geospatial analysis and visualization tools offered by Microsoft. In this session, I learned a lot from discussions with audience (That is the best session that  a speaker could ever ask for, IMHO). In fact in my session, Jason Horner (b | t) who is a geospatial expert, attended my session and made good points about GIS! I digressed.

This article summarizes what GeoFlow is / does and what it is not / does not based on my experience and a number of discussions with peers.

As I use the product more and more, I will endeavor to update the list. This is also a view of the current state of GeoFlow beta at the time of writing. Some of the items in list may change due to improvement of the product or as I learn more about it.


What GeoFlow is / does:

  1. It supports 3D Visualization of geospatial data / analytical geography data. It aggregate values (e.g. sales volume) on categories (e.g. product categories) across geographical data.
  2. It claims to handle millions of rows – but this does not mean handling millions of unique points. When dealing with 548 number of close proximity Earthquake incidents, I can see that the points start flickering.
  3. It supports Column bar chart with a number of different shapes, as well as Bubble and HeatMap chart types.
  4. It takes textual address such as Street, City, Zip, County, State and Country.
  5. It also takes Longitude and Latitude points.
  6. It allows zooming in-out using Ctrl key and mouse wheel. With the keyboard you can use Ctrl key and + sign to zoom in or Ctrl key and – sign to zoom out.
  7. It has time play feature that is great to see growth over time. However, it is not always easy to analyze data at time play when there are large varieties of data movement. This can be addressed by slowing down and focusing on shorter time play.
  8. It uses Scene to capture Time play or a snapshot the geospatial visualization.
  9. It has Tour management tool to capture multiple scenes.
  10. It supports a number of pre-selected themes that shows geographical names, such as country name, country boundaries.
  11. It relies on Bing to render the geospatial information.
  12. It has Search functionality which will display and zoom in to the location specified. Searching some locations may be directing us to unintended location (this is normal / common). As an example, when searching “Silicon Valley” on my demo directed me to India (20.00565, 73.765862). 
  13. It supports multiple layer overlay which can be handy to analyze multiple data sets.
  14. Its HeatMap feature can represent intensity based on aggregation value such as volume of sales.
  15. It allows Annotation text boxes on the map.

What GeoFlow isn’t / doesn’t:

  1. It does not support SQL Server Geography data type.
  2. It does not support location hierarchy or aggregation level (unlike Power View, where you can double click on Country and it will go to more granular geographical level as long as the model has been setup properly).
  3. It does not work without internet connection.
  4. It does not have a “Z” index. Such information may be handy to map air traffic. This conversation came up during SQL Saturday 177 – my audience is super smart!
  5. It does not support elevation information. Another gem that came up at SQL Saturday 177.
  6. It does not have any tool to export to mp4 or other video file format. Although you can use screen capture software to save the video.
  7. It does not support Chloropleth map.
  8. Currently GeoFlow does not seem to support discrete GPU; some laptops, Lenovo and Sony VAIO require the discrete GPU to be disabled. This means that it may not be possible to show case GeoFlow on a larger HD display via HDMI. The issue with VAIO laptop has been logged on Connect.
  9. It does not allow custom map background.
  10. Data refresh is not reflected on the map. A new separate workbook needs to be recreated. GeoFlow seems to import the data from the workbook once. This issue has been logged on Connect.
  11. It does not allow customizable tooltip; i.e. it only uses the information that is used for mapping. This suggestion has been logged on Connect.
  12. It does not support filtering on Category and Height values. The data must be prepared first for filtering prior to creating the first GeoFlow map due to the limitation on data refresh as outlined in number 10.
  13. GeoFlow is restricted to 1,048,575  rows (+ 1 table heading row) to be precise. This is because GeoFlow is based only on a table that can be created within Excel 2013.
Update (April 1, 2013): GeoFlow is limited to 1,048,575 rows if the data model is based on an Excel table. However, when PowerPivot is used as the data source (e.g. connecting to a SQL Server database via PowerPivot), larger volume of data can be accessed by GeoFlow.  The caveat is that it will not be able to refresh the data from SQL Server and that the data must be in the Data Model.

Other GeoFlow Examples

Jason Thomas has written a very good introduction article on GeoFlow. You can check it out here.

Paul Turley has just started a community project with GeoFlow. The project is a short world tour with visits to a few SQL Server community members and leaders around the globe talking about their SQL Server communities.


Wrap Up

Still in beta, GeoFlow add in for Excel 2013 has shown good potential for visualizing geospatial data in 3D.


Further Reading

Ability to customize Tooltip on Connect.

Geoflow Error when GPU is enabled on Sony VAIO laptop on Connect

Data refresh issue – GeoFlow does not reflect changes on Connect

Legend Box Disappears when reopening GeoFlow on Connect

GeoSpatial Analytics Using Microsoft BI at PASS Business Analytics Conference, April 10 – 12, 2013; presented by Jason Thomas

Analytic Cartography: Master Geospatial Reporting at SQL Saturday Chicago, April 13, 2013; presented by Julie Koesmarno


Starting 2013 with BI Talks

After the great PASS Summit 2012 finished, I have been counting down to the next SQL community events. Partly because I have learning withdrawals, and mostly because I miss my SQL Family!

Luckily, there are two SQL Saturdays scheduled near* my area. One of them is SQL Saturday #198 in Vancouver, Canada (16 Feb 2013) and another one is SQL Saturday #177 in Silicon Valley, US (23 Feb 2013). What I love the most about SQL Saturday is that it’s the best bang for your buck! If you look at the schedule for SQL Saturday #198 (Vancouver) here, you can immediately recognize many of the speakers are MVPs! Same goes to SQL Saturday #177 (Silicon Valley), the Partial List of Speakers shows that you will be in for a treat.


Also, if you are in the Business Analytics arena, the PASS BA Conference 2013 in Chicago is surely not to be missed. It’s a one stop shop for business analysts, data scientists, data architect and BA/BI practitioners. Not convinced yet? Twelve of these talks are going to be previewed for free on the upcoming 24 hours of PASS.  So check them out!


I am very delighted to also announce that I will be speaking at all those three events with the following topics:

1. Power View for Geographical Analysis for SQL Saturday #198.

2. The Essential 8: Narrative Reporting Techniques, with an expert BI architect and a friend, Josh Fennessy, for both SQL Saturday 198 and PASS BA Conference.

3. Analytic Cartography: Master Geospatial Reporting for SQL Saturday #177.


That’s my way of starting 2013!

Hope to see you, SQL Family, in some (or all!) of these events.


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.