Power BI - Record Limit

Suzie Capps · August 27

Hi all,

I'm very new to Power BI and have just setup the connector with Blackbaud. I want to do a basic report including all gifts in the past 3 years. After transforming the data to filter by date + remove unneeded columns, when I try and load this data into Power BI I get an error "Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Web.Contents failed to get contents from 'https://api.sky.blackbaud.com/gift/v1/gifts?limit=5000&offset=285000' (500): Internal Server Error. '."
Searching here, I have seen mention of needing to do multiple calls to retrieve more data - can someone explain in basic terms how I'd go about doing this? It feels like being able to report on all gifts from the past few years should be a relatively simple thing to do?

Shaun Germany · August 27

Hi Suzie,

Someone more knowledgeable than me will comment on the record limit issue. But I would say that using Power Automate to schedule Query exports to SharePoint, then pointing Power BI at that file (also on a scheduled refresh) give better data options for Power BI reporting. You're in the UK I think so could probably arrange a Teams walk through of that process and the trip hazards I found when I first did it. I'm just using one of the templates kindly shared by I think either Alex Wong/Ashley Moose so its mostly just tweaking those.

Sharing my Power Automate flow: Get query results using the new Query connectors! — Blackbaud Community

Shaun

Erik Leaver · August 27

@Suzie Capps Check out this post where Matt Thacker & Alex Wong discuss the methodology of getting larger data sets:

https://community.blackbaud.com/discussion/comment/269777#Comment_269777

Alex Wong · August 27

it appears you are using the custom power bi connector (unofficial, supported by the community).

I recently saw someone having this issue too. So to explain the error: Power BI custom connector basically call the "list gift" api endpoint to get gift data, the list gift api only allows limit of 5000 gifts at a time, and in order to get all the gift data, it will require to make multiple calls, and then offset the next set of 5000. (you filtering for last 3 years is not taken into consideration, at least I don't believe so @Matt Thacker can better confirm this behavior), Meaning, if you have a total of 2 million gift records, it will try to get all 2 million gift records starting with:

offset 0 limit 5000
offset 5000 limit 5000
offset 10000 limit 5000

so on and so forth for 400 calls (400 x 5000 = 2M), THEN it will do the filter on gift date for last 3 years.

Your error is when you got to offset of 285000 (you got 280K gift record successfully), there is an internal server error in Blackbaud, and your call failed.

@Erik Leaver something that might need Blackbaud API team to look into, I see 2 posts about this problem already.

You can avoid this problem by using a SAVED RE NXT Gift List that is already filtered on last 3 years of gifts. Then in Power BI, select the list saved, and it will only get gifts that is based on this saved list's filter.

Shaun's comment is a good one as well, which basically means, get the data using power automate and save it somewhere in cloud (SharePoint), and then have Power BI go to that saved location for the data. There are many templates that will help you with it in template showcase.

Rebecca Sundquist · August 29

@Suzie Capps , great suggestion from @Shaun Germany . Typically, folks find there is data they can get from Query that they can't get from the connector.

Additional thoughts:

For the Power Automate route, even Power Automate has a limit on the size of .csv file that can be created from the Query results, so it can be necessary to break the data pull into smaller record sets and then combine all like-shaped files in a SharePoint folder connection from Power BI. Maybe 3 years of records is not large enough to be a problem.
For the custom connector: You can pull in a private or public gift LIST instead of all Gifts. Create a list in the Raiser's Edge that is filtered for the past three years and choose the list in your connector instead of all gifts. This will filter before the data gets to Power BI, which is the caution Alex mentioned.

Tony Mah · September 16

@Rebecca Sundquist Is there a trick on how to get my RE query to show up in in public/private lists? I noticed that there are a hand full of list already there but there are hundreds of queries. When I make a gift or a constituent query on an Appeal my query doesn't seem to show up on any of the PBI lists.

Rebecca Sundquist · September 16

@Tony Mah , you may be confusing Lists with Queries. You can create a Constituent List under the Constituent functional area: Constituents > Constituent lists. When you save a list, then you choose whether or not it is viewable by other users, and this determines Private/Public status.

Documentation: Lists

Note that the only part of the list definition that impacts what comes back through the Power Query connector is the filter on the list. It doesn't matter what columns you choose. The columns returned are defined by the endpoint Get Constituent list - API Reference - SKY API. In other words, the connector is using your selected list's ID as a parameter on this end point.

The same concept applies to Gifts where the connector taps into the list_id parameter on the end point Get Gift list - API Reference - SKY API.

The Community's open-source connector does not include Query retrieval.

Tony Mah · September 17

@Rebecca Sundquist Thanks a lot. I did mix up query with lists

Power BI - Record Limit

Answers

Categories