Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using a REST API as a data source

I'd like to be able to connect to a Blackbaud REST API to pull data from that system and use it in a Power BI Desktop report: http://on-api.developer.blackbaud.com/getting-started/

 

That said, I don't have experience using REST APIs.

 

Is that's possible, either for a live data connection or for a scheduled refresh? If it's not possible in Power BI Desktop, would it be possible if I managed the report on the Power BI Service?

 

Further, if that's possible, could someone give me a quick and dirty of how to accomplish that and perhaps point to toward the materials I need to read to figure out how to do it? Part of my misunderstanding here is how the Blackbaud and Power BI REST APIs interact (if at all).

 

I have an E5 license that allows me access to Power BI Pro features.

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

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

Capture.PNG

 

In the query editor,

Capture.PNG

 

Capture.PNG

Done

Capture.PNG

 

Regarding refreshing, you can publish it to Power BI Service and schedule refresh.

View solution in original post

56 REPLIES 56

@Quicky preaching to the choir! 

The GoodData/Insights that @GGetty was referring to are great. We've been told BB is set to go live with Insights somewhere around BBCON this fall. They will be great for building regular dashboards and other day to day reporting for a smaller orgs. The insights are also great for major gift dashboards as they have opportunity endpoints available (a few still missing). But try to build a dashboard around a direct mail performance, pulling in Appeal information and calculating response rates and you will hit the limit of insights quite quickly.

Just for info, tonight I built a custom data connector to the SKY API. I followed the tutorial here: https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph and tailored it to fit - the example provided didn't actually need much work to get it to function. Once built and deployed, it now appears as a connector in the Get Data menu in my install. It solves the problem of having to update the token periodically, as it's performed automatically, but it won't solve the issue of the data source not being refesh-able in the service because the service doesn't yet support custom data sources (although I believe it's on the roadmap).

 

The connector I've build currently only returns data from the constituents list endpoint (just a proof of concept for now), but it will handle paging and return all records. Unfortunately I haven't been able to test it too heavily since as I mentioned, we're yet to transition to Raiser's Edge, and so I'm curently working with a database of only 10 records! I suspect for larger orgs (and mine when we finally transition), there may be some work required to sort out any quota limitations, but it should just be case of introducing a delay inbetween calls.

 

I haven't yet parameterised it so it's running with my app credentials hardcoded within it, otherwise I'd host somewhere tonight to get some feedback on performance/issues from any other RE users who might be interested in the connector. Annoyingly, I'm off on holiday for a couple of weeks so I'll have to put further development on hold until I get back - the other half will kick my ass if I fire up my laptop abroad 😉 That said, it wasn't too difficult a process to undertake if anyone is interested in making one themselves.

@Quicky @Anonymous @williamdasilva Can anyone convince Blackbaud to just build connectors for Power BI for both the ON API and SKY API? My dream scenario would be direct query via Power BI on built-in connectors created by Blackbaud - just like I can do with my Mailchimp data.

 

Imagine how great it would be to just click on GET DATA and see this listed under Online Services: 

 

Blackbaud APIs as services in Power BI.png

@GGetty Love the screen shot! Had me fooled for a sec!

That would definitely help convince the powers that be. BB is a publically traded company, there needs to be positive ROI. They also listen to their customers, so if there's a need for it and a positive ROI, then it might get put in their pipeline!

@williamdasilva @Anonymous Blackbaud's own support knowledgebase already has guides for connecting powerbi via oData feed for their Altru product: https://www.kb.blackbaud.com/articles/Article/111459 wonder if adding oData would light up things easier for everyone?

@GGettyThat OData link is for another Blackbaud product unfortunately. The SKY API isn't an OData feed, so annoyingly isn't quite as simple to implement (otherwise my connector would be finished by now, since the custom data connector samples Microsoft have provided focus on OData feeds). You'd still also have to implement a method for handling OAuth 2 tokens, at least in the case of the SKY API.

 

However, in good news I managed to squeeze in a bit more development time before heading off. My connector now implements the schema functions shown here which means that including support for any of the available endpoints in the SKY API is fairly trivial. Unfortunately though, that's as far as the tutorials go at the moment - the next section "Advanced Schemas" hasn't been published yet! 

 

My M knowledge isn't good enough yet for me to plow on with including the final bit of functionality without guidance, so with any luck Microsoft will get the next stage out asap, and I can make the connector available.

Hi guys, really sorry about the radio silence and lack of any update on this for a while. A bunch of other things had taken precedence over the last few months unfortunately.

 

Anyway, in the interim, Microsoft added to the custom data connector samples and released the 7th in th TripPin series. As a result, today I knocked up a version of my connector that implements the techniques they show in that tutorial, with a couple of amendments. It should now pull back data from the constituents endpoint and (hopefully) set the correct data type for all the returned fields.

 

If anyone is interested in doing a bit of testing for me and providing feedback I'd be grateful. Let me know and I'll make it available. I'll be adding the schemas for additional endpoints in the next few days.

 

For info, with our database of 100,000 constituents, pulling back all records took around 6 or 7 minutes, and the API quotas weren't hit in that time. Obviously with the addition of some of the larger endpoints going forward, this is likely to become an issue.

@GGetty @Anonymous @williamdasilva Hi all, I've made v1 of the SKY API connector available on git here: https://github.com/GrantQuick/BlackbaudSkyApi

 

If you get the chance, I'd welcome any testing/feedback.

 

@Quicky G-d bless you! It's awesome and working. @Anonymous and @williamdasilva if you have access to a RE NXT tenant you can build this right now! I'm not anywhere close to being a developer but even I was able to follow you GitHub instructions (once I realize notepad can't save files without extensions and my URI in my Blackbaud app had to also be the same localhost reference in your .mez file. Still, if I can use this tool to build a working app that connects to my SKY API data, almost anyone can. Here's some screenshots for proof of life:

 

Custom App based on @Quicky GitHub:

 

Custom App Based on @Quicky GitHub

 

Working PBIX report connecting via custom connector from @Quicky modified .mez file:

 

PBIX report connecting via SKY API custom connector

 

Now that I've copied you. I have to go figure out how you knew how to do this! Thanks so much. I only wish more data were exposed by the API and there were a way to reference lists / queries / or views. Hopefully all that is coming soon.

@GGettyNo worries, thanks for the feedback and the localhost tip. I've updated the git readme to include a note about setting the correct Redirect URL when creating your app.

 

For info, the app creation part of the tutorial is purely for the purposes of generating the necessary IDs. Blackbaud employ rate limiting and quotas on the API, meaning that if I were to share the application ID for the app I made, the limiting/throttling would be hit very quickly as the number of API calls would be the sum of all calls made by all organisations using the app. Creating your own app means that any limiting is only based on calls you have made locally in your organisation.

 

I'll add in as many list endpoints as I can in the coming days, before I start looking at the endpoints that take parameters (those that require a specific constituent ID to be passed in the header, for example). They're likely to add a fair bit of complexity to the connector!

As I am beginner in this field so I research a lot and found another beneficial Article, check it out https://zappysys.com/blog/howto-import-json-rest-api-power-bi/
So thanks to all those people who share their knowledge.

 

Hope it Helps!

Best regards

@GGettyI've updated the connector to include the gifts endpoint now, if you want to download the new mez file and give it a go.

 

I won't keep flooding the thread here with every minor update, but being able to report on gifts is fairly significant for us!

@Quicky this is great. I got the new mez working and can see the gift endpoints but, oddly, gift amount is no where to be found. Did I mess something up? I can see funds, gift dates, dates added, post date, but no gift amount anywhere.

Screenshots here:

 

 

 

Here are the values from the gift table that I'm seeing:

 

headers                               values
id                                         4859
lookup_id                             4463
date                                      4/1/2014 0:00
type                                      Donation
constituent_id                      9759
is_anonymous                      FALSE
post_status                           NotPosted
post_date                              5/27/2011 0:00
gift_status                             Active
batch_number
date_added                           5/27/2011 11:27
date_modified                       11/22/2017 13:30

@GGettyNo worries - the connector doesn't really do antything with regards to building a data model for you as yet, beyond pulling back all the data and setting the correct data types. The gift amounts are there, but because they are records themselves, you would need to tell the data model how you want them to be handled. In the case of the gift amounts, you would just need to expand the record. To do this, click Edit Queries, and you'll see all the data pulled back. You can then click the expand button in the corner of a column's header to surface the data.

 

You may want to expand some records into separate tables. You can follow this blog post to do that: https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/

 

Most of the endpoints have record-type fields, so I'd recommend editing the queries for each endpoint you connect to in order to see if any additional data can be exposed. At some point I'll handle this in the connector directly, but the initial focus is to bring back all possible data.

 

I should get the chance to update the connector today with a solution to the issue of rate limiting on larger datasets.

@Quicky @Anonymous @williamdasilva Just wanted to contribute here albeit for Blackbaud's K12 ON Api instead of RE NXT Api. However, if you want to connect to Blackbaud's K12 API with only eight lines of M Code using the preferred POST calls, all you have to do is insert your YOURUSERNAMEHERE, YOURPASSWORDHERE, YOURSCHOOLNAMEHERE, and YOURSLID#HERE (for your Advanced List) in the sample code below. I'm going to put together a blog post and screencast on this, but wanted to share / giveback here: 

 

let
    ONLoginInfo = "{""username"": ""YOURUSERNAMEHERE"", ""password"": ""YOURPASSWORDHERE""}",
    Source = Json.Document(Web.Contents("https://YOURSCHOOLNAMEHERE.myschoolapp.com/api/authentication/login",
[Content = Text.ToBinary(ONLoginInfo), Headers=[#"Content-Type"="application/json"], IsRetry=true])),
T = Source[Token],
GetList = Json.Document(Web.Contents("https://YOURSCHOOLNAMEHERE.myschoolapp.com/api/list/YOURSLID#HERE/?t=", [Query=[t=T, format="json"]]))
in
    GetList
 
Also on @Quicky SKY API connector, now that Microsoft has announced general availability of custom connectors, could this be opened up to anyone? Everyone seems excited about the general availability of custom connectors but if you have to login and API calls are metered I'm not sure what that means for the SKY API. https://powerbi.microsoft.com/en-us/blog/announcing-general-availability-of-custom-and-certified-connectors-for-power-bi/

@Anonymous @Quicky @williamdasilva Just wanted to say that if you've got Grant Quick's custom connector working, you can get a jump start on data modelling and reporting with RE NXT received revenue with a free Power BI template (PBIT) here: https://resolvedllc.com/grab-the-butter/

@Quicky Ah-ha! Here I was thinking the custom connector handled all the query editing, else. I've got it now. All I had to do was click the [-] and rename to amount. I'm good now. Of course, now I'm going to poke around on the other queries to see what else is "hidden". Thanks again for all this!

@GGetty I can do that now...admittedly it's in pre-alpha form and only for the SKY API though 😉

 

But yes, as interesting a learning experience as it is, it would be ideal if this was something that was already produced by Blackbaud!

Good work @Quicky . 

 

With our ETL data warehouse, we've realized that we can run out of quota quite quickly (as you mentioned), especially when pulling objects that don't have a list end point. We've been able to circumvent this by doint more of a "sync ETL" vs a full dump, however I don't see how that would be possible with a custom data connector.

 

In any case, please continue to share your findings. Super interesting, and exploring all options is the best way to find the fit.

@williamdasilva No you're right, without a separate database/staging area it wouldn't be possible to just grab the most recent last_modifed records and update existing/insert new constituent IDs, so this method will never be as efficient as a local warehouse implementation in terms of calls to the API. It would have to be a full extract each refresh, but I'm sure it could be throttled, and refreshed once a day (in my org that should be fine). Plus I suspect your solution won't have any trouble refreshing with the Power BI Service since you're using a separate datastore to host the warehouse.

@Quicky Exactly. We are working with orgs that want to refresh anywhere between 75k - 500k constituents (let alone gifts, proposals, attribute etc...). 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors