Power BI, Twitter and the Old Settlers Music Festival

Learning by Combining Multiple Interests:

Power BI, Social Media, Music and my Daughter

A few weeks ago I discovered the Power BI Solution Template for Twitter Campaign/Brand Management. I’ve played with the template a bunch since then, made some discoveries, dug a little deeper and put it to use on a few occasions. I’ve used it to review tweet patterns about #sqlpass, #mvpbuzz and most recently #osmf2017.  What is #osmf2017? I’m so glad you asked! (proud Mama Bear’s gotta share).

Last weekend my daughter Riley Curnutt (please go like her Facebook page) was one of the top 10 finalists in the Youth Competition at the Old Settler’s Music Festival in Driftwood, TX. The Festival is held adjacent to the world famous Salt Lick BBQ (which Bobby Flay says is “the best BBQ beef ribs he ever ate“). This year the lineup was fantastic and included some of my personal favorites: The Old 97s, Los Lobos, Sarah Jarosz, Gaelic Storm and many others. Riley competed as a singer/songwriter against 9 other young musicians (all under 18) and ended up taking 3rd place. Did I mention she’s only 13? I was really beside myself. Here is a video of her performance:

Anyway, when I was trying to come up with something fun to use as a hashtag to visualize using Power BI the thought crossed my mind to use the hashtag from the Festival, which was #osmf2017. So…that’s what I did. My daughter rolled her eyes when I told her I did this analysis – “Mom, your such a Nerd”.  Hopefully those of you who are fellow-nerds will find this stuff of interest! Let me go through the process step-by-step with you, (it’s not difficult), because I found there are several places that you can get mucked up and where things are a bit vague.

In order to make this work you will need Power BI Desktop, an Azure SQL Database and a Twitter account.

Read more Power BI, Twitter and the Old Settlers Music Festival

Do You Want to Build a Snowman?

I’ll be teaching a session on Spatial Data coming up this weekend at SQL Saturday, Washington DC.  In the recent past, at the end of my presentation we have a little fun and use spatial methods to render a drawing of Hillary and Donald using only T-SQL. Honestly, I’m getting rather tired of that story, and I’m ready to move on to something much more lighthearted.

So – I decided to replace that section of my presentation with a drawing of Olaf.

JPG that I started out with.
JPG that I started out with.

I used the technique created and outlined clearly by Alex Whittles in this blog post: http://www.purplefrogsystems.com/blog/2011/05/sql-server-art-using-spatial-data/

  1. Convert a lineart image to a bitmap
  2. Convert bitmap to a vector (vectormagic.com)
  3. Use Excel workbook for formatting
  4. Paste in SQL

I took the JPG above and opened it up in Adobe Photoshop and then converted and saved it as a BMP file. The result of that transformation left me with an image that looked like this (pretty much looks exactly the same…)

Bitmap Olaf

I used the vectormagic site to convert the BMP to an EPS. For some reason WordPress will not let me upload and share an EPS, so I changed the extension to TXT. I have attached that here so that you can see what it looks like. You’re going to open the EPS file in Notepad anyway in order to work with the contents.

Olaf EPS file contents: Olaf EPS File Contents

I took the contents of the EPS and dropped it into the excel workbook that Alex Whittles created. You can see how that looks here: Olaf Excel Workbook

I added an additional step of blending all of the vector lines together using the UnionAggregate spatial method. Then I combined the result with a Union of itself with a little buffering to make the drawing darker and more clear.  The resulting SQL statement that draws Olaf in SSMS is here (just rename to an .SQL extension):  Draw Olaf Using TSQL

The result should look something like this:

Spatial Olaf
Spatial Olaf

Now, as one person recently pointed out in a session I taught – “what is the business case for this?”. You know, I can’t think of one other than having fun while learning. I have used this technique to prank a few folks, emailing them a query to run that draws their own face. Nothing wrong with making learning fun if you ask me.  Happy coding.

Sources of Free Spatial Data

Some of the websites I’ve found that offer free Shapefiles. I’m going to be categorizing this further and adding to this collection over time.