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.
How To Prepare Your Azure Environment
The first thing I did was to create a new SQL Server in the Azure environment. I called my new SQL Server “mindytwitter”. I put it into a new Resource Group I created called (so clever) “mindytwitterRG”.
Next, I created an Azure SQL Database called “mindytwitterDB” on the SQL Server “mindytwitter”. This automatically got created in the same resource group as the SQL Server. This is important for simplified cleanup later on when you decide you’re done with this project. You just go delete the resource group and it flushes all the “things” you created down the toilet with it. (note to self, deleting resource groups can be VERY dangerous).
I didn’t want to wait a long time for things to run, so I picked a decent sized Azure DB (20 DTUs and 1GB). If I don’t decommission the Azure DB it will run me $30/month at this size. It’s easy to scale down or up however and you can go as low as about $5/month.
Once that’s up and running, you will want to go into the settings of your SQL Server and poke a hole in the firewall for the IP address you are currently working from. Do this by clicking on “Show Firewall Settings” as seen below.
Then click on “Add client IP”. You can name your IP address with a Rule Name. I called mine “WhereMindyIsToday”, who knows where I was, I travel way too much it seems. You can go in later and easily remove these.
Working With the Power BI Twitter Solution Template
Whew….OK, once that’s done, now you’ll need to go out to the following URL: https://powerbi.microsoft.com/en-us/solution-templates/brand-management-twitter/. Click on the “Install Now” button and off we go.
The first thing it’s going to want you to do is to connect to your Azure Tenant. This bit me about 10X before I finally realized — my Azure tenant is NOT my login. My Azure tenant is my Domain. **Good Grief** (shame face here) that was painful!!
When you are logged into Azure you can see what your Domain is by clicking on your Avatar. My login is my email address, but my Domain is this twisted up version of my full email all smashed together followed by .onmicrosoft.com. So there you go. (if one of you hacks me I’m warning you now, I know Keyser Söze)
And I stopped and questioned whether or not I should obfuscate this, but then I thought, anyone can figure this out, if they know my email account (hotmail) it’s like 90% likely that my Azure account is all of that smashed together followed by onmicrosoft.com. In fact, anyone with a hotmail our outlook email account you can pretty much guess their Azure domain, it’s not rocket science. So….I’m just going to show it. You could have figured it out anyway if you just took a second to think about it.
And now I’ve probably created a huge opportunity for **wanting** to hack into Azure for those of us who have either their email on hotmail or outlook, so you’re welcome.
So take that and type it into the area where the wizard is asking for your Azure Tenant.
Next, pick the Azure Subscription you want to use (if you have more than one), then hit Next.
Now you’re going to tell the wizard what SQL Server you want to connect to, remember we made a server called mindytwitter? So our SQL Server is going to be mindytwitter.database.windows.net. Put in your SQL User name and password you use to create objects and insert / read data from the server (most likely an administrative one). Then pick your Azure SQL Database. Ours in this case is mindytwitterdb. All pretty simple.
If all goes well, next up it will ask you to authorize the app to use your Twitter account. So here, enter your Twitter account login / pwd. My twitter handle is @sqlgirl.
Great. Now we’re through the hard part. (that wasn’t so hard, but the first few times know what exactly to enter in that Domain section threw me for a loop, and the application doesn’t “back up and forget” very well, you have to start over if things go south).
So, I mentioned I was going to pull data related to the hashtag used for the Old Settler’s Folk Festival. That hashtag is #osmf2017. You can put in more than one hashtag, and you can also put handles in at this point, or a mix/match of both. The kicker here is that this wizard is only going to pull 100 results, so the more pure you leave it, the higher sample per keyword you’re going to get. I’ve tried to change it to pull more than 100 but apparently that’s a limit from the Twitter API. I have it on my list to dig deeper into this little “problem” and how to get around it. I’ve still found the information interesting and it’s a fun way to learn some new Power BI skills playing with real data that’s about a subject that you find interesting. Here I’m putting the hashtag in that we’re playing with in this example:
Next, you can put in Twitter Handles. This is for the first 2 tabs reports of the dashboard. It helps track tweeting FROM or TO. I haven’t found this incredibly useful with samples of only 100 tweets so I’ve been leaving this part blank.
Same with the next screen…”Scale with Analysis Services”, with only 100 tweets I haven’t tried this one yet. So here I just hit NEXT.
Then you are presented with a screen to “Verify and run solution”. Look over your Target Server, Target Database and Target Username. All of it look good? Great….hit RUN.
Now you wait. It really hasn’t taken that long, I don’t think any of mine have been 10 minutes. I think the average has been more like 5-6 minutes.
Once it’s done, you can Download your Report. This will download a .pbix file that you can use with Microsoft’s free Power BI Desktop.
OK…now for the fun stuff!
Working with the Power BI Dashboard
Open up the .pbix file. When you do, the first thing you will see is a button at the top that says “Apply Changes”. Go ahead and click on that. It will ask you for your username and password to connect to your Azure SQL DB. The connection string will already be in place so you don’t need to worry about that, just the credentials. The data will then be pulled from your Azure SQL DB in the Cloud down to your local Power BI Desktop install.
Now you can start poking around and seeing what’s in the Dashboard. Since I opted to not put any handles in for analysis of FROM and TO, the first two tabs in the workbook (Outbound Tweets and Inbound Tweets) will not have any information, this is normal.
But then we get to tab #3 – Author Hashtag Graph. The gray dots are hashtags and the green dots are accounts that have tweeted. You can see that I made a tweet that had 2 hashtags – #osmf2017 and #mvpbuzz. And boy was @TexasMusicDude busy tweeting up a storm – and using lots of other hashtags in conjunction with his tweets. Other hashtags that were popular appear to be #CampGround, #ShinyRibs, #TexasMusic, #DreamFolk and #Strings. Along the bottom you can see the day/timeline and the quantity of tweets at what time of day. If you click on any of the nodes, the information about what time the tweet(s) took place is highlighted in the timeline. It’s very interactive.
Next tab over, Pivoting Overview gives us another interesting way of looking at the #osmf2017 hashtag activity. You can see the total tweets pulled into the tool was only 100, by design at this point. Out of those 100 there were 72 unique tweets (meaning there were 28 retweets). Along the bottom the tweets are actually shown, with pictures! You can scroll to the right and left through all of them. And there’s my tweet, with me wearing my new Microsoft MVP vest that was sent in preparation for our Central US MVP Community Summit coming up on 5/20.
To the right the donut ring is a sentiment indicator. It is using Cortana Intelligence and Azure Machine Learning to review and make a determination, what is the sentiment of the tweet? I clicked on the tiny Negative slice to see what tweets may have been nasty. It was only 2 of them. The first contained the trigger word “Lazy”. Well, that’s tricky. A Lazy Morning is actually a positive sentiment. I guess Cortana is still learning. The other tweet was referring to one of the acts in the Youth Competition which was a quartet of young ladies called The Belle Tones who were singing harmony beautifully together. I guess the trigger for Cortana in this case is “not barbershop”, which it may think shows disappointment. They were hardly disappointing! Very interesting how machine learning has come so far – but still has a lot to learn. I bet over the next 24 months the progress in this area is amazing.
One more tab over and we are on the Sentiment tab. This looks at sentiment by author, sentiment by tweet quantity by hashtag, volume of tweets by sentiment bucket and sentiment of tweets over time (this is really interesting to me). Wouldn’t it be interesting to correlate this with a speech and see if at some point words were said which triggered negative sentiment on twitter almost immediately? This tab gives me lots of ideas.
The problem I had with this one is that I wanted to see labels on some of this. Well, that’s easily fixed. You can edit the pbix, since you own it. This is just a starting template, so customize away. Here I went into the visualizations editing window and added Category Labels to the plot diagrams so I could more clearly see WHO was tweeting or what the hastags were that the diagram is referring to. And there’s my @sqlgirl tweet with a slightly higher than neutral sentiment.
The final tab in the dashboard is named High Impact. Like all of the tabs before, this one is very interactive. Below I found my tweet and touched it. The information in the boxes to the left are then about my particular twitter account @sqlgirl – that I’d tweeted a total of 1728 times and have 1066 followers, etc. (hey, if you don’t follow me, please go do…I have a goal this quarter to get to 1250 followers and I only have about 1 month left so I could use a lift). [note…as of this morning I’m at 1071, scratching and crawling my way to that goal].
Anyhow, it’s a pretty fun template to play with.
You can use the Publish button to push the report up to Power BI in the Cloud.
And from there, you can publish to the web where the report is available to anyone who has the URL.
You want the URL, don’t you?
Here you go….[click the image below]
Using the Template to Reverse Engineer Things
A couple of other things about this template I wanted to mention. If you go back into your Azure environment you will see that the template used the Resource Group you designated for your SQL Server and your Azure SQL Database, however, it also created a somewhat cryptic new resource group that looks like something like “SoultionTemplate-abcdefghi”. If you open your Resource Group node in Azure and click on that newly created resource group you’ll see some of the infrastructure that was created by the wizard to make this whole thing work. I love reverse engineering things, so this is like a little slice of heaven, there’s a Logic app, a Machine Learning web service, two API connections, an App Service to name more than a few.
Clicking on the twitter API Connection and poking around I found an area that looked like it MIGHT be the thing that is limiting the tweets to only 100. (Unfortunately, I tried editing it and nope…didn’t change anything.) But, you get my drift. There’s a lot in here to poke around and play with, and use to teach yourself.
So there you go. I hope you have as much fun with this as I have.
Mindy Curnutt is a five time Microsoft Data Platform MVP with over 20 years of experience working with Database Solutions. She specializes in Performance Tuning and Architecture. She speaks regularly across North America at Local User Groups, SQL Saturdays and Conferences.