developer musician photographer

Tuesday, July 28, 2015

How to create a StackOverflow driven Power BI Dashboard with the StackOverflow API, Azure WebJob, Azure SQL and Node.js

Power BI is Microsoft’s new data visualization platform. It allows you to easily pull data from datasources like Salesforce, GitHub and QuickBooks. As I was going through the product I noticed they didn’t have a StackOverflow service connection, so I built one. StackOverflow has a REST/JSON API, but Power BI doesn’t allow you to connect directly to an API yet. Instead of hitting the API directly, I wrote an Azure Web Job to pull the data from the StackOverflow API into an Azure SQL Database and then built the Power BI dashboard from that.

A StackOverflow dashboard is useful for teams who want to monitor and improve their community engagement. The dashboard will be a quick and easy way for teams to see how many questions are unanswered, how many have no responses, how long it takes to get a response, number of question views over time and number of questions created over time.  There’s a lot more insights you could pull from the StackOverflow data, but for now we’ll start with these metrics:

1. Number of Unanswered Questions – These questions may have responses, but none of them are marked as “Answered”.

2. Number of Responseless Questions – These questions have no responses at all.

3. Time to First Response – The time difference between the creation of the question and the first response.

4. Number of Question Views Over Time – The number of views per month.

5. Number of Questions Created Over Time – The number of questions created per month.


Untitled-3

Here’s a list of the components you’ll need to build this out yourself.

1. StackOverflow API – The StackOverflow endpoint that allows you to programmatically access question data.

2. Azure SQL Database – Where you will store the StackOverflow questions relevant to your Power BI Dashboard.

3. Azure WebJob (Node.js or C#) – The “console app in the cloud” that will pull data from the StackOverflow API and push it to an Azure SQL Database

4. Power BI – You will use DataSets and Reports to create your Dashboard.


All of the code for this post can be found on GitHub here: https://github.com/jonbgallant/PowerBI-StackOverflow


StackOverflow API

StackOverflow has a robust API at http://api.stackexchange.com. You’ll want to use V2.2.

Here’s an example URL:

https://api.stackexchange.com/2.2/questions?order=desc&sort=creation&tagged=powerbi&site=stackoverflow. That returns a JSON that looks like this:

image

Use a filter

The query above will return questions that are tagged with ‘powerbi’ – but it doesn’t contain the ‘answer’ data, which you will need for your Power BI Dashboard. StackOverflow allows you to create ‘filters’ to add that additional data.  You can use the one I created ‘!9YdnSIN1B’ or create your own here: http://api.stackexchange.com/docs/create-filter

Here’s the same URL as above, but with the filter applied:

https://api.stackexchange.com/2.2/questions?order=desc&sort=creation&tagged=powerbi&site=stackoverflow&filter=!9YdnSIN1B

You’ll notice that it now has answer data:

1

Register your app

By default, StackOverflow will limit your requests to 300 a day, but will up that to 10k a day if you register your application and send a key with each request.

Register your application to get a key http://stackapps.com/apps/oauth/register/submit. Save your key, you will need it when you setup your Azure WebJob.

Here’s the same URL as above, but with the key applied:

https://api.stackexchange.com/2.2/questions?order=desc&sort=creation&tagged=powerbi&site=stackoverflow&filter=!9YdnSIN1B&key=undefined

(It’s not hyperlinked, because I don’t want my apps key to be on the internet in plain text)


Azure SQL Database

As mentioned above, Power BI cannot talk directly to the StackOverflow API, so we need to cache the data in an Azure SQL Database. (You, of course, can use any database you’d like, but I prefer to use Azure SQL Database). Just create a new database and execute the following scripts to create one table, one proc and two views.

questions table

questions_upsert proc

questions_answers_dates view

questions_by_month view


Azure Node.js WebJob

An Azure WebJob is like a console app running on a scheduled task in the cloud. You can write them in C# or Node.js. I’ll use Node.js for this example. You can find the WebJob code on GitHub here.

The WebJob process flow is simple; it will request the question data via the StackOverflow API and insert it into the database.

Create the WebJob

WebJobs are housed within Webapps, so just create a Webapp and click on the WebJob tab:

image

Create a new WebJob and upload this zip file. I set mine to run once an hour – feel free to configure your schedule as you wish.

image

image

You can also fork the PowerBI-StackOverflow repo and update the code as you wish.  I’ve found that using an FTP client to upload WebJob is much easier than using the Azure web interface. If you use FTP, then just upload the code to this folder: /site/wwwroot/App_Data/jobs/triggered/pbisof


Config

The WebJob uses appSettings from the Webapp. Documentation for all of the variables can be found in the header of the run.js file.

You can configure them using the Web app Config tab in Azure:

 image

When you run the Node.js script locally you will want to create an .env file in the WebJob folder that looks like this and enter the values applicable to your database and your StackOverflow key. 

image


Power BI

We are going to create a Dashboard with the following visualizations:

1. Number of Unanswered Questions – These questions may have responses, but none of them are marked as “Answered”.

2. Number of Responseless Questions – These questions have no responses at all.

3. Time to First Response – The time difference between the creation of the question and the first response.

4. Number of Question Views Over Time – The number of views per month.

5. Number of Questions Created Over Time – The number of questions created per month.


In Power BI you have 3 things to be concerned about:

1. Datasets – The data that you are working with

2. Reports – The raw visualizations on top of your dataset

3. Dashboards – The organized reports that you will share with others.

Get Data

The first thing you need to do is setup a dataset in Power BI

1. Go to http://app.powerbi.com

2. Click Get Data

image

3. Select Databases, then Select Azure SQL Database and Click Connect

image


4. Enter your Azure SQL database connection info:

image

Power BI will connect to your database and import the data into a new dataset, which you can find in the left rail. image


Create Reports

REPORT: Number of Unanswered Questions

Over in the left rail, click on the dataset, mine is called pbisof. Then, over in the right rail click on Fields, then questions.

image

For this report we need to do 4 things:

1. Select our fields. We want is_answered and question_id

image

2. Set our field functions. We want the Count of question_id, not the sum

image


3. Filter by field value. We want to filter by is_answered = false

image

4. Select our visualization. For a count like this, the “card” visualization makes the most sense

image


Here’s what you’ll now see in the report draft view.

image


Click Save and name your report

image

You will now see it in your Reports list

image


When you hover over the card you’ll see a pin, click that to add it to a Dashboard. (Make sure you create a Dashboard before clicking that pin)

image

image

When you hover over the card you’ll see a pencil icon. Click that to edit the card title.

image

image

image



REPORT: Number of Responseless Questions

This visualization needs to show the number of questions that have no response at all.

imageimage

REPORT: Time to First Response

image

image


REPORT: Number of Questions Created Over Time

image

image


REPORT: Number of Question Views Over Time

image

image


Add all of those reports to  your Dashboard and you’ll now have a Dashboard that looks something like this:


Untitled-3

Here’s the same view in the Power BI iPhone app.

IMG_6002

Jon

Wednesday, April 1, 2015

How I Gained 30 Pounds in a Year

This has been an amazing year.  About a year ago, I decided to quit Microsoft and join Starbucks as a Barista Manager in Issaquah.  A lot has happened over the last year, but the most important thing is my renewed commitment to my health.

I've always been on the border between a L and XL and this year I finally decided to make a commitment to XL. In doing so, I came to the conclusion that I would need to gain some weight - at least 30 pounds - a huge challenge, but I was up for it!

So, one year ago today I made the commitment to gain 30 pounds and I'm proud to say that as of a week ago I reached that mark. A year ago today I weighed 160...now....wait for it!...I'm a whopping 190 pounds.  I'm super excited to have met this goal and so thankful to my friends and family (especially my wife) for their continued support over the last year. It's been life changing - new daily habits, new food, new clothes, new car, new doorways - all very thrilling.

Here's how I went about turning my life around:

Exercise: Before I used to walk at least 10k steps a day. I had a FitBit and I would obsess over that number. Now I move very little. I work from home a lot - often right from my bed. I also used to take afternoon walks - cut those out as well. I now have a hard time making it up a flight of stairs without taking a break - but that's okay - because I rarely use the stairs.

Food: Before I use to eat fresh fruit and nuts for snacks. Now I stack up on bars, candy and chips. I also used to stay away from fried foods and burgers - now that's all I eat. I actually have this fun game I play where I will ONLY eat fried foods for a month. Breakfast, Lunch, Snacks, Dinner - All fried. Fried Eggs, French Fries, Chicken Katsu and Fried Chicken Wings.  Before this change, I used to drink a few beers at night - now I try to finish off at least a 6 pack before crashing for the night. Usually followed by some late night snacks - with lots of carbs.


Huge changes - lots of sacrifices - but worth it! I know this diet won't work for everyone, but it worked for me. Obviously, consult your doctor before taking on something like this.

A huge thanks to everyone who cheered me on over the last year!  Here's to 30 more!

Jon






Sunday, February 15, 2015

beakn v0.4.2 - Lync Status Light - Now with easy to edit config settings and reset light on start support.

beakn is a Lync status light that I’ve been building for last couple of months. I tweeted a quick drawing of beakn v0.5, which will include a rechargeable battery and David Washington was quick to respond with a couple of feature requests.




Great ideas!  I’ve been wanting to do all of them for a while…that tweet pushed me to start investigating the Lync REST endpoint for cloud registration and inductive charging is probably a v2 feature, but #2 “get status on reset” is totally doable – so I just implemented it and released beakn v0.4.2.

Here’s what is new in v0.4.2

  • beakn.exe now listens for the Spark “reset” event and sets the light to the current color. Before this you would have to toggle your status. Now it just works after your Spark is reset. For this change I used EventSource4Net and modified my fork of SharpSpark.
  • I moved Spark Device Id and Access Token from beakn.exe.config to Properties.Settings and added a simple two textbox UI. Before this you would have to open Notepad as Admin and edit beakn.exe.config directly.

image

  • I added a new “quick and dirty” beakn icon.windows-icon


The best way to get everything setup is to follow the steps from this post: How to Build a Lync Status Light in Minutes with a Spark Core


beakn v0.5 is coming very soon! Follow me on twitter for updates.








Enjoy!

Jon

Tuesday, February 10, 2015

How to Build a Lync Status Light in Minutes with a Spark Core or Photon

I’ve been building a battery-powered wifi-enabled Lync Status light for the last couple of months. Lots of people have been building the beakn and I did a hackathon class last week, so I thought it would be ideal to have a very simple step-by-step walk through. I’m working on getting the beakn officially manufactured so there are more complex versions available if you are comfortable with soldering. The version below is a great starting point. 

1: Get a Spark Core or Photon from https://store.spark.io/ core-a6fcc2b47e235d529ab58fe0a1124588

2: Plug Spark Core into your computer via USB

IMG_0381

3: Install Spark Core iPhone or Android app.

If you don’t have an iPhone or Android you will need to follow the “Connect over USB” section of the Spark Getting started guide

IMG_0382

4: Connect phone to Wifi network

Must be WPA2 (SSID and Passcode) – WPA2-Enterprise won’t work.

IMG_0393

5: Create or Login to Spark Account and connect to same wifi network

IMG_0384IMG_0385

IMG_0389

6: Connect Core

a) Click on the icon in the upper left and then click “Connect a Core”. You will likely need to sign-in to wifi again.

b) Give your Core a name and hit ok.

IMG_0390

7: Flash beakn code to Spark

a) Go to Spark Build (https://www.spark.io/build)

b) Click on Libraries in lower left rail

image

c) Search for and click on “beakn”

image

You will see the beakn-sparkcore-onboard-led.ino file to the right

image

d) Click on “USE THIS EXAMPLE” button in the left rail

image

You will then see this page that shows the current app that loaded

image


e) Click on the “Flash” lightning bolt icon in the left rail to send the code to the Spark Code

image

You will see the Spark Core led flash while it is being flashed. If you don’t then use the Spark Core debug help.


8: Install beakn Windows App from http://bit.ly/beakn-client

The beakn.exe app listens for Lync events and calls the beakn.setStatus function that you just deployed to the Core. Install the latest version of beakn-{version}.msi from http://bit.ly/beakn-client - which was beakn.v0.4.2 as of this post.

image

The last page of the install will look like this – you will want to keep that “Launch beakn” checkbox checked.

image



9: Set your Spark settings

When beakn.exe launches it checks for your Spark Device Id and Access token and if either are not set you will see this:

image

Click OK and you will see the settings dialog:

image

Go to http://spark.io/build to find both Device Id and Access token and copy and paste them into this settings dialog.

To get the Device Id, click on the icon that looks like a target, second from bottom

imageimage

To get the Access Token, click on the icon that looks like gears (standard settings icon)


image

Copy and paste both of those into the beakn settings dialog and then click Ok.


You will now see the beakn dialog that is really just a log of the messages that are being sent to your beakn device.

image

10: Open Lync and Toggle your status to try it out

image

IMG_0391

(It’s hard to tell in the photo, but the onboard led changed to red)


You should now be all setup with your beakn. Stay tuned for updates on twitter


Jon