Project

General

Profile

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

Jon Goldberg, 09/29/2014 01:24 AM

1 24 Jon Goldberg
{{toc}}
2
3 1 Jon Goldberg
h1. Exporting Raiser's Edge for CiviCRM
4
5 23 Jon Goldberg
There are two basic approaches to exporting RE data.  There's the built-in export tool, and there's direct SQL interaction.  This document will try to cover both approaches where possible.  The Export tool has a lower barrier to entry, but a) there's some data you can't export with the tool, and b) the data will be denormalized, requiring additional transformation compared to extracting normalized SQL data.
6 1 Jon Goldberg
7 23 Jon Goldberg
h2. Export tool - general guide.
8
9
The Raiser's Edge Export tool is on the left toolbar when you first enter Raiser's Edge.
10
11 1 Jon Goldberg
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).
12
13 23 Jon Goldberg
For most export, select Constituent as the Export type.  This is the "base table" - all records will be joined relative to it.
14 3 Jon Goldberg
15 1 Jon Goldberg
h2. Constituent Based Exports
16
17 21 Jon Goldberg
h3. Contact Information
18 1 Jon Goldberg
19 23 Jon Goldberg
RE differentiates between constituents and non-constituents in their system.  If you create a new contact, they're a constituent - but then you might decide to add a spouse or employer record, which is NOT considered a constituent, and doesn't show up in most queries.  Notably, non-constituents aren't exported when using the Export tool and your base table is "Constituent".
20 1 Jon Goldberg
21 23 Jon Goldberg
h3. SQL
22
23 1 Jon Goldberg
If extracting directly from SQL, @SELECT * FROM RECORDS@.
24 21 Jon Goldberg
25 23 Jon Goldberg
Note that you can extract only constituents by adding @WHERE IS_CONSTITUENT = -1@.  For a Civi migration, I recommend importing all contacts.
26 21 Jon Goldberg
27 23 Jon Goldberg
h3. Export tool (NOTE: This ONLY gets constituents).
28
29 1 Jon Goldberg
Tab 1. General:
30
- Include all records.
31
- Head of Household processing: Export both constituents separately.
32
- Check all of the "Include these Constitutents": Inactive, deceased, no valid address
33
34
Tab 2: Output.
35
First, expand the "Constituent Information" in the left pane, and add every field to the export.  Do the export (as a CSV).
36 6 Jon Goldberg
37 3 Jon Goldberg
h3. Constituent Codes
38 15 Jon Goldberg
39 19 Jon Goldberg
In RE: Found at the bottom of the "Bio 2" tab.
40
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.
41 3 Jon Goldberg
42 1 Jon Goldberg
Export as _one to many_, below.
43 3 Jon Goldberg
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.
44
45
No need to export these fields:
46
System Record ID
47
Import ID
48 23 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 direct SQL.
49 6 Jon Goldberg
50
h3. Solicit Codes
51
52
Export as _one to many_, below.
53
These can map to groups - but also may map to privacy preferences or custom fields (e.g. Email Only, Do Not Solicit)
54
Export the "Solicit Code" only (along with the Constituent's System Record ID, of course).
55 3 Jon Goldberg
56 20 Jon Goldberg
h3. Addresses
57
58
SQL tables: ADDRESS, CONSTIT_ADDRESS
59
60
Addresses are a many-to-many relationship in RE.
61 27 Jon Goldberg
Not all addresses in the database are visible in RE.  Addresses with a 1 or 7, for instance.  Make sure to look your data over and filter those out accordingly.
62 20 Jon Goldberg
63 25 Jon Goldberg
h3. Phones/E-mail/websites
64 1 Jon Goldberg
65 25 Jon Goldberg
RE is a children of the 90's, so a) phones are tied to addresses, not contacts, and b) e-mails and websites are a type of phone.
66
67 26 Jon Goldberg
Notes:
68
* You can NOT have duplicate phone types in RE, so no need to try and catch multiple "Home" numbers!
69
* Oh - except that one contact can have two home phone numbers on two different addresses.
70
* Don't forget to filter out duplicate numbers/e-mails/etc. when someone puts the same phone number on two different addresses.
71 22 Jon Goldberg
72
This SQL gets me a useful list of phones and e-mail for further processing in Kettle:
73
<pre>
74
SELECT DISTINCT
75
  CONSTITADDRESSID
76
, CONSTIT_ID
77
, PHONETYPEID
78
, CONSTIT_ADDRESS_PHONES."SEQUENCE"
79
, NUM
80
, DO_NOT_CALL
81
, TEXT_MSG
82
FROM CONSTIT_ADDRESS_PHONES
83
LEFT JOIN PHONES ON CONSTIT_ADDRESS_PHONES.PHONESID = PHONES.PHONESID
84
LEFT JOIN CONSTIT_ADDRESS ON CONSTITADDRESSID = CONSTIT_ADDRESS.ID
85
</pre>
86
87 9 Jon Goldberg
h3. Relationships
88
89 29 Jon Goldberg
Relevant SQL table: CONSTIT_RELATIONSHIPS
90
91 9 Jon Goldberg
Relationships are different in Civi and RE in the following significant ways:
92
* Relationships don't have to have a relationship type.
93 1 Jon Goldberg
* 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".
94 29 Jon Goldberg
* 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 in RECORDS, they're just not a constituent.
95 30 Jon Goldberg
* There need not be a relationship type at all.  This doesn't make sense, except that:
96
* There are hardcoded fields for IS_SPOUSE, HON_MEM_ACKNOWLEDGE, IS_HEADOFHOUSEHOLD, and SOFTCREDIT_GIFTS.
97
98 32 Jon Goldberg
Because relationships aren't necessarily reciprocal, I find it helpful to take my list of invalid relationships and do BOTH of the following:
99
* Look up the RELATIONSHIP_TYPE against the @name_b_a@ field in @civicrm_relationship_type@.
100
* Look up the RECIP_RELATIONSHIP_TYPE against both @name_a_b@ and @name_b_a@ in @civicrm_relationship_type@.
101 9 Jon Goldberg
102 10 Jon Goldberg
h3. Attributes
103
104
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.
105
106 11 Jon Goldberg
Valuable information about the setup of the attributes is available in RE from *Config > Attributes*.
107 1 Jon Goldberg
108 33 Jon Goldberg
* The analogous field to @civicrm_custom_field@ is @AttributeTypes@.
109
* @AttributeTypes.CODETABLESID@ gives a lookup for the RE "option group" that contains valid options for that attribute.
110 37 Jon Goldberg
* All constituent attribute data is stored in the table @ConstituentAttributes@.  Note that it's stored in a Key-Value Pair-style table - you'll need to do a bunch of SQL queries, or run a Kettle "Row Denormaliser" step to get this data in order.
111
 
112 33 Jon Goldberg
Here's my preliminary SQL to export attributes from RE:
113
<pre>
114
SELECT
115 36 Jon Goldberg
ca.PARENTID as external_identifier
116
, ca.ATTRIBUTETYPESID
117
, at.DESCRIPTION as Category
118
, TABLEENTRIES.LONGDESCRIPTION as Description
119 33 Jon Goldberg
, TEXT
120
, NUM
121
, DATETIME
122
, CURRENCY
123
, "BOOLEAN"
124 35 Jon Goldberg
, COMMENTS
125 33 Jon Goldberg
, ca.ATTRIBUTEDATE
126
FROM ConstituentAttributes ca
127
JOIN AttributeTypes at ON ca.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID
128 36 Jon Goldberg
LEFT JOIN TABLEENTRIES ON ca.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID
129 33 Jon Goldberg
</pre>
130
131 34 Jon Goldberg
*note:*  In the SQL above, "PARENTID" and not "ConstitID" is the correct foreign key to link this to the contact.
132
133 38 Jon Goldberg
To get a list of option values out of RE for the attributes, use this SQL:
134
<pre>
135
SELECT
136
DESCRIPTION
137
, at.CODETABLESID
138
, LONGDESCRIPTION
139
FROM TABLEENTRIES te 
140
LEFT JOIN AttributeTypes at ON te.CODETABLESID = at.CODETABLESID
141
ORDER BY DESCRIPTION
142
</pre>
143
144 39 Jon Goldberg
Attributes can be multi-record custom fields by their nature, so you have to account for that.  Here's some alpha-grade SQL for sussing out which fields have multi-record custom fields:
145
<pre>
146
SELECT ATTRIBUTETYPESID, PARENTID, COUNT(LONGDESCRIPTION)
147
FROM ConstituentAttributes ca
148
JOIN TABLEENTRIES te ON ca.TABLEENTRIESID = te.TABLEENTRIESID
149
GROUP BY PARENTID, ATTRIBUTETYPESID
150
HAVING COUNT(LONGDESCRIPTION) > 1
151
ORDER BY ATTRIBUTETYPESID
152
</pre>
153
154 38 Jon Goldberg
*note:*  In Civi 4.5+, you could conceivable use "EntityRef" functionality to facilitate chained selects of OptionValue lists.  That would let you create a multi-record custom field group that would very closely map how Attributes work in RE - but you'd have all the disadvantages of multi-record custom fields.
155 10 Jon Goldberg
156 3 Jon Goldberg
h3. Other constituent tables:
157 5 Jon Goldberg
158 2 Jon Goldberg
Skip these tables:
159
* Spouse
160
* Gifts
161
* First Gift, Last gift, Largest Gift
162
* Actions
163 1 Jon Goldberg
* First Action, Last Action
164
* Summary Information
165
166 41 Jon Goldberg
h2. Contribution-related exports
167
168
h3. Contributions/Gifts
169
170
Contributions (in RE parlance: Gifts) are complicated beasts!
171
172
Here are some relevant database tables and their equivalent in Civi:
173
GIFT	civicrm_contribution
174
GiftSplit	civicrm_line_item
175
CAMPAIGN	Roughly maps to Campaign.  Your mapping may vary and/or include custom fields.
176
APPEAL	Also roughly maps to Campaign (or Source).  Your mapping may vary and/or include custom fields.
177
FUND	Roughly maps to Financial Type.  Your mapping may vary and/or include custom fields.
178
179
Note that gift type is hardcoded into a function called "TranslateGiftType) - so you may want to include that function in your SQL, e.g.:
180
<pre>
181
SELECT
182
gs.GiftId
183
, g.CONSTIT_ID
184
, gs.Amount
185
, g.DTE as gift_date
186
, FUND.DESCRIPTION as fund
187
, CAMPAIGN.DESCRIPTION as campaign
188
, APPEAL.DESCRIPTION as appeal
189
, g.PAYMENT_TYPE
190
, g.ACKNOWLEDGE_FLAG
191
, g.CHECK_NUMBER
192
, g.CHECK_DATE
193
, g.BATCH_NUMBER
194
, g.ANONYMOUS
195
, gst.LONGDESCRIPTION as giftsubtype
196
, g.TYPE
197
, DBO.TranslateGiftType(g.TYPE) as type2
198
FROM GiftSplit gs
199
LEFT JOIN FUND on gs.FundId = FUND.id
200
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
201
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
202
LEFT JOIN GIFT g on gs.GiftId = g.ID
203
LEFT JOIN TABLEENTRIES gst on g.GIFTSUBTYPE = gst.TABLEENTRIESID
204
</pre>
205
206 42 Jon Goldberg
Payment Type is also hard-coded, it seems:
207
1	Cash
208
2	Personal Check
209
3	Business Check
210
4	Credit Card
211
6	Direct Debit
212
8	Other
213
214
h3. Soft Credits
215
216
Stored in GIFTSOFTCREDIT.  RE does NOT have the concept of a soft credit type - which is fine.
217
<pre>
218
SELECT
219
, GiftId
220
, ConstitId
221
, Amount
222
, 'Soft Credit' as soft_credit_type
223
FROM GiftSoftCredit
224
</pre>
225
226
h3. Solicitor
227
228
I imported these as soft credits, but a different TYPE of soft credit.  Here's the SQL I used to get the data out of RE:
229
<pre>
230
SELECT
231
ParentId as gift_id
232
, SolicitorId as soft_creditee_external_identifier
233
, Amount
234
, 'Solicitor' as soft_credit_type
235
FROM GiftSolicitor
236
</pre>
237
238 41 Jon Goldberg
h3. In Honor/Memorial Of (aka Tributes)
239
240
As of CiviCRM 4.5, In Honor/Memorial of is considered a form of soft credit.  In RE, they're still separate, and are called Tributes.  The structure is a little more complex - the table structure is Constituent <-> Tribute <-> Gift_Tribute <-> Gift.  Civi is Contact <-> Soft Credit <-> Contribution.  
241
242
Here is some preliminary SQL that pulls tribute data suitable for transformation and import to Civi as ContributionSoft entities.  Note that CiviCRM doesn't have a concept of a "Description" but does have the concept of a PCP Note, so I'm importing the description there - in the future, I could see the argument for Civi exposing the PCP Note as a description.
243
244
<pre>
245
SELECT
246
gt.GIFT_ID
247
, gt.TRIBUTE_TYPE
248
, t.DESCRIPTION
249
, t.RECORDS_ID as tributee_extenal_identifier
250
, te.LONGDESCRIPTION as tribute_type
251
FROM GIFT_TRIBUTE gt
252
JOIN TRIBUTE t ON gt.TRIBUTE_ID = t.ID
253
LEFT JOIN TABLEENTRIES te on gt.TRIBUTE_TYPE = te.TABLEENTRIESID
254
</pre>
255
256 7 Jon Goldberg
h3. Tables that Civi doesn't have a direct counterpart for
257 5 Jon Goldberg
258 3 Jon Goldberg
* Aliases (stores Maiden Name and d/b/a - unsure how to import into Civi just yet)
259 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.
260 2 Jon Goldberg
261
262
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.
263
264 1 Jon Goldberg
265
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.
266
267
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.
268
269
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.
270
271
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.
272
273
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".
274
275
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.
276
277
NOTE: Letters sent is incomplete, there's more than 12 letters to some folks!
278
279
GIFTS is related to constituent on the last column (Constituent System Record ID)
280 8 Jon Goldberg
281 13 Jon Goldberg
h3. Code Tables/Option Groups/Option Values
282
283 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).
284
285
h2. Deciphering MS SQL
286
287
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.
288
289
h3. Looking Up Functions
290
291
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.
292
293
h3. Looking Up Stored Procedures
294
295 18 Jon Goldberg
If, in the profiler, taking a certain action shows a command like this:
296 17 Jon Goldberg
These have a syntax like:
297 1 Jon Goldberg
<pre>
298 18 Jon Goldberg
exec sp_execute 48,43,'Acknowledgee'
299 1 Jon Goldberg
</pre>
300
301 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:
302
<pre>
303
declare @p1 int
304
set @p1=48
305
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'
306
select @p1
307
</pre>
308
309 40 Jon Goldberg
Note that there's a tool called "SQL Hunting Dog", a free plug-in for SQL Server Management Studio, which makes locating stored procedures, etc. easier.
310 17 Jon Goldberg
311 13 Jon Goldberg
312 14 Jon Goldberg
h3. Addressee/Postal Greeting/E-mail greeting
313
314
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.
315
316 8 Jon Goldberg
See also:
317
http://support.littlegreenlight.com/kb/migration/migrating-from-the-raisers-edge-to-lgl
318 14 Jon Goldberg
319
h3. Things I see that RE does better than Civi:
320
321
* 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.
322
* 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.
323 28 Jon Goldberg
* There's a lot more data stored in many-to-many tables.  For instance, job titles are stored in the relationship tab, reflecting the fact that someone can have more than one job.
Go to top