Forum Discussion
Using a REST API as a data source
Anonymous
I don't know the Blackbaud REST API in your case and it is not in the list of suppported "Online Service".
But according to the content in the link that the API reponses in JSON, you can get the data by using "Get Data"-->"Other"-->"Web". And then extend JSON to a table in query editor.
eg. http://jsonplaceholder.typicode.com/posts/1/comments
You can replace it with a link like "http://[school].myschoolapp.com/api/academics/department/?t=[token returned from previous step]" in your link.
In the query editor,
Done
Regarding refreshing, you can publish it to Power BI Service and schedule refresh.
I had rest API Call working in Power BI Desktop but its give me error when I push it on power BI Service that it can't be refreshed. Anybody refreshed succesfully REST API DataSource in Power BI Service?
I think you can schedule a refresh on the dataset once you publish a report and dataset is created.
I've also managed to get Power BI to communicate with the Blackbaud Sky API for Raiser's edge NXT. It can be refreshed via Power BI Desktop, but not via the service. The error message is: "Data source error: Unable to refresh the model (id=2519924) because it references an unsupported data source."
The other problem I have is that of token refresh. Having to manually generate an OAuth token every hour or so, and plugging that into a parameter in Power BI Desktop to be able to refresh data is a real pain.
Quicky yes I still haven't found a way around the error in Power BI service. But you can write a variable that will ask, get, store, and enter your token for you. Check this out: http://duff.io/2017/07/17/using-microsoft-powerbi-with-the-blackbaud-api/
Cheers GGetty. The Blackbaud Sky API appears to have a different authentication, and instead of returning a Json, the access token is returned in the URL itself when using Implicit Flow: https://apidocs.sky.blackbaud.com/docs/authorization/implicit-flow/ although it does return a Json containing the token when using Authorisation Code Flow https://apidocs.sky.blackbaud.com/docs/authorization/auth-code-flow/ but only after you've processed the access code in the initial call to the authorisation service, which returns an access code in the return URL again. Both methods require the user to authorise the access via clicking an Authorise button.
My Power Query knowledge isn't great; do you kow of a way to automate that? Send a request to the Blackbaud authorisation page, wait for the user to authorise, and grab and process the returned URL? It's annoying that the authentication methods differ between the APIs!
At the moment I'm using a PowerShell script to perform the actions and return the token, which I'm then manually copying and pasting into the authorisation parameter in Power BI. The script also optionally queries the api and exports the returned data into a JSON file which I'm half tempted to use as the Power BI data source rather than querying the API directly since there's so many hoops to jump through.
I reckon I could modify the PowerShell to run on a schedule and programmtically "click" the link to get the access token. It's a shame though because the Power Query script I'm using at the moment nicely handles the API pagination of the endpoints (I modified this: https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78) and it would save having to have a separate datasource. That said though, if I did do that, it would likely solve the problem of the datasource not being able to be refreshed in the Power BI Service.
