MD_Prosp_RecruitmentPlusPKID

Unique identifier in Recruitment Plus. Mandatory in Recruitment Plus, so mandatory (without a default) in the data warehouse as well.

DW_RecruitmentPlusPKID

int

MD_Pers_Surname

Surname in the US means the same thing as "last name." Does not include suffixes like Jr., or honorifics.

This attribute is mandatory and has no default.

DW_Surname

nvarchar(32)

32

MD_Pers_MiddleName

Middle name or initial (if available). This field is fairly "dirty" and poorly maintained in some of our systems, especially in Recruitment Plus (not because our staff is doing a poor job, but because they sources from which they obtain their data are of uneven quality). This field should be avoided, therefore, if possible, particularly for prospective student data.

The default value is "missing".

DW_GivenName

nvarchar(32)

32

MD_Pers_Citizenship

A country name, not code. It is not guaranteed that all values for this attribute will be consistent across all dimensions. For example, citizenship for a prospective student in MD_Prospect (populated from Recruitment Plus) may be "Czek Republic," while citizenship for that same person in MD_DegreeStudent may be "Czekoslovakia". It would be nice to have these values all be consistent across the campus, but doing so would constitute a project in and of itself. Defaults to 'Unknown'.

DW_Addr_Country

nvarchar(64)

64

Slate_Prosp_BirthDate

<None>

datetime

MD_Pers_HomeAddrCity

City of home address. E.g., if my home address is 203 Woodley Street West, Northfield, MN 55057 USA, then my city is 'Northfield'. If my home address is unavailable, and all we have is a mailing address, we use the mailing address (in preference to nothing at all) because, presumably, I receive mail somewhere near where I live, or in a place I spend a lot of time.

Note that this field often contains extra information if the city is outside the US, so for non-US cities YMMV, i.e., you may not get the results you're looking for. For example, the postal code for foreign addresses may be coded after the city. The country may also be tacked on.

This field is therefore not suitable for serious analysis.

DW_Addr_City

nvarchar(32)

32

MD_Pers_HomeAddr5DigitUSZip

5-digit US zipcode. Five digits (and not nine) because this attribute is intended for aggregate spatial analysis, i.e., for grouping people and things into geographical units. It's not meant to identify individual people or addresses, which is not something one typically does in a data warehouse. If you need details about where someone lives, exactly, use Microsoft Access to join data here to data in the appropriate transactional system (e.g., Recruitment Plus, Colleague, or Advance). Defaults to '0000'.

DW_Addr_5DigitUSZipcode

char(5)

5

MD_Pers_HomeAddrUSRegionCarleton

Contains the US region (e.g., "Middle States" or "New England"). Defaults to "Unknown." Used for various Carleton reports. Compare the USRegionFIPS, which is also used for various reports. Everyone is encouraged to use one of these two systems, if at all possible - for consistency's sake.

DW_USRegion

varchar(64)

64

MD_Pers_HomeAddrLatitude

Latitude of home address, as a floating point number. Best populated for US addresses, and populated in a very coarse fashion for those (using the zipcode) before late summer 2011.

Warning: Note that there is no default, and the value here may be NULL.

Values for this attribute from before late summer 2011 are not truly fit for user consumption, except by special request, and should not (yet) be exposed via views.

Note that the longitude and latitude for a given address may change over time, without any change in the address, because we occasionally change geocoding providers. The geocoding providers are also continually improving their algorithms. If a longitude or latitude changes, without any address change, a new row is generated in the data warehouse. But history is not rewritten. The earlier, presumably less accurate figure remains in non-current rows.

<None>

float

MD_Pers_HomeAddrNonUSProvince

DW_Addr_StateOrProvince

nvarchar(64)

64

Slate_Prosp_AlienStatus

DW_CodeDesc

varchar(32)

32

MD_Pers_IsOfUnknownRaceEthnicity

"Yes" if neither the race nor ethnicity is known. "No" otherwise. Calculated from Race and IsHispanicOrLatino, as well as from OLDPrimaryRaceList. If the old race list is empty or 'Unknown' and if Race is 'Unknown', and IsHispanicOrLatino = 'Unknown' or 'No', then this attribute will be 'No'. It basically means we don't know enough to classify someone by ethnicity or race, in either the old or the new 2010 census system.

DW_YesOrNoNOTNULL

char(3)

3

MD_Pers_Race

Holds the person's race. This attribute can be treated as "primary race" although the way this information is collected makes the two not entirely equivalent, since in some cases Carleton may choose to collect fuller information on people of multiple races.

This attribute (as opposed to MD_Pers_OldPrimaryRace) does not tell us whether a person is of Hispanic, Latino, or Spanish origin.

Note that in the new ethnicity/race classification system (2010 census and beyond), multi-racial people cannot indicate what races they belong to. They are simply multi-racial. Also, Hispanic is not considered a race, as noted. It's an ethnicity.

Hence this attribute will always have a single value, one of:

African American
American Indian-Alaska Native
Asian
Hawaiian-Pacific Islander
Two or More Races
Unknown
White

This attribute is not populated before 2009, and is unevently populated until 2011 (as of 2011, employees have not been fully resurveyed).

Note that some systems store only one race/ethnicity. Others do not. The data here will therefore be of uneven completeness and consistency. When reporting, stick with a single system of record, or a single constituency (like 'prospective students') and you'll be fine. Otherwise, be extremely careful, and be sure to work with people who administer the source systems who can advise you.

DW_RaceForCensus2010

varchar(64)

64

MD_Pers_OLDPrimaryRace

Until the census-2010 race/ethnicity system is fully implemented, this is our main "race" attribute. It should eventually become, hence the designation "old." It will still be populated, however, even after we have converted to the new census 2010 system, so that we can report easily across multiple years, both before and after the transition. And it is the only attribute you can reliably use to report on ethnicity data, longitudinally, before and after the transition (which began in late 2009).

Possible values of this attribute include:

Asian
Black or African American
Hispanic
Mixed race or other
Native American-Alaskan Native
Unknown
White

The primary race is not necessarily selected by the person reflected in the data. For students it is inferred using a 'trumping' system that's complex and that most folks don't understand fully (e.g., if you're white and African American, the latter trumps the former and becomes your primary ethnicity). In this old system, 'Hispanic' was considered a race. Or, conversely, everything was an ethnicity. In the new 2010 census system, 'Hispanic' is an ethnicity, and most everything else ('Asian', 'White', etc.) is a race.

When reporting, stick with a single system of record, or a single constituency (like prospective students or degree students) and you'll be fine. Otherwise, be extremely careful, and be sure to work with people who administer the source systems who can advise you.

Note also that, as per institutional standards, ethnicities like 'Unknown' are not automatically counted as white, as sometimes happens in reports generated, e.g., by Student Financial Services and other offices.

DW_CodeDescLong

varchar(64)

64

MD_Pers_IsVeteran

"Yes" if a person is a veteran by federal government reporting standards, particularly EOE, for employees. "No" otherwise. Although the database also allows "Unknown" here, this value is not used.

Veteran status is available in Recruitment Plus for prospective students, although not well maintained. Veteran status is maintained in Colleague, for students, but we rarely have anything to put in this field, since so few of our students are veterans (e.g., eight as of March 2009).

DW_YesNoOrUnknown

varchar(7)

7

MD_Pers_Disability2

DW_Disability

varchar(32)

32

MD_Pers_DisabilityList

Comma-separated list of all disabilities. Feds will be asking us to track this for staff and students.

This attribute is currently, as of 2009, not populated.

DW_CommaDelimitedList

varchar(128)

128

MD_Prosp_StatusCategory

Generalized version of prospect status. Coalesces detailed states into broad categories. E.g., students who have applied, been waitlisted, etc. are all listed as 'applied.' Generally useful for broad counts, yield calculations, and so on. Typically far more useful than Status. Every prospect will have both a Status and a StatusCategory (with no blank or 'Unknown' values).

DW_CodeDesc

varchar(32)

32

MD_Prosp_LegacyType

Prospects often have family members that attended Carleton, or possess other characteristics that need to be taken into account in the admissions process. This attribute records these characteristics. Possible values include: Alumnus/a, First Generation Student, Parent(s), and Sibling(s). In Recruitment Plus, the value here may also be NULL, which we translate here to "None".

The value of this attribute may change over time in Recruitment Plus. It is actively updated. It is hence treated as a type-II attribute here.

DW_CodeDesc

varchar(32)

32

MD_Prosp_IsNationalMeritScholar

This data is kept by Student Financial Services, not Admissions (oddly). The data therefore will come from Colleague eventually. For now we load it from a custom field in Recruitment Plus (DataStuCust), but the data there is not entirely reliable.

This field is either "Yes" or "No" at the moment. Do not rely on this field for reporting, though.

DW_YesNoOrUnknown

varchar(7)

7

MD_Prosp_AnticipatedMajor

Carleton major that an applicant indicates he or she is interested in. Here we restrict our majors to those with the category of 'Carleton Majors' in DataStuAppAcad in Recruitment Plus. This attribute is single-valued (even if there are methods by which an applicant can specify multiple majors). Defaults to 'Unknown' if the prospect has not applied. If the prospect has applied, but listed no major, this attribute will have the value 'Unspecified'.

DW_CodeDescLong

varchar(64)

64

MD_Prosp_HomeRPlusGeomarket

The full name of the area where the applicant lives. This attribute contains values more useful for outreach and marketing than traditional zipcodes (which are too small) and counties (which aren't appropriately situated and vary widely and arbitrarily in size from state to state). Defaults to 'Unknown'. The value here corresponds to DataStu.HomeGeomDescr in Recruitment Plus.

DW_RPlus_Geomarket

nvarchar(64)

64

MD_Prosp_ProjectedEntryTermYear

Year of term that a prospect is projected (or has applied) for entrance. Calculated from the term. The term is a mandatory field in Recruitment Plus, so this field can be mandatory in the warehouse as well. We have a default of 9999, though, just in case. 9999 should be taken as equivalent to "Unknown."

DW_YearAtCarleton

int

MD_Prosp_IntendsAidApp

If a student has indicated that he/she will apply for aid, this field will be 'Yes.' If not, 'No.' If the student hasn't indicated anything with respect to the financial aid application, or if this information is otherwise unavailable, the value here will be 'Unknown'.

This information is taken from the DataStuAppFaid view in Recruitment Plus, and goes along with data in DataStuAppAcad—although the linkage in Recruitment Plus is by student, not by application. This could theoretically lead to inconsistencies if multiple applications are submitted, but in practice this is unlikely. More common are inconsistencies between the group of students who indicate intent to apply for aid and the group who actually does apply.

Compare the HasFinanciaAidNeed attribute, which is based on actual financial aid information received through the federal government (ISIR), the College Board, or other source - and not just from the student's application.

DW_YesNoOrUnknown

varchar(7)

7

MD_Prosp_PrimaryRecruitedSport

Name of main/primary sport for which a prospective student was recruited, i.e., the sport for which a prospective student received the highest PEAR ranking, if that ranking is higher than 3 (we don't consider prospects rated 3 or lower to have been actively recruited for a given sport). If the highest rating was received for more than one sport, we pick one of the sports randomly. This is the definition of "recruited athlete" we also use for the College Sports Project study.

The sport is taken from DataStuIntr in Recruitment Plus, specifically the 'Interest' field (where InterestCat = 'Athletics'). The rating is taken from the first digit of the IntrComment field in the DataStuIntr view in Recruitment Plus (again, where InterestCat = 'Athletics'). Coaches sometimes give out '+' and "range" ratings (e.g., 1+ or 2-3), but we take here only the first digit for simplicity's sake.

For reporting to NCAA, CSP, etc., the sport names will need to be converted to NCAA codes. The sport listed here may or may not correspond to a Carleton varsity sport, although it usually will.

DW_CodeDesc

varchar(32)

32