3D Geospatial Visualization Using GeoFlow Slides

Tonight I had the opportunity to give a GeoFlow presentation titled 3D Geospatial Visualization Using GeoFlow to OKCSQL. Thanks to all who attended for some great discussion. The slides are available here.

Abstract:

Learn what GeoFlow is and how to use it for 3D visualization of data that can be time-stamped. You can create static charts or interactive animated tours. GeoFlow is one of Microsoft’s latest releases in the BI spectrum.

Analyzing Against Weather Data using GeoFlow

I had been wondering what would be a good use of multiple layers within GeoFlow. Today I finally realized that they could be used when analyzing a measure against weather temperatures. For example, you might want to analyze sales revenue at stores against the local temperature. In my example below, I used real weather data against fake customer count data for the first week in May.

The following are the key points that I followed:

1) I selected City and State for the Geography and clicked Map It

2) I then clicked the check boxes for Mean Temp and Date. Next I selected HeatMap as the Chart Type. For the Value I selected Mean Temp with No Aggregation. I used Date for the Time.

2) I clicked the Add Layer button in the ribbon. For the second layer, I selected Customer Count and Date. I selected Column as the Chart Type. For Height, I selected Customer Count with No Aggregation. I used Date for the Time.

3) I then performed other standard tasks like adding my title via text box and renaming my layers.

Then end result allowed me to compare customer counts against weather temperatures. Below are screenshots from the resulting GeoFlow tour.

Warmer weather had higher customer counts

 Colder Weather had lower customer counts

The real weather data came from an free on-line source. Below is the overall dataset that I used. I added in the State and Customer Count columns.

I'll be speaking at OKCSQL next week on GeoFlow. Now I have a great answer to "Why would you want to use two layers?"

North American SQL Saturdays in GeoFlow

I was asked earlier this week to break out my US SQL Saturdays GeoFlow chart by year. While I was at it, I added in the 5 events from Canada and Mexico. The results are below.

North American SQL Saturdays through April 2013

GeoFlow Tour VIDEO here


I thought this GeoFlow tour would be a good chance to show off various GeoFlow themes and chart types.

Zoom In on Oklahoma, Texas, and Louisiana with a different theme

Contiguous United States and Canada with Bubble Chart

Dataset Excerpt (blue fields were used by the GeoFlow Tour)

I had fun putting together these charts for Karla Landrum. During this process, however, I did run into some issues with GeoFlow.

GeoFlow Issue Encountered

I added the Event Type by Year and Country fields to my Excel table in order to do the breakdown by year and to include Canada and Mexico. Unfortunately, the GeoFlow tour was not acknowledging Country as a possible field to select as a geography field. Closing Excel and re-opening did not solve this problem. Trying to create a new GeoFlow Tour within the Excel spreadsheet did not work either. My solution ended up being to copy/paste the entire table into a brand new spreadsheet and create a new GeoFlow Tour. At that point Country became available as a Geography field.

Minus these kinds of issues, I've been enjoying working with GeoFlow. I would welcome the opportunity to hear your GeoFlow questions or hear about your GeoFlow experiences in the comments section.

GeoFlow Viewer Please

Over the past week or so, I've seen a lot of people ask the following:

How can I provide my GeoFlow Tour for consumption in

• PowerPoint
• SharePoint
• My website
• A tablet
• Any mobile device?

I think it's great that people are asking these questions. It suggests that people want to use GeoFlow. The problem, is that currently there isn't a way, that I know of, to present GeoFlow tours in those above formats minus a tablet running Windows 8 (non-RT) with Excel 2013 Professional or certain versions of Office 365.

There are a lot of discussions about Microsoft needing a mobile BI strategy, and I agree. When I was at Dallas Day of Dot Net in February, I heard a presenter talking about how the PC era is dying and mobile is taking over. From my perspective, software developers not learning about mobile software development may find themselves not nearly as marketable as they used to be. I say this as someone with a lot of software development industry experience and not someone looking in from the outside.

Now back to BI, and GeoFlow specifically, what are some options for making GeoFlow consumable from the aforementioned devices and software? Let's consider Office 365. Office Mobile Viewers are already supported on iPhone, Android and Windows phones. GeoFlow, however, is not.  

Here are some ideas for the future:

• GeoFlow mobile viewer that works with Office 365. Thanks to Jen Stirrup for bringing to our attention that Office 365 may be a great direction for Microsoft to go with their mobile BI strategy. Jen's feedback was provided via comment on a blog post here.

• GeoFlow SharePoint web part for displaying GeoFlow tours

• Save to Animation option within GeoFlow. This should provide a good option for presenting GeoFlow data within a PowerPoint Presentation.

There were several people on-line that also mentioned wanting to see GeoFlow merged in with Power View and then providing a Power View Viewer. At least one person mentioned wanting to be able to build GeoFlow tours using mobile. The primary message, though, was GeoFlow consumption beyond Excel 2013 on a PC. Hopefully, Microsoft will be able to take some of our GeoFlow ideas and put them into reality.

Using GeoFlow to create SQL Saturday Emergence Video

On Thursday evening, I downloaded the Community Preview of GeoFlow. I just had to try out GeoFlow the first day it was available. That resulted in me creating a map of the past 1.5 years of U.S. based SQL Saturdays and tweeting a picture of that map here.

This afternoon my goal was to make a video showing the emergence of all U.S. based SQL Saturdays which may be seen here.

GeoFlow is a free add-on to Excel 2013. You'll need Office Professional Plus 2013, Office 365 ProPlus, or the Midsize, E3, or E4 versions of Office 365.

I liked how easy GeoFlow was to start using once I had installed both Excel 2013 and GeoFlow. I did, however, have an initial issue of the GeoFlow button being greyed out. This appears to have been an issue for some others, as well. I've heard at least one person say that disabling and re-enabling the add-on worked for them. In my case I un-installed my no longer needed Office 2007, and that fixed the issue for me.

In order to use GeoFlow you'll need to put data into an Excel table and then select Insert--> Map-->Launch GeoFlow-->New Tour

Much of what you need to know to get started may be found by downloading the sample Excel data sets available on the site containing the Community Preview. The sample data set I downloaded included instructions on how to build an initial tour.

Having worked with the tool beyond those tutorials, though, I have found GeoFlow powerful but sometimes hard to navigate. For example, the below option to set the time length I found by clicking Play Tour and then exiting the tour. There's probably an easier way to find the setting, but I wasn't able to quickly find it. (Update: To get to the below setting, with Tour Editor on, click on the Scene and then click on the cog/wheel icon on the scene.)

The following items are things I would like to see in a future version, assuming they are not hidden somewhere in the CP. (Update: Added a few items and clarified a few others.)

• Capability to align the Title text in a textbox.
• Capability to change the colors being used for items in a legend, minus changing Themes. For example, if I have two event types like SQL Saturday and SQL Rally, I would like to be able to pick the color attached to each item.
• Provide an optional window for a second portion of the map, like for Hawaii and Alaska which do not show up in a zoomed in map of the U.S. (In my video I scrolled the map over to Hawaii at the end)
• Capability to save the animation of a tour to video file format. I used Camtasia to screen grab my tour's video.
• Capability to embed an actual interactive GeoFlow tour into a PowerPoint presentation. Even if the machine had to have Excel 2013, this would be nice.
• An option for the values in the optional 2D chart to grow as time passes in the animation. Currently, the values appear static in the 2D chart.
• An optional 2D rolling total for the measurement regardless of geo-spatial location. For example, if 67 SQL Saturdays have appeared by date X, then 67 would show as the total.
   

Below are a few of the things I really like.

Once I went through one of the tutorials, it was very easy to get my first map screenshot ready. I had one tweeted on day 1.

When playing a tour, you can stop it at any time and hover over an item in the map to see the data as of the point in time when the video was stopped.  This screenshot was from me stopping at the 10/27/2008 point in time. Orlando has had several more SQL Saturdays since October 2008.

Overall I've enjoyed trying out GeoFlow and look forward to seeing more of GeoFlow's potential.

SQL Server vs. Oracle Differences Part 2 (Object Naming Differences)

There are a couple of key object naming differences that I noticed when working with Oracle for the first time. The first is the max character difference. In SQL Server, an identifier (table name, view name, stored procedure name, etc.) can have a max of 128 characters. In Oracle, the max length is 30 characters, except for a few cases. Coming from SQL Server, the 30-character restriction in Oracle was quite noticeable. I especially noticed it when naming stored procedures. I've found myself having to abbreviate words when naming Oracle stored procedures instead of spelling words completely out.

Another difference with naming appears to be somewhat related to the GUI tools. With SQL Server, if you create table Orders, SQL Server Management Studio (SSMS) will display it as Orders. In Oracle, however, a table created as Orders will display in the GUI as ORDERS. This occurs because nonquoted identifiers, like Orders, are not case sensitive with Oracle. Oracle interprets them as uppercase. Thus you'll see the table you created as Orders listed as ORDERS when looking at the database through a GUI tool like Toad or SQL Developer.  Quoted identifiers, such as "Orders" are case sensitive, but then you are required to include the quotes when referencing the object. Since nonquoted identifiers tend to be more popular when naming common objects such as tables, views, stored procedures, etc. I found this important to note.

Oracle's upper-casing interpretation of nonquoted identifiers has resulted in a somewhat common practice of using underscores when naming objects. For example a SQL Server stored procedure SelectProductsByCategory might be named SELECT_PRODUCTS_BY_CATEGORY in Oracle. In this case we have a 27 character name, which is just barely under the 30 character limit. If we wanted to rename SelectProductsBySubCategory to SELECT_PRODUCTS_BY_SUB_CATEGORY we would be at 31 characters which is 1 beyond the max. The use of underscores, in this case, is what pushes us above the 30 character max.

Fortunately, from a querying perspective,  the casing of object names doesn't seem to be as relevant. That is, of course, dependent upon the settings. Assuming your SQL Server database is created with a Case Insensitive collation, SQL Server will not require specific casing when querying your objects. If you create table Orders, both "SELECT * FROM Orders" and "SELECT * FROM ORDERS" will be valid queries. With Oracle (using the default settings), "SELECT * FROM Orders"and "SELECT * FROM ORDERS" would both be valid, as well.

There are, of course, more details on SQL Server and Oracle naming rules than I've been able to put into my post. SQL Server's naming conventions are available here. Oracle's naming rules are available here.

SQL Server vs. Oracle Differences Part 1 (Cursors)

Since my first job out of college, where I was working with SQL Server 6.5, I've been told that cursors are BAD, BAD, BAD! Cursors are evil! I was, eventually told the reason to be performance. In SQL Server world, cursors are still considered evil in most situations. I would submit that T-SQL code with a cursor would be considered "bad code" by many in SQL Server world, assuming there wasn't a code comment explaining the use of cursor. I'm not saying this is right or wrong, I'm just submitting what I would expect would happen.

Now get ready for it, with Oracle PL/SQL, cursors are often a BEST PRACTICE! This was, probably, the hardest best practice difference for me to grasp. Cursors are very much accepted as good practice in Oracle world. You'll even see them used quite often in Steven Feuerstein's Oracle PL/SQL Best Practices book that I found to be very useful.