Using a REST API as a data source

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.

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.

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 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: 

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.

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.

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:

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

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

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/

GGetty, I could not agree more! In fact, that's exactly what we are trying to offer through our ETL DW, and easy way for customers to plug PBI onto their RE NXT instance. This whole thread is case and point that it's too complicated right now.

The new insights through GoodData is great. In fact, we've been using it (in beta) since about April and were hesitant to release a DB product because of this included analysis tool. Our take on things is that the built in insights will work for most orgs using RE NXT. It's still missing key end points, but BB should add them in as demand for them comes in. What it will never do is more custom reporting for those complex orgs that want to cross key data points that are not/will never be available throuhg insights. Perhaps it's grouping on a subset or prefix of an appeal code structure, or something that requires a littlle data massaging before visualization. 

In short, we are providing the easy way to plug PBI onto RE NXT... for those that need PBI.

GGetty Thanks for that, but I'm not sure that it would work from what I've read so far, since the Sky API uses OAuth v2 for authentication. At this point I'm veering towards the creation of a custom data connector, but even if one was to be created, it would only work in Power BI Desktop and not the service. Microsoft have provided some example data connectors and this one https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph implements OAuth v2 and isn't exactly trivial!

williamdasilvaThere's definitely scope for a proper warehouse implementation with Power BI for RE NXT. My org hasn't even completed the migration to RE, but I'm trying to get ahead of the game - from what I've seen of the Sky Reporting tool, it is substantially more limited than is required by BI teams, particularly with respect to the examples you've given, as well as the opportunity to incorporate separate data sources.

Quicky, that's great! I'd love to play around with that data connector if you still want to share it when you get back from your vacaation. In the meantime, enjoy!

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

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: 

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!

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.

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!

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

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!

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