Raiser's Edge Custom Connector Data Loading Issues

[wemmeLongi]

Hi, I am wondering if anyone has any experience and can help with the Raiser's Edge custom Power BI connector.

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 a couple of minutes 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!

[v-hashadapu · Accepted solution]

Hi @wemmeLongi , Thank you for reaching out to the Microsoft Community Forum.

The difference in time may come down to how Power BI processes the data. It adds overhead by automatically detecting data types, expanding nested fields and applying transformations, things Power Automate skips. It also fetches pages sequentially and if Blackbaud enforces rate limits, Power BI may pause and retry, slowing things down further. If you're using the Power BI Service, your data gateway might be contributing to the delay as well.

Start by optimizing your Power BI query. In Power BI Desktop, open the Query Editor, review the Applied Steps and remove any unnecessary transformations, especially expanding records or sorting, which are best done after the data loads. Then replace your query with this efficient pagination script:

let

    PageSize = 500,

    GetPage = (offset as number) =>

        let

            Source = Json.Document(Web.Contents("https://api.sky.blackbaud.com/gift/v1/gifts", [

                Headers = [#"Bb-Api-Subscription-Key"="YOUR_KEY", Authorization="Bearer YOUR_TOKEN"],

                Query = [start_gift_date="2022-06-30", limit=Text.From(PageSize), offset=Text.From(offset)]

            ]))

        in

            Source[value],

    AllPages = List.Generate(

        () => [offset = 0, page = GetPage(0)],

        each List.Count([page]) > 0,

        each [offset = [offset] + PageSize, page = GetPage([offset])],

        each [page]

    ),

    Combined = List.Combine(AllPages),

    Output = Table.FromList(Combined, Splitter.SplitByNothing(), {"gift"})

in

    Output

This minimizes processing and loops efficiently through the pages. To check performance, try running it with a smaller date range to isolate whether the issue is data volume or something else. If it’s still slow, use Postman to test the same API call and see if you’re hitting rate limits (look for 429 errors). If so, reduce the batch size in the script from 500 to 100 and test again. If you're refreshing through Power BI Service, compare the speed to Power BI Desktop. If Desktop is much faster, the gateway may be slowing things down, check that it’s updated and not under heavy load.

Since Power Automate performs well, another reliable option is to use it to pull the data into a CSV file (e.g., on OneDrive) or a SQL database. Then connect Power BI to that file or source instead of querying the API directly. This offloads the slow part of the process and lets Power BI just read already-staged data.

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[v-hashadapu]

Hi @wemmeLongi , I hope you're doing well! Just checking in to see if you had a chance to review the details shared earlier. If any of the information addressed your needs, feel free to mark it as "Accept as Solution"  to help others in the community. Please let me know if you have any further questions!

[v-hashadapu]

Hello @wemmeLongi , Just getting back to see if the shared details answered your question. If so, marking it as "Accept as Solution" and giving a 'Kudos' would be greatly appreciated to guide others in the community. Feel free to reach out with any additional questions!

[v-hashadapu]

Hi @wemmeLongi ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[Poojara_D12 · Accepted solution]

Hi @wemmeLongi 

The performance discrepancy you're seeing between the Raiser's Edge custom Power BI connector and the Power Automate connector—even though both use the same API endpoint and batching approach—likely stems from how Power BI handles data retrieval under the hood. Power BI’s Power Query engine is optimized for flexibility and transformation rather than raw speed, and when it makes API calls, it typically does so sequentially rather than in parallel, unless custom code or a custom connector explicitly enables parallelism. In contrast, Power Automate workflows can be more efficient at processing API calls in parallel or in batches more quickly, especially if the flow is designed for performance and minimal transformation overhead. Furthermore, Power BI may be doing additional metadata inspection, type conversion, or refresh validation during the data load, which can add significant overhead compared to the relatively lightweight execution of Power Automate. If you're using a custom Power BI connector, ensure that it’s optimized for pagination and consider using techniques such as Table.Buffer, disabling unnecessary schema validations, or enabling concurrent calls if your connector supports it. Otherwise, the bottleneck may persist due to Power BI’s more transformation-heavy and sequential query execution model.

[v-hashadapu · Accepted solution]

Hi @wemmeLongi , Thank you for reaching out to the Microsoft Community Forum.

The difference in time may come down to how Power BI processes the data. It adds overhead by automatically detecting data types, expanding nested fields and applying transformations, things Power Automate skips. It also fetches pages sequentially and if Blackbaud enforces rate limits, Power BI may pause and retry, slowing things down further. If you're using the Power BI Service, your data gateway might be contributing to the delay as well.

Start by optimizing your Power BI query. In Power BI Desktop, open the Query Editor, review the Applied Steps and remove any unnecessary transformations, especially expanding records or sorting, which are best done after the data loads. Then replace your query with this efficient pagination script:

let

    PageSize = 500,

    GetPage = (offset as number) =>

        let

            Source = Json.Document(Web.Contents("https://api.sky.blackbaud.com/gift/v1/gifts", [

                Headers = [#"Bb-Api-Subscription-Key"="YOUR_KEY", Authorization="Bearer YOUR_TOKEN"],

                Query = [start_gift_date="2022-06-30", limit=Text.From(PageSize), offset=Text.From(offset)]

            ]))

        in

            Source[value],

    AllPages = List.Generate(

        () => [offset = 0, page = GetPage(0)],

        each List.Count([page]) > 0,

        each [offset = [offset] + PageSize, page = GetPage([offset])],

        each [page]

    ),

    Combined = List.Combine(AllPages),

    Output = Table.FromList(Combined, Splitter.SplitByNothing(), {"gift"})

in

    Output

This minimizes processing and loops efficiently through the pages. To check performance, try running it with a smaller date range to isolate whether the issue is data volume or something else. If it’s still slow, use Postman to test the same API call and see if you’re hitting rate limits (look for 429 errors). If so, reduce the batch size in the script from 500 to 100 and test again. If you're refreshing through Power BI Service, compare the speed to Power BI Desktop. If Desktop is much faster, the gateway may be slowing things down, check that it’s updated and not under heavy load.

Since Power Automate performs well, another reliable option is to use it to pull the data into a CSV file (e.g., on OneDrive) or a SQL database. Then connect Power BI to that file or source instead of querying the API directly. This offloads the slow part of the process and lets Power BI just read already-staged data.

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.