Options and recommendations for integrating FE NXT to a custom data warehouse?

Berkley Guse

Do you have any options and recommendations you can share for integrating FE NXT to a custom data warehouse? We're looking to connect with anyone who has already successfully connected FE NXT data to a custom data warehouse(DW).


Below are the options we've investigated so far, but there are big trade-offs on each option,so we're unsure of which option to pursue.

• Option 1: SKY API alone
  • Blackbaud, a Blackbaud partner, a peer customer, and our team have confirmed that the SKY API does not (and likely will not in the future) expose about 50 FE fields we currently use from FE7 in our custom DW
  • Costs
    • $ - $$ for 100k - 250K calls per day
  • We can technically pull the FE data available in the SKY API and have completed a sandbox proof of concept test
  • Known issues
    • the vendor can change API without communicating changes in advance to API callers at some times
    • SKY API can have slow response times on calls
    • SKY API callers can hit max calls per period causing issues
      • if hit 5 calls per second threshold, can cause issues
      • if hit 25,000 per day threshold, can be locked out for four hours
  • Unknowns
    • How many API calls we will need to make per day
    • How many changes are needed in our custom DW design
• Option 2: SKY API supplemented with FE front end automated scheduled Queue Query Export CSV files
  • Build scheduled FE Queue exports for data not in SKY API and add exported data to the data from SKY API in the custom DW
  • Unknowns
    • If FE queue exports can be reliably scheduled in NXT (A peer customer indicated the scheduler in NXT doesn't work yet)
    • if FE exports can include all the data needed which is missing from the SKY API.  Since some of the data missing in SKY API are unique identifiers that may also not be exposed in the FE front end query/export, we still may not be able to pull all the data needed for our custom data warehouse.
• Option 3: DB backup/restore
  • A peer institution is live with a solution using a FE NXT backup/restore as the data source to feed a custom DW
  • Known Issues
    • This option would delay the data in our custom DW another day (2 days instead of the acceptable 1 day like today) because the backup isn't available until 8am the next morning after our nightly DW build has completed.
  • Unknowns
    • Can Blackbaud commit to an earlier backup 'as of' and delivery time?
    • Would our business stakeholders accept extra day delay in DW data all the time or on the frequency of the occasions when the Blackbaud backup is delayed?
    • How would BBCRM and FE data be kept in sync in DW if FE data is delayed?
• Option 4: MissionBI SQL Access
  • MissionBI is a Blackbaud recommended partner
    • https://www.missionbi.com/
  •  Costs
    • Some ongoing and one time
  • Know issues
    • New product (late 2019) not in production with any clients yet (as of 3/16/2020)
    • Not sure if all fields used by UWF DW not in SKY API can be pulled from FE front end by MissionBI
    • Adds another vendor and another MissionBI proprietary database we would need to map to our custom DW
  • Unknowns
    • Will required VPN connection to MissionBI DB work with SSIS or not?
• Option 5: Remove fields not in SKY API from custom DW and related reports
  • Impact assessment is in progress for review with business owners and report customers
• Option 6: A third-party ODBC driver from CData - https://www.cdata.com/drivers/blackbaudfenxt/odbc/
  • Not a known or recommended solution to Blackbaud
• Other Options
  • tbd

Thanks,

Berkley

 

Steven Cinquegrana

Hi Berkley‍. Being careful not to promote a product or service directly in the Community, I will PM some details regarding our SKYLib.NET SKY API library and SDK which might be of use to you.


It's not a turn-key solution to your requirement but it would make things much quicker and easier to develop.


Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions


 

William da Silva

Hi Berkley,


In the same sprit as Steven, not to promote but rather to help, we have a data warehouse solution that can sync your FE NXT (as well as RE NXT and BBLO) data to an SQL data warehouse.

This solution is live, in production with clients since 2019-06-01. We can grant you access to a managed SQL DB, or push directly to your own SQL DB. It's fully managed and we can sync your FE instance multiple times per day without hitting limits.


This would still require that Option 2 (exports for data not in SKY API) or Option 5 (remove or move data). Having done many of these, a combination of both is sometimes best. A little inventory of fields and reports might reveal that some fields are no longer as useful as they were when the reports were made, while other fields might live well in a new location (Custom Fields for example).


I'll PM you.


Thanks,

William

Thomas Evans

I'd like to clarify a few things regarding the Mission BI SQL Access comments. First, with regard to SQL Access Customers, we do in fact have customers. We released SQL Access for The Raiser's Edge NXT and Financial Edge NXT in December and our first customer came on board that same month. Since the start of the new year, we have continued to add new customers at a steady pace. 


It's also important to point out that SQL Access has been in use by a handful of our customers for over three years as a data source for their web based and specialized reports that we had developed for them. SQL Access, which is essentially an ETL platform, is proven technology that we have been actively developing for several years. It is not a newly developed offering.


With regard to RE and FE field availability from SQL Access, all RE and FE data that is available from the SKY API is delivered (and continually sync'd) to the SQL Access RE and FE databases via our platform. For the shrinking subset of RE and FE data that is not yet available from the SKY API, we have a proven, completely automated solution in place that can deliver (and continually sync) some or all of this subset of data (if needed) to the SQL Access RE and FE databases. This solution ensures that all of your RE and FE data is included in your SQL Access RE and FE databases, with data refreshes for most data every 20 minutes.


Regarding the VPN connection, the VPN establishes a secure connection and has the added benefit of establishing essentially a local connection to your SQL Access RE and FE databases. We are happy to provide our customers with a site-to-site VPN over individual VPN connections or even lock down the connection to designated IP addresses. We have found that most of our customers appreciate the high degree of encryption and security that a VPN provides. 


-Thomas Evans

Kathy Miller

I have been following your discussion about integrating the FE NXT into a sql data warehouse. We are working on developing a similar process. Do you happen to know what API to use to access the users that are set up in FE NXT? We are looking for a way to link our Human Resources Management System to sql DB so that we can determine when a new hire needs to have an account created for access to FE NTX? THanks for the help.