Database Auditing: Four Modules Commonly Used by Consultants to Audit Your Database
Are your queries, reports, or exports running slowly? Is reporting on data in The Raiser’s Edge (RE) a struggle? Does producing reports in RE take FOREVER? Do seemingly simple tasks take SO long to complete? These are all signs you may need database auditing.
As a database consultant, I have conducted numerous audits over the years. While each audit is unique based on the problems reported by an organization, there are four areas of the application I always use to assess and audit a database. In this article, I will share with you the four areas most often used for diagnosing problems in RE as well as some potential problems to look for. The areas are system statistics, configuration, reports, and queries. In addition to these areas, I will also provide you with some resources to assist with resolving issues.
System Statistics
System statics, found in the Admin module in RE, is the first place to look when auditing your database. Here you will find total record counts for constituents, gifts, campaigns, funds, appeals, actions, and notes. If you have optional modules such Event, Membership, Volunteer, or Tribute, record counts for the associated records will be displayed here as well. System Statistics provide a high level view of your database.
How do record counts help a consultant in auditing a database? These numbers are important to know particularly if you are having problems with performance. If you have a large number of constituents and gifts, you may need to avoid running reports without filters or date ranges. You might also focus your report on a query as opposed to all records. When creating queries, you may need to avoid negative operators, e.g. not equal to, not one of, does not contain, etc. These operators actually require more processing because the system must perform two passes. The first pass identifies people who meet the positive criteria, e.g. equal to, one of, contains, and the second pass finds those people in the database who were not returned in the first pass.
Record counts for Campaigns, Funds, Appeals, Actions, and Notes will reveal how efficiently you are using these records. For most organizations, the number of records for Campaigns, Funds, and Appeals could be described visually as a pyramid. Campaigns are typically at the top of the pyramid with the fewest number of records, and appeals are at the bottom with the largest number of records. While this may not always be the case, analyzing the number of records is a great place to start and will prompt follow-up questions on how these are set up in your system.
Actions and Notes are two areas I have found that are typically used incorrectly. Notes are very difficult to query on and should only be used when the information cannot be tracked somewhere else. Action records track all activity for a constituent, including prospect research on the constituent. If you have thousands of notes and only a few actions, your staff may not be using Actions and Notes effectively.
Configuration
The Configuration module is the next place to look. This module will provide valuable details on table values, attributes, business rules, and field properties. I typically review values for the following tables: titles, suffixes, solicit codes, address types, contact types, relationship types, gift sub-types, and others depending on the problems reported by the client. The goal is to identify values that do NOT belong in the table. Poor table entries make it difficult to report on, query on, and mail to constituents.
A quick review of the attributes an organization uses will provide information on what data they are tracking in this area of the application. I typically look for attributes that contain information that should be tracked elsewhere in the system such as deceased, inactive, prospect rating, etc. When data is not tracked where it should be in RE, reporting and querying is difficult. I typically review Constituent, Gift, Action, Address, Campaign, Fund, Appeal, and Proposal attributes.
Business Rules are not imperative for an organization to use RE effectively, but they can make life easier for data entry staff. I usually check this area to make sure some of the standard business rules are being used. Notifications for an inactive or lost constituent, prospect giving when entering gifts, and major donor action notifications when completing an action are among the rules I typically look for. Field properties provide an overview of which fields have been renamed, set to be required, hidden, or is a lookup field. Understanding field properties will assist with understanding how the organization handles data.
Reports
The Reports module is a great place to get a high-level view of the actual data. The reports used in a data audit will vary depending on the issues an organization is experiencing. However, I have found the Tally Report on Constituent Codes particularly useful in most cases. The report will provide a complete list of all Constituent Codes being used and the number of records on which they are found. Viewing this report will provide insight on how the organization uses these codes. Are there codes with only one or two records assigned? Are there codes that are clearly invalid? Constituent Codes are an important feature in RE and the Tally Report will provide details on how these are being used.
Campaign, Fund, and Appeal summary reports are also useful in validating the data and understanding how the organization reports on giving. Financial reports such as the Gift Detail Report are important in developing an overall picture of the organization’s use of RE.
Queries
As you already know, the Query module is an excellent tool for grouping records together based on a common factor, e.g. everyone who lives in a specific state, everyone who gave a gift last/this year, every gift entered last week, etc. However, creating queries for auditing data can also provide a detailed view of the data and help identify potential issues. For example, a query can identify records that are missing address information but are not marked as has no valid address. Querying on key words in notes, actions, attributes, and addresses will reveal if an organization is tracking deceased, invalid addresses, or inactive constituents properly.
Queries are not just for identifying issues with constituent records; they can also be used to identify breakdowns in the gift entry process. I have found creating queries to identify gifts that are missing a campaign, appeal, package, or solicitor is very helpful in diagnosing reporting problems. Additional queries that focus on soft credits, split gifts, acknowledgements, and receipts provides information on policies and procedures as well as possible breakdowns in these processes.
What’s next?
Once you have looked at these areas and found issues with your data, where do you go for help? Fortunately, there a number of resources available. First, you should check Blackbaud’s Knowledgebase, as it is an excellent resource for solving many data-related and process-related problems. Secondly, post questions and problems on one of the LinkedIn forums. Very rarely will you find a problem that no one else has experienced, and you will find the RE user community very active and helpful. You can also contact Omatic Software. We have a number of highly trained consultants who have been in the trenches with over 40 years of combined RE and nonprofit experience who can help you get your database back on track, perform a deeper audit, or help you get started. Additionally, Omatic Software can be used to assist with data clean up. For example, you can use our TableOmatic utility to analyze the use of tables throughout your system.
Conclusion
The first step in solving database problems is identifying the root cause. Diagnosing the cause will require thorough database auditing. Reviewing system statistics and the configuration of the application, running reports to gain a better understanding of the data, and developing queries that enable close examination of the data will assist in providing the details necessary to not only diagnose the root cause, but also provide clear solutions for the problems.
