Mirroring RE NXT Data for Use in Power BI

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 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).

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 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.

@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.

1. @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!
2. @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!
3. 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
4. @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
5. @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.

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

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

Blackbaud Community Power Platform template showcase - Microsoft Integrations

Hi Jax,

I have released v1 of my airbyte connector.

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.