DStuEnrolledForRegularTerm

MF_SectionEnrollmentByTerm

MD_CollPers_SurrogateKey

Relationship_17

MF_AwardAmount

MD_CollPers_SurrogateKey

Relationship_37

MF_ReaderRating

MD_CollPers_SurrogateKey

Relationship_85

MF_SurveyCellData

MD_CollPers_SurrogateKeyDStu

Relationship_91

MF_MajorCount

MD_CollPers_SurrogateKey

Relationship_145

MF_RoomAssignment

MD_CollPers_SurrogateKeyDStu

Relationship_171

MF_AlumEventParticipation

MD_CollPers_SurrogateKeyDStu

Relationship_173

MF_AlumActivityParticipation

MD_CollPers_SurrogateKeyDStu

Relationship_235

MF_CardTransaction

MD_CollPers_SurrogateKeyEmp

VMD_StuClassYearLevel

VMD_STUCLASSYEARLEVEL

VW_GradesByInstructorWithDynamicSecurity

VW_GRADESBYINSTRUCTORWITHDYNAMICSECURITY

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)

32

MD_ColPers_ColleagueID

Unique identifier in Colleague. Typically not exposed in reports. Used for internal joins and views within the data warehouse. If you think you need to print and analyze this attribute, think again - or check with the data warehouse team. Consider using Application ID instead.

DW_ColleagueID

char(7)

7

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)

64

MD_Pers_GivenName

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

DW_GivenName

nvarchar(32)

32

MD_Pers_Gender

Must be Male, Female, or Unknown.

DW_Gender

varchar(7)

7

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

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

Armed forces 'states' (really postal routing codes) as of 2012 still are not expanded. E.g., if someone's address is 'APO AA' (Army Post Office, Armed Forces Americas), the city will be 'Apo' and the state will be 'AA' as of 2012. In other words, military postal codes of AA, AE, AP will not be expanded to a longer name as of 2012. This leads to awkward 'state' lists in some reports, where you see, e.g., AA, AE, and AP alongside Pennsylvania, Illinois, Rhode Island, Minnesota, etc.

DW_Addr_USState

varchar(24)

24

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)

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)

64

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

7

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)

7

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_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_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_Stu_AcadLevel

Colleague academic level names, Undergraduate (UG), Summer Graduate (SG), and Non-Degree (ND). We use the description here, not the 'code,' for human readability. If a student has graduated, then the value here will be 'Not applicable.' Ditto for students who have received aid offers, etc., but who have not matriculated.

DW_AcademicLevel

varchar(30)

30

MD_DStu_AdvanceID

Advance ID used for joins to alumni tables. This attribute may be NULL and therefore should not be exposed to most users. Do not use this attribute in typical reports or queries. This attribute is a ten-character, zero-left-padded string of digits, e.g., 0000012481.

DW_AdvanceID

char(10)

10

MD_DStu_EnteredAsTransfer

"Yes" if a given student entered as a transfer student. "No" otherwise. This attribute is a type 1 SCD, which means that even though it indicates how a student entered Carleton, it is applied to all RowStart/RowEnd times. In other words, once a "yes," always a "yes" here, unless the student's program changes fundamentally (e.g., leaves and is readmitted).

Note that this field doesn't really, truly mark someone specifically as entering as a transfer. At Carleton, you either are or aren't a transfer, and if you are, you (by implication) entered as one.

DW_YesOrNoNOTNULL

char(3)

3

MD_DStu_LegacyType

This attribute indicates whether a student is a first-generation college student or a legacy of some type. Only a single value is stored here (so a person can't be a first-generation college student, and a parent-legacy). What gets stored here is determined by what is in Recruitment Plus at the time of a student's application.

Values are available for this attribute in Colleague only from Nov 2008 onward, and the data that is there (as of 2010) appears not to be fully consistent with Recruitment Plus. Hence be careful when using this attribute in longitudinal analysis.

Here we treat this attribute as a type-1 attribute, meaning that once it's set, it's set for all time for a given student. The reason for this is that the legacy status is currently only set for applicants, and the data comes (as noted) from Recruitment Plus once, and is then no longer updated (theoretically). So there's no point in making this a type-II SCD. Instead, we treat it as an intrinsic attribute of the student, like gender or ethnicity.

You can think of this attribute as 'legacy status in Recruitment Plus at the time of the student's application'.

DW_CodeDesc

varchar(32)

32

MD_DStu_GraduatingCohortYear

Actual, or projected, graduating class year for a given student. The procedure for assigning a value to this field is complex, and if a student makes slower or faster than anticipated academic progress, their class year may change periodically.

This attribute is similar to, but not quite the same, as the pref_class_year in Advance (which denotes the class an alum prefers to be associated with, and can be virtually anything; for alumni relations purposes we just don't care what class year a person says they want to be associated with).

Students without a graduation year get assigned a nonsense year of 9999 here, in lieu of NULL or an empty string.

Note that having a graduating cohort year does not mean that a given student has actually graduated. The value here is just the group they should (have) graduate(d) as part of. Also, note that lacking a graduating cohort year may not indicate that a student hasn't graduated. For example, for about a decade (up until 2011) Carleton routinely removed the class year when a student was reported to have died. This practice ceased during the 2010-11 academic year, but the data cleanup may not have been complete in its aftermath, and there is always the possibility of other such practices being introduced in the future. There are also a number of cases where a student receives a graduating cohort year that does not reflect the actual year in which they graduated. For example, a student that entered in the fall of 2003 and graduated in 2010 may in some cases keep their original anticipated class year of 2007.

Do not use this field to limit results to graduates. To be sure to get only graduates in a given query, filter on the MD_DStu_EnrollmentStatus field. Select only students who have graduated. As long as this field is being proprly mainained (and not, e.g., converted to a different code if a student dies), this will limit results, correctly, to graduates.

DW_YearAtCarleton

int

MD_DStu_CohortCode

The cohort code is used for grouping students when creating various key (e.g., IPEDS) reports. It is set once for a student, and then forever kept the same. Hence we have implemented it here as a type-I SCD attribute.

The values expressed here are taken directly from XSTU.ENTERING.COHORT in Colleague, which is stored in the Federal Cohort Group field on the "UG" student academic level record. The data entry field in Colleague is "Federal Cohort" on the "STAL - Student Academic Level" screen.

The format of the cohort code is: two digit year, single letter for term (F=fall, W=winter, S=spring), single letter for first-year student (F) or transfer (T), for example, 09FF would mean that a given student entered in the fall of 2009 as a full-time student.

The cohort codes go back to the fall of 1993, and they are very carefully maintained, because they are used in various key (e.g., IPEDS) reports. Some of these reports require looking at a specific group of students who entered at a specific point in time, but with some changes and exemptions allowed based on later events, such as death, military service, and missionary work. To factor in later events such as this, but otherwise utilize a point-in-time snapshot requires a mixture of type-I and II SCDs. The cohort code is our type-I SCD. When we update it, e.g., due to a death, it has a retroactive effect.

DW_CodeDescShort

varchar(16)

16

MD_DStu_EntryTerm

Term of initial entry to Carleton (e.g., "09/FA"). Defaults to 'Unknown' if the value here is unknown, not applicable, etc.

A lot of IPEDS statistics require grouping students by entry term and by excluding those that didn't start in the fall, and this attribute is very useful in performing such groupings/exclusions.

Other stats require a so-called full-year cohort. For that, use MD_DStu_EnteringCohortYear.

DW_TermShortName

char(7)

7

MD_DStu_TermsInResidence

Terms in residence, for graduates, is a KPI, because it tells us how much (potential) revenue can be gained from each student. Those who graduate with fewer terms in residence, that is, cost us tuition money.

When we count here we refactor double or triple terms (e.g., FW, AY, etc.) as multiple terms, not just as one term.

Because the counting method is volatile (we're still not absolutely sure how to do this), this field is not exposed in views like VMD_DegreeStudent.

This number is highly unreliable before the 'converstion' to Colleague in 1992, and is still, as of 2010, not particularly good, in the sense that we expect our understanding of what the Registrar wants here to be refined over time.

<None>

int