Implementing SQL into RE7 NXT

Nawras Amin

Hello, 


I recently started at my school's alumni foundation and they've asked me to find a way to link SQL with RE7 in order to execute more precise queries. 


I'm also unsure of how the database system of RE7 works and where the SQL server is located (how to access it), in order to connect to it with Microsoft SQL management. 

I am relatively new to RE7 NXT and don't know exactly how to proceed, would anyone be able to help me out or give me a path to follow?


Thanks!

John Heizer

If you have NXT then your database is hosted by Blackbaud and you will not have direct SQL access to the data.

Larry Wheeler

You can request a backup of your database once a month.  If your IT department will allow it, and drop it onto a sql server environment, you can use the MS management studio (or other methods) to write sql against the data.  The issues are ... data is not current obviously, you have to put in a request each month.   But, for a lot of informational purposes, it suffices.

David Ritchie

Hi Nawras,

Your other option is to use Sky API to pull or push data from NXT. If SQL Server is your preference, you can build your API scripts to get the data you want and then use SSIS or another method to ETL the data in your SQL Server DB. Then you can run any program on top of that. T-SQL to just get the data out, SSRS or a visualiztion tool like Qlik or Tableau.

Good luck,

David

Nawras Amin

Hello David,


Do you think you could provide a link to a guide on how to proceed with the method you've given me?

I'm relatively new to RE7 and been asked to try to find a way to implement SQL. I should mention, however, that our main database is with RE7 and is hosted by Blackbaud. We do have access to the RE7 NXT platform, but we have yet to create anything on it. If there is a way to query through the database we have hosted by Blackbaud, it would be better, but I don't believe that is possible.


I also believe that SkyAPI is only available for RE7-NXT, but we are planning to build a database onto the NXT platform.

So, if the SkyAPI method can allow me to push and pull data from the database freely and query SQL commands on it, I'm all for it, however a guide on how to proceed would be very helpful!

 

Kevin Hardy

You can not connect SQL server to the database in an RE NXT environment. You won't be able to access the database using SQL Server Management Studio.

If you just need the database to support local/archival reporting you can request to have the latest backup copy sent to you up to once a month (or more for an additional fee)


If you need to access live information in the database on-demand then your option is to use the SKY API

For example, if you just wanted to retrieve a list of your consituents you can use this call:
https://developer.sky.blackbaud.com/docs/services/56b76470069a0509c8f1c5b3/operations/SearchConstituent


Now even though it's a bit more work to make API calls instead of direct SQL queries, the SKY API will allow you not only read but write/update access to your db.


And there's also the hybrid approach that people who are used to working with SQL implement try - using the SKY API to download various data sets from RE NXT and then upload it to a custom database schema in their local environment to use for their local apps/reporting. Sort of a local cache of commonly reported on data.

Theresa Markham

@Kevin Hardy
hi there,

I know this is a very old post, but I'm new to setting up a SQL Server database with management studio (I've been using SSMS to query and write reports for 5 years though) and I'm wondering if this hybrid model is still the best method to create a reporting back up?
I'm interested in building this API tool for specific data updates as well, but wondering if there have been any changes to how we can use SSMS for RE7 and RENXT?

Kevin Hardy

@Theresa Markham I'm still a little torn on this, but yes - having something such as a scheduled task that will make API calls to grab all the data you need and store it in a local SQL Server database for various reporting queries will still work, depending on your needs.

The main drawback here is that not everything is available through the SKY API. For example, if you needed to do reporting on Membership details (levels, history, upgrades/downgrades, etc.), you'll be out of luck. Something like that you'd need to request and have full database backup delivered (free - once a month, $$ - if you need a higher frequency) if you're in the Blackbaud hosted environment.

Also there are some datasets that are just not available through the any means at all (not the SKY API or stored in the Database we can download) such as attachments stored through the WebView or NXT E-mail statistics.

It just depends on your needs. If they're simple, one-off live API calls might just work too!

Theresa Markham

@Kevin Hardy Thank you so much for the reply.
I think I might be stuck with SKY API calls for now, I think I'm getting the impression that my IT dept doesn't want to host a backup database. Maybe I just need to explain the situation more clearly.
The database is so full of expired data! I can't imagine doing updates without SQL. Guess I'm going to get very good at other methods.

Karen Diener 2

I'm most interested in your comment about “executing more precise queries”. I've found queries to be pretty precise when you know how to run them. When they “don't work” it is usually because people try to use the output as their report, the query isn't constructed properly, or the underlying data is a mess.

It just seems like (potentially) a decent amount of effort to set this up and it still may not really address the problem.

Now that I've answered, I see the original post was from September 2019. That doesn't change my response, which I'll leave here, but I recognize that I may not get an answer which is fine.

Karen

Theresa Markham

@Karen Diener hi there,

here's an example from today,

I want to pull a list of the gifts I've entered today to ensure they are entered accurately.

I changed a number of fields, and I'd like to see them, if updates were mades.
I'm not formatting a report or anything complicated, I just want to see the gifts I've entered, and if there is an email present and it's primary, I'd like it to show. we have email type of e-mail, and e-mail-Work.

my query is Gift Date added equals 1/6/2022
and (phone type blank
OR (phone is primary equals yes
and (phone type equals e-mail
or phone type equals e-mail-Work)

I need to add two more parens, but the query editor only allows a max of one per line. I'd be done with the entire project by now if I had sql server management studio connected to the database.

Another situation I encounter often, I am asked to pull lybunt and sybunt data quarterly so we can target our renewal efforts strategically, and it would be nice to use that information for marketing segments without an import selection. but It's so extra difficult in the query editor/information library/canned reporting that Blackbaud provides, especially if I want to run LYBUNTS for donors who gave to one area but not another (whether it's a unit, or a fund, or a campaign.) I just moved from a university that used Blackbaud CRM to an org that uses RE7, but I'm still hitting the same roadblocks.

I'm a bit limited by my dependence on SQL, I admit it. If you have another way to get to this information elegantly, I'd be thrilled to hear it.
for today, I'm left with pulling several different queries to check the data I entered in separate data sets. I find this… inefficient.

Karen Diener 2

@Theresa Markham I know queries can be frustrating because it is really tempting - and more efficient - to use them as auditing tools. In the first example you described, you could always use a Gift Export to review the email information.

For the second, it is hard to know for sure without understanding your full process, but I've always used the segmentation feature of QuickLetters to help me with mailings for renewals that fall into those categories.

I agree that not having the ability to nest parentheses in query is very annoying! I've also often wished I could add comments to explain why some criteria is included, since the Description field doesn't provide a lot of space.

Personally, I don't think what you're looking to do requires SQL since other tools exist in RE that would help you. But I appreciate your response so I can learn from it!

Karen

Kevin Hardy

@Theresa Markham

I come from an SQL background so I'm a bit biased in the ways I prefer to pull my data (but i would recommend SQL as a last resort) but given what you've described, that you want to pull a list of the gifts that you enteredtoday, if you're in the Blackbaud hosting environment SQL might not be an option.

They only allow you 1 backup copy of the database once per month (unless you work out an extra agreement $$$) so although this might work for you for a db pull today, if you get this same question in a week you're going to have to wait a full month!

The other option would be what I mentioned before, using the SKY API to download the data sets you need real time (ok… maybe up to 30 minutes for some endpoints) , storing them in a local database and then performing your queries there.

If your IT department doesn't want to host a SQL Server installation, you can always install a free version of SQL Server Express on your Windows workstation which should give you everything needs. (your local IT Policy permitting, of course!)

Theresa Markham

@Kevin Hardy @Karen Diener I am currently leaning toward this second option, SKY API and a local database with SQL Server express.

The thing is, I'm cleaning up a database that has been under-utilized for five years and making it functional for a very small office. I'm coming for a large university where these systems were already in place, and it still took me quite some time to get at the data I needed! So, I do need to do some auditing here, and I'm working with a short timeframe and the bare necessities in terms of available tools. It's just me setting this up as quickly and efficiently as I can.

Eventually I DO want to get the team set up on using on built-in features, I want to enable them to get their data without me, because I'm only here temporarily! But I need to look at this data before I can clean it up.

They've never used QuickLetters, but aside from that, I'd like to give them some historical trend information and do some prospect analysis before I go, and I just hate the limitations of the Blackbaud query tool for anything but a quick data grab or basic segmentation tool. We used query to build marketing efforts in Blackbaud CRM at my last place, which I believe would be similar to using Quickletters for segmentation and it was a nightmare of datachecking to ensure we had what we needed in each segment. We never felt confident that people weren't getting left out, or included who shouldn't be. We constantly had to revise queries, or rerun them because one component or another had failed to execute properly. In my position before that, I programmed all mailing lists as ad-hocs in SQL from Ellucian Advanced, we never used the database to track our mailings.

So without getting too deep into the weeds about why I'm doing what I'm doing, SQL is the fastest option for me to dig into what information and what fields are actually available for us to use, without everyone having to learn TOO many extra new things.

Theresa Markham

@Kevin Hardy as for example one below, I ended up having to pull multiple queries to see the data and check it against the original lists I pulled. It wasn't very efficient.
I'll be looking for the Gift export Karen mentioned to see what it contains.

That is another problem, this is a new org for me, and I want to learn as much as I can as fast as I can, but Blackbaud doesn't offer some of the classes on demand, so I'm having to wait to do trainings that would teach me about the canned reporting. Some things you just learn with time, but I don't have as much of that as I would like.

Theresa Markham

@Kevin Hardy @Karen Diener Just one more thing, I appreciate these replies more than I can say. Thank you for answering. You've given me some good ideas for options. I would love any other advice you might have.