Saturday, June 29, 2013

Displaying a 2 Level Hierarchy in GeoFlow

While working with GeoFlow, one concern that I quickly saw was that it didn't have the option to display data at a higher level of a geographical hierarchy and then drill down into a lower level. An example would be viewing data for a State/Province and then drilling down into City. While presenting GeoFlow to one of my employer's architects, she came up with a way to display two levels of a geographical hierarchy within GeoFlow, and she gave me permission to blog about it.

The core concept is to display each level of the geographical hierarchy in a different layer. Each layer would use a different chart type. In our example we'll use bubble chart type for State/Province and column bar for City. Our source data is SQL Saturday 2012 Oklahoma City registrations by State and City. The result will be the following:

GeoFlow: SQL Saturday Oklahoma City 2012 Registrations by State and by City

The bubble chart type represents the State data, and the column bars represent data by City.

After starting the GeoFlow tour creation, the following are key points to follow:

1) Select City and State for the Geography and click Map It

2) Click the check boxes for Attendee Type, Registration Count and Date Added. Chart Type of Column should default, if not, change the chart type to Column.

3) Rename the first layer to Registration by City

4) Click the Add Layer button in the ribbon. This layer will display data at the State/Province layer, thus, allowing us to see data at that level of the geographical hierarchy, as well.

5) Select State and click Map It

6) Click the check boxes for Registration Count and Date Added

7) Change Chart Type to Bubble

8) Remove the Layer's legend and rename the layer to Registration by State

For those interested, below are screenshots of the same data in PowerView.
PowerView: SQL Saturday Oklahoma City 2012 Registrations by State

PowerView: SQL Saturday Oklahoma City 2012 Registrations by Cities in Oklahoma (drilled down from Oklahoma)

While PowerView provides drill down capability via geographical hierarchy, using multiple layers allows us to display both levels of the hierarchy simultaneously within GeoFlow.

Update (tip from @mssqlgirl): In GeoFlow, if you have selected multiple geography fields, like City and State, within a layer, you can use the below option to switch back and forth between the geographical hierarchy levels, while in "Design" mode. I've yet to find a way to do this within "Play" mode for a tour.

No comments: