Data Loading Issues

Adnan Longi

I am noticing an issue when loading and refreshing the Raiser's Edge Gifts data source via the Power BI connector. The connector/API is taking over 20 minutes to load ~220,000 rows. I tested using the Power Automate connector and that only took 5 seconds to run the same query (~220,000 rows). I am wondering if anyone is experiencing similar issues or if there is a reason why there is a discrepancy in load times if both applications are using the same API calls to grab the data.

In both cases, I am using the following API call and grabbing batches of 500 rows at a time:

https://api.sky.blackbaud.com/gift/v1/gifts?start_gift_date=2022-06-30

Any help would be greatly appreciated. Thanks!

Alex Wong

@Adnan Longi
if you intent to grab a lot more records, I don't think Power BI connector is your best choice.

I am not certain if Poewr BI is using 500 limit per API call, but in Power Automate you certainly can make the limit 5000, thereby achieving faster loading by factor of 10.

Adnan Longi

@Alex Wong
I am using the 500 limit for batching the results in both Power BI and Power Automate, but I am still getting this issue. Power BI takes over 20 minutes while Power Automate is taking 5 seconds for the exact same query.

Alex Wong

@Adnan Longi
Power Automate takes 5 seconds for one call to the API to get up to 5000 records (default 500). If you intention is to get 220K records, it will take 44 calls to the API, with 5s per call, that's ~3.6 minutes. There is no reason to get 500 at a time when you can do 5000.

Adnan Longi

@Alex Wong
I think there is a disconnect with what I am saying. Power Automate is getting all 220k records in 5 seconds. The problem that I am having is that Power BI is taking over 20 minutes to get the same 220k records. I was hoping to get some feedback on why Power BI is taking so much longer than Power Automate (20 minutes vs 5 seconds for 220k records).

Alex Wong

@Adnan Longi
I would be interested to know how you able to get all 220K (220,000) records in 5 seconds.

A single call to list can only return 5000 records. Even if you run concurrently, which you can probably can up to 10 due to SKY API limitation to run 10 calls per second, you are still looking at a maximum of 50K in one burst, and each calls take 5s to return the 5000 records. Since you said you are using Power Automate, can you provide a screenshot of the run history that shows your setup and time each action took? (keep in mind, the action to list record may take 5s, but it looping 44x requires you to click next 44 times to see each time list action, the over “apply to each” or “do until” is what will tell you in total how long getting all 220K took.

Rebecca Sundquist

@Adnan Longi , my first thought is that it is a common misconception to apply a filter on gift date in Power Query and assume this means the API is only pulling records for that date forward. Filters in Power Query are applied after the entire list of gifts is pulled in.

However, I suspect you knew this already and you have done something tricky. We are talking about this open source connector for Power BI, right? GitHub - blackbaud/powerbi-connector: Blackbaud data connectors for Power Query and Power BI

As @Alex Wong is curious about your Power Automate approach, I am curious about how you are applying a limit of 500 in Power BI. Did you edit the custom connector in Visual Studio? The DefaultRecLimit in the Blackbaud.pq file is 5000, and I think the Gift call references the default.

Were you in the Blackbaud.pq script to add a filter parameter for the start date in GiftByList?

One thing you can try instead is to create a Gift List in the Raiser's Edge NXT and then reference that public list in the Power BI connector.

screen shot of Power BI Connector Navigator in Power BI