cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
williamdasilva Frequent Visitor
Frequent Visitor

Re: Using a REST API as a data source

Wonderful! We are actually looking for organizations to be part of an early adopter program.

I will wait for your DM.

williamdasilva Frequent Visitor
Frequent Visitor

Re: Using a REST API as a data source

Sounds good. Would love to hear how that turns out.

 

 

GGetty Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

Calls from Power BI into REST APIs still have a purpose. Two things I want to note: 1. http://on-api.developer.blackbaud.com/ (Blackbaud K12 API) and https://developer.sky.blackbaud.com/ (Blackbaud RE / FE API) are different APIs but both useful with Power BI (you can also do direct query calls from within RE if your SQL is good enough). 2. Power BI as a tool to tell a story with your data or explore your data is just as important (maybe more important) than an entire, complicated extract-transform-load > data warehouse model > reporting solution like @williamdasilva describes. I think the entire data warehouse and reporting model begins to compete with Blackbaud's own SKY Reporting / Insights available through analyze (currently using GoodData). I'm much more interested in EVERYONE, ANYONE being able to tell a story with their data and get from a query output / advanced lists / export into Power BI and explore. I think it will be great when folks want to see all of last year's donors at $5000 and up and they start with the question, "How can I SEE this visualized in Power BI?"

GGetty Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

@Quicky you would want to write an advanced query in the M language. The token is part of the URL in K12 API as well. Try something like this:

 

let
Source = Json.Document(Web.Contents("https://YOURURLHERE/api/authentication/login?username=QUICKEYDOE&password=12345&format=json")), //SWITCH FROM JSON TO WHATEVER YOUR API RETURNS//
T = Source[Token],
GetList = Json.Document(Web.Contents("https://YOURURL2HERE?",[Query = [t=T, format="json"]])),
#"Converted to Table" = Table.FromList(GetList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"FirstName", "LastName", "EMail", "NickName", "BadEMail", "PushpageUnsubscribe", "ChildFirstName", "ChildLastName", "ChildGradYear", "ChildGender", "ChildGradeLevelDescription"}, {"FirstName", "LastName", "EMail", "NickName", "BadEMail", "PushpageUnsubscribe", "ChildFirstName", "ChildLastName", "ChildGradYear", "ChildGender", "ChildGradeLevelDescription"})
in
#"Expanded Column1"

 

I think separating out the login / authentication from the target request to pull data will get you what you need.

 

This leads to two queries listed as sources in your Power BI desktop file Data Sour Settings that look like this outside of advanced query editor:

 

powerbi_sources.png

williamdasilva Frequent Visitor
Frequent Visitor

Re: Using a REST API as a data source

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

Quicky Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

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

williamdasilva Frequent Visitor
Frequent Visitor

Re: Using a REST API as a data source

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

Quicky Regular Visitor
Regular Visitor

Re: Using a REST API as a data source

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.

caseyh Member
Member

Re: Using a REST API as a data source

@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 Frequent Visitor
Frequent Visitor

Re: Using a REST API as a data source

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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 393 members 4,082 guests
Please welcome our newest community members: