Examples of queries used with the data from the nightly backups

Mark Bezanson · June 2021

We are receiving nightly backups of our Raiser's Edge data. Does anyone have some example of queries that we could use as a pattern so that we can start using this data.

Thank You

JoAnn Strommen · June 2021

Mark Bezanson‍, how are you wanting to use the data?

Is it to have a backup should your db crash today that you can restore from? If so, I don't know that you would want to make any significant changes to the backup data.

Is it to query for changed records, reviewing data entry? Again, I'm not sure why you would want to do that in the backup as it could be done in your live data and any corrections would need to be made in live data set.

I'm probably unaware of some wonderful purpose for nightly backups. Will follow post to see what I need to learn.

Mark Bezanson · June 2021

One of the issues we have is that we can not get certain information using query in RE. For example we are asked to pull a list of people who have a relationship with funds as a report recipient or a department contact. We can do this but we cannot include the address and contact information for the individuals. Apparently there was a plugin in the past that would let us do this in RE, but we are not able to get the plugin any longer.

JoAnn Strommen · June 2021

Sorry, Mark, still not seeing why whatever you're pulling from a backup cannot be done in current database.

As to what you described, it sounds like you can do a query of those with the fund relationship. Is the address/contact info not available to pull using an export of that query? I don't have a lot of experience pulling relationships or fund exports and I know not everything is available in all exports but I would think it should be there somewhere.

Alex Wong · June 2021

Mark: From what you said, I think it is do-able in RE Query without using SQL to query the database directly.

RE Constituent Query
Criteria tab:
- Relationship > Funds > Fund ID = [[[Fund ID you want to see relationship of]]]
- Relationship > Funds > Relationship = Report Recipient
Output tab:
- Constituent Information > *** (i.e. First Name, etc)
- Addresses > Preferred Address > *** (i.e. Preferred Address Lines, etc)
- Phones > *** (i.e. Phone Number, etc)
  - this is based on RE v7.96 where the phone/email were changed and no longer “associated” with an address record

There are times when SQL querying of a nightly backup is needed as there are data points that are not available to be exported or available in the way you want it, but it doesn't seems what you trying to do needs it.

Dennis Ladnier · June 2021

Mark, it all depends on what you are trying to accomplish. We use our daily NXT backup for a range of activities from running standard RE queries which have VB fields (you can't run those in NXT) TO various SQL extracts which populate our data warehouse and other systems TO data cleansing with api calls back to NXT to correct the data. Again, it all depends on what you are trying to accomplish.

Mark Bezanson · July 2021

Hello Alex. I think that is part of it, but we need to be able to see the fund category and whether or not it active.

Mark Bezanson · July 2021

Hi Dennis. One of our immediate needs is to be able to pull a list of all funds that are active and that have a category of ‘Endowed’ or ‘Building to Endowment’ and their corresponding individuals who have a relationship as a ‘Report Recipient’. In our old system we were able to link tables much easier.

We have also been trying to link the basic tables in the ‘backup’ to run queries, but have been having a difficult time with the ‘key’ numbers and making the queries distinct. It is giving is grief!

Examples of queries used with the data from the nightly backups

Comments

Categories