nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Mirroring RE NXT Data for Use in Power BI

Jax Murphy

Hello,

I am a programmer/analyst but not a finance person. My experience with RE NXT is next to none. I have been asked to assist in building a Power BI dashboard for our institution's advancement office to reflect data in RE NXT. When connecting up via the custom connector in Power BI, there are some limitations to what data comes through, and my client tells me this incomplete import doesn't suffice for their reporting needs.

Doing some reading here on the forums, it sounds like this is a common issue, and that the workaround is to import the complete data to some kind of database using Power Automate. My experience with Power Automate is also very limited, and I'm having a hard time figuring out how to start. My goal is to export all of the same tables available via the Power BI connector (plus those that are missing, namely Fund custom fields) from RE NXT into a database that I can query with Power BI. In the end, the database should be near-instantly updated to mirror RE NXT.

Am I going in the right direction, or is there a better way to regularly export data from RE NXT into a database? If Power Automate is the best approach, which actions should I be looking at to read and write the relevant tables?

Thanks in advance for any advice.

Find more posts tagged with

Reporting

Microsoft Power Platform

Accepted answers

Alex Wong

https://community.blackbaud.com/discussion/comment/306660#Comment_306660

Power Automate is not free, but non-profit pricing is low: $3/month (per user, and you only need 1 user)

if you seeking high code programming route, that is not free neither. A server application will need to be hosted on a server (web server or otherwise).

high code programming route is similiar steps but with same hurdle to overcome first: authorization token.

ignoring steps needed to get authorization token or refresh a auth token, you can figure that out on your own
Execute a POST request to execute an Ad-hoc query or saved query on RE NXT.
DO UNTIL failed or success
1. wait 30 seconds (Blackbaud recommended checking interval)
2. Execute a GET request to get execution job status
if success
1. use returned sas_uri to download the results in csv, json, or jsonl
process the downloaded data as you like

All comments

Alex Wong

Power Automate is versatile enough to do what you want to do. More importantly there are official connector that already handles authorization for you. You can also create your own server application to do the data pull, and has some advantage over Power Automate, but does require you to handle authorization and refresh token yourselves.

You are always going to be limited by what SKY API expose data for, so your steps is going to be to check the SKY API reference documentation to see what data is available for you to pull into a data warehouse. Then you have a choice to gather data using Query API to pull additional data that is not available via the regular SKY API endpoints.

Carol Grant

My other suggestion if you are not ready to begin with Power Automate is to automate some queries or exports using the Queue module. Queue is an extra cost if you don't already have it. Queue is a scheduler for queries, imports, exports. Although it's not an instant mirror to NXT, they can be run every few hours, nightly, etc. I still use Queue for many things but I also use Power Automate for populating my Power BI reports.

There are many resources in the Microsoft Power Platform area to get started with Power Automate.

https://docs.blackbaud.com/microsoft-connectors-docs/microsoft-power-platform/getting-started

Jax Murphy

https://community.blackbaud.com/discussion/comment/306486#Comment_306486

From your description, I assume the Queue module is an add-on to RE NXT. We do not have this, but I'm glad to know there are some alternatives to consider.

Jax Murphy

https://community.blackbaud.com/discussion/comment/306485#Comment_306485

Alex,

Thank you for the advice. I have had no success so far in retrieving data using Power Automate, but I will keep reading the documentation in hopes I'll find something useful. Like I said, I know very little about the application, and even less about how data is stored in RE NXT, so I am going into this completely blind. Any suggestions for further reading or which connectors I could try using would be greatly appreciated.

Matt Clemmens

I've shifted away from the Blackbaud PBI connector on new PowerBI builds and am leveraging the Query API since nearly every field in the system is available via Query. By building a couple queries (or sometimes a LOT of queries), automating hourly feeds of each query to separate tabs in a Google Sheet via Google's Apps Script tools, and feeding this Google Sheets data to PowerBI, I've helped several clients access every field in RE (except the unqueriable Pledge Installment Balances… but have another workaround for that) within the PowerBI environment. @Nick Marchese and I are presenting these tools at BBCon on 10/7 at 11:30 if anyone wants to join us for this session in Philly next month!

Allan Delmare

@Jax Murphy currently going down this road myself, but to build a bespoke NXT interface for specific tasks/needs (specific reporting, task-specific data entry, etc.).

I've found that with a simple proxy to handle the oAuth and Query fetching + provide the basic API connection endpoint for Airbyte, I can us a 'custom connector' in Airbyte to establish a data pipeline that looks like this: NXT Query ←→ Proxy (Vercel) → Airbyte custom connector → (Airbyte "destination").

Currently using this to send NXT data to BigQuery where I need to blend the data with other sources. But for the project in question, I think this is unnecessary, and it will make more sense to just drop the data directly into a postgres (skip the whole proxy/airbyte connector leg of the journey and let my CMS handle the data management)

(NextJS/Payloadcms on Vercel + Neon Postgres for all of the above. Can share proxy code + YAML airbyte connector template if curious - just let me know).

Erik Leaver

@Allan Delmare Have you considered submitting your work process to the bbdevdays hackathon? I think it's a good candidate! https://bbdevdays.brightidea.com/2025Hackathon

Wyatt Mosteller

I built a python library for NXT. So, if your background in programming pertains to Python this can be a thing. Currently, it is set up for internal use and isn't the most complete thing, but it works well. We have made a connection to Power BI by utilizing Google Cloud and Google Big Query. We went the extra mile to automate things to run on an Apache Airflow instance (overkill for basic reporting, we just also have it handle other things) over a Google Cloud VM and dump the data directly to Big Query.

Prior to that though we just made an always on machine that would link to file systems accessible to Power BI and used basic json files. Still using this same library

Snippet of using the custom library:

#gather API credentials
creds = kwargs['ti'].xcom_pull(task_ids="get_refresh_creds",key="creds")
#"login" to SKY
SKY = SKYAPI(creds['auth_code'],creds['client_id'],creds['client_secret'],creds['sub_key'])
SKY.access_token = creds['access_token']
SKY.refresh_token = creds['refresh_token']

#fetch gift data
gifts = SKY.get_list('gift',params={'last_modified': start_date},timeout=120,limit=5000)

Essentially that is our connector interfacing with kwargs from a previous step in Airflow to then re-establish an existing connection to SKY. Then it does a basic gift fetch, we work with large but varying sized databases a lot, so I passed in longer timeouts and forcing the limit of calls per page to that 5000 mark. We recently moved our focus more towards using query lately as it is structured more similarly to other mass data pulling APIs I have encountered throughout my data engineering career.

#fetch data via unsaved query (easier than establishing a query in every database and then having to hunt it's ID down)
data = SKY.query_by_ad_hoc(json.loads("""{ad_hoc_dict_here}"""),formatting_mode='UI',timeout=600)

I do not currently have this library public, but if people care I don't mind sharing it (if company allows). But sadly, the best way to get data into Power BI, that we have found, is to store it off Blackbaud and reference that mirror.

James Parsons

I'd be interested to hear more about your journey — we've gone through a similar experience. We always understood the Power BI connector to be limited because of which endpoints it could access, and supplimented this by doing exports and queries. Originally we did this separately for each report, meaning our reports were sometimes not alligned; the easy fix was to export everything to a common folder and all reports point there.

From there we've been using Queue. The challenge here is that (as far as we've been able to find) you can only schedule it to export to the FTP drive (you can export them locally but only if you're signed in), and we haven't found a way for Power BI to access it. We copy the exported files to a local drive and now our reports are a lot more nimble.

We're exploring Power Automate as well. The query module is pretty robust but I'm not finding it very intuitive; once you get the hang of it you should be able to export any query to a drive in the cloud, and point Power BI there as a source. The bonus is that you should also be able to do some upstream transformations before the data gets to your report, which is a good practice anyway.

Alex Wong has a good template/articel about how to do this here. I found it helpful!

Erik Leaver

@James Parsons Check out this flow from @Dan Snyder which picks up a file in the FTP area and moves it to a folder where you can consume with power bi.

Alex Wong

https://community.blackbaud.com/discussion/comment/306534#Comment_306534

Here's a way to overcome (or not use) Power BI custom connector:

Sharing my Power Automate and Power BI template: Power BI use of Query API via Power Automate — Blackbaud Community

Rebecca Sundquist

@Jax Murphy , I am so glad your question has brought out the best of our Community! If you feel you still need an introduction or more context, check out Beginner’s Guide to Power BI featuring Blackbaud's SKY API® - YouTube.

@Matt Clemmens, I hope my BBCon schedule will allow me to attend your presentation with @Nick Marchese on Oct 7 @11:30 for your Google App Script solution managing Query results!
@Allan Delmare and @Wyatt Mosteller provided excellent examples of how transferrable SKY API is, as long as you get the secure authorization figured out. Well done!
Blackbaud's certified connectors in Power Automate take care of this authorization for you, which is a reason that Power Automate is a go-to for beginners. Today I published a Power Automate flow that loops through a SharePoint list to control query execution and output destinations: Sharing my Power Automate flow: Get multiple query results with Query connector and SharePoint list
@Alex Wong's template can be adjusted with parameters to re-use one PA flow to populate multiple tables directly in Power BI, bypassing a repository: Sharing my Power Automate and Power BI template: Power BI use of Query API via Power Automate — Blackbaud Community
@Carol Grant's suggestion by passes SKY API authorization by relying on exports and Queue. @Dan Snyder has shown how to use Power Automate to automatically move files from Blackbaud's SFTP to your repository.

I hope that the linked video helps make some sense of the many responses you have received.

Allan Delmare

Update - happy to report that I've created a custom airbyte connector that properly handles oauth and all SKY API endpoints (including incremental/append sync for queries) without any proxy — setup takes about two minutes with the custom YAML file … simple copy/paste, and I'm requesting that Airbyte add the connector to their marketplace so it's freely available for one-click deploy (basically like zapier for the SkyAPI). … 'no code' for basic endpoints (constituents, actions, funds, campaigns, gifts, etc.) and 'low code' (minimal configuration for environment-specific variables such as code table, list, and query ID's).

I'll share a walkthrough and source code when I'm satisfied things are optimized and mostly friendly for forking the connector and tweaking it to individual needs

Carlene Johnson

https://community.blackbaud.com/discussion/comment/306508#Comment_306508

Matt - looking forward to seeing you there and attending this session!

Jax Murphy

I think I have determined that the most versatile solution to my question is to leverage the Query API. However, I don't see any documentation about actually retrieving records, just piecemeal information about them. If someone has a fully built workflow that captures data using the Query API, I'd love to hear more about how you're getting the information you need.

Alex Wong

https://community.blackbaud.com/discussion/comment/306655#Comment_306655

there are many templates you can download and import to customize in template showcase

Blackbaud Community Power Platform template showcase - Microsoft Integrations

Jax Murphy

https://community.blackbaud.com/discussion/comment/306658#Comment_306658

Thanks, but I've decided against using Power Automate for this process as it is not free.

At this point, I am trying to harvest tables from RE NXT using the Query API, but haven't found documentation on how to retrieve actual records programmatically. The goal is to mirror our RE NXT database, but I am not finding a way to retrieve the tables.

Alex Wong

https://community.blackbaud.com/discussion/comment/306660#Comment_306660

Power Automate is not free, but non-profit pricing is low: $3/month (per user, and you only need 1 user)

if you seeking high code programming route, that is not free neither. A server application will need to be hosted on a server (web server or otherwise).

high code programming route is similiar steps but with same hurdle to overcome first: authorization token.

ignoring steps needed to get authorization token or refresh a auth token, you can figure that out on your own
Execute a POST request to execute an Ad-hoc query or saved query on RE NXT.
DO UNTIL failed or success
1. wait 30 seconds (Blackbaud recommended checking interval)
2. Execute a GET request to get execution job status
if success
1. use returned sas_uri to download the results in csv, json, or jsonl
process the downloaded data as you like

Allan Delmare

https://community.blackbaud.com/discussion/comment/306655#Comment_306655

Hi Jax,

I have released v1 of my airbyte connector.

https://community.blackbaud.com/discussion/83482/announcing-sky-bridge-an-airbyte-connector-for-blackbaud-nxt

Still need to create a video walk-through on setup, but you should be able to figure it out from the readme and setup instructions on the github.

The key for you will be to adjust the query ID. I have included two query stream templates/examples to use: one with incremental sync, one without. For incremental sync, you'll need to update the cursor field as necessary.

Please let me know if you need help. Welcome feedback and contribution.

About Airbyte cost: my airbyte usage stays below $10/month. Their no-obligation 14 day free trial provides something like 400 credits to use during the trial, which is an insane amount of free data transfer.

Nick Marchese

Hey all. Thanks @Matt Clemmens for tagging me in here. @Jax Murphy, we’d love for you to attend our session. If you can’t make the one with Matt and I, I have a second session specifically about this on Wednesday at 10:15 which is similar to my Bbdevdays presentation from June. I welcome you to either or both!

If you, or anyone else here, cannot make it and is interested in checking out what I’ve created, I have a pre-built Google Workspace Marketplace Extension for Google Sheets called Breaking into Blackbaud: RENXT/FENXT. This automatically syncs select query data into Google Sheets on a hourly (or different frequency) schedule without any coding whatsoever. Feel free to try it out for free on your own or reach out if you’d like to dig into it together. I won’t be at bbcon until Tuesday but would love to share with anyone in person. My email is nick@collarcityconsulting.com and you can check out my website at www.collarcityconsulting.com.

Have a good weekend!