PowerBI and RE NXT connected via SKY API - Error when Loading Files

Elizabeth Hall

I am thrilled that I successfully linked Power BI to Blackbaud via SKY Developer. Unfortunately, Power BI is taking a very long time to “Load” all of the files available and times out at the very end. Has anyone else experienced this and how did you overcome? Should I load each “Type” Constituent Data, Gift Data etc. one by one and save or is there another method perhaps? Thanks for your support!

Alex Wong

@Elizabeth Hall
Power BI connector for Blackbaud data can be very slow depending on what you are trying to do and how much data is there in your RE.

What exactly are you trying to achieve? let's start there.

Elizabeth Hall

@Alex Wong
I would like to create various dashboards using Gift and Constituent Data. I will need at minimum Gift, Gift Custom Fields and Constituent, Constituent Custom Fields. At this point I am trying to see how easy it is to use this API functionality and what are the most common issue's folks are having. We have large exports of gift data done on a regular basis containing all of the fields we need that I could use and refresh but was hoping to avoid this using the API. Either way, I would like our Org to start to use Power BI which I'm familiar with.

Alex Wong

@Elizabeth Hall
if you are going to create multiple report and dashboard via Power BI, I don't recommend you go the Power BI connector route. Every BI report you create will need to issue call to SKY API to get data (i..e Giving Analysis will need gift and constituent data, then if you create a Donor map, you will need constituent data and gift data again and some additional other tables.) You can “shared” data between multiple reports but has limitation to add more data as you go.

So if your plan is to create many reports, I recommend you use another method to gather data (Power Automate) and save to a location (data warehouse, Sharepoint, etc) that can then be picked up by Power BI (multiple BI can consume the same data source).

When I toyed with Power BI, the most difficult problem is with gift attributes, simply b/c there are way too many per gifts. The way Power BI will preload some data, first 1000 i think, and allow you to filter for example, however, the filterable values is based on the 1000 that got loaded and if you click the wrong button, it is going to try to load everything to give you all the unique “category” to filter on, which caused my Power BI will freeze for hours and never load.

Can just using Power BI connector works? yes, but you will need to be careful on what you click on in Power Query editor or else your Power BI will freeze and never return. I strongly suggest you start with just constituent and gift table, not attribute (custom field).

David Hecht

@Alex Wong, @Elizabeth Hall

I hope you don't mind me jumping in on your conversation. I have been using the method that Elizabeth is discussing with the Power BI connector for a couple of years. It seems to be a stable process for the most part. But Alex is right about it taking some time to refresh. A couple of my larger data sets take 30-40 minutes to refresh.

I will also say that starting Tuesday afternoon, I started having issues with the refresh and have not been able to complete the process for my larger data sets since then. It appears to me that something has changed and the API calls are not responding as fast normal. My refreshes run for about an hour or so and then time out. So there might be a problem that has not been identified at this point.

Alex Wong

@David Hecht
I have a overnight flow that starts at 1AM, runs normally for 2 hours to get FULL gift data, close to 2millions gift record.

Wednesday morning, it failed with “server timeout” and happened again this (Thursday) morning.

So I think something did happen in Blackbaud and I was going to report this.

My error message is:

{

"error": {

"code": 504,

"message": "Request to the backend service timed out",

"source": "flow-apim-unitedstates-002-eastus-01.azure-apim.net",

"path": "choose[3][file://when[1]/choose%5b1%5d/otherwise/send-request]\\\\when[1]\\\\choose[1]\\\\otherwise\\\\send-request",

"policyId": "",

"clientRequestId": "7a26fd8c-b09a-41ca-b82c-d335dd775f5c",

"dreeSet": "true"

}

}

@Erik Leaver do you think it make sense for me to report to support (knowing they aren't too much help there with API especially with “intermittent error”)

Erik Leaver

@Alex Wong Let's report. @David Hecht can you report as well? More reports of the same issue will raise the priority. I'll flag the cases when they are created so I can follow.

Alex Wong

@Erik Leaver
Thanks, this is the support case 019964731

David Hecht

@Alex Wong, @Erik Leaver

Thank you both for your help. I will also submit a ticket. I also heard that there is maintenance being done that might be an issue. Hopefully this gets some attention.

David Hecht

@Alex Wong @Erik Leaver

Here is my case number in case you need it: 019964820.

Alex Wong

@Erik Leaver @Ben Wong
I don't know what's going on, but no one in Blackbaud responded to my ticket that was created yesterday noon est. My full gift sync failed again with server time out last night, this is consistent error every night so far for 3 nights in a row.

@David Hecht did anyone response for yours?

Sorry did not mean to hijack this post, but since it was started here….

David Hecht

@Alex Wong @Erik Leaver @Ben Wong

Hi Alex,

I started my ticket using the chat. The agent said they would need to pass this one along to someone else and they would get back to me. I have not heard anything since. That was a little after noon, central time. All my refreshes failed again this morning so it doesn't appear that anything has changed from my perspective. Thanks!

Ben Wong

@David Hecht @Erik Leaver @Alex Wong sorry about the issue. I'll check out the support tickets and make sure the product teams are aware.

David Hecht

@Ben Wong @Alex Wong @Erik Leaver

Hi Ben,

I appreciate your help. I have not hear anything more on my case. I have gone into the case and escalated it hoping to get some more information.

Ben Wong

@David Hecht @Alex Wong do you know which specific endpoints are being called that are timing out? I suspect GET Gift List but are there others?

Alex Wong

@Ben Wong
Get method, gift list.

Specifically Power Automate connector action of send HTTP request (not the get gift list action directly). This sync uses date_modified such that the call that keep failing is using sort_key

David Hecht

@Ben Wong
My issues seem to be with the gift list as well.

David Hecht

@Ben Wong
Ben,

Just to be clear, I am trying to refresh Power BI dashboards using the connector.

David Hecht

@Ben Wong @Alex Wong

Good morning, Ben and Alex. Just to let you know, I was able to refresh my dashboards yesterday and again this morning. It appears that whatever the issue was has been resolved. Any idea as to what changed? Thanks!

Ben Wong

@David Hecht @Alex Wong Good to hear that things are working! I don't have the details but there was a change made on Tuesday that fixed a bug but there were reports of some slowness after the change and your reports of the impacted the Gift List endpoints. They continued working on it and pushed out another fix on Friday.

Thanks for filing the support tickets to bring this to our attention!

Alex Wong

@David Hecht
Thanks for the update.

I have turned off my nightly full sync and only relied on my every 4 hours iterative sync for gift in the last few days. I have not retry the full gift sync that has the error reported. I will do that tonight to see if the issue went away.

I am also interested in knowing if BB confirm something was fixed.

David Hecht

@Alex Wong

Hi Alex,

In case you are interested, here is the explanation I got back on the issue:

"A change was made to the Gift List to address a customer bug related to invalid data that was stored on their APPEAL table. A change was made to the code to try to properly handle the unexpected characters. While this worked and corrected that customer issue, the additional handling of the invalid characters increased the time required to process/format a gift in the gift list.

This wasn't noticeable to most front-end users of the Gift List since the list will only show the top few records, but it was impactful to API users that were processing large numbers of records. To correct the problem, we removed the fix that was put in place for the customer bug which allowed performance to return to normal and we cleaned up the invalid data from the APPEAL table from the customer that reported the original issue"

Alex Wong

@David Hecht
Thank you for the info. Confirming that my issue is resolved as well. Thanks.