Project

General

Profile

Exporting Raiser's Edge for CiviCRM » History » Version 20

Jon Goldberg, 08/08/2014 03:21 AM

1 1 Jon Goldberg
h1. Exporting Raiser's Edge for CiviCRM
2
3
You can export all of the data using the Raiser's Edge Export tool.  You'll see it on the left toolbar when you first enter Raiser's Edge.
4
5
From the tool, you will create a number of exports.  When you first create an export, you'll be asked a number of questions, including Export Type (Constituent, Gift, etc.), a checkbox to include inactive records (check this), and an export file type (select CSV).
6
7 2 Jon Goldberg
For the Export type, start with Constituent.  This is the "base table" - all records will be joined relative to it.
8 1 Jon Goldberg
9 3 Jon Goldberg
h2. Constituent Based Exports
10 1 Jon Goldberg
11 3 Jon Goldberg
h3. Constituent Information
12 4 Jon Goldberg
13 12 Jon Goldberg
If extracting directly from SQL, @SELECT * FROM RECORDS WHERE CONSTITUENT_ID = -1@.
14
15 1 Jon Goldberg
Create a new export.
16
Tab 1. General:
17
- Include all records.
18
- Head of Household processing: Export both constituents separately.
19
- Check all of the "Include these Constitutents": Inactive, deceased, no valid address
20
21
Tab 2: Output.
22
First, expand the "Constituent Information" in the left pane, and add every field to the export.  Do the export (as a CSV).
23
24 6 Jon Goldberg
h3. Constituent Codes
25 3 Jon Goldberg
26 15 Jon Goldberg
In RE: Found at the bottom of the "Bio 2" tab.
27 19 Jon Goldberg
In SQL: CONSTITUENT_CODES maps to "GroupContact".  TABLEENTRIES stores the codes ("groups").  In my case, @SELECT *  FROM [CCR_July_snapshot].[dbo].[TABLEENTRIES] WHERE [CODETABLESID] = 43@ did the trick.  YMMV - see "deciphering stored procedures" below.
28
29 3 Jon Goldberg
Export as _one to many_, below.
30
These map to "groups" in Civi - can also be mapped to "tags" if you don't need to track the begin/end date on them.
31
32
No need to export these fields:
33
System Record ID
34
Import ID
35 1 Jon Goldberg
As of Civi 4.4.6, there's no way to import Group Begin/End dates via API, you need to do it via SQL.
36 6 Jon Goldberg
37
h3. Solicit Codes
38
39
Export as _one to many_, below.
40
These can map to groups - but also may map to privacy preferences or custom fields (e.g. Email Only, Do Not Solicit)
41
Export the "Solicit Code" only (along with the Constituent's System Record ID, of course).
42 3 Jon Goldberg
43 20 Jon Goldberg
h3. Addresses
44
45
SQL tables: ADDRESS, CONSTIT_ADDRESS
46
47
Addresses are a many-to-many relationship in RE.
48
49 9 Jon Goldberg
h3. Relationships
50
51
Relationships are different in Civi and RE in the following significant ways:
52
* Relationships don't have to have a relationship type.
53
* The A-B relationship doesn't have to have the same relationship type as B-A (e.g. if my relationship is "parent", the reciprocal relationship could be "son" or "daughter".
54
* Most importantly, related contacts need not have their own constituent record (though they can).  If they don't have their own constituent record, they nevertheless have a record, just not one that's available from within the normal queries/lookups.
55
56 10 Jon Goldberg
h3. Attributes
57
58
Attributes are the RE equivalent of custom fields.  However, unlike custom fields, they can also have a "date" value and a "comments" value.  While this can be replicated in Civi via multi-record custom field groups, ideally the data is evaluated attribute by attribute.
59
60 11 Jon Goldberg
Valuable information about the setup of the attributes is available in RE from *Config > Attributes*.
61 1 Jon Goldberg
62 11 Jon Goldberg
*note:*  I'm currently evaluating the use of CiviCRM 4.5+'s "EntityRef" functionality to facilitate chained selects of OptionValue lists.  If this is successful, that would facilitate creating a single multi-record custom field group (with the fields "Attribute", "Description", "Date", "Comments") that would work VERY similarly to how RE handles attributes.
63 10 Jon Goldberg
64 3 Jon Goldberg
h3. Other constituent tables:
65 5 Jon Goldberg
66 2 Jon Goldberg
Skip these tables:
67
* Spouse
68
* Gifts
69
* First Gift, Last gift, Largest Gift
70
* Actions
71 1 Jon Goldberg
* First Action, Last Action
72
* Summary Information
73
74 7 Jon Goldberg
h3. Tables that Civi doesn't have a direct counterpart for
75 5 Jon Goldberg
76 3 Jon Goldberg
* Aliases (stores Maiden Name and d/b/a - unsure how to import into Civi just yet)
77 7 Jon Goldberg
* Solicitor Goals - Can be found on an RE contact record on "Bio 1" tab by clicking "Details" next to "Is a Solicitor" checkbox.  Don't know how to use them.
78 2 Jon Goldberg
79
80
Open each CSV file in Excel or similar.  Sort each field by ascending AND descending to see if any data is stored in that field.  If every record has no data or the same data, delete it - it's not being tracked in the current system.  If you see only one or two records with a particular field, they're also probably fine to go, but check with the client first.
81
82 1 Jon Goldberg
83
Next, strip out all of the constituent information except for primary/foreign keys.  I like to keep in First/Middle/Last name just for human readability though.  So leave in those three fields, plus any field with the word "ID" in it.  This is your base constituent info, and will be in every other export you do.
84
85
Now comes the fun part!  Export each table, one at a time, by adding those fields to an export that already includes the base constituent info.
86
87
For one-to-many relationships, the system will ask you how many instances of the information to export.  I default to 12, then look over the data to see how many are actually used, then re-export with a higher or lower number.
88
89
I also remove records that don't contain the relevant data.  For instance, when exporting Solicit Codes, I sort by the first Solicit Code.  Then I scroll down past the folks that have Solicit Codes to those who have none, and delete the rows for folks who have none.
90
91
Note that for simplicity's sake, RE contains many views of the tables that, if you export them all, you'll have redundant data.  There's no need to export "First Gift", "Last Gift", or "Largest Gift" - simply export all gifts.  Likewise for "Preferred Address".
92
93
When exporting one-to-many tables that themselves contain one-to-many tables (e.g. Addresses contains Phones), do NOT select 12 of each!  That means you're exporting 144 phone numbers per record.  First determine the maximum number of addresses being tracked, re-export with that number, THEN export with phone numbers.  Also, it's reasonable to export with 5 phone numbers per address.
94
95
NOTE: Letters sent is incomplete, there's more than 12 letters to some folks!
96
97
GIFTS is related to constituent on the last column (Constituent System Record ID)
98 8 Jon Goldberg
99 13 Jon Goldberg
h3. Code Tables/Option Groups/Option Values
100
101 17 Jon Goldberg
If you're extracting data from the SQL back-end, you'll see that the RE equivalent to Civi option groups is "code tables".  There's two functions that handle lookups: dbo.GetTableEntryDescription and dbo.GetTableEntryDescSlim.  To determine where the data is being accessed by the function, see "Deciphering MS SQL", below.  Use the "lTableNumber" passed to those functions and you'll find your data in dbo.CODETABLES (comparable to civicrm_option_group), dbo.CODETABLEMAP and dbo.TABLEENTRIES (comparable to civicrm_option_value).
102
103
h2. Deciphering MS SQL
104
105
SQL Server Profiler is a tool that lets you spy on SQL statements passed to MS SQL, which is good for determining where certain data lives.  However, RE depends on functions and stored procedures, so sometimes the SQL won't tell you exactly where to look.
106
107
h3. Looking Up Functions
108
109
These are embedded in SQL and have a nomenclature like: dbo.GetTableEntryDescSlim. Find them in SQL Server Management Studio: database > Programmability > Functions > Scalar-valued Functions.
110
111
h3. Looking Up Stored Procedures
112
113 18 Jon Goldberg
If, in the profiler, taking a certain action shows a command like this:
114 17 Jon Goldberg
These have a syntax like:
115 1 Jon Goldberg
<pre>
116 18 Jon Goldberg
exec sp_execute 48,43,'Acknowledgee'
117 1 Jon Goldberg
</pre>
118
119 18 Jon Goldberg
You're dealing with a stored procedure.  You need to find the corresponding @exec sp_prepexec@ command (in this case, the one with a 48).  In this case, it looks like:
120
<pre>
121
declare @p1 int
122
set @p1=48
123
exec sp_prepexec @p1 output,N'@P1 int,@P2 varchar(255)',N'SELECT  Top 1 TABLEENTRIESID  FROM DBO.TABLEENTRIES WHERE CODETABLESID = @P1 AND LONGDESCRIPTION = @P2  ',43,'Acknowledgee'
124
select @p1
125
</pre>
126
127 17 Jon Goldberg
128 13 Jon Goldberg
129 14 Jon Goldberg
h3. Addressee/Postal Greeting/E-mail greeting
130
131
RE has a much wider variety of greeting formats out-of-the-box.  The "spouse ID" is stored on the record to enable quick lookups of addressee greetings that include the spouse.
132
133 8 Jon Goldberg
See also:
134
http://support.littlegreenlight.com/kb/migration/migrating-from-the-raisers-edge-to-lgl
135 14 Jon Goldberg
136
h3. Things I see that RE does better than Civi:
137
138
* Better greetings/salutations UI out of the box.  In Civi, you must in-line edit the greetings, then press "Edit" next to the greetings, and even then you only see the tokens you'll use.  RE lets you edit with no clicks, and parses the tokens for you.
139
* The equivalent of option values are stored with their id, not their value.  This isn't a big deal, but it DOES make data transformation easier in RE, and I suspect it makes their equivalent of pseudoconstant code easier to read.
Go to top