OK to Just Delete Queries?

Erin Vinton

Our system has hundreds of queries - many are even multiples because one person may not know that another person created a similar query. We do not use them all and we don't need them all. For example, there are gift batch queries going years back. Everything in this system is primarily processed through gift batches so there are a lot of those queries and these are used in donor acknowledgements. We wouldn't delete gift queries for this year, but perhaps any at least two years ago and father back?? Any reasons why we wouldn't want to delete queries i general??? Or any wise advise before we tackle that project? Thank you for your insight.

Karen Stuhlfeier

The only reasons not to delelte them are if they're attached to a report, export or dashboard report you are using. Your database can get really cluttered with past queries and I try to go in an delete the ones that aren't being used. They can always be replicated. Look at the last run date and that will help you decide what can go.

Gina Gerhard

I did a big query clean-up project about 5 years back and here are some points:

• I'd highly recommend implementing a query folder structure to organize queries.
  • I created a TEMPLATES folder and then created folders for each RE user.
  • Staff are encouraged to look in the TEMPLATES folder for template queries set up for very common/standard data requests (ex: board members, donors who fall into specific categories, etc.)
  • If they need to 'tweak' these in any way, they're asked to save the query out into their own folder and rename starting with their initials. They can then tweak away.
  • Staff are warned, however, that if any basic criteria have changed and they've saved their own version we cannot guarantee that these queries are the latest/up-to-date. We do, however, update the TEMPLATES queries so they can go back there to get an up-to-date one.
• Audits of query folders
  • The default 'General' folder always ends up with uncategorized queries, so I reach out to everyone and ask them to either delete or move the queries they created that land her to their own folders (I actually do this quarterly)
  • Annually I check all staff query folders to see if they have what might be a lot of duplicate queries, if they haven't adhered to the naming conventions, etc.
  • I'll reach back out to them to request that they review and cleanup if needed.
• Before deleting any queries, as administrator I will reach out to staff before deleting any of their queries.
  • This is an opportunity to remind them of our protocols.

My mindset is that any query can be reproduced - it's not like you're losing anything you can't recreate. So it's not the end of the world if you delete a query that you find out might have been needed.  And we all know it's much easier to never do housecleaning, but it's good to pull the couch out and vacuum underneath it every now and then!



So I would encourage you to develop some protocols, do some staff training, implement an audit and cleaning process in order to keep your system cleaner.



Good luck!

Erin Vinton

Gina Gerhard:

I did a big query clean-up project about 5 years back and here are some points:

• I'd highly recommend implementing a query folder structure to organize queries.
  • I created a TEMPLATES folder and then created folders for each RE user.
  • Staff are encouraged to look in the TEMPLATES folder for template queries set up for very common/standard data requests (ex: board members, donors who fall into specific categories, etc.)
  • If they need to 'tweak' these in any way, they're asked to save the query out into their own folder and rename starting with their initials. They can then tweak away.
  • Staff are warned, however, that if any basic criteria have changed and they've saved their own version we cannot guarantee that these queries are the latest/up-to-date. We do, however, update the TEMPLATES queries so they can go back there to get an up-to-date one.
• Audits of query folders
  • The default 'General' folder always ends up with uncategorized queries, so I reach out to everyone and ask them to either delete or move the queries they created that land her to their own folders (I actually do this quarterly)
  • Annually I check all staff query folders to see if they have what might be a lot of duplicate queries, if they haven't adhered to the naming conventions, etc.
  • I'll reach back out to them to request that they review and cleanup if needed.
• Before deleting any queries, as administrator I will reach out to staff before deleting any of their queries.
  • This is an opportunity to remind them of our protocols.

My mindset is that any query can be reproduced - it's not like you're losing anything you can't recreate. So it's not the end of the world if you delete a query that you find out might have been needed.  And we all know it's much easier to never do housecleaning, but it's good to pull the couch out and vacuum underneath it every now and then!



So I would encourage you to develop some protocols, do some staff training, implement an audit and cleaning process in order to keep your system cleaner.



Good luck!

 

Gina, thank you so much! THis was very helpful. Might you be able to share with me your naming conventions - that sounds like it would really help us to identify moving forward what is what! Thank you, Erin

Nicole S

Both good answers.  I think that most anything can be replicated.  The only time I hesitate to delete a query is if it was created during a report or import or global or some other RE function and it's the easiest was to group those records.  But I typically name those appropriately and keep them in a folder.  After a certain period of time they are pretty safe to delete.  I also always let staff know that I'm going to start deleting queries.  Each person has a folder where they can keep stuff they routinely use (or another appropriate folder) and they get a few days to move anyting there.  And even within the folders I go in and delete anyting more than a 2 years old.  It really keeps it down.  And the folders help.

Gina Gerhard

Erin -



If this is helpful, here's a look at my query folder structure:

• General (this is a default folder you can't get rid of)
• ^TEMPLATES (all our templates are in this folder, and all templates have same ^CAPITAL LETTER format so you know it's a template from the way it's named).
  • Here's my list of naming conventions within this TEMPLATES folder (you'd create whatever categories make sense for your organization...).

^ANNUAL-

^APPEAL-

^BOARD-

^COMMITTEE-

^DONOR-

^EVENT-

^FOUNDATION

^FUND

^GIFT

^GRANT

^MAILING

^NEWSLETTER

^PROSPECT

Then I create folders for each department to organize the users by department/function, with the users underneath. The user could put any 'shared' queries for their department within the --> folder but otherwise they are saving within their own folders:

 

-->FINANCE

Rogers, Karen (KR) (Karen then names all her queries here starting with KR-xxxx)

Carr, Rebeca (RC)

etc.

-->EXECUTIVE

-->PHILANTHROPY

-->COMMUNITY IMPACT

-->COMMUNICATIONS

-->TECHNOLOGY

--DO NOT USE BELOW-- (staff KEEP OUT of the ones below!!)

-->ADMIN  (all these folders are for data admin use ONLY)

-->BBNC

-->BBNC-Emails

etc...



 

 

JoAnn Strommen

Erin,

Just a couple other thoughts for query folders...



In addition to those for various funds and our branch Ys, I have:

RE Data Management - the queries I run when checking for missing/data needing fixing...

Batches - where the queries go when batch created for use in Mail etc.  Then easy to find and delete

Audit reports - the queries that are fairly detailed that I don't want to lose to run for our auditors at year end

Reports - queries used for monthly/quarterly/annual reports that are not specific to a fund

We're a small # of users so I haven't had to go as detailed as Gina - her's is a good example for a many-user org to keep everyone organized.

Gina Gerhard

JoAnn - Yes, our org is just under 50 people but also I should have mentioned, we have a very distributed model for entry of data into Raiser's Edge - so we have about 30 active staff members who are entering a fair amount of data, running queries and reports in RE on a regular basis.



Probably not the most typical model for most organizations - most probably have a more centralized data entry model.  



But that's why I've had to try to make things as clear and structured as possible in order to maintain some sense of order!!

John Heizer

Gina Gerhard:

I did a big query clean-up project about 5 years back and here are some points:

• I'd highly recommend implementing a query folder structure to organize queries.
  • I created a TEMPLATES folder and then created folders for each RE user.
  • Staff are encouraged to look in the TEMPLATES folder for template queries set up for very common/standard data requests (ex: board members, donors who fall into specific categories, etc.)
  • If they need to 'tweak' these in any way, they're asked to save the query out into their own folder and rename starting with their initials. They can then tweak away.
  • Staff are warned, however, that if any basic criteria have changed and they've saved their own version we cannot guarantee that these queries are the latest/up-to-date. We do, however, update the TEMPLATES queries so they can go back there to get an up-to-date one.
• Audits of query folders
  • The default 'General' folder always ends up with uncategorized queries, so I reach out to everyone and ask them to either delete or move the queries they created that land her to their own folders (I actually do this quarterly)
  • Annually I check all staff query folders to see if they have what might be a lot of duplicate queries, if they haven't adhered to the naming conventions, etc.
  • I'll reach back out to them to request that they review and cleanup if needed.
• Before deleting any queries, as administrator I will reach out to staff before deleting any of their queries.
  • This is an opportunity to remind them of our protocols.

My mindset is that any query can be reproduced - it's not like you're losing anything you can't recreate. So it's not the end of the world if you delete a query that you find out might have been needed.  And we all know it's much easier to never do housecleaning, but it's good to pull the couch out and vacuum underneath it every now and then!



So I would encourage you to develop some protocols, do some staff training, implement an audit and cleaning process in order to keep your system cleaner.



Good luck!

 

Let me add one caveat to the "any query can be reproduced" philosophy ... situations like an output query from a Global Change could be very difficult if not impossible to recreate under certain circumstances.

Bob Wiebe

Good suggestions thus far. One step I have found useful in file cleanup is to have a folder called "Archive".  This is where I put queries I don't think I need any more.  It gives me the option of deleting them at a later date if I have not gone back to them.



I also have a folder for Data Audit queries and another one called Data Tools.  This is where I put some often used "tool queries" like constituent ID lookup.

Jen Claudy

I use a two-step process, but I also have a situation where there are really only 2-3 people writing queries (and right now, it's just me, until we hire new staff...pros & cons).  I have an Archive folder where I move anything that hasn't been used recently (and "should" have been...i.e. not something like "tax receipts" or "annual report for Finance" that would obviously not be used regularly).  About every 6 months, I go in to the Archive folder and delete anything that hasn't been run in over 3 years.



When I first started at this org, I found well over 3500 queries in the database!  Many duplicates and most hadn't been run in 3-5 years.



Each staff member has a query folder, and when they write a query, that's where they're supposed to save it.  When I write one for them, I save to their folder.  Then, we have other folders that are basically for my use, audit queries, scrub queries (for those cleanup projects that I have time to start but never to finish), gift entry/acknowledgment/reconciliation queries, address update queries, etc.  And the all-important SAVE-Do Not Move or Delete folder.  (No one else has the rights to change, move, or delete those anyways...but a reminder to myself, and future DBAs to at least think twice.)



When we get new staff on board, I'll create new folders, and then talk with each of them to show them their predecessor's folder and how to move to their own folder.  Then, after about a year or when they think they've taken what they want, the remainder will go to the Archive folder...where they're still accessible, just a bit harder to find.

Jeffrey Montgomery

Hi Erin, check out our blog post "Taking Control of Your Raiser’s Edge Queries with QueryOmatic" (excerpt below):

 

RE: Query Part 1 - Taking Control of Your Raiser’s Edge Queries with QueryOmatic

Querying is such an important function in The Raiser’s Edge^® that we are dedicating a three-part blog series to the topic to ensure your success. In this entry, the focus is getting a handle on organizing queries to make things easier for everyone who uses RE!

 

Have you ever been looking for a query and couldn't remember if you created it or if a colleague created it, when it was made, or better yet…what it was named? 

Although Blackbaud’s The Raiser’s Edge 7 has grown and changed to make handling queries easier by giving you the option of creating folders, and of routing queries to specific folders upon creation, could there be an even better way?

QueryOmatic answers that question, and the answer is "Yes!"

QueryOmatic (QOM) is one of the newest offerings in the Omatic Software product family. QOM is designed to empower the user to take control of queries, and their categorization, in a more concise, clear, and directed manner than has ever been available using standard RE:Query functionality!

Below, you can see the layout of the QOM application. At a glance, we can see the number of queries that exist in all folders. This is the type of view that would be helpful to a database administrator who may not need to see each user’s queries individually, but would like a bird's eye view of how many queries there are in the system, and exactly where they are being currently stored.

 

A more detailed view can be seen when we select to look inside of one of the folders. The folders are on the right side of the QOM screen, in the navigation pane (below). For this example, I am going to open the SegmentOmatic folder, which is a folder I created for the queries that go with my SegmentOmatic profiles. In this view, we see information that can only be accessed from either the query options menu, or properties of the actual query, in the actual RE:Query module. As you can see, information such as the Run Time, Output Limits, and Record Suppression, is readily available in QOM’s more detailed view. 

  

The tool bar also presents us with query management options. The option to create a new "Query List" allows users to create a list of constituents based off of other query results (either existing or new query results). The "Summary" option provides a window that allows us to get a summary view of queries by RE user. It lists the RE user name, the number of queries for that user, and the number of categories that user owns. The view of all users, in the Query Summary, is shown here:

The Query Summary function will also allow us to view just one specific user’s queries, with more detail than the view for all users. The view of our selected user "Emilie" is shown here:

Additional options available in our toolbar, for query management, include adding queries to Queue (RE:Queue is an add-on module for RE), and even the ability to delete more than one query at a time (yes, you read that correctly!), all from within the same QOM interface.

Another query management capability available exclusively through QOM – can you take more? - is the ability to move multiple queries between folders at the same time. In the screenshot below we can see where multiple queries have been selected using the "shift" key, combined with mouse clicks (just as you would in Excel). The selected queries are currently stored in the General folder, but we actually want them to be stored in our SegmentOmatic folder.

Now you will simply drag the selection of queries to the SegmentOmatic folder: 

QueryOmatic is a huge time-saving tool, and it is a great product to add to the suite of Omatic products that your organization already uses (It’s also a fantastic introduction to our products!). It will work wonders for your current query management strategy. You could almost call it QueryOmagic!

 

Sharon Campbell

Gina Gerhard:

I did a big query clean-up project about 5 years back and here are some points:

• I'd highly recommend implementing a query folder structure to organize queries.
  • I created a TEMPLATES folder and then created folders for each RE user.
  • Staff are encouraged to look in the TEMPLATES folder for template queries set up for very common/standard data requests (ex: board members, donors who fall into specific categories, etc.)
  • If they need to 'tweak' these in any way, they're asked to save the query out into their own folder and rename starting with their initials. They can then tweak away.
  • Staff are warned, however, that if any basic criteria have changed and they've saved their own version we cannot guarantee that these queries are the latest/up-to-date. We do, however, update the TEMPLATES queries so they can go back there to get an up-to-date one.
• Audits of query folders
  • The default 'General' folder always ends up with uncategorized queries, so I reach out to everyone and ask them to either delete or move the queries they created that land her to their own folders (I actually do this quarterly)
  • Annually I check all staff query folders to see if they have what might be a lot of duplicate queries, if they haven't adhered to the naming conventions, etc.
  • I'll reach back out to them to request that they review and cleanup if needed.
• Before deleting any queries, as administrator I will reach out to staff before deleting any of their queries.
  • This is an opportunity to remind them of our protocols.

My mindset is that any query can be reproduced - it's not like you're losing anything you can't recreate. So it's not the end of the world if you delete a query that you find out might have been needed.  And we all know it's much easier to never do housecleaning, but it's good to pull the couch out and vacuum underneath it every now and then!


So I would encourage you to develop some protocols, do some staff training, implement an audit and cleaning process in order to keep your system cleaner.


Good luck!

 

Gina - I want to organize our queries to the appropriate categories plus move a lot to a new Archive folder. Is there any way to move multiple queries at one time? I know you can delete multiple queries but I want to MOVE these. 

Gina Gerhard

I don't think there's really a way to choose multiple queries, but you can just grab one and drag it into another folder (be careful you can accidentally drop it in the wrong place) -- but you still have to do them one-by-one.

 

Alicia Barevich

Karen Stuhlfeier:

The only reasons not to delelte them are if they're attached to a report, export or dashboard report you are using. Your database can get really cluttered with past queries and I try to go in an delete the ones that aren't being used. They can always be replicated. Look at the last run date and that will help you decide what can go.

Or if they're attached to a function in NetCommunity! The "run dates" on those don't refresh!

That said, they can indeed always be replicated.

Sunshine Reinken Watson

My (likely unpopular yet time saving) take: Old queries aren't hurting anything in your system as long as you have a decent filing system and naming conventions for current queries that multiple people might look at. I keep nice tidy folders for those types of queries and have some "junk drawer" type folders for the technical stuff that is more behind the scenes.