ViewReference_61

VMF_SectionGradeByTerm

MD_ColPers_ColleagueID

char(7)

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.

MD_Pers_IsVeteran

varchar(7)

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

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_MiddleName

nvarchar(32)

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

MD_Pers_PrimaryAffiliation

varchar(16)

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.

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.

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.

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_IsOfUnknownRaceEthnicity

char(3)

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

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

varchar(32)

MD_Pers_DisabilityList

varchar(128)

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.

MD_NonDegStu_Type

char(30)

Non-degree students come in various flavors, including St Olaf students, High School students, etc. This field records the flavor.

MD_NonDegStu_RowStart

datetime

MD_NonDegStu_RowIsCurrent

bit