RE Nxt data integration with other data source

here are what I can help answer.

• Is there any way to data sync all tables data from RE Nxt to other data sources based upon modified date?
  SKY API will not do this as you already aware, you can do 'nightly backup" which is extra fee from Blackbaud
• Can we suppress consent access dialog to obtain bearer token and make API calls using Client Id, Client Secret and Bb-Api-Subscription-Key in real-time data integration?
  cannot with OAuth, you need to do OAuth (that need user interaction to login) to get auth token and refresh token, store that securely. Then you can use the refresh token to get new auth token (which will also provide a new refresh token if you desire, and re-store that for next use). Auth token is good for 60 minutes, while refresh token good for 365 days.
• Can we use service account kind of user instead of specific user developer account?
  I think you can, but you will need to deal with 2FA if multiple user going to login to do work
  

@Prasad G

• Raiser’s Edge Nxt (Online) will retain the same database schema (tables, fields, triggers etc.,) as Raiser’s Edge (On-prem) or any other changes in database schema?
  RE NXT (Online) retain the same database schema, don't think of RE NXT being a completely different program, RE NXT is basically the same RE (on-perm or blackbaud hosted) that you access via citrix receiver or RDP, but added a “web interface” (call webview) to it, along with new functionalities that exists only in the webview. So the underlying database is exactly the same. However, the webview only functionalities added new database tables that you do not have access to. Check out this URL
  https://developer.blackbaud.com/skyapi/docs/in-depth-topics/api-lists
• Post data migration of Raiser’s Edge to Raiser’s Edge Nxt all tables ID’s data will remain same as before or regenerate new IDs?
  the migration is just taking your database backup and restore in Blackbaud environment, it is completely the same data (meaning all table data including ID, codetableid, tableentriesid, are all the same)

@Prasad G:

Can we get deleted records data from RE Nxt post delete action or records will get deleted permanently without marking as inactive in Database?

not sure I understand the question or maybe it is a multi-part question, so I'll try to answer what I think your question may be:

There is no SKY API endpoint that will give you info on deleted record (i.e. constituent, gift, etc). There is a webhook API that you can “hook” the event of constituent delete and gift delete to a webapp. So anytime a record is deleted, the webapp will be triggered and provided the record id of the deleted record (but nothing else related to the record). See this post from Ben Wong: https://community.blackbaud.com/forums/viewtopic/586/55393

records can be deleted permanently from RE, some allowed on the webview, some only can be deleted in database view.

@dennis ladnier
yes, you can do that (one table at a time), depending on what business needs are being satisfied. I thought Prasad is asking a delta for the entire database, which I'm not sure about.

I'm assuming that Prasad wanted to get a list of deleted records (constituent, gift, etc) in RE to remove from the data warehouse that he is planning to create. Since a “last modified” list of constituent does not provide any details of deleted record nor have, if he simply get constituent last modified, then his database table for constituent will be flawed, as it will have constituent record that was deleted.

However, if you are going to do get a complete list of constituent each and everytime anyway, then the constituent table will always be correct, no need to know what has been deleted.

getting a complete list is OK when your constituent table is not that big, but quickly become not practical when it is half a million records. (I have ~500K constituent record and keep my data warehouse refreshed every 4 hours, and doing a full download everytime is not ideal. Considering max # of records to get from cosntituent list is 5000, you will need to loop and run the API 100x to get all 500K records). A full run will take ~20 minutes, meanwhile I have 30 other tables to sync.

Data warehousing is an art, an art that depends on many factors, including database size, so plan accordingly.

@dennis ladnier
it's good to talk to fellow community member that has done data warehousing.

it doesn't takes “hours” but I allow only 1 hour, as I mention, I keep data warehouse refreshed every 4 hours. 5AM, 9AM, 1PM, 5PM, 9PM. then at 6AM, 10AM, 2PM, 6PM, 10PM is when I scheduled all my Power BI to refresh its data from the Azure data warehouse. Given API rate limit (10 calls per second), I cannot do too much concurrency. Also, I do bulk insert using OPENJSON function of T-SQL, not looping to insert 1 record at a time.

Here's how long some of my major table takes:

• Full constituent table - ~16-25 minutes
  • 100 loops - SKY API call is ~5s-10s, INSERT into SQL is ~2-3s, so each loop is about 10-15s.
  • so to finish all 100 loops, will take ~16-25 minutes.
  • I do not do this anymore, I use iterative last modified load for this table and rely on my constituent delete webhook to remove constituent deleted from my constituent table, which takes ~1 minute
• Full constituent code table ~9-13 minutes
• constituent custom fields (only selected one) ~3 mintues
• constituent email, phone, relationship and constituent note ~19-22 minutes
• Full gift table ~1.5 - 2 hours (I only do this nightly at midnight, as the iterative gift approach has a flaw where some “change” to the constituent record id is not consider last modified)
  • Iterative gift (also loading pledge installment and payment) ~3-5 minutes
• gift custom field (selected, some done through QueryAPI for the more massive ones) ~25-35 minutes

to name a few and that's not all of them, so you can imagine trying to do all of them within a 1 hour window isn't that easy, some runs concurrently, some has to be sequential due to rate limit, and use iterative for the constituent and gift table is needed.

@Prasad G SimpliPhi has available a Data Warehouse solution that leverages the SKY API (for RE NXT and FE NXT) and the Luminate API (for Luminate Online). Please email me at: joel@simpliphi.io if you would like to discuss in greater detail. Thank you