Request a Demo

Raiser’s Edge Tutorial: How to Merge Databases with RE

March 10th, 2014

So you want to load in all of the data from another database into your organization’s Raiser’s Edge (RE) database. You’ve gotta ask yourself a question: “Do I feel lucky?” Accomplishing this task doesn’t have to be like a threatening scene out of a movie, but it could turn out that way if you don’t prepare well before undertaking it. My goal in this Raiser’s Edge tutorial is to give you some things to consider while you are planning out your project.

If you are a regular reader of this blog then you already know we preach consistency in your data entry practices as one of the tenets of good database management. This doctrine holds true when you are merging the information from one database into another database. The first step is to determine which database is going to be the “database of record” / “master database” / “parent database” for the merge project, and also for updates moving forward if both databases are to continue to be used for live data entry. The “database of record” is the one that contains the data your organization (or sometimes just a particular department) considers to be always correct. If there is a discrepancy between the two databases, the information in the database of record will override the information in the other database. Generally, you should select the same database of record for both the merge project and for the long-term update procedures.

As an example, if you were merging your RE database and your online fundraising database, you might decide that RE should be the database of record because you have paid to have an NCOA service performed on the data, and you consider those addresses to be more reliable than the ones in the online database. Or perhaps you would consider the online database to be more current because the constituents update it themselves, and they update their information more regularly than their records in RE get updated.

Next, you will want to examine what is stored in the database you are bringing into RE. Do you want all of the data it contains, or do you just want select pieces of the information?

After deciding what data you want, you will sit down in front of RE and decide where each piece of information from the other database will reside. Some things, like name fields, are easy. Some things might be more difficult. For example, if you have event participation information in the online database, where are you going to store that data? If you have the RE:Events module, it might seem like a simple choice, but if there are historical events in the online database that are not in RE, do you want to go through the steps to set up the historical events in RE, or would you perhaps rather use Actions or Attributes to track this particular historical data, and only set up more current events (ones in the last year / two years / three years) in the RE:Event module?  

Once you have decided where you want to store the data in RE, you will very likely have some advanced preparation work to do in the RE database before you actually bring in the data. Things to think about:

If you are bringing in gifts, do you need to add any Campaigns, Funds, Appeals, or Packages to RE?

What will be the Post Status of any incoming gifts? If your RE database is integrated with Blackbaud’s The Financial Edge™ or Education Edge™ (FE/EE) database, remember that any gifts that are marked as “Not Posted” will transfer to the GL in FE/EE the next time the “Post to General Ledger” function is run in RE. The same thing is also true if you are using Omatic’s PostOmatic tool to post to an accounting program other than FE/EE. If the incoming gifts are already in your accounting system, the resulting duplication could seriously stress your relationship with your accounting team. In this case, it may be that all gifts from the other database need to be brought in as “Posted” or “Do Not Post” in order to maintain a happy accounting database. Otherwise, you may have to remember to always use a particular date range in the “Post to General Ledger” function to avoid picking up the historical gifts, and someday someone might forget.

If you will be tracking any of the incoming data as Actions or Attributes, you will need to be sure some data points have been set up in RE before you bring in data. Examples: Action Types and Statuses, Attribute Categories and Descriptions, and you may want to populate any tables for Attribute Descriptions with the expected values ahead of time. Remember that most of these are table-driven fields in RE, and as such in order to successfully bring in the data, the values in the incoming data file and the values in RE must match exactly. Fortunately, since you are using ImportOmatic (and you are, right?), you can have the original value in the file, and use a dictionary to translate that value to the one you want to use in RE. If you are going to do this, you will want to make notes for yourself so you remember to set up your dictionaries before you attempt the import.

If you are using RE 7.93 or higher, you will need to pay attention to date formats in the incoming data. Birthdates now require a four-digit year, for both MM/YYYY and MM/DD/YYYY format.

The next big piece of the process is to run an extraction/export from the non-RE database, and examine how the data comes out.

Are names all in one field, perhaps along with titles and suffixes? If so, you’ll have to change the way the data exports, if possible, or use Excel functions to parse the information into separate columns before you can import the data. A good rule to remember here is that if a value has its own field in RE, it has to be in its own column for importing. Example: The values of Last Name and First Name have their own fields in RE, therefore they must be in separate columns to import properly.

Speaking of names, do spouses export with their names together (Ex: “John and Jane” for the first name)? If so, you would need to parse the names into separate columns and remove the “and” conjunction.

Do dates come out in a non-standard format (Ex: YYYY/MM/DD) that would need to be changed, or include a time stamp that would need to be removed?

Is Address data all in one column? Or maybe the address lines are broken out, but City, State, and ZIP/Postal Code all come out in one column. These values would also need to be parsed.

Does the data export in such a way that you will be able to easily tell what type of relationship exists between people? Do spouses, and children or other relationships, ever land in the same sets of columns? Remember that RE treats spouses slightly differently, so you would want spouse data in its own columns for the best outcome. This would also allow you to use the Spouse Grouping function in ImportOmatic.

You’ve got all the RE prep work done, and you’ve got your files cleaned up, you know where everything will be stored, and you’ve built your IOM profiles and dictionaries. Now it’s time to import, right? Nope. Now it’s time to TEST. Preferably in your test environment. If you’ve ever spoken to me, here in Support, you know I advocate extensive testing of new processes. I recommend you test every import profile with as many possible permutations of the data you can think of, being sure to check the results carefully after each run, before you run the live import(s) for your merge project.

Why do I want you to test so extensively? It’s because an import, just like construction adhesive, is forever. While you can restore a backup to recover from an import, if you make a mistake with the adhesive, you’re…well…stuck. Of course, if you don’t have good database backups, you could be stuck with the results of a poorly-executed import.

That said, if you take the time to plan, and test, your database merge, the project should go off without a hitch…or at least with only minor bumps in the road. While the above items are in no way a comprehensive list, they are a good foundation to start the conversation surrounding planning your database merge project.

Be sure to check out our other Raiser’s Edge Tutorials:

SHARE THIS POST