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; } } }

Read-Only SQL Raiser's Edge Database Access

Kevin Hardy

This almost feels like a long shot, but hey... can't score if you don't take the shot!

As a hosted NXT client, is there a way to get SQL access (read-only) to our hosted database?

The SKY API is definitely of some use here, but of course certain data sets don't currently have endpoints (such as the schedule for Pledge payments or membership renewal history)

We're not looking to change any data, but just perform SELECT queries on the db like we used to when we had RE in-house (or when we do the tedious manual "request a backup" process).

Any options here? Direct SQL access to the DB? To a mirror of the DB?

If anyone has any ideas it would be most helpful!

Thanks

Find more posts tagged with

Comments

Christopher Dawkins

Impossible, I know, but YES PLEASE. Particularly to get those fields for which there is no API access. Maybe through an API call we send with an SQL payload. Maybe with SQL crafted for us, for a hopefully small fee, by a Blackbaud developer (though this might reduce the incentive for Blackbaud to extend the API). The fields we have that are inaccessible to the API are a BIG nuisance.

Patrick Clancy

Read only access would be perfect.

I'm reporting on RE NXT using PowerBI and in order to have meaningful tables, I would like to be able to make SQL do all the heavy lifting and then query my own tables.

robert badley

Sadly, I believe that this will have to remain a dream for you. It is one of the main reasons that we are still on RE 7.96.

Steven Cinquegrana

SKY API is supposed to give you access to everything that you're supposed to need access to. Obviously, this is not yet reality. But one day ...

Personally, I'm not sure what would be so difficult about this, appropriate security, load throttles, etc withstanding. I mean, remote SQL access is a given for cloud servers. It's probably more of a policy thing.

Cheers,

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

David Zeidman

I think that it is understandable that Blackbaud do not give SQL access. For one, we are not certain that all the functionality going forward is sitting in a SQL Server database. I know that there are a number of processes that move data between databases so that would suggest Blackbaud are not simply using the same RE7 database.

Secondly by giving access to SQL Server Blackbaud make it much harder to update their application. All of a sudden they would be bound to a specific schema. With an API there is a layer of abstraction that you do not get with direct access to a database.

Clearly there is a need for a datawarehouse and I imagine that is something that will appear at some point in the future.

Joel Weinbach

We (Reporting Xpress) may be able to help you with this. We run a process where we extract data through the SKY API and build a temporary data warehouse in the cloud that reports can be run against. We can do things like build calculations in that process to provide access to info that may not otherwise be accessible. We have our own report design surface, and Tableau integration, but may be able to allow you to run your reports against it as well (don't quote me on that yet <g>), or because a "Report" for us could be anything, we may be able to output a file that you could run PowerBI or other tools against. If this sounds interesting, you can reach out to me at Joel.Weinbach@ReportingXpress.com and we can schedule a quick conversation.

-Joel

Christopher Bush

There is a need to have access to ALL of our BlackBaud user data. We are currently using the free once a month sql server backup request for custom in-house reporting. There is a nightly version of that method for cost, we chose not to go that route at this time in hopes that there will be a more elegant solution.

The sql server backup request was not the way we wanted to go but it appeared that Sky API does not provide access to ALL of the data we use. What makes it ok to limit access to some user data? Why do we have to fill out idea tickets to get access to the data that we store and maintain? Sky or some service should have a feature for providing dynamic/transparent read only access to all user data. Then BB and/or 3rd party vendors or internal IT teams can thrive in creating great replication solutions along with great reporting solutions. There is always a need for custom reporting services.

I'm happy to get involved with Blackbaud devs to help better understand the need. Have been providing custom reports and APIs for the RE7 product for years. And I know there are many other devs out there like me with the same issues.

For the Raiser's Edge product, to give an idea, here is a list of the most commonly used data for reporting.

[ACTION_SOLICITOR]

[ACTIONCATEGORIES]

[ACTIONS]

[ADDRESS]

[APPEAL]

[ATTRIBUTETYPES]

[BATCHHEADER]

[CAMPAIGN]

[CODETABLEMAP]

[CODETABLES]

[CONSTIT_ADDRESS]

[CONSTIT_ADDRESS_PHONES]

[CONSTIT_FUNDS]

[CONSTIT_GIFTS]

[CONSTIT_RELATIONSHIPS]

[CONSTIT_SOLICITORS]

[CONSTITUENT_CODES]

[CONSTITUENT_SOLICITCODES]

[CONSTITUENTATTRIBUTES]

[EDUCATION]

[EDUCATION_ALUMNI]

[EDUCATIONATTRIBUTES]

[EDUCATIONMAJOR]

[EVENTPRICES]

[FUND]

[FUNDATTRIBUTES]

[FUNDDISTRIBUTION]

[GENERAL_INFORMATION]

[GIFT]

[GIFT_PLEDGEPAYMENTLINK]

[GIFTADJUSTMENT]

[GIFTAMENDMENT]

[GIFTAMOUNTPAID]

[GIFTATTRIBUTES]

[GIFTBENEFIT]

[GIFTGLDISTRIBUTION]

[GiftHistoryFields]

[GIFTMATCHINGGIFT]

[GIFTPREVIOUSSPLIT]

[GIFTSOFTCREDIT]

[GIFTSOLICITOR]

[GIFTSPLIT]

[GLDISTRIBUTIONDETAIL]

[INSTALLMENT]

[INSTALLMENTPAYMENT]

[JOB]

[PACKAGE]

[PARTICIPANTFEES]

[PARTICIPANTGIFTS]

[PARTICIPANTS]

[PHONES]

[PROPOSAL]

[PROSPECT]

[QFUNDDISTRIBUTION]

[RECORDS]

[RECURRINGGIFTACTIVITY]

[SOLICITORS]

[SPECIAL_EVENT]

[TABLEENTRIES]

[USERS]

[VIEW_GIFTSPLIT]

[VOL_ASSIGNMENT]

[VOLUNTEER]

Patrick Clancy

Also worth noting is that if you are using the Sky API to connect to Power BI, you can't schedule refresh for any dataset that uses the API meaning a manual refresh and publish every time I want to update the reporting suite I am developing, and also you have to sign into Blackbaud within PowerBI each time.

I understand that the Insight Designer has been developed but its functionality isn't quite there yet.

Kevin Hardy

Joel Weinbach:

We (Reporting Xpress) may be able to help you with this. We run a process where we extract data through the SKY API and build a temporary data warehouse in the cloud that reports can be run against. We can do things like build calculations in that process to provide access to info that may not otherwise be accessible. We have our own report design surface, and Tableau integration, but may be able to allow you to run your reports against it as well (don't quote me on that yet <g>), or because a "Report" for us could be anything, we may be able to output a file that you could run PowerBI or other tools against. If this sounds interesting, you can reach out to me at Joel.Weinbach@ReportingXpress.com and we can schedule a quick conversation.

-Joel

Thanks Joel - but if you're using the Sky API to build a temporary data warehouse then you probably won't have access to all of the data (such as membership details) since there currently isn't an API endpoint for it. But like others have said, if the API has endpoints for most major data sets then a read-only database may not be necessary.

But until that happens, I think access to a read-only mirror of the database for those who need it should be an option.

Has anyone got a line on release dates for future APIs and endpoints?

Steven Cinquegrana

Has anyone got a line on release dates for future APIs and endpoints?

There used to be a quarterly developer roadmap webinar but it hasn't been happening for more than a year now, unfortunately. Why it was stopped I have no idea. You couldn't always take the contents to the bank but it was still useful.

There're still some recordings here and there, though they're not easy to locate: https://community.blackbaud.com/reviews/item/29/2111

Cheers,

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

Joseph Vaughan

we are in a similar situation as others. PoweBi and want read only access to the database. I don't buy the argument that it is hard to manage schema changes if you give SQL read only access, although I do agree that the abstraction provided by the API is valuable. If you have a new schema, you would publish that and give people the information they need to use it.

we are likely to contract with this company, Mission BI, whose business proposition is a solution to our quandary: they provide an SQL database to which we have VPN access and which contains everything in the API and the data that is not available through the API (because they are partners with Blackbaud they can do that). It's kind of indicative of where Blackbaud is on the API journey that one can make money by putting a second SQL database between the API and the end user, but there you have it.

Keith Wilson

I'm just joining this conversation...I'm still very new to the SKY API functionality within RE NXT. Our department uses NXT for our Donor Development, but we are part of a larger organization that uses PowerBI for reporting. We have been trying to provide meaningful reports and data using PowerBI but have been struggling with getting the data connections. Like Joseph Vaughan, we too had a conversation with Mission BI and are very interested in their solution. In the meantime, does anyone have suggested resources for "SKY API for Dummies" like me?! ?

Patrick Clancy

I have this working using the following guide;

https://github.com/GrantQuick/BlackbaudSkyApi-RENXT

William da Silva

Easy access to SQL data is huge, and can be easy.

eSimpleIT offers a fully managed Data Warehouse solution that connects to BB's big 3 (RE NXT, FE NXT and Luminate Online).

This gives you full SQL access to ALL your data in one single database. All this without a line of code.

As stated in this thread, only the data that BB makes available through their APIs can be pulled this way. Even when you're a BB partner like we are (eSimpleIT) and others in this thread, BB does not grant any special partner access to customer's databases that customers don't have. There are 2 ways to get to data in RE NXT, API or click on an export button from RE NXT. We all play by the same rules.

We work with several large foundations and have found that the main and most interesting data pieces to report on are available. There are a few others that would be interesting for reporting. We have regular conversations with BB about what is relevant to our customer base.

Would be glad to further discuss your BI needs.

William

Thomas Evans

William - We recently confirmed that our automated solution that utilizes CSV Exports and Queue to include this additional data does work in the NXT/Azure environment. I'll email you with more details. Let's reconnect soon when we both have some time.

-Thomas

Lee Welper

Hi Chris,

Thanks for your post. We are new to BB and were hoping to use the ODBC or API options to create data feeds to our analytics platform. Sounds like both ODBC and API options have obvious limitations. You mentioned "We are currently using the free once a month sql server backup request for custom in-house reporting. There is a nightly version of that method for cost, we chose not to go that route at this time in hopes that there will be a more elegant solution". Is the free monthly backup available via Blackbaud or some 3rd party? How would I find out more about the nightly backup options and pricing? Thanks in advance for you help.

Christopher Bush

Hi Lee,

It is provided by BB not a 3rd party. The once a month snapshot is requested through a support ticket. I think the paid option has to be discussed with your BB sales rep.

But it might not make sense to build out on this method for new RE instances - if you weren't already using direct sql database access (a paid offering that was once known as REOPEN). There is no official documentation or support on how to query the data model. There was at one time searchable help on querying tips but I think that's long gone. REOPEN was always considered use at your own risk. It took years to figure out work-arounds and data model aspects in order to setup practical views of the data model. While I still find it invaluable and a timesaver for reporting, it is/was not all easily useable to those without the prior experience/knowledge.

Chris

Lee Welper

Hi Chris,

Thanks for the information, Chris. I’ve been
accessing the data via VPN and ODBC connection and am pretty good
with writing SQL and understanding the architecture, but we hoped
to automate instead of having to manually connect and authorize to
build nightly feeds from BB to our analytics platform. If BB can
automate the backup to our sql server we could automate from there
to our platform. Eventually we hope the API will have more complete
endpoints. I’ve noticed some data/ tables are not even available
via the OBDC connection but it seems a richer source than the
API.

Thank you for the information. I’ll reach out to
our consultants on both ends.

Mr. Lee Welper,

Data Management Team Lead

NCSSM Enrollment Services

919-416-2884

William da Silva

Hi Lee,

I second Chris Bush‍ , the SQL dumps are practical when you already have build a legacy reporting system against the SQL backend and you need to maintain that while you rebuild your new reporting.

The SQL dump is from the DB view not the Web View. The DB view will eventually be retired all together (eventually). To Chris's point, the database is also hyper-normalized, the data model is massive and the learning curve is steep.

If you are looking to fast track accessing an SQL database, you can check out our data automation platform Charity Automator. It has an ETL/Data Warehouse at the core that can be leveraged for direct SQL access and reporting in whatever platform you wish. It's fully managed and produces a clean, flattened database that's easy to report on (code tables appear as the value not an ID, ex: Constituents.Title will have "Mr." in it).

PM me if you wish to discuss further.

Thanks,

William

Alex Wong

Hi,

We are going to migration into RE/FE NXT from 3rd party hosted RE/FE soon, and we have been exploring RE/FE NXT default ability to do list, report, and insight. While RE NXT by itself can do a lot, it doesn't do everything we are currently using with a 3rd party hosted situation and direct access to both RE and FE database.

2 of the biggest concerns are:

Pledge, Installment, Payment, Write off reporting
- The Get Gift API does expose the “Linked Gift” (gift id of the pledge(s) if the gift is a payment or write off gift record), however, something important is missing: AMOUNT APPLIED.
- While for a write off gift record, the AMOUNT write off is the full amount of the write off gift record itself, it is not the case with PAYMENT gift type.
- A Payment gift can be applied to more than 1 pledge gift record and we need to know how much of the payment is applied to which pledge record in order to create the most comprehensive Pledge Report
- Installment information is also key info (due date/amount) in running a comprehensive Pledge Report too so we know how much we can expect to receive in the next quarters/years.
FE transactions getting RE data points
- Our RE and FE are integrated and we post using RE posting utility
- RE to FE integration, there are only “some” information (fields) that are going into FE transaction, a lot of details remain only available in RE. In the FE database transaction table, there is a field that indicate the RE gift system record id where the transaction originated, which our custom GL report is able to join and report on all RE gift data (including gift attributes) that would otherwise not available from FE reporting.

William and Joel both advertised their product here, so I want to ask, does your datawarehouse solution gets these data points from Blackbaud?

William da Silva

Hi Alex,

Great points!

You are correct, Pledge, pledge payment and pledge instalments are areas of the SKY API that require improvements especially for reporting and projections.

The good news is that as BB adds new endpoints or fields within existing endpoints, our customers on Charity Automator automatically benefit from these updates and can access the new data pieces.

We also work with BB to communicate use cases for new endpoints so they can continue to focus their development efforts on new features that will bring real value to their customers.

This is a fine example of a need beyond what is available out of the box in RE NXT, requires more custom reporting and also requires access to a few additional endpoints that are currently not available.

Meanwhile, we've worked with customers to find temporary workarounds for instalment payments while that data becomes available through the API.

As for an FE - RE per transaction relationship, this is possible with Charity Automator, we would be happy to discuss with you how this can be done. You can PM me, or better yet send me an email at william.dasilva@esimpleit.com .

Thanks,

William

Alex Wong

Hi William,

Thank you for your information, I want to confirm:

Are you saying that the SKY API that's available to customers have less endpoints than SKY API that is available to parteners?
- If that is the case, of the 2 points I made, are they information we seek in these partners' SKY API which is why you are saying your solution can pull the needed info?

Thanks,

Alex

Leo Coronado

Hi Alex,

Are you aware of the Bulk Data Delivery solution for RE And FE NXT?

This article might helps:
https://kb.blackbaud.com/knowledgebase/articles/Article/117979

It's not real time but you can do pretty much everything that you are currently doing.

Alex Wong

Hi Leo,

We are aware of the nightly backup that customer can pay for. However, it is nightly backup and it is currently consider a “last resort” if we find no other means.

Thank you,

alex

William da Silva

Hi Alex,

For the points you mentioned, partners have access to the same APIs as end users/customers.

As partners however, we do have regular meetings with BB and "lobby" for certain feature enhancements. In one discussion, we can represent the needs of many clients, making it easier for BB to understand customer needs and justify/prioritize development.

The temporary workaround we've done with some clients for Instalments involves leveraging DB View. Again, this was always viewed as a temporary solution as it is best to invest time and energy into the more modern API solutions. We do not want to develop complex solutions that can also add data security risks in order to work around something that can be easily resolved by BB.

For the RE-FE link, this is all information that is available in our data warehouse.

Thanks,

William

Joel Waldman

Hi Kevin,

Please check out eSimpleIT's data automation platform Charity Automator.

It has an ETL/Data Warehouse at the core that can be leveraged for direct SQL access and reporting in whatever platform you wish. It's fully managed and produces a clean, flattened database that's easy to report on (code tables appear as the value not an ID, ex: Constituents.Title will have "Mr." in it).

If you would like to discuss the Charity Automator is greater detail, please email me at: joel@esimpleit.com.

Thanks,

Joel

Joel Waldman

Hi Alex,

The SKY API available to customers is the same as available to partners.

Once new APIs are made available by Blackbaud, we add them to our Charity Automator solution so our clients can quickly start accessing the new data.

As a Blackbaud partner, we have been successful, so far, in working with Blackbaud, to make available additional APIs.

Please email me at joel@esimpleit.com if you would like to discuss this in greater detail.

Thanks,

Joel

Joel Weinbach

SO, Reporting Xpress isn't technically a data warehouse since we are not persisting storage of your source data. Rather, we build a temporary data warehouse that only exists for long enough to generate reports and then we delete the source data. Having said that, we have developed a number of creative workarounds to get around some of the API limits that enable us to join multiple data sources and get the info you want into reports. Also, it is worth noting that if you want our output to be some kind of data file versus a person consumable report (PDF, XLS, etc) we can also do that. We would be more than happy to speak with you offline to dig into more specifics of what you are trying to accomplish. You can reach me at Joel.Weinbach@ReportingXpress.com or reach out through our page in the Blackbaud partner marketplace.

Kevin Hardy

I'm glad this topic is getting more activity!

Since it was mentioned that partners have some influence over what endpoints are developed and released, has there been any word on when we'll get endpoints relating Membership history details (dates of upgrades, downgrades, renewals, etc.) Currently only the information seems to be int he database view.

During BBCon in Nashville they mentioned releasing a Membership module that would have this information but I haven't seen anything come from that.

Also any endpoints relating to Pledge Payment Schedules would be great.

Robert Jones

We migrated to RE NXT in the midst of the pandemic, leaving the 3rd party client to be hosted with Blackbaud. As great as NXT is, unfortunately, aside from API, there isn't a way to use RODBA, as this functionality is unavailable in their Azure environment. Instead, we get a weekly backup of our database. With that, we have a VM that has an instance of SQL Server on it. We use that for any/all of our SQL code pulls. This becomes necessary when dealing with specific audits and validations that are not easily obtainable through NXT Web View or DB view. (Perfect scenario would be creating a Dynamic Pivot on all constituents with multiple constituencies or attributes). Hope this helps.