Reply
Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

[ Edited ]

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

Frequent Visitor
Posts: 13
Registered: ‎04-17-2017

Re: Using a REST API as a data source

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

Regular Visitor
Posts: 23
Registered: ‎12-22-2016

Re: Using a REST API as a data source

@Quicky @caseyh @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

Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

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

 

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

Frequent Visitor
Posts: 13
Registered: ‎04-17-2017

Re: Using a REST API as a data source

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

Regular Visitor
Posts: 23
Registered: ‎12-22-2016

Re: Using a REST API as a data source

@williamdasilva @caseyh 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?

Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

[ Edited ]

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

Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

[ Edited ]

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.

Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

[ Edited ]

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

 

Regular Visitor
Posts: 23
Registered: ‎12-22-2016

Re: Using a REST API as a data source

@Quicky G-d bless you! It's awesome and working. @caseyh 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.