THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

A Fitbit report in Power BI for T-SQL Tuesday

Finding things to publish online for public consumption is something I’m often a bit reluctant to do. Most of my work is for customers, and there’s no way I’m going to share some of their data unless they’ve explicitly allowed it.

So when Jorge Segarra (@sqlchicken) posted a challenge to publish a Power BI report on the web for T-SQL Tuesday, I had give some thought about what kind of data to show. Luckily, Scott Stauffer (@sqlsocialite) has been Fitbit-challenging me over recent weeks, and in particular, in some that don’t include some of the big-steppers like Steve Stedman (@sqlemt), who should probably change his name to Stepman, considering he has a treadmill desk and does over 100k each week. Anyway – with a group of people who do the same order of magnitude of steps as me, I stretched myself to do better than I had been doing, and figured this could make useful data.

TSQL2sDay150x150

I started with an export of the last 31 days from Fitbit. That’s as much data as you can pull down from them, and although I could go to the effort of getting extra exports and combining them, I didn’t for this. After all, I’d rather be out getting more steps done than analysing them.

I had a bit of cleaning to do first, because the data had an annoying first line. In fact, I found it easier to pull the data in as text, remove the top line, then split the data up by the delimiter. I could then mark the various columns as numbers, which made life a lot easier.

image

After all this was done, I was ready to throw some stuff onto a report (ok, I also added some measures to show the steps as a percentage of the total steps – they have to be measures to handle the division properly). It was easy to get a chart on there, and a card to show some of the numbers. But I wanted to make it a bit more interesting... so I added an average, to show how my increased steppage made an impact on my average.

I added a column: RollinAvgSteps = AVERAGEX(FILTER(fitbit_export_20160214, EARLIER(fitbit_export_20160214[Date])>=fitbit_export_20160214[Date]),fitbit_export_20160214[Steps])

...which takes the average of my steps to date. There are a bunch of ways to achieve this, but this is the way that I chose. And you can see that the average line is (happily) improving! Oh, and because I pulled down the extract on the 14th, there’s a dip at the end. My numbers were much healthier by the end of the day, and despite spending way too long NOT walking, I did about 7244 steps that day.

You can see the result at http://bit.ly/RobFitbit, which looks like:

image

Oh, and having done all this, I discovered that fellow SQL Saturday Melbourne precon presenter Reza Rad has a series on doing Power BI on Fitbit data – I was pleased to see that he did a similar set of transforms to the data.

@rob_farley

Published Tuesday, February 16, 2016 11:20 AM by Rob Farley
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement