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