ViewReference_6

VMF_TopSATScores

ViewReference_11

VMF_AwardAmountSnapshot

ViewReference_15

VMF_AwardAmountCurrent

ViewReference_34

VMF_Yield

ViewReference_38

VMF_StudentCumulativeStats

ViewReference_95

VMF_SurveyAdminFact

MD_Prospect

VMD_Prospect

VMD_PROSPECT

View

ViewsProspect

VMD_Prospect

VMD_PROSPECT

View

ViewsProspect

ViewsProspectDiagram

SELECT,VIEW DEFINITION

FinancialAidReader

SELECT,VIEW DEFINITION

ProspectViewReader

SELECT,VIEW DEFINITION

StudentReader

SELECT,VIEW DEFINITION

SurveyReader

MD_Prosp_RecruitmentPlusPKID

int

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

MD_Pers_CommonName

nvarchar(64)

Full legal name, with middle name or initial. Does not include honorifics unless these are part of the legal name and are available in the system from which the data is derived. This field is mandatory and has no default.

Note that this field is 'calculated' for prospective students (i.e., put together out of the given name, middle name, and surname). Because the middle name is not well maintained for prospective students, the common name will be "dirty" as well. It is recommended that this field be avoided, therefore, for prospective students. Also, note: If you believe names are needed for prospective students, then consider also whether the report you are creating should be generated off of Recruitment Plus, and not the data warehouse.

Common names in Colleague should be very clean, and as a result common names in the data warehouse for students, faculty, and staff may be relied upon. If you need names for a report, however, consider reporting directly off of Colleague. Typically the data warehouse is best for aggregate analysis, and if you are using individual names, you may find other sources of information to be better.

Common names in Advance are also relatively clean. Hence also are common names in the warehouse for alumni and other constituencies maintained by External Relations.

MD_Pers_Surname

nvarchar(32)

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.

MD_Pers_Gender

varchar(7)

Must be Male, Female, or Unknown.

MD_Pers_AgeRange

varchar(12)

Age of person, discretized into a set of ranges useful for reporting and demographic classification. If age is unknown, the value of this attribute will be "Unknown."

Note that the age is calculated from the birth date, and does not take death into account. The age range, therefore, indicates the age if the person were alive (and in most cases they are).

MD_Pers_HomeAddrUSState

varchar(24)

State of home address. E.g., if my home address is 203 Woodley Street West, Northfield, MN 55057 USA, then my state is 'Minnesota'. If I live outside the US, this attribute will be 'Not Applicable'. If no home address is available for me, the value will be 'Unknown'. 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 where I spend a lot of time.

MD_Pers_HomeAddrUSRegionFIPS

varchar(64)

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

FIPS codes, in general, are a set of standardized numeric or alphabetic codes issued by the US National Institute of Standards and Technology (NIST) to ensure uniform identification of geographic entities throughout all federal government agencies.

MD_Pers_HomeAddrCountry

nvarchar(64)

DO NOT USE THIS FIELD FOR SERIOUS REPORTING. The data is entered for operational/addressing purposes, and is not of sufficiently high quality to work for analysis.

In Colleague, at least as of 2011, international addresses are not handled well, and therefore the country may not be well or correctly populated. Colleague, for example (again, as of 2011), has no place for a foreign postal code, so often people place that, along with the country, in the space that's supposed to house the city. This makes things print correctly, but it means the country will not be populated. And since the data warehouse defaults to 'USA' if this field is blank (which is generally appropriate, across most systems), this means that many international addresses end up in the warehouse with a country of the United States.

In general, therefore, it is wise to use MD_Pers_Citizenship instead of MD_Pers_HomeAddrCountry, although the challenge there is that citizenship is designated in different ways across systems (not necessarily with consistent ISO country names or codes).

Note that country data for 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.

MD_Pers_HomeAddrLongitude

float

Longitude of home address, as a floating point number (negative = west longitude). 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.

MD_Pers_IsNonResidentAlien

varchar(7)

"Yes" if the person in question is not a citizen or national of the United States and who is in the United States on a visa or temporary basis and does not have the right to remain indefinitely. "No" otherwise.

IPEDS does not require us to report the ethnicity/race of nonresident aliens, so (although we may collect race/ethnicity and alien status independently) when reporting to IPEDS, "Non-Resident Alien" becomes like a race, i.e., you're white, black, etc., or a nonresident alien. To do IPEDS reporting, most people-based views in the data warehouse have an IPEDS ethnicity attribute that includes nonresident alien as a race. Use that attribute for IPEDS reporting.

Use this attribute if you simply want to divide a population up into two piles, one for nonresident alieans and another for other people.

MD_Pers_IsHispanicOrLatino

varchar(7)

Census requires that we ask first if a person is Hispanic, Latino, or of Spanish origin. This is the 'ethnicity' question. We may then ask a 'race' question. If the person in question lists Hispanic ethicity (or in the 'old' pre-2010 system) lists Hispanic as their 'race' then this attribute will be 'Yes'. If not, then 'no'. If no information is available, then 'Unknown'.

Note that some systems (e.g., Colleague) store only one race/ethnicity. Recall also that before 2010 Hispanic was considered a race. Hence if someone considers themselves primarily African American, but secondarily Hispanic, the Hispanic ethnicity will not be stored in Colleague (at least before the 2010 census standards kicked in). And as a result, this attribute will, incorrectly, be registered as 'No' in some cases.

MD_Pers_OLDPrimaryRace

varchar(64)

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.

MD_PERS_IPEDSETHNICITY

MD_Prosp_StudentType

varchar(16)

Type of student, i.e., Transfer, Freshman, Non-Degree, International, for most recent application. This is not the same as the 'student status' value in Colleague, although some values here look similar. This field is geared towards helping us determine what deadlines, workflows, and processes should apply to a given student (which will differ depending on whether they're looking to transfer, become a freshman, etc.).

MD_Prosp_Status

varchar(64)

Detailed characterization of student's status. E.g., someone who's general status category is 'Applied' may have applied, been waitlisted, etc. This field breaks out all these detailed states. Typically less useful than StatusCategory. Every prospect will have both a Status and a StatusCategory (with no blank or 'Unknown' values). From R+ DataStu view.

MD_Prosp_HasBeenAdmittedOrWill

varchar(7)

"Yes" if a student has been admitted (i.e., has a status category of Admit, Deposit, or 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 Admit, Deposit, or 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 HasBeenAdmittedOrWill, 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_LegacyType

varchar(32)

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.

MD_Prosp_IsFirstGenerationCollege

varchar(7)

MD_Prosp_AnticipatedMajor2

varchar(64)

If a student indicates interest in multiple majors on his/her application, we place the second one here. We do not record the third (or others). The second is actually recorded in DataStuAppAcad, in the PreProfProg field. 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_ProjectedEntryTerm

char(7)

Term that a prospect is projected (or has applied) for entrance. This is a mandatory field in Recruitment Plus, so we make it mandatory in the warehouse. If unknown, defaults to 'Unknown.'

Format is YY/TERM (e.g., 08/FA), as in Colleague.

MD_Prosp_BestSATScoreRange

varchar(16)

Discretized three-part SAT score. If that sounds like jargon, then try this: SAT score aggregated into a range like "1850-1899." Note that we do not store self-reported SAT scores (where the 'Source' in Recruitment Plus is 'Student'). If the student took the SAT in question before it had three parts, this attribute will be set to 'Not Applicable'. Otherwise it defaults to 'Unknown'.

MD_Prosp_BestSATMathRange

char(7)

Best overall math section score for SAT I test, renormalized to fit a set of ranges (i.e., discretized score). Note that we don't store self-reported SAT scores. Defaults (if information is missing) to 'Unknown'.

MD_Prosp_BestSATCriticalReadingRange

char(7)

Best critical reading section score for SAT test, renormalized to fit a twelve-range (50-point chunk) scale. The critical reading test succeeded the old "verbal" section in 2006.

MD_Prosp_BestTOEFLiBTScoreRange

varchar(12)

Discretized TOEFL Internet-Based Test of English as a Foreign Language. I.e., this attribute holds a range (e.g., 110-120) instead of a composite TOEFL iBT score (like 112). 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_BestTOEFLiBTListeningRange

varchar(12)

Discretized TOEFL Internet-Based Test of English as a Foreign Language listening component score. I.e., this attribute holds a range (like 27-30) instead of an exact TOEFL iBT listening 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'.