Project

General

Profile

Exporting Raiser's Edge for CiviCRM » History » Revision 40

Revision 39 (Jon Goldberg, 09/26/2014 06:18 PM) → Revision 40/71 (Jon Goldberg, 09/28/2014 04:17 PM)

{{toc}} 

 h1. Exporting Raiser's Edge for CiviCRM 

 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. 

 h2. Export tool - general guide. 

 The Raiser's Edge Export tool is on the left toolbar when you first enter Raiser's Edge. 

 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). 

 For most export, select Constituent as the Export type.    This is the "base table" - all records will be joined relative to it. 

 h2. Constituent Based Exports 

 h3. Contact Information 

 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". 

 h3. SQL 

 If extracting directly from SQL, @SELECT * FROM RECORDS@. 

 Note that you can extract only constituents by adding @WHERE IS_CONSTITUENT = -1@.    For a Civi migration, I recommend importing all contacts. 

 h3. Export tool (NOTE: This ONLY gets constituents). 

 Tab 1. General: 
 - Include all records. 
 - Head of Household processing: Export both constituents separately. 
 - Check all of the "Include these Constitutents": Inactive, deceased, no valid address 

 Tab 2: Output. 
 First, expand the "Constituent Information" in the left pane, and add every field to the export.    Do the export (as a CSV). 

 h3. Constituent Codes 

 In RE: Found at the bottom of the "Bio 2" tab. 
 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. 

 Export as _one to many_, below. 
 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. 

 No need to export these fields: 
 System Record ID 
 Import ID 
 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. 

 h3. Solicit Codes 

 Export as _one to many_, below. 
 These can map to groups - but also may map to privacy preferences or custom fields (e.g. Email Only, Do Not Solicit) 
 Export the "Solicit Code" only (along with the Constituent's System Record ID, of course). 

 h3. Addresses 

 SQL tables: ADDRESS, CONSTIT_ADDRESS 

 Addresses are a many-to-many relationship in RE. 
 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. 

 h3. Phones/E-mail/websites 

 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. 

 Notes: 
 * You can NOT have duplicate phone types in RE, so no need to try and catch multiple "Home" numbers! 
 * Oh - except that one contact can have two home phone numbers on two different addresses. 
 * Don't forget to filter out duplicate numbers/e-mails/etc. when someone puts the same phone number on two different addresses. 

 This SQL gets me a useful list of phones and e-mail for further processing in Kettle: 
 <pre> 
 SELECT DISTINCT 
   CONSTITADDRESSID 
 , CONSTIT_ID 
 , PHONETYPEID 
 , CONSTIT_ADDRESS_PHONES."SEQUENCE" 
 , NUM 
 , DO_NOT_CALL 
 , TEXT_MSG 
 FROM CONSTIT_ADDRESS_PHONES 
 LEFT JOIN PHONES ON CONSTIT_ADDRESS_PHONES.PHONESID = PHONES.PHONESID 
 LEFT JOIN CONSTIT_ADDRESS ON CONSTITADDRESSID = CONSTIT_ADDRESS.ID 
 </pre> 

 h3. Relationships 

 Relevant SQL table: CONSTIT_RELATIONSHIPS 

 Relationships are different in Civi and RE in the following significant ways: 
 * Relationships don't have to have a relationship type. 
 * 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". 
 * 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. 
 * There need not be a relationship type at all.    This doesn't make sense, except that: 
 * There are hardcoded fields for IS_SPOUSE, HON_MEM_ACKNOWLEDGE, IS_HEADOFHOUSEHOLD, and SOFTCREDIT_GIFTS. 

 Because relationships aren't necessarily reciprocal, I find it helpful to take my list of invalid relationships and do BOTH of the following: 
 * Look up the RELATIONSHIP_TYPE against the @name_b_a@ field in @civicrm_relationship_type@. 
 * Look up the RECIP_RELATIONSHIP_TYPE against both @name_a_b@ and @name_b_a@ in @civicrm_relationship_type@. 

 h3. Attributes 

 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. 

 Valuable information about the setup of the attributes is available in RE from *Config > Attributes*. 

 * The analogous field to @civicrm_custom_field@ is @AttributeTypes@. 
 * @AttributeTypes.CODETABLESID@ gives a lookup for the RE "option group" that contains valid options for that attribute. 
 * 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. 
 
 Here's my preliminary SQL to export attributes from RE: 
 <pre> 
 SELECT 
 ca.PARENTID as external_identifier 
 , ca.ATTRIBUTETYPESID 
 , at.DESCRIPTION as Category 
 , TABLEENTRIES.LONGDESCRIPTION as Description 
 , TEXT 
 , NUM 
 , DATETIME 
 , CURRENCY 
 , "BOOLEAN" 
 , COMMENTS 
 , ca.ATTRIBUTEDATE 
 FROM ConstituentAttributes ca 
 JOIN AttributeTypes at ON ca.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID 
 LEFT JOIN TABLEENTRIES ON ca.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID 
 </pre> 

 *note:*    In the SQL above, "PARENTID" and not "ConstitID" is the correct foreign key to link this to the contact. 

 To get a list of option values out of RE for the attributes, use this SQL: 
 <pre> 
 SELECT 
 DESCRIPTION 
 , at.CODETABLESID 
 , LONGDESCRIPTION 
 FROM TABLEENTRIES te  
 LEFT JOIN AttributeTypes at ON te.CODETABLESID = at.CODETABLESID 
 ORDER BY DESCRIPTION 
 </pre> 

 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: 
 <pre> 
 SELECT ATTRIBUTETYPESID, PARENTID, COUNT(LONGDESCRIPTION) 
 FROM ConstituentAttributes ca 
 JOIN TABLEENTRIES te ON ca.TABLEENTRIESID = te.TABLEENTRIESID 
 GROUP BY PARENTID, ATTRIBUTETYPESID 
 HAVING COUNT(LONGDESCRIPTION) > 1 
 ORDER BY ATTRIBUTETYPESID 
 </pre> 

 *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. 

 h3. Other constituent tables: 

 Skip these tables: 
 * Spouse 
 * Gifts 
 * First Gift, Last gift, Largest Gift 
 * Actions 
 * First Action, Last Action 
 * Summary Information 

 h3. Tables that Civi doesn't have a direct counterpart for 

 * Aliases (stores Maiden Name and d/b/a - unsure how to import into Civi just yet) 
 * 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. 


 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. 


 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. 

 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. 

 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. 

 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. 

 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". 

 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. 

 NOTE: Letters sent is incomplete, there's more than 12 letters to some folks! 

 GIFTS is related to constituent on the last column (Constituent System Record ID) 

 h3. Code Tables/Option Groups/Option Values 

 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). 

 

 h2. Deciphering MS SQL 

 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. 

 h3. Looking Up Functions 

 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. 

 h3. Looking Up Stored Procedures 

 If, in the profiler, taking a certain action shows a command like this: 
 These have a syntax like: 
 <pre> 
 exec sp_execute 48,43,'Acknowledgee' 
 </pre> 

 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: 
 <pre> 
 declare @p1 int 
 set @p1=48 
 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' 
 select @p1 
 </pre> 

 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. 


 



 h3. Addressee/Postal Greeting/E-mail greeting 

 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. 

 See also: 
 http://support.littlegreenlight.com/kb/migration/migrating-from-the-raisers-edge-to-lgl 

 

 h3. Things I see that RE does better than Civi: 

 * 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. 
 * 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. 
 * 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