| CONSTITUENTSYSTEMID |
uniqueidentifier |
yes |
|
dbo.[CONSTITUENT].[ID] |
| CONSTITUENTLOOKUPID |
nvarchar(100) |
yes |
|
dbo.[CONSTITUENT].[LOOKUPID] |
| FULLNAME |
nvarchar(154) |
yes |
|
dbo.[CONSTITUENT].[NAME] |
| TITLE |
nvarchar(100) |
yes |
|
dbo.[TITLECODE].[DESCRIPTION] |
| FIRSTNAME |
nvarchar(50) |
yes |
|
dbo.[CONSTITUENT].[FIRSTNAME] |
| MIDDLENAME |
nvarchar(50) |
yes |
|
dbo.[CONSTITUENT].[MIDDLENAME] |
| NICKNAME |
nvarchar(50) |
yes |
|
dbo.[CONSTITUENT].[NICKNAME] |
| KEYNAMEPREFIX |
nvarchar(50) |
yes |
|
dbo.[CONSTITUENT].[KEYNAMEPREFIX] |
| KEYNAME |
nvarchar(100) |
yes |
|
dbo.[CONSTITUENT].[KEYNAME] |
| SUFFIX |
nvarchar(100) |
yes |
|
dbo.[SUFFIXCODE].[DESCRIPTION] |
| WEBADDRESS |
UDT_WEBADDRESS |
|
|
dbo.[CONSTITUENT].[WEBADDRESS] |
| BIRTHDATE |
UDT_FUZZYDATE |
|
|
dbo.[CONSTITUENT].[BIRTHDATE] |
| AGE |
int |
yes |
|
dbo.[CONSTITUENT].[AGE] |
| CONSTITUENTINFORMATIONDIMID |
int |
yes |
|
Reference key to the constituent information dimension |
| MARITALSTATUS |
nvarchar(100) |
yes |
|
dbo.[MARITALSTATUSCODE].[DESCRIPTION] |
| ORIGINATION |
nvarchar(100) |
yes |
|
dbo.[INFOSOURCECODE].[DESCRIPTION] |
| GENDER |
nvarchar(10) |
yes |
|
dbo.[CONSTITUENT].[GENDER] |
| DONOTMAIL |
bit |
yes |
|
dbo.[CONSTITUENT].[DONOTMAIL] |
| GIVESANONYMOUSLY |
bit |
yes |
|
dbo.[CONSTITUENT].[GIVESANONYMOUSLY] |
| ISACTIVE |
bit |
yes |
|
case when dbo.[CONSTITUENT].[ISINACTIVE] is 1 then 0 else 1 end |
| ISCONSTITUENT |
bit |
yes |
|
dbo.[CONSTITUENT].[ISCONSTITUENT] |
| ISDECEASED |
bit |
yes |
|
A record for the constituent exists in dbo.[DECEASEDCONSTITUENT] |
| ISGROUP |
bit |
yes |
|
dbo.[CONSTITUENT].[ISGROUP] |
| ISORGANIZATION |
bit |
yes |
|
dbo.[CONSTITUENT].[ISORGANIZATION] |
| ISNETCOMMUNITYMEMBER |
bit |
yes |
|
dbo.[CONSTITUENT].[NETCOMMUNITYMEMBER] |
| ISACTIVEBOARDMEMBER |
bit |
yes |
|
Is currently a Board Member in dbo.[BOARDMEMBERDATERANGE] |
| ISACTIVECOMMITTEE |
bit |
yes |
|
Is currently a Fundraiser in dbo.[COMMITTEEDATERANGE] |
| ISACTIVEFUNDRAISER |
bit |
yes |
|
Is currently a Fundraiser in dbo.[FUNDRAISERDATERANGE] |
| ISACTIVEPROSPECT |
bit |
yes |
|
Is currently a Prospect in dbo.[PROSPECTDATERANGE] |
| ISACTIVESTAFF |
bit |
yes |
|
Is currently a Staff Member in dbo.[STAFFMEMBERDATERANGE] |
| ISACTIVESPONSOR |
bit |
yes |
|
Is currently a Sponsor in dbo.[SPONSORDATERANGE] |
| ISACTIVEVOLUNTEER |
bit |
yes |
|
Is currently a Volunteer in dbo.[VOLUNTEERDATERANGE] |
| CONSTITUENTADDRESSDIMID |
int |
yes |
|
Reference key to the constituent address dimension |
| PRIMARYADDRESS |
nvarchar(150) |
yes |
|
dbo.[ADDRESS].[ADDRESSBLOCK] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSCITY |
nvarchar(50) |
yes |
|
dbo.[ADDRESS].[CITY] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSSTATE |
nvarchar(100) |
yes |
|
dbo.[STATECODE].[DESCRIPTION] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSSTATEABBREVIATION |
nvarchar(50) |
yes |
|
dbo.[STATECODE].[ABBREVIATION] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSCOUNTRY |
nvarchar(100) |
yes |
|
dbo.[COUNTRYCODE].[DESCRIPTION] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSCOUNTRYABBREVIATION |
nvarchar(5) |
yes |
|
dbo.[COUNTRYCODE].[ABBREVIATION] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSPOSTCODE |
nvarchar(12) |
yes |
|
dbo.[ADDRESS].[POSTCODE] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSCART |
nvarchar(10) |
yes |
|
dbo.[ADDRESS].[CART] where [ISPRIMARY] = 1 |
| PRIMARYADDRESSDPC |
nvarchar(8) |
yes |
|
dbo.[ADDRESS].[DPC] where [ISPRIMARY] = 1 |
| PRIMARYADDRESSLOT |
nvarchar(5) |
yes |
|
dbo.[ADDRESS].[LOT] where [ISPRIMARY] = 1 |
| CONSTITUENTDEMOGRAPHICDIMID |
int |
yes |
|
Reference key to the constituent demographic dimension |
| RELIGION |
nvarchar(100) |
yes |
|
dbo.[RELIGIONCODE].[DESCRIPTION] |
| TARGET |
nvarchar(100) |
yes |
|
dbo.[TARGETCODE].[DESCRIPTION] |
| INCOME |
nvarchar(100) |
yes |
|
dbo.[INCOMECODE].[DESCRIPTION] |
| ISINCLUDED |
bit |
yes |
|
Flag indicating when data should be included in results. |
| SOURCEDIMID |
int |
yes |
|
Source system used. |
| ETLCONTROLID |
int |
yes |
|
ID generated through the ETL process. |
| CONSTITUENTADDRESSDETAILDIMID |
int |
yes |
|
Reference key to the constituent address detail dimension, derived using [dbo].[ADDRESSVALIDATIONUPDATE].[COUNTYCODEID], [dbo].[ADDRESSVALIDATIONUPDATE].[CONGRESSIONALDISTRICTCODEID], [dbo].[ADDRESSVALIDATIONUPDATE].[STATEHOUSEDISTRICTCODEID], [dbo].[ADDRESSVALIDATIONUPDATE].[LOCALPRECINCTCODEID], [dbo].[ADDRESSVALIDATIONUPDATE].[INFOSOURCECODEID], [dbo].[ADDRESSVALIDATIONUPDATE].[REGIONCODEID], and [dbo].[ADDRESSVALIDATIONUPDATE].[ORIGINCODE] |
| CONSTITUENTADDRESSFLAGDIMID |
int |
yes |
|
Reference key to the constituent address flag dimension, derived using [dbo].[ADDRESS].[DONOTMAIL],[dbo].[ADDRESS].[ISCONFIDENTIAL], and [dbo].[ADDRESS].[ISPRIMARY] |
| CONSTITUENTADDRESSTYPEDIMID |
int |
yes |
|
Reference key to the constituent address type dimension, derived using [dbo].[ADDRESS].[ADDRESSTYPECODEID] |
| ONLINECONSTITUENTSYSTEMID |
int |
yes |
|
Stores the Online System Id for the constituent in Blackbaud CRM. |
| ISINDIVIDUAL |
bit |
yes |
|
If the constituent is not a group or an organization then this will be true |
| ISHOUSEHOLD |
bit |
yes |
|
[dbo].[CONSTITUENTHOUSEHOLD].[ISHOUSEHOLD] |
| PROSPECTDIMID |
int |
yes |
|
Reference key to the prospect dimension, derived using [dbo].[CONSTITUENT].[ID] |
| SUFFIX2 |
nvarchar(100) |
yes |
|
[dbo].[SUFFIXCODE].[DESCRIPTION] |
| TITLE2 |
nvarchar(100) |
yes |
|
[dbo].[TITLECODE].[DESCRIPTION] |
| MAIDENNAME |
nvarchar(100) |
yes |
|
[dbo].[CONSTITUENT].[MAIDENNAME] |
| CONSTITUENTEMAILADDRESSDIMID |
int |
yes |
|
Reference key to the constituent email address dimension, derived from dbo.[EMAILADDRESS].[INFOSOURCECODEID] and dbo.[EMAILADDRESS].[ORIGINCODE] |
| PRIMARYEMAILADDRESS |
UDT_EMAILADDRESS |
yes |
|
dbo.[EMAILADDRESS].[EMAILADDRESS] |
| CONSTITUENTPHONEDIMID |
int |
yes |
|
Reference key to the constituent phone dimension, derived from dbo.[PHONE].[INFORSOURCECODEID] and dbo.[PHONE].[ORIGINCODE] |
| PRIMARYPHONE |
nvarchar(100) |
yes |
|
dbo.[PHONE].[NUMBER] |
| DONOTEMAIL |
bit |
yes |
|
dbo.[EMAILADDRESS].[DONOTEMAIL] |
| DONOTCALL |
bit |
yes |
|
dbo.[PHONE].[DONOTCALL] |
| ADDEDDATE |
datetime |
yes |
|
[dbo].[CONSTITUENT].[DATEADDED] |
| ADDEDDATEDIMID |
int |
yes |
|
Reference key to the date dimension, derived using [dbo].[CONSTITUENT].[DATEADDED] |
| CHANGEDDATE |
datetime |
yes |
|
[dbo].[CONSTITUENT].[DATECHANGED] |
| CHANGEDDATEDIMID |
int |
yes |
|
Reference key to the date dimension, derived using [dbo].[CONSTITUENT].[CHANGEDDATEDIMID] |
| PRIMARYADDRESSLINE1 |
nvarchar(150) |
yes |
|
First line in dbo.[ADDRESS].[ADDRESSBLOCK] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSLINE2 |
nvarchar(150) |
yes |
|
Second line in dbo.[ADDRESS].[ADDRESSBLOCK] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSLINE3 |
nvarchar(150) |
yes |
|
Third line in dbo.[ADDRESS].[ADDRESSBLOCK] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSLINE4 |
nvarchar(150) |
yes |
|
Forth line in dbo.[ADDRESS].[ADDRESSBLOCK] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| PRIMARYADDRESSLINE5 |
nvarchar(150) |
yes |
|
Fifth line in dbo.[ADDRESS].[ADDRESSBLOCK] where dbo.[ADDRESS].[ISPRIMARY] = 1 |
| SPOUSECONSTITUENTDIMID |
int |
yes |
|
Reference key to the Constituent dimension for the spouse record |
| PRIMARYBUSINESSCONSTITUENTDIMID |
int |
yes |
|
Reference key to the Constituent dimension for the primary business record |
| PRIMARYBUSINESSCONSTITUENTRELATIONSHIPEMPLOYMENTDIMID |
int |
yes |
|
Reference key to the Constituent Relationship Employment dimension for the primary business record |
| GENDERDESCRIPTION |
nvarchar(100) |
yes |
|
dbo.[GENDERCODE].[DESCRIPTION] |