cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
caseyh Member
Member

Re: Using a REST API as a data source

No, unfortunately I did not. I stepped away from it a while ago, though. With the API changing all the time, it'd be worth another look to see if it could be hacked together. If I do, I'll come back and update.

GGetty Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

@Eric_Zhang and @caseyh Here's some sample code from the Advanced Query editor in Power BI desktop that would work for making an API call to Blackbaud's ON products, get and store the token from Blackbaud API as variable (NOTE: these are fakes URLs, usernames, passwords, and SLI id for list, but I promise it works in Power BI desktop just be sure your user has access to the Web Services API Manager role and that the list you're pulling from also gives access to the Web Services API Manager role and/or your username).

 

let
Source = Json.Document(Web.Contents("https://2018036.myschooltraining.com/api/authentication/login?username=jdoe&password=openthedoor&for...")),
T = Source[Token],
GetList = Json.Document(Web.Contents("https://2018036.myschooltraining.com/api/list/99999/?t=" & T & "&format=json"))
in
GetList

 

@Eric_Zhang can you tell me why this query can't refresh in power bi service - only desktop? I get error message saying this data source can't be refreshed in Power BI online.

samxitij Frequent Visitor
Frequent Visitor

Re: Using a REST API as a data source

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?

surfersamu Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

I think you can schedule a refresh on the dataset once you publish a report and dataset is created. 

Quicky Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

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.

GGetty Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

@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/ 

Quicky Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

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.

williamdasilva Frequent Visitor
Frequent Visitor

Re: Using a REST API as a data source

Hi Quicky (love the name).

 

We started trying to connect to PBI directly through the Sky API and quickly came to the realization that it was not designed for that.

 

We've built a reporting data warehouse product that provides a proper data model to connect and build with PBI. It uses the APIs to refresh and we've been working with BB to incorporate some additional missing end points.

 

We will be releasing the product later this year. Contact me directly if this is something that would be of interest to your organization.

 

Thanks,

William

caseyh Member
Member

Re: Using a REST API as a data source

I've very interested. I'll send you a DM.

Quicky Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

Cheers William. It sounds good, but unfortunately my organisation's budget is non-existent. I'd be interested in reading up on it once you've released it though.

I've got a bit of experience consuming APIs for data warehousing from a previous position, so I reckon I'll put something together in-house with SSIS or CloverETL should it come to it.