Dict-Physical

Table MD_Prospect

Card of table MD_Prospect

Name	MD_Prospect
Dimensional Type	Dimension
Comment	This dimension contains everyone registered in Carleton College's Admissions database. Generically speaking, these people are 'prospects,' although properly speaking the term 'prospect' (to admissions officers) refers to people who have had significant contact with us, but have not (yet) formally applied. Whether someone is a prospect, applicant, admit, matriculant, etc. may be gleaned from the StatusCategory and Status attributes. Caution: This is a "dimension," and not a spreadsheet or report. It is not designed to be used as an isolated repository of information on prospects. If you are trying to use this dimension in isolation, you probably won't get the results you are looking for - and you may end up with more data than you can reasonably manipulate on a standard desktop computer. If what you want is a big prospective-student spreadsheet, then check with Admissions. They can either give you a data dump, or they can give you access to the Recruitment Plus DataStu view. In general, when using this dimension in reports, note that comparisons and statistics we gather often group prospects by ProjectedEntryTerm or ProjectedEntryTermYear, so in most cases you will be grouping on these attributes - or filtering out only those with specific values. Note also that some attributes on this dimension are potentially multi-valued (e.g., SAT score range). In such cases, we select one value. E.g., in the case of SAT score ranges, we take the best one. For High School we take the one from which the student received a diploma (or the most recently attended one, if he or she received no diploma, or multiple diplomas). As with all 'people' dimensions, this dimension is heavy with type II SCDs, and to use it effectively one must select an as-of date or filter on isCurrent = Yes. BI tools should have a handy 'current' filter that's ON by default, so people get current rows by default, and not historical ones. Prospects may be deleted and merged as part of Admissions's de-duplication process. Those deletions are reflected in this dimension table, with the restriction that deletions will only occur here for students whose (theoretical) entry term has not yet passed. We assume that, by the time the entry term has passed, we've eliminated duplicates. After the entry term has passed, we delete nobody from the data warehouse - although Admissions may well delete such prospects from their system. Generally deletions from dimension tables are difficult to automate in a data warehouse environment, because of referential integrity issues. But we do them anyway here, where possible, because otherwise we end up with duplicates, as noted. Note that AuditKey and Timestamp attributes are for internal use only, and should not be used in reports. Ditto for the SurrogateKey.

Check constraint name of the table MD_Prospect

CKT_MD_PROSPECT

List of incoming references of the table MD_Prospect

Relationship_3

MF_ProspectActivity

MD_Prosp_SurrogateKey

Relationship_7

MF_ReaderRating

MD_Prosp_SurrogateKey

Relationship_9

MF_TopSATScores

MD_Prosp_SurrogateKey

Relationship_14

MF_Rate

MD_Prosp_SurrogateKey

Relationship_16

MF_AwardAmount

MD_Prosp_SurrogateKey

Relationship_24

MF_Yield

MD_Prosp_SurrogateKey

Relationship_27

MF_StudentCumulativeStats

MD_Prosp_SurrogateKey

Relationship_36

MF_PerYearAidFacts

MD_Prosp_SurrogateKey

Relationship_85

MF_SurveyCellData

MD_Prosp_SurrogateKey

Relationship_88

MF_SurveyAdminFact

MD_Prosp_SurrogateKey

List of columns of the table MD_Prospect

MD_Prosp_SurrogateKey

<None>

numeric

MD_Pers_AlienStatus

As opposed to MD_Pers_IsNonResidentAlien, which simply classifies a person as a nonresident alien or not (and is normally more than sufficient for reporting), this field houses detailed alien status information.

As of 2011, this field is not populated in the data warehouse, and is not even implemented in the physical data warehouse tables. It will probably need to be added and populated at some point.

DW_CodeDesc

varchar(32)

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_Prosp_SlateID

GUID (36-char/16-byte) number uniquely identifying pretty much everything in Slate/Technolutions systems.

<None>

uniqueidentifier

MD_Pers_HomeAddrNonUSProvince

DW_Addr_StateOrProvince

nvarchar(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)

MD_Pers_Disability1

First of max 3 possible disabilities we track. Used for EOE purposes. Feds will be asking us to track this for students. Possibly needed for alumni and admissions statistics/reporting as well.

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

DW_Disability

varchar(32)

MD_Pers_Disability2

DW_Disability

varchar(32)

MD_Pers_Disability3

DW_Disability

varchar(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_Pers_EnterpriseUniqueID

Unique "person" ID assigned to everyone connected with Carleton. Maintained by the identity management system. Should be mandatory, but we cannot make it so until the IdM system is complete and fully functioning - and we can call out to it in our ETL code.

This attribute is currently not populated.

DW_EnterpriseUniqueID

char(8)

MD_Pers_CommonName

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.

DW_CommonName

nvarchar(64)

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)

MD_Pers_GivenName

Given name in the US typically means the same thing as "first name." Default value is "missing".

DW_GivenName

nvarchar(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)

MD_Pers_Gender

Must be Male, Female, or Unknown.

DW_Gender

varchar(7)

MD_Pers_PrimaryAffiliation

Main relationship to Carleton. Values should be taken from the identity management system, if available. Defaults to 'Unknown'. Values are taken from a controlled vocabulary including: Student, Faculty, Staff, Alum, Parent, Friend.

Where populated, this attribute is an excellent measure of general affiliation. It is not, however, populated for all 'people' dimensions.

DW_CarlAffiliation

varchar(16)

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)

MD_Pers_AgeRange

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

DW_AgeRange

varchar(12)

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)

MD_Pers_HomeAddrUSState

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.

DW_Addr_USState

varchar(24)

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)

MD_Pers_HomeAddrUSRegionFIPS

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.

DW_USRegion

varchar(64)

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)

MD_Pers_HomeAddrCountry

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.

DW_Addr_Country

nvarchar(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_HomeAddrLongitude

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.

<None>

float

MD_Pers_HomeAddrMilesFromCampus

Distance in miles from Carleton main campus. Populated using the home address latitude and longitude, and only for US addresses with a zipcode. The value here has poor resolution and should not be used except for very general groupings (nothing with a resolution less than twenty miles). Eventually we may license a geocoding service, and populate this attribute well. Until then, YMMV.

This attribute is not 'fit' for user consumption, except by special request, and should not (yet) be exposed via views.

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

<None>

float

MD_Pers_IsNonResidentAlien

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

DW_YesNoOrUnknown

varchar(7)

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)

MD_Pers_IsHispanicOrLatino

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.

DW_YesNoOrUnknown

varchar(7)

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)

MD_Pers_RaceList

Comma-separated list of all races a person claims to belong to, ordered (by preference? alphabetically? by an obscure "trumping" system we concoct?).

Use this attribute as a filter, to select out people by racial affiliation (e.g., WHERE MD_Pers_OLDRaceList LIKE '%African%').

Note that this attribute is populated using information that the US government doesn't require us to collect. According to 2010 census standards, a multi-racial person is just multi-racial (i.e., "multi-racial" is their race). No further information is collected. Also, Hispanic is not a race.

This attribute is currently not populated, and will not be until Carleton implements the 2010 census ethnicity/race classification system across its enterprise systems.

DW_CommaDelimitedList

varchar(128)

128

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)

MD_Pers_OLDRaceList

Comma-separated list of races a person claims to belong to, e.g., "Asian, White". Follows the order of races provided by the source system, which may or may not be ordered. Defaults to the empty string. Note that this attribute will still be populated, even after we have converted to the new census-2010 race/ethnicity system, so that we can report easily across multiple years, both before and after the transition.

Use this attribute as a filter, to select out people by racial affiliation (e.g., WHERE MD_Pers_OLDRaceList LIKE '%African%').

This attribute uses the "old" coding standards where the primary race is determined by a "trumping" system we utilize (it's not based entirely on what the person tells us). It also treats Hispanic as a race, which the 2010 census standards have changed (Hispanic is now an ethnicity; a Hispanic person can be of any race).

DW_CommaDelimitedList

varchar(128)

128

MD_Prosp_StudentType

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

DW_CodeDescShort

varchar(16)

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)

MD_Prosp_Status

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.

DW_CodeDescLong

varchar(64)

MD_Prosp_HasAppliedOrWill

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

DW_YesNoOrUnknown

varchar(7)

MD_Prosp_HasBeenAdmittedOrWill

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

DW_YesNoOrUnknown

varchar(7)

MD_Prosp_HasMatriculatedOrWill

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

DW_YesNoOrUnknown

varchar(7)

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)

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)

MD_Prosp_IsFirstGenerationCollege

DW_YesNoOrUnknown

varchar(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)

MD_Prosp_AnticipatedMajor2

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

DW_CodeDescLong

varchar(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)

MD_Prosp_ProjectedEntryTerm

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.

DW_TermShortName

char(7)

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_BestSATScoreRange

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

DW_SATCompositeScoreRange

varchar(16)

1 2

Home