MD_Pers_Race

varchar(64)

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.

MD_PERS_OLDIPEDSETHNICITY

Like MD_Pers_OLDPrimaryRace, but with alien status marked as a race. This is a standard IPEDS reporting (as opposed to "collection") category.

MD_PERS_CROSSWALKEDIPEDSETHNICITY

Uses new Census 2010 race/ethnicity categories if available, but otherwise maps old categories to the new system. Note that a racial status of Pacific Islander cannot be reconstructed from pre-2010 race/ethnicity information. All such people will appear as Asian.

MD_Prosp_StatusCategory

varchar(32)

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

MD_Prosp_HasAppliedOrWill

varchar(7)

"Yes" if a student has applied, "no" if not and the application deadline for the student's EntryTermYear has passed. "Unknown" if the student has not applied, and the application deadline for the student's EntryTermYear has not passed. Note that the definition of having applied is taken directly from Recruitment Plus, which considers people who have initiated the application process (but whose applications are not complete) as applicants.

HasAppliedOrWill - i.e., this attribute - IS A TYPE 1 SCD ATTRIBUTE, meaning that although the MD_Prospect dimension keeps history (i.e., it records past student states), for this attribute it does not keep history. Every row for a given student gets updated with the same value of HasAppliedOrWill, in other words - even past 'old' rows for a student who applies.

Why do we do this? So that we can examine old information on students years before they apply, and then do predictive analysis to see what factors predict future applications. We can then use this data to examine current search suspects and prospects to see what we need to do to get them to apply.

Ask the DW team if this is unclear.

MD_Prosp_HasMatriculatedOrWill

varchar(7)

"Yes" if a student has matriculated (which we define here as having a status category of Matriculant). "No" if either 1) a student has not applied and the application deadline has passed for his/her EntryTermYear, or 2) a student has applied but has a status category that is not Matriculant and a status that is not "Wait Listed", and the decision deadline has passed. Otherwise the value here is "Unknown".

This attribute - IS A TYPE 1 SCD ATTRIBUTE, meaning that although the MD_Prospect dimension keeps history (i.e., it records past student states), for this attribute it does not keep history. Every row for a given student gets updated with the same value of HasMariculatedOrWill, in other words - even past 'old' rows for a student who applies.

Why do we do this? So that we can examine old information on students years before they apply and get a decision, and then do predictive analysis to see what factors predict future admits (or denies). We can then use this data to examine current search suspects and prospects to see what factors are most strongly tied to our 'admit' numbers, or help us prioritize resources to optimize for admits (rather than, say, just applieds).

Ask the DW team if this is unclear.

MD_Prosp_IsNationalMeritScholar

varchar(7)

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.

MD_Prosp_AnticipatedMajor

varchar(64)

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

MD_Prosp_HomeRPlusGeomarket

nvarchar(64)

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.

MD_Prosp_ProjectedEntryTermYear

int

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

MD_Prosp_BestMathVerbalScoreRange

char(9)

Best Math + Verbal (or Critical Reading) score. Note that this attribute is populated for both two-part and three-part SAT tests. Defaults, in the absence of test information, to 'Unknown'.

MD_Prosp_BestSATWritingRange

char(7)

Best overall writing score for SAT I exam, discretized in the sense of being aggregated into a range of scores, like 750-800. Note that we don't store self-reported scores. Defaults to 'Unknown' (which frequently happens in cases when a given SAT test was administered before the Writing component was introduced).

MD_Prosp_BestACTScoreRange

char(7)

Discretized ACT composite score. I.e., ACT score that's been put into a range (e.g., 26-30). This is more useful as a label on a report or graph than a raw ACT score. Defaults (if no ACT score is available) to 'Unknown'.

MD_Prosp_BestTOEFLiBTReadingRange

varchar(12)

Discretized TOEFL Internet-Based Test of English as a Foreign Language reading score. I.e., this attribute holds a range (like 27-30) instead of an exact TOEFL iBT reading score (e.g., 28). Ranges are easier to use than raw scores as labels on graphs or reports. Defaults, if no TOEFL information is available, to 'Unknown'.

MD_Prosp_BestTOEFLiBTSpeakingRange

varchar(12)

Discretized TOEFL Internet-Based Test of English as a Foreign Language speaking component score. I.e., this attribute holds a range (like 27-30) instead of an exact TOEFL iBT speaking score (e.g., 28). Ranges are easier to use than raw scores as labels on graphs or reports. Defaults, if no TOEFL information is available, to 'Unknown'.

MD_Prosp_HighSchoolCluster

varchar(7)

High School cluster assigned by the College Board. Not used at Carleton as of 2008, but we expect this to change.

Used to provide aggregate stats on similar (grouped) schools. To get these groupings, one must buy Descriptor PLUS from the College Board. The College Board allocates 5 characters max for the cluster; we allocate 7 so we can include "Unknown."

MD_Prosp_HighSchoolCEEBCode

varchar(7)

College Board unique identifier for prospect's high school. If more than one was attended, we take the one marked 'Active' by Admissions. If multiple are marked Active, we take the one from which a diploma was received. If diplomas were received from more than one HS (or no diploma was received), take the most recent of these. Otherwise, pick randomly (this actually should not happen; typically there is one Active High School). Taken from the DataStuHschOrg view in Recruitment Plus.

If the CEEB code is unknown, it is marked as 'Unknown.'

MD_Prosp_HighSchool5DigitUSZip

char(5)

MD_Prosp_HighSchoolCountry

nvarchar(64)

Full name (not code) for country. Uses Colleague country naming conventions. 'Unknown' if the country is not available.

Note that country data for high schools of prospective students, especially ones who are only 'suspects' (early in the admissions cycle - not applicants or admits) may not be wholly reliable, because we obtain their addresses from third parties who don't consistently mark the country. This will be true especially for high schools not in the US.

MD_Prosp_HighSchoolRPlusGeomarket

nvarchar(64)

Full name of geomarket in which prospective student's high school is located. Not the code.

MD_Prosp_ApplicationRequirementRule

varchar(32)

Specifies what set of requirements are applied to the application. Example values: Regular Decision, Transfer, Winter Early Decision.

MD_Prosp_HasFinancialAidNeedNextYear

varchar(7)

"Yes" if a student has financial aid need for the next academic year (for the upcoming fall, if before October 1; otherwise, for the academic year beginning the next fall). "No" if not. "Unknown" if we don't know (yet). It is typically used when generating statistics in the spring and early summer on students whose projected entry term is the upcoming fall. Use it for other purposes at your own peril.

By "next year" (in HasFinancialAidNextYear) we mean the upcoming academic year, beginning in September.

This attribute is populated with information received from the federal government, College Board, etc.

This attribute gets updated continuously with a "Yes" or "No" value, even after a student has matriculated—although it is only typically used for incoming students during the spring before their projected entry term in the fall (applicants, matriculants, etc.). Its value will revert to "Unknown" when the applicant is no longer in the applicant file in Colleague (or was never there in the first place), or his/her applicant information is no longer pushed from Colleague to the ODS.

This attribute was only added to the data warehouse on May 10, 2009. All rows added to the warehouse before this date will be "Unknown."

To look more closely at need numbers for given students across various terms and years, do not use this attribute. Use the Student Financial Services cubes/data marts.

MD_Prosp_PrimaryRecruitedSport

varchar(32)

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.

MD_Prosp_AvgReaderRatingAcademic

varchar(7)

Average Academic rating across all readers rounded to the nearest integer 1-9. Taken from Recruitment Plus DataStuScor table; corresponds to subscore 1 for Reader Rating. If nobody has read the prospective student's application, then the value here will be "Unknown." Note that this attribute is meant to be used as a label on a graph, or as a way of classifying students into groups (those with a reader academic rating of X, those with a rating of Y, etc.). For exact summing and averaging, consult Recruitment Plus directly or ask the data warehouse team if an appropriate fact table is available.

MD_Prosp_AvgReaderRatingOverall

varchar(12)

Total/aggregate reader rating score. Calculated from Academic and Personal score. Averaged across all total Reader Rating scores, across all readers (0 to 3 readers), then discretized into ranges, 1-2, 3-4, etc. If nobody has read the prospective student's application, then the value here will be "Unknown." Note that this attribute is meant to be used as a label on a graph, or as a way of classifying students into groups (those with a rating of X, those with a rating of Y, etc.). For exact summing and averaging, consult Recruitment Plus directly or ask the data warehouse team if an appropriate fact table is available.