Speaking on Power Map at SQL Saturday Oklahoma City

I'm excited to see #SQLFamily and learn a ton while at SQL Saturday Oklahoma City this Saturday, August 24th. I'm also quite looking forward to a full day of Power Pivot immersion on Friday from Bill Pearson's Practical Self-Service BI with PowerPivot for Excel pre-con. You can register for the pre-con here. You can register for SQL Saturday Oklahoma City here.

I'll also be giving an updated version of my 3D Geospatial Visualization Using Power Map presentation. This new version includes a quick overview of datums, plus a high level overview of geospatial reporting in SSRS and Power View. Then we'll deep dive into Power Map. I hope to see you guys there!

Do you know the Datum of your Latitude and Longitude?

Last month I had the opportunity to present GeoFlow, now called Power Map, to the GIS team at my employer. Less than one minute into the live demo, I heard something like "That looks like a Mercator projection expecting a WGS84 Datum." Inside my head I was thinking "a what?"

I was not familiar with either map projections or datums. As datums are important when using latitude and longitude data, I'd like to share some of what I've learned about the topic since that presentation. The concept of datums can be quite large, so I'll just focus on what matters most to those of us using tools like Power Map and Power View. Those tools use Bing Maps for their geocoding. The primary point to make is that Bing Maps expects a WGS84 datum.

If your latitude and longitude coordinates do not correspond to the WGS84 datum, then your geographic points might not display in the proper location in Bing Maps. Let's first cover some basic points about datums.

Datum

• Latitude and longitude always have a corresponding datum, such as WGS84 or NAD27

• The latitude and longitude coordinates for your current position may vary across datums

• Sometimes the difference may be less than a few hundred feet, but it may be much more drastic. WGS84 and NAD83 are nearly identical. The difference between NAD83 and NAD27 varies dependent upon your location within North America. If you're on the U.S. West Coast, the difference between NAD83 and NAD27 could be more than 300 feet. Further East the difference might be less than 100 feet

• Map projections correspond to a datum

• When gathering latitude / longitude data for geographic reporting, knowing the datum is important, as you may need to convert your coordinates to the proper datum before using them

Let's look at a basic example using lat/long coordinates for the Golden Gate Bridge.

Golden Gate Bridge Example

The following latitude and longitude coordinates are both valid for the Golden Gate Bridge:

37.819721, -122.478615 in WGS84
37.8197926, -122.4775316 in NAD27

Of the two, only the WGS84 coordinates will display on the Golden Gate Bridge in either Power Map or Power View.

The NAD27 coordinates do not display on the bridge

 The WGS84 coordinates properly display on the bridge

 Here's a comparison of the same coordinates in Power View

The above example provides a scenario where users are likely to notice the geographic point is not correct. That may cause them to not trust the data.

In some cases the difference may not be enough for users to notice. For example, I tried using NAD27 coordinates for several Smithsonian museums in Power Map. The tool displayed the column chart on the correct museum in most cases. The reason being that the museums are fairly large and the shift between WGS84 and NAD27 on the East Coast isn't as big as it is on the West Coast. There were a few Smithsonian locations, though, where the incorrect placement was noticeable.

Thus, when gathering and eventually using latitude and longitude coordinates for geographic reporting, it's important to make sure we know the corresponding datum.

For those interested in more advanced information on datums, you can find more information from Wikipedia here.

My Journey in the SQL Community / #SQLFamily

Last week two co-workers and I registered for our first PASS Summit!!! 

I've been heavily involved with the regional #sqlfamily for 3 years, but this is probably the first PASS event for either of my co-workers to attend. In order to prepare them for Summit I've been telling them about a few of my great #sqlfamily experiences. Talking to them got me thinking about my recent journey in becoming part of #sqlfamily and lead to this blog post.

SQL Saturday Dallas

In early 2010 I received an e-mail promoting a speakers call for an event I'd never heard of before. It was promoting a SQL Saturday, specifically SQL Saturday #35 in Dallas. Submitting to speak at that event, I believe was one of the best decisions of my professional career. Although speaking was a positive experience, the relationships I started building are what I both remember and value most. At the Speaker's Dinner and other portions of SQL Saturday Dallas, I met for the first time many of the following individuals: Sri Sridharan, Tim Mitchell, Ryan Adams, Sean McCown, Jen McCown, Bryan Smith (MS), Jeremy Marx, and many other great members of the SQL Community. Relationships quickly grew beyond the event. Within a few months Tim Mitchell was giving a remote presentation to OKCSQL speaking on "SSIS Scripting" and Bryan was presenting to OKCSQL in person. Contact with others from the SQL Community was continuing for various reasons, as well, including when Sri was asking for help with promoting Dallas’s bid to host SQL Rally.

My next SQL Saturday to attend was SQL Saturday #63 in Dallas in April 2011. As I was not actively presenting at the time, I chose not to submit to speak. Since I was a chapter leader, though, Sri still decided to invite me to the Speakers Dinner the night before the event. Side note, we had an OKCSQL officer speaking, as Rob Sullivan was a first time SQL Saturday presenter at that event. Multiple times during the Speakers Dinner, Rob and I heard, "When is Oklahoma City going to have their first SQL Saturday?" The answer was among the lines of "we'd need another OKC person to chair it, as Rob and I are the only two OKCSQL officers and don't have the capacity to take on running an event all by ourselves." Fast forward to the next day, someone introduced me to MattBrimer, also from Oklahoma. Matt Brimer was telling people that he wanted to help host a SQL Saturday Oklahoma City. 

SQL Saturday Oklahoma City 2011

Within a few months Matt was the newest OKCSQL officer and Event Chair for SQL Saturday #90 in Oklahoma City, with me as Speaker Chair, and a Rob Sullivan, Jamin Mace, and Amanda Hardeman (now Harlin) also on the Event Committee. Relationships with Texas members of the #sqlfamily were absolutely critical to us being able to host a well-run 200 person event. When asked, Sri provided us his sources for anything from event insurance to event bags. Texas speakers, several MVPs, comprised 11/14 of our first event’s speakers. The night before the event, Ryan Adams and others were giving us advice and double-checking that we’d covered certain things. I remember the advice being great, and at least one item was changed last minute due to the conversations. The event itself took place on August 27th, 2011, less than 5 months after we had met Matt. The event was high energy with a lot of great sessions, and we got some really good feedback.

SQL Rally 

The following year SQL Rally 2012 took place in Dallas in May. My involvement started before the event, as I volunteered to assist with session selection. That was a great opportunity to be involved with some “behind the scenes” for an international event and gave me some great ideas for how to handle session selection for our next SQL Saturday Oklahoma City. I personally paid for all my SQL Rally expenses, and it was definitely worth attending! While at SQL Rally, I got to hear some great presentations by some people I’d never seen in person before like Adam Jorgensen, Devin Knight, Jen Stirrup, Vicky Harp, and Julie Koesmarno (@mssqlgirl). Although, I didn’t meet all of them in person, I remember a great long conversation that a couple of us had with Adam Jorgensen and later with Rob Farley. Lately I've been learning a lot from the the BI blogs of several of the speakers from SQL Rally.

SQL Saturday Oklahoma City 2012

SQL Saturday #125 in Oklahoma City took place in August 2012, with a lot of great speakers returning and some talented new ones coming in. As we were getting close to the event, I remembered thinking "I can't wait to see Sean, Jen, Tim, Ryan, Mike, and the names rolled on." I also met Grant Fritchey, Carlos Bossy and others for the first time. The relationship building continued. On the relationships note, when 2 of the SQL Saturday OKC 2011 speakers didn’t make the 2012 cut due to the 90+ submissions we had for 24 sessions, I made sure that they were each invited to the Influencers’ Dinner. We started using that name, since we include Sponsors, event committee, speakers, former speakers, chapter leaders, and more. Relationships are critical, and we don’t want to exclude someone passionate about SQL because they happened to not have time to commit to prepping a session or didn’t get selected to speak.

This past Monday evening I was on a Google+ Hangout with TulsaSQL President Jeremy Marx. He’s the Speaker Chair, under my mentorship, this year for SQL Saturday #223 in Oklahoma City to take place on Saturday, August 24^th. We were talking about final session selections for the event. I can’t believe it’s almost time for Oklahoma City’s 3^rd SQL Saturday.  It was just over two years ago that I met Matt Brimer, and around three years ago that I attended my first SQL Saturday. Several people that I met at Dallas’s first SQL Saturday will be speaking at their 3^rd SQL Saturday Oklahoma City. Their continued support has been amazing.

Although, my #sqlfamly involvement started to sky-rocket with my attendance at SQL Saturday Dallas, there are some key relationships I made before then. I met Greg Galloway at the Microsoft BI Conference in 2007. We had some e-mail conversations where he helped me out with some SSAS processing strategies. I was quite the SSAS newbie at the time! We lost contact at some point, but then through his connection to Tim Mitchell, Greg ended up speaking at Oklahoma City’s first SQL Saturday.  I met Mike Hotek in 2009 when he was consulting at my then employer. Mike helped OKCSQL start strong by being our speaker for the first 3 OKCSQL meetings, which were averaging around 25 attendees. That was just our first great experience with Mike and his SQL Community involvement. Of course, I can’t forget Rob Sullivan with whom I co-founded OKCSQL back in April 2009. I met Rob a couple of years earlier in the Oklahoma City .Net Developers Group. In 2008 he was Sr. Vice President during my one year term as President of the OKC.Net group.

As I type this blog post, I keep thinking of so many more people I’ve met just in the past 3 years like BillPearson, Tim Costello, and our newest OKCSQL officer Allen Smith. There are too many to mention in one blog post.

I also keep thinking back to SQL Rally 2012, when Sri was passing out #sqlfamily stickers. This is a great family to be a part of and I can’t wait to build even more relationships and help others join this great #sqlfamily.

Below are some of my favorite #sqlfamily photos.

SQL Saturday Oklahoma City 2011

Most of the TX speakers and crew. Top row: Sri Sridharan, Ryan Adams, Shawn Weisfeld, Russel Loski, and Paul Hunter. Bottom row: Tim Mitchell, Greg Galloway, Jen McCown, and Sean McCown.

Speaker Paul Hunter with Rob Sullivan (event committee)

 Jeff Blankenbiller (my husband) and Sean McCown (Jen's husband)

 Matt Brimer and I on stage together

Event Committee on stage together (Matt's wife, Matt Brimer, Jamin Mace, Rob Sullivan, Amanda Hardeman, and I)

SQL Rally 2012

 With Julie Koesmarno (@mssqlgirl on the right) after her awesome session! I've been reading her blog lately to learn more about GeoFlow. I've been selected to speak on that topic at SQL Saturday Oklahoma City 2013

@VickyHarp rocking it with Karaoke at a SQL Rally 2012 Party

 

Future OKCSQL Officer Allen Smith on stage as a volunteer with presenters Adam Jorgensen and Devin Knight

SQL Saturday Oklahoma City 2012

Grant Fritchey and Jen McCown

Grant Fritchey, Tim Mitchell, Ryan Adams, Carlos Bossy, and Jeff Blankenbiller hanging out the night before

More photos from SQL Saturday Oklahoma City 2011 available at
http://www.flickr.com/photos/timdmitchell/sets/72157627532203718/ thanks to Tim Mitchell.

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.

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.