Building the Power BI Strava data connector

I love cycling, both watching and doing it myself. One of the cool things is that is very data driven sport and thanks to Strava much of this becomes available to anyone, not just the elite athletes. Of course I would love to do more and get this data into Power BI. Thanks to Strava’s API’s and the new Power BI custom data connector SDK this is now possible!

I started building this connector the day the SDK became available but unfortunately got stuck a bit and I wasn’t happy with the code I wrote, I knew this could be done easier but I am a DAX guy not an M guy at the same level as DAX :). But thanks to the new amazing examples Matt has created I was able to finish the connector!

To start, the connector I wrote is fully available on GitHub: https://github.com/Kjonge/PowerBIStravaConnector. It is mostly based on the samples provided by the data connector team so I won’t go too much into the actual building of the connector itself. I added many comments to the code and point to the samples I stole them from Smile. But honestly it is pretty straightforward copy and paste from the samples when your service uses REST API’s with OAuth 2 and JSON payloads.

So let’s look at how to get it running yourself before we look at using it:

  • First make sure you have the latest version of Power BI desktop
  • Second make sure you have Visual Studio (2015 or 2017) installed so you can download and set up the Power Query SDK from here: https://marketplace.visualstudio.com/items?itemName=Dakahn.PowerQuerySDK
  • Next you need to get the code from my project from GitHub, just go to https://github.com/Kjonge/PowerBIStravaConnector and click on download and open in Visual Studio:
    image
  • This now opens it in Visual Studio where you can “clone” the code to your local HD
    image
  • This opens the project where I can double click the solution
    image
  • Now this will open the solution that we need:
    image
  • As you might notice two files are missing, these are the files that are needed to authenticate with Strava using OAuth, a Client ID and Client Secret. In the current version of the Connector SDK this information is needed locally to be able to connect. There is an item on the road map (before it goes RTM) to move this into a cloud based solution so you can distribute connectors without having to add the client id and secret into the code itself.  But for now during the preview we need to add them locally, so I add these files for now.
  • To get the Client ID and Secret I need to create an app with Strava, so I go to https://www.strava.com/settings/api to create the app. This will allow me to interact with strava information on a users behalf. I go to my profile and “API application”
    image
    As you can see (well I deleted my Client ID and secret) but now you can get the client ID and Secret
  • Also important is to set the right redirect URL, in this case OAuth.PowerBI.com, this used in the OAuth Authentication flow:
    image
  • I use that information to add two files to my solution called client_id and client_secret that contain just those 2 values that you can copy from the app you just created in the Strava site.
  • Now you will notice two new icons next to the files, this means these files are excluded from github so you will never accidentally publish the client id and secret when making updates to the connector (feel free to :))
    image
    Also it is important that both these files are set to Compile so that they will be included in the build (they should be automatically but it is worth double checking)
  • Now we are ready to build the connector and load it into Power BI desktop. Just Build the solution and you should get a compiled mez file in your debug folder (it doesn’t matter if you do a release or debug build)
    image
  • This file I copy to a folder that the Power BI desktop will look at “C:\Users\YOURUSERNAME\Documents\Microsoft Power BI Desktop\Custom Connectors”
  • Next I have to make sure I turn on the Preview feature in Power BI desktop:
    image
  • If this is turned on I should now get the Strava connector available.

Now that we have the connector available, let’s look at using it:

  • I now have a Strava connector in my Power BI desktop
    image
  • When I select it I get asked to sign in:
    image
  • Clicking sign allows me to sign in to Strava and Connect
  • After connecting it shows me the Navigator where the real magic happens:
    image
  • Here you see 2 tables that are exposed, 1 with Activities and another with Athlete information, I select both. Clicking on them gives me a preview:
    image
  • After pressing Load the data gets loaded into Power BI desktop. Done!
  • This now gives me a ton of data for all my tracked activities in Strava:
    image
  • That I can now use to analyze:
    image
  • And build a report on:
    image

 

 

28 Replies to “Building the Power BI Strava data connector

  1. Congratz, Kasper! really happy to see you finishing that custom connector. I know that you were working on it for a long time. How much time did you spend reading the API documentation vs actually writing code on Visual Studio?

  2. Great job Kasper!
    This is really something that will be truly useful!

    But there seems to be something strange when I try to use the connector. I cant sign in to Strava from Power BI Desktop, when clicking sign in I only get a http 400 error.
    I read something in the readme file about IE issues but that shouldn’t be the case when trying to sign in from Power BI Desktop, right?
    Any idea what might be causing this?

    1. Did you make sure you set oauth.powerbi.com as the redirect domain in Strava? You can always run fiddler to see the actual error message.

  3. Stumbled on this when trying to see how data connectors work… I followed the steps but I get “The webpage can not be found”-error when clicking “Sign In”… Any ideas? Thnx.

      1. Ha. I had filled that one in. But it is case sensitive! so ‘OAuth.PowerBI.com’ does not work. ‘oauth.powerbi.com’ does work.

  4. Kasper, I built a similar report using the REST API calls to Strava’s API and mashed up with data form Peloton, this certainly makes connection to Strava easier, thanks for the post!

  5. Hi Kasper – awesome solution, just what I’ve been looking for! I’m having authentication trouble. After signing in to Strava I get the following:
    Unexpected Error.
    Exception of type ‘Microsoft.Mashup.Engine.Interface.ResourceAccessAuthorizationException’ was thrown.

    Any ideas?

  6. Hello Kasper, is it possible to parameterize this connector ? right now only for our own accounts we can use this connector, it would be great if you can expand this with parameters and in that way we can use for our friends as well.

  7. This is great, thanks so much for doing this! I have very little experience with Power BI and Visual Studio but I was able to follow this and make it work.

  8. I just noticed that I can’t schedule an automatic refresh, and I can’t even refresh from the Power BI portal. I have to refresh from Power BI desktop and then re-publish. Am I doing something wrong?

  9. Hi Kasper, nice work! One question about formatting:
    The data I get in PowerBI has some strange formatting. For example the speed is not in km/u but in a low number like 10,6 in stead of 38.2km/u.
    Is there a formula for the actual speed? Same for distance.

    I would like to compare my bikes in average speed on the same (commuter) traject.

  10. Have Strava made a change to their API? This has been working beautifully for me since November (thanks again BTW!) but it stopped working last week.

  11. Hi Kasper,
    I’m unable to load the solution in VS2017 – getting an error while loading the Strava.mproj file.

    Please any thoughts?

    Many thanks in advance!

  12. Same here. Additional, since I upgraded to the Version: 2.60.5169.3201 64-bit (Juli 2018) I’m unable to see custom connectors (such as Strava) in the list of data sources (Get Data). However, the preview feature is still activaited and my custom connector is still located under My Documents\Microsoft Power BI Desktop\Custom Connectors. Any idea, Kasper?

  13. Same here. Additional, I’m unable to select a custom connector (Get Data) since I’ve upgraded to Version: 2.60.5169.3201 64-bit (Juli 2018). The Strava Connector vanished from the list even though the preview feature is still active and the mez-file is still in My Documents\Microsoft Power BI Desktop\Custom Connectors. Any idea, Kasper?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.