Microsoft Azure + Office365 Power BI == Power to the Internet of Things

Here’s a sneak peak of what I got. The end result is the ability to do self-service analytics on my daily activities by doing a data mashup of Fitbit and Strava activity data points. This is not really enterprise-grade, just a fun personal project, but in later posts I will discuss more about making it more “enterprise-grade” by decoupling all the mashing work to an Event  Hub and Azure Worker Roles. Sure I’ll even defend why I use this later but don’t beat me too much to it.

I just want to be able to do some self-service analytics using Excel 2013’s new add-ins called PowerQuery and PowerMap. Why Excel? Well because I have it and I think it’s rather ubiquitous.

Why Fitbit and Strava? Fitbit One is a device I carry with me all the time, and Strava is an app which was intended for cycling (because I’m an avid mountain biker) but I use it a lot and I am glad that this app is really power-friendly in not draining off my smartphone battery when I record my activities.  So the simple task is to create a IoT gateway proxy with 2 endpoints, but let’s just start with the first end-point which is the following:

GET /v1/mashup?ondate=YYYY-MM-DD&aftertime=hh:mm&beforetime=hh:mm

I’ve not turn the endpoint live yet in the cloud will do so soon.

The end result, a visualization of where I spend my activities, when, and how. This is the PowerMap, very powerful indeed. Those skyscrapers in the map below are not new building in Sydney but rather a representation of my steps count as recorded on Fitbit and the lat/long coordinates recorded by the Strava app. A quick tour of what I did a few Sundays ago:

  1. Skyscraper #1 – I left my apartment in Waterloo/Redfern area, walked to the bus stop with my family
  2. Skyscraper #2 – Alighted at Central station, walked to the other side of the station to catch another bus
  3. Skyscraper #3 – Got off at Parramatta Road to go to a kid and baby Sunday market, can’t remember the name of the place but from the map it’s somewhere near Barnwell golf course. This kind fits the purpose of why I build this, to remember where I’d been considering I’m really new in Sydney.
  4. Skyscraper #4 – After much shopping for kid and baby stuffs, we were starving, so we went to Westfield Burwood for lunch
  5. Skyscraper #5 – Did some groceries around the commercial area near the Burwood train station
  6. Skyscraper #6 – Walked back to apartment from the bus stop
Family Sunday Funday
Family Sunday Funday

Here’s the Excel spreadsheet which packs all these goodies, but the actual data is pulled from Azure Storage tables.

Just download these two add-ins to get started.

PowerQuery –

PowerMap –

The brains behind all of this: it’s all in Microsoft Azure. I’ll just quickly summarize the technologies I used:

  1. Cloud services – virtual machines to be exact
  2. Azure Service Bus Event Hubs
  3. Azure Worker Roles
  4. Azure Storage tables

You can take a look at a rough sketch of the architecture below:

EPSON MFP imageDisclaimer: I’m not an enterprise architect, just know enough to get a quick IoT POC moving.

Hope you like it. My next step is to tighten this up and I’m also exposing this as a well-designed RESTful API, hopefully.

More about GetFitY’all

Just to describe more of my personal “Internet of my Things” project as a proof-of-concept that the Fitbit is a great telemetry device and its data could be analysed in the cloud. The scenario that I am trying to build to create a great user experience for my users is described below:

Goal: To allow Fitbit users to see good visualization such as a power
map of where and when their activities occurred.

– Motivation: Let users find out where and when certain activities
occurred. This is especially useful for remembering which activities were pleasurable so much that it recorded the most steps. Then repeat those activities. While the Fitbit device does not record the latitude, longitude data, I could do a mashup of data recorded from an app like Strava by accessing its API.

Workflow is something along the lines of:

  • Create a message pump that retrieves a stream of data points from 2 API source; Fitbit and Strava.
  • Fetch intraday data points up to 1 minute detail for the activities collection. Currently this is not possible yet because I’m still waiting for access to this part of the Fitbit Partner API to be granted. Meanwhile I am using a workaround by Andrew Wilkinson’s python-fitbit libraryUpdate: I had since gotten my Fitbit Partner API access and I’d been ingesting thousands of data points. Small data getting bigger.
  • Fetch a stream of latitude, longitude, time and distance data from Strava API.
  • Message pump sends data points to a cloud-scale telemetry ingestion hub.
  • Use Office 365’s new PowerQuery and PowerMap to merge the data streams from Fitbit activities time series data and Strava activities stream to create a visualization like a map to show where the activities occurred.
  • Publish this on social media to encourage more friends to join in the activities.


GetFitY’all is (I’m making this up as I go)

  • a Fitbit personal fun project that is to help me learn more about the Fitbit API but more importantly in the scheme of Internet of Things (IoT), really  use my Fitbit One as it really is, a special-purpose device that is one of the thousands of items which I own, which is also a smart product that could send a bunch of telemetry data that it already collects into the cloud.
  • Beyond that I also want to test out some IoT patterns which allows me to control the device. While there is limited commands I could issue to my Fitbit one, some of the more useful and obvious commands are setting an alarm, and also changing the timezone as I travel.


  • I will be creating a proxy of a device gateway that is the connection gateway which allows all my special-purpose devices to connect.
  • While the Fitbit One never initiates a connection, I will be creating a client proxy for it as well to test the telemetry, inquiry and command patterns to be truly tested between my device (the thing) and the device gateway.
  • Device gateway will be implemented simply using the Azure Service Bus and the client proxy will be using a wire-level protocol called AMQP. Sounds cool already huh?

Let’s get started!