Blackbaud Power BI connector to Raisers Edge NXT

Thomas Kroljic
Thomas Kroljic
Fourth Anniversary Facilitator 1
in SKY Developer General Discussions
Power BI Connector and the PBI on-premise Gateway: Performance

I recently went through the appropriate steps to create a custom connector to access Blackbaud / Raisers Edge NXT. I was then able to create a PBI report on my local desktop where I was able to connect to our Raisers Edge data in the cloud and import the Gift and Constituent tables into the report. Note, for the Gift table I filtered the data to only import gifts with a date >= Oct 1st, 2020.


The import process took about 30 minutes to import roughly 120,000 rows from the Gift table.
Once the data was imported, I published the report to a Workspace on Power BI in the cloud. I then logged into Power BI online and went through the configuration process to associate the dataset for this report with the on-premise Gateway. After completing this task, I then schedule the data source connection to refresh at midnight. This morning I noticed that the refresh took about 45 minutes.
Question:
  • is there a way to decrease the amount of time it takes to refresh the dataset or is this just amount of time it will take using this approach?
  • Are there other options available, such as an API, that would allow me to directly access the Raisers Edge data and download it from the cloud to a local SQL table on premise?
Thank you for your help,
Thomas Kroljic
Philabundance

Comments

  • Ivan Peev
    Ivan Peev
    Fifth Anniversary Name Dropper Participant Facilitator 1
    Hi Thomas,


    I would recommend that you check the commercial COZYROC SSIS+ library that extends Microsoft SQL Server Integration Services with a connector for the Blackbaud's SKY API. The SSIS framework will provide the best possible performance compared to any other solution on the market.
  • Thomas Kroljic
    Thomas Kroljic
    Fourth Anniversary Facilitator 1
    Ivan,

    Thank you for the information. I'll take a look at this specific product.


    Tom
  • Brian Pugh
    Brian Pugh
    Ninth Anniversary Kudos 1 Name Dropper Facilitator 1
    I have been using MS Power Bi to connect to RE NXT. I can retrieve almost any data other then Gifts. With gifts, the app returns "Expression.Error: Access to the resource is forbidden." I have full access to our RE db.


    Do you have any idea what I need to do?


    Thank you very much.


    Brian
  • Ben Lambert
    Ben Lambert
    Ninth Anniversary Kudos 5 Name Dropper Participant
    Hi Brian,


    The Power BI connector uses the ListGifts endpoint in the SKY API to fetch the data from the instance of Raiser's Edge NXT you're accessing. This endpoints requires access to Lists in NXT (because it pulls the data from the same data structures that power gift lists in NXT). So double check that you have access Lists -> Gifts in the system. You can also try using the SKY API Console to make a call to the ListGifts endpoint to see if that is successful.


    If you still have problems, let us know and we'll troubleshoot further.
  • David Lee
    David Lee
    Fourth Anniversary Kudos 2 Participant Facilitator 1
    Good point Ben Lambert, thanks for the tip.
  • Carol Grant
    Carol Grant
    Seventh Anniversary Kudos 5 First Reply Name Dropper
    Hi Thomas- I have voiced your same concern many times without resolution. Gifts take forever to load into Power BI Desktop. It took me 2 hours to process about 20,000 rows which seems ridiculous! Did you filter the date in Transform when using Power BI Desktop? As far as Ben Lambert's suggestion- I do have access to Gift Lists in NXT. I don't think you can make API calls in Power BI Desktop unless someone else knows how to do that in DAX or M query language?


    Good luck with this!


    Carol Grant

    Middlebury College
  • Thomas Kroljic
    Thomas Kroljic
    Fourth Anniversary Facilitator 1
    Brian,

    I followed the instructions stated in the Youtube video on how to configure the connector. Other then the instructions in the video, I didn't do anything else. As stated in my first posting, it takes quite a bit of time to download the data.

    After downloading the data I eventually published the report to the Power BI online site. I then had to configure the Gateway which was pretty easy and now the "dataset" refreshes nightly at midnight.

    Sorry I can help you out in your specific situation. Again I would go through the Youtube video as those instructions worked like a charm.


    Tom
  • Thomas Kroljic
    Thomas Kroljic
    Fourth Anniversary Facilitator 1
    Ben,

    Thanks for the post. I went back to my power bi report and went into the Transform configuration. It was that I could expand the Gift List to get the additional fields (columns like Appeal_ID, Fund_ID...)


    Tom
  • Thomas Kroljic
    Thomas Kroljic
    Fourth Anniversary Facilitator 1
    Carolyn,

    Thank you for you post. I was able to expand the Gift List in the Transform module in Power BI. I'm now able to get the additional columns such as Appeal_ID, Fund_ID that I can use to join the tables in the model view.

    At this point, we published the dataset to the Power BI online site, configured the Gateway, and created a schedule to refresh the data nightly. So far it's refreshing nightly without a problem. My next step is to see if we can use Power Automate to "export" the data to a CSV file on one of our servers. If I can do that, I can easily import that data into our SQL tables on premise for additional reporting.

    Tom
  • Brian Pugh
    Brian Pugh
    Ninth Anniversary Kudos 1 Name Dropper Facilitator 1
    Which video do you refer to, Thomas? (maybe I'm "blind" as I didn't see the title.


    Thanks very much,


    Brian
  • Brian Pugh
    Brian Pugh
    Ninth Anniversary Kudos 1 Name Dropper Facilitator 1
    Ben, I have full access to our RE NXT Database, 'cloud' and database views. I create lists frequently. Still blocked when I use Power BI Desktop. I really appreciated your help.


    Brian
  • Thomas Kroljic
    Thomas Kroljic
    Fourth Anniversary Facilitator 1
    Brian,

    (2) Create a Power BI Connector for Raiser’s Edge NXT - YouTube


    Tom
  • John Wooster
    John Wooster
    Fourth Anniversary Facilitator 1
    Hi Thomas - Thanks for your post. I believe we can help you.

    Mission BI Reporting Access™ and SQL Access™ are Blackbaud Marketplace Applications that enable standardized data source connections to your RE NXT™ and FE NXT™ data from virtually any reporting platform such as Microsoft Power BI, Tableau, or Crystal Reports.

    Reporting Access™ and SQL Access™ are much more than simple API connectors. They are specialized database solutions, hosted and managed in the secure Mission BI Cloud, and optimized as data source connections for advanced custom reporting.


    Let's jump on a call to become acquainted and discuss how I can help. Please use the link below to drop a time on my calendar for us to connect.


    View my schedule and book a Conversation with me HERE.

    Best,

    John

    John Wooster

    CRO | Mission BI, Inc.
    843.491.6969 | https://www.missionbi.com

    bb-partnerships.png

Categories