|
SELECT
MD_COLLPERS_SURROGATEKEY,
MD_COLPERS_COLLEAGUEID,
MD_PERS_COMMONNAME,
MD_Pers_Surname + ', ' + MD_Pers_GivenName AS MD_Pers_LastThenFirstName,
MD_PERS_SURNAME,
MD_PERS_GIVENNAME,
MD_PERS_MIDDLENAME,
MD_PERS_GENDER,
MD_PERS_CITIZENSHIP,
MD_PERS_AGERANGE,
MD_PERS_HOMEADDRCITY,
MD_PERS_HOMEADDRNONUSPROVINCE,
MD_PERS_HOMEADDRUSSTATE,
MD_PERS_HOMEADDR5DIGITUSZIP,
MD_PERS_HOMEADDRUSREGIONFIPS,
MD_PERS_HOMEADDRUSREGIONCARLETON,
MD_PERS_HOMEADDRCOUNTRY,
MD_PERS_HOMEADDRLATITUDE,
MD_PERS_HOMEADDRLONGITUDE,
MD_PERS_HOMEADDRMILESFROMCAMPUS,
MD_PERS_ISNONRESIDENTALIEN,
MD_PERS_ISOFUNKNOWNRACEETHNICITY,
MD_PERS_ISHISPANICORLATINO,
MD_PERS_RACE,
MD_PERS_RACELIST,
MD_PERS_OLDPRIMARYRACE,
MD_PERS_OLDRACELIST,
CASE
-- For IPEDS, we report 'race' as for the census bureau, unless a student's a non-resident alien (which case we report that as a 'race')
WHEN MD_PERS_ISNONRESIDENTALIEN = 'Yes' THEN 'Nonresident Alien'
ELSE MD_PERS_OLDPRIMARYRACE
END AS MD_PERS_OLDIPEDSETHNICITY,
CASE
-- Hispanics should always have a race as well as IsHispanic = 'Yes' at least in the new 2010 census system
WHEN MD_PERS_RACE = 'Unknown' AND MD_PERS_OLDPRIMARYRACE <> 'Unknown' THEN 'Unknown'
-- For IPEDS, we report 'race' as for the census bureau, but if a student is a non-resident alien, we report that as a 'race'
WHEN MD_PERS_ISNONRESIDENTALIEN = 'Yes' THEN 'Nonresident Alien'
-- IPEDS says Hispanic trumps not only black but also 'Two or More Races'
WHEN MD_PERS_ISHISPANICORLATINO = 'Yes' THEN 'Hispanic or Latino'
-- Although black has historically trumped Hispanic at Carleton, IPEDS says Hispanic trumps black and 'Two or More Races'
ELSE MD_PERS_RACE
END AS MD_PERS_IPEDSETHNICITY,
--
-- IPEDS requires that we convert old ethnicity codes into new ones for past years, as well as we can
CASE
WHEN MD_PERS_ISNONRESIDENTALIEN = 'Yes' THEN 'Nonresident Alien'
-- IPEDS says Hispanic trumps not only black but also 'Two or More Races'
WHEN MD_PERS_ISHISPANICORLATINO = 'Yes' THEN 'Hispanic or Latino'
WHEN MD_PERS_RACE <> 'Unknown' THEN MD_PERS_RACE
WHEN MD_PERS_OLDRACELIST LIKE '%, %' THEN 'Two or More Races'
ELSE
CASE MD_PERS_OLDPRIMARYRACE
WHEN 'Black or African American' THEN 'African American'
-- Pacific Islanders go here in the old system, and there's no way to translate them into the new
WHEN 'Asian' THEN 'Asian'
WHEN 'Hispanic' THEN 'Hispanic or Latino'
WHEN 'Native American-Alaskan Native' THEN 'American Indian-Alaska Native'
WHEN 'White' THEN 'White'
WHEN 'Mixed race or other' THEN 'Two or More Races'
ELSE 'Unknown'
END
END AS MD_PERS_CROSSWALKEDIPEDSETHNICITY,
MD_DSTU_RECRUITMENTPLUSPKID,
MD_DSTU_ADVANCEID,
MD_DSTU_ISFIRSTTIMECOLLEGE,
MD_DSTU_ENTEREDASTRANSFER,
MD_DSTU_ENTEREDASFULLTIME,
MD_DSTU_LEGACYTYPE,
MD_DSTU_ENTERINGCOHORTYEAR,
MD_DSTU_GRADUATINGCOHORTYEAR,
-- IPEDS has two systems for calculating the actual graduation year, one with a Sept-Aug year and another with a July-Jun year
CASE
WHEN YEAR(MD_DSTU_COMMENCEMENTDATE) = 9999 THEN 9999
WHEN MONTH(MD_DSTU_COMMENCEMENTDATE) < 7 THEN YEAR(MD_DSTU_COMMENCEMENTDATE)
ELSE YEAR(MD_DSTU_COMMENCEMENTDATE) + 1
END AS MD_DSTU_IPEDSGradYrJulyJune,
CASE
WHEN YEAR(MD_DSTU_COMMENCEMENTDATE) = 9999 THEN 9999
WHEN MONTH(MD_DSTU_COMMENCEMENTDATE) < 9 THEN YEAR(MD_DSTU_COMMENCEMENTDATE)
ELSE YEAR(MD_DSTU_COMMENCEMENTDATE) + 1
END AS MD_DSTU_IPEDSGradYrSeptAug,
MD_DSTU_COHORTCODE,
CASE
-- Figure out academic year that corresponds to cohort code
WHEN MD_DSTU_COHORTCODE = 'Unknown' THEN 'Unknown'
WHEN MD_DSTU_COHORTCODE = '' THEN 'Unknown'
WHEN CAST(SUBSTRING(MD_DSTU_COHORTCODE, 1, 2) AS INT) <= 60
THEN
-- E.g., if cohort code is 00FF (00 <= 60), then we're basing the acad year on the year 2000
CASE SUBSTRING(MD_DSTU_COHORTCODE, 3, 1)
WHEN 'F'
THEN CAST(2000 + CAST(SUBSTRING(MD_DSTU_COHORTCODE, 1, 2) AS INT) AS CHAR(4))
+ '-' + SUBSTRING(CAST(2000 + CAST(SUBSTRING(MD_DSTU_COHORTCODE, 1, 2) AS INT) + 1 AS CHAR(4)), 3, 2)
ELSE CAST(2000 + CAST(SUBSTRING(MD_DSTU_COHORTCODE, 1, 2) AS INT) - 1 AS CHAR(4))
+ '-' + SUBSTRING(MD_DSTU_COHORTCODE, 1, 2)
END
ELSE
-- E.g., if cohort code is 98FF (98 > 60), then we're basing the acad year on the year 1900
CASE SUBSTRING(MD_DSTU_COHORTCODE, 3, 1)
WHEN 'F'
THEN CAST(1900 + CAST(SUBSTRING(MD_DSTU_COHORTCODE, 1, 2) AS INT) AS CHAR(4))
+ '-' + SUBSTRING(CAST(1900 + CAST(SUBSTRING(MD_DSTU_COHORTCODE, 1, 2) AS INT) + 1 AS CHAR(4)), 3, 2)
ELSE CAST(1900 + CAST(SUBSTRING(MD_DSTU_COHORTCODE, 1, 2) AS INT) - 1 AS CHAR(4))
+ '-' + SUBSTRING(MD_DSTU_COHORTCODE, 1, 2)
END
END AS MD_DSTU_COHORTCODEACADYEAR,
MD_DSTU_CATALOGYEAR,
MD_DSTU_ENTRYTERM,
-- Volatile definition; do not expose right now; when we do, reconstruct history
-- MD_DSTU_TERMSCOMPLETED,
CASE
WHEN MD_DSTU_TERMSCOMPLETED IS NULL THEN ' 0'
WHEN MD_DSTU_TERMSCOMPLETED < 7 THEN ' 1 - 6'
WHEN MD_DSTU_TERMSCOMPLETED < 10 THEN ' 7 - 9'
WHEN MD_DSTU_TERMSCOMPLETED < 13 THEN '10 - 12'
ELSE '13 or More'
END AS MD_DStu_TermsCompletedRange,
-- Volatile definition; do not expose right now; when we do, reconstruct history
-- MD_DSTU_TermsInResidence,
MD_DSTU_TERMSTOGRADUATE,
MD_DSTU_YEARSTOGRADUATE,
MD_DSTU_YEARSTOGRADUATE8,
CASE
WHEN MD_DSTU_TERMSTOGRADUATE = 'Not applicable' THEN 'Not Applicable'
WHEN MD_DSTU_TERMSTOGRADUATE = 'Unknown' THEN 'Unknown'
WHEN MD_DSTU_TERMSCOMPLETED < 12 THEN 'Yes'
ELSE 'No'
END AS MD_DStu_IsFinEarlyGraduate,
MD_DSTU_CARLGPARANGECUMULATIVE,
MD_DSTU_CARLGPARANGECUMULATOF,
MD_DSTU_MAJOR1,
MD_DSTU_MAJOR2,
MD_DSTU_MAJOR3,
MD_DSTU_MAJOR4,
MD_DSTU_MAJORLIST,
MD_DSTU_CONCENTRATION1,
MD_DSTU_CONCENTRATION2,
MD_DSTU_CONCENTRATION3,
MD_DSTU_CONCENTRATION4,
MD_DSTU_CONCENTRATIONLIST,
MD_DSTU_ENROLLMENTSTATUS,
MD_DSTU_WRITINGPORTFOLIOSTATUS,
MD_DSTU_ISDONELANGREQUIREMENT,
MD_DSTU_CREDITSFORDISTRO,
MD_DSTU_PREMATRICCREDITRANGE,
MD_DSTU_HASCURRENTFINANCIALAIDNEED,
MD_DSTU_ISPOSSE,
MD_DSTU_ISTRIO,
MD_DSTU_TERMSINOFFCAMPUSPROGRAMS,
-- Volatile definition; do not expose right now; when we do, reconstruct history
-- MD_DSTU_TERMSWITHNORTHFIELDOPTION,
MD_DSTU_ISPHIBETAKAPPA,
MD_DSTU_ISMORTARBOARD,
MD_DSTU_ISSIGMAXI,
MD_DSTU_ISCUMLAUDE,
MD_DSTU_ISSUMMACUMLAUDE,
MD_DSTU_ISMAGNACUMLAUDE,
CASE
WHEN MD_DSTU_ISCUMLAUDE = 'Yes' THEN 'Yes'
WHEN MD_DSTU_ISSUMMACUMLAUDE = 'Yes' THEN 'Yes'
WHEN MD_DSTU_ISMAGNACUMLAUDE = 'Yes' THEN 'Yes'
WHEN MD_DSTU_ISCUMLAUDE = 'Not applicable' THEN 'Not applicable'
ELSE 'No'
END AS MD_DStu_IsLatinHonors,
CASE
WHEN MD_DSTU_ISSUMMACUMLAUDE = 'Yes' THEN 'Summa Cum Laude'
WHEN MD_DSTU_ISMAGNACUMLAUDE = 'Yes' THEN 'Magna Cum Laude'
WHEN MD_DSTU_ISCUMLAUDE = 'Yes' THEN 'Cum Laude'
WHEN MD_DSTU_ISCUMLAUDE = 'Not applicable' THEN 'Not applicable'
ELSE 'None'
END AS MD_DStu_LatinHonor,
MD_DSTU_MAJOR1DISTINCTION,
MD_DSTU_MAJOR2DISTINCTION,
MD_DSTU_MAJOR3DISTINCTION,
MD_DSTU_ISDISTINCTIONINANYMAJOR,
MD_DSTU_RELIGION1,
MD_DSTU_RELIGION2,
MD_DSTU_RELIGION3,
MD_DSTU_RELIGIONLIST,
MD_DSTU_TERMSASVARSITYATHLETE,
CASE
WHEN MD_DSTU_TERMSASVARSITYATHLETE = 0 THEN '0'
WHEN MD_DSTU_TERMSASVARSITYATHLETE <= 4 THEN '1 to 4'
WHEN MD_DSTU_TERMSASVARSITYATHLETE <= 8 THEN '5 to 8'
ELSE '9 or More'
END AS MD_DSTU_TERMSASATHLETERANGE,
MD_DSTU_TERMSONACADPROBATION,
MD_DSTU_TERMSASRESIDENTASSISTANT,
MD_DSTU_TERMSASHOUSEMANAGER,
MD_DSTU_TERMSASSTUDENTWORKER,
MD_DSTU_TERMSASLANGUAGEASSISTANT,
MD_DSTU_TERMSONDEANSLIST,
MD_DSTU_ROWSTART,
MD_DSTU_ROWEND,
MD_DSTU_ROWISCURRENT
FROM
dbo.MD_DEGREESTUDENT
|
|
MD_CollPers_SurrogateKey
|
numeric
|
|
|
|
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_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_LastThenFirstName
|
|
|
|
|
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_GivenName
|
nvarchar(32)
|
Given name in the US typically means the same thing as "first name." Default value is "missing".
|
|
|
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_Gender
|
varchar(7)
|
Must be Male, Female, or Unknown.
|
|
|
MD_Pers_Citizenship
|
nvarchar(64)
|
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'.
|
|
|
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_HomeAddrCity
|
nvarchar(32)
|
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.
|
|
|
MD_Pers_HomeAddrNonUSProvince
|
nvarchar(64)
|
|
|
|
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_HomeAddr5DigitUSZip
|
char(5)
|
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 '00000'.
|
|
|
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_HomeAddrUSRegionCarleton
|
varchar(64)
|
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.
|
|
|
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_HomeAddrLatitude
|
float
|
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.
|
|
|
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_HomeAddrMilesFromCampus
|
float
|
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.
|
|
|
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_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_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_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.
General documentation on how race is now stored (2010) in major systems: http://apps.carleton.edu/campus/ira/fed_race_ethnic/
|
|
|
MD_Pers_RaceList
|
varchar(128)
|
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.
|
|
|
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_OLDRaceList
|
varchar(128)
|
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).
|
|
|
MD_PERS_OLDIPEDSETHNICITY
|
|
This attribute collapses various unrelated demographic attributes down into one, for IPEDS reporting purposes: Non-resident alien status and race. Note that for this attribute (which uses pre-2010 census categories), Hispanic is a 'race'. Note also that this attribute follows Carleton's old 'trumping' system in which a person who said he/she was both Hispanic and African American would be re-listed (regardless of how the student self-classified) with 'African American' as their primary race. This attribute follows that convention. If the newer behavior is desired, use MD_PERS_IPEDSETHNICITY instead.
|
|
|
MD_PERS_IPEDSETHNICITY
|
|
This attribute collapses various unrelated demographic attributes down into one, for IPEDS reporting purposes: Non-resident alien status, ethnicity (Hispanic or not), or race. Note that IPEDS specifies that Hispanic trumps African American, counter to traditional Carleton reporting practices. This attribute will only be defined for students with 2010 race/ethnicity data in our system. For those lacking this information, the value will be 'Unknown.'
Use MD_PERS_CROSSWALKEDIPEDSETHNICITY instead of this attribute, if you require that all students have information present here, even those for whom we lack new-style (2010 Census) ethnicity/race information. The only disadvantage to using the crosswalked attribute is that it cannot determine whether someone was Asian or a Pacific Islander if the only information available is pre-Census-2010 race/ethnicity information.
|
|
|
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_DStu_RecruitmentPlusPKID
|
int
|
Recruitment Plus PKID used for joins to prospect/Admissions 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 an integer (as in Recruitment Plus).
|
|
|
MD_DStu_AdvanceID
|
char(10)
|
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.
|
|
|
MD_DStu_IsFirstTimeCollege
|
char(3)
|
"Yes" if student is a first-time student. Some reports require us to select out only first-time full-time students who started as freshmen.
This is a so-called type-1 attribute, meaning that once it's set, it's set for all times for a given student. Some attributes only get set 'as of' a certain date, and change as history moves on, like a zipcode or a phone number, or a student status. This attribute, however, reflects an intrinsic property of a person (or their state upon entry to Carleton), and applies universally, much like a gender (well, usually) or an ethnicity.
Right now we 'calculate' the value of this attribute by taking the inverse of the EnteredAsTransfer attribute. If somebody entered as a transfer, they get a "No" here. Otherwise we mark them as a "Yes," i.e., as a first-time college student. Compare this, e.g., with the HERI/CIRP rules: http://www.gseis.ucla.edu/heri/researchers/FTFT.PDF.
|
|
|
MD_DStu_EnteredAsTransfer
|
char(3)
|
"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.
|
|
|
MD_DStu_EnteredAsFullTime
|
varchar(7)
|
"Yes" if a given student entered as a full-time student. "No" otherwise.
This value is calculated as of the entry term, whether or not a given student completed that term, or was even registered as of census date. Since Carleton has no part-time status (we use just F, L, O, R, X, etc.), we simply mark anyone as registered for less than twelve credits in their first term as entering part time. This is quite rare, if it ever happens, at Carleton.
This attribute is to be distinguished from the EnrollmentStatus, which is a current value - i.e., one that refers to the current term.
|
|
|
MD_DStu_LegacyType
|
varchar(32)
|
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'.
|
|
|
MD_DStu_EnteringCohortYear
|
int
|
Year in which student first enrolled and was assigned a cohort. Not all students are assigned a cohort. Students without an assigned cohort get assigned a nonsense year of 9999 here, in lieu of NULL or an empty string. See also the FinancialAidPerson dimension, where the cohort typically exists, but is provisional for many people (who have applied for/been offered aid, but have not matriculated).
There are 2 pieces of information that can be gleaned from the 'cohort' field in Colleague (from which this data is taken). The first is the entering year (08FF indicates a 2008 start of the undergraduate program). The second is the full-time/transfer information (cohort group ends in FF if first time student, FT if this is considered a transfer student). We break the year out here. The fullt-ime/transfer status is broken out elsewhere.
Note that this attribute is particularly useful in grouping students by what IPEDS calls the full-year cohort, which IPEDS defines as the group of students entering at any time during a particular 12-month period September 1 through August 31 that is established for tracking and reporting Graduation Rate (GRS) data for institutions that primarily offer occupational programs of varying lengths. Students must be full-time and first-time to be considered in the cohort (which we determine from other attributes).
A lot of IPEDS stats, however, focus on the fall cohort, i.e., the group of students who all entered as first-time full-time freshmen in a particular term. For that, see MD_DStu_EntryTerm.
|
|
|
MD_DStu_GraduatingCohortYear
|
int
|
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.
|
|
|
MD_DSTU_IPEDSGRADYRJULYJUNE
|
|
|
|
|
MD_DSTU_IPEDSGRADYRSEPTAUG
|
|
|
|
|
MD_DStu_CohortCode
|
varchar(16)
|
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.
|
|
|
MD_DSTU_COHORTCODEACADYEAR
|
|
Academic year corresponding to the cohort code. E.g., if the cohort code is '00FF' then the corresponding academic year is '2000-01'. Similarly, for '99WF' (winter first-time, non-transfer students) the corresponding academic year would be '1998-99'.
|
|
|
MD_DStu_CatalogYear
|
int
|
Used for degree audit; tells us what set of requirements applies to a given student. If unknown, defaults to 9999.
This attribute is a type-I SCD, because it applies retroactively to all courses a given student took/takes, when determining what requirements they have met. There is generally no reason to 'keep history' on the value entered here.
|
|
|
MD_DStu_EntryTerm
|
char(7)
|
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.
|
|
|
MD_DSTU_TERMSCOMPLETEDRANGE
|
|
|
|
|
MD_DStu_TermsToGraduate
|
varchar(16)
|
Number of regular (FA, WI, SP) terms elapsed (but not necessarily completed, or even registered) since starting at Carleton, assuming the student was registered as of census day his/her first term. Although internally this number is an integer, it is discretized here into ranges appropriate for reporting. The default is 'Unknown'. This attribute is only populated for first-time students. For transfers, this attribute is set to 'Not applicable.' The reason for this is that terms-to-graduate can't be evaluated as straightforwardly for transfers, who receive varying amounts of transfer credits. To study terms-to-graduate for transfers, talk directly with the Colleague Team.
The values here are NOT RELIABLE for students whose graduating class year is < 1999.
Calculation is done using the graduation date in Colleague, not the commencement date. Reason: Although the two dates could theoretically be independently entered, and may, theoretically, differ, in fact (and in practice) when one inputs a commencement date, the graduation date is also filled in and defaults to the same month, the first of the month. Hence the commencement and graduation dates will never differ in a way that matters for IPEDS.
Terms to graduate is recalculated term by term, from the start date until graduation, at which point it stops being incremented. If the resulting name weren't so darn long, we would have called this attribute MD_DStu_TermsElapsedFromStartTermUntilGraduationTerm.
This value is mainly used internally at Carleton. IPEDS reporting, by way of contrast, uses values based on years - not terms.
Compare YearsToGraduate.
This attribute tracks history. Technically speaking, it is a type-II attribute. Cf. MD_DStu_YearsToGraduate8.
|
|
|
MD_DStu_YearsToGraduate
|
varchar(16)
|
Number of years or year parts elapsed (but not necessarily completed, or even registered) since starting at Carleton, assuming the student was registered as of census day his/her first term. The numbers here are descretized into ranges appropriate for IPEDS reporting. For transfers, this attribute is set to 'Not applicable.' The reason for this is that terms-to-graduate can't be evaluated as straightforwardly for transfers, who receive varying amounts of transfer credits. To study terms-to-graduate for transfers, talk directly with the Colleague Team.
This number is not reliable before the 'conversion' in 1992 to Colleague.
This number is recalculated year by year, from the start date until graduation, at which point it stops being incremented. If the resulting name weren't so darn long, we would have called this attribute YearsOrPartialYearsElapsedFromStartUntilGraduationDate. This figure is used, e.g., for IPEDS reporting, which classifies students into those who 1) graduate in 48 or fewer months (4 years or less), 2) 49-60 months (five years), 3) 61-72 months (six years), or others, i.e., people who don't graduate in 150% of the time normally allocated for a degree.
Calculation is done using the graduation date in Colleague, not the commencement date. Reason: Although the two dates could theoretically be independently entered, and may, theoretically, differ, in fact (and in practice) when one inputs a commencement date, the graduation date is also filled in and defaults to the same month, the first of the month. Hence the commencement and graduation dates will never differ in a way that matters for IPEDS.
If you use this for IPEDS reporting, remember to select out only first-time full-time students (attributes are available in the data warehouse for these things). And use August 31 as your as-of date for reporting (i.e., take student rows where the start date <= August 31 and end date >= August 31). Ask the data warehouse team for help doing this, if you have any questions.
Compare TermsToGraduate.
Please note that the "MD_DStu_YearsToGraduate" attribute was set up to keep history. For example, if you are reporting "as of" a date before a given student graduated, MD_DStu_YearsToGraduate will show a value of 'Not Applicable' - even if the student has actually graduated as of the present date. By way of contrast, MD_DStu_YearsToGraduate8 (which was introduced in 2011 to fulfill HERA requirements) will always show the current value. If a student has graduated, that is, there will be a value here (like "5" or "6"), even if the "as of" date for your report is before the student's graduation date.
Probably both attributes should always have worked the same (neither tracking history). But there is some utility to having both reporting options, so the different behavior should be regarded as a feature, not a bug ;-).
|
|
|
MD_DStu_YearsToGraduate8
|
varchar(16)
|
This attribute works just like MD_DStu_YearsToGraduate except 1) that it does not keep history (see below), and 2) that it presupposes an eight-year schedule, rather than a six-year one.
That is, the MD_DStu_YearsToGraduate is based on old US federal government graduation rate reporting standards, which were based on six-year graduation rates (how many students graduated in 150% of the normal time allocated for a degree program).
This attribute is based on eight-year (200%) graduation-rate reporting.
As for this attribute's not keeping history: The older MD_DStu_YearsToGraduate attribute was set up to keep history. For example, if you are reporting "as of" a date before a given student graduated, MD_DStu_YearsToGraduate will show a value of 'Not Applicable' - even if the student has actually graduated as of the present date. By way of contrast, MD_DStu_YearsToGraduate8 will always show the current value. If a student has graduated, that is, there will be a value here (like "5" or "6"), even if the "as of" date for your report is before the student's graduation date.
Probably both attributes should always have worked the same (neither tracking history). But there is some utility to having both reporting options, so the different behavior should be regarded as a feature, not a bug ;-).
|
|
|
MD_DSTU_ISFINEARLYGRADUATE
|
|
|
|
|
MD_DStu_CarlGPARangeCumulative
|
varchar(16)
|
Student's cummulative GPA - to two decimal places, discretized into ranges. For current students, this is current as of the end of the last term. For graduates, it is their final GPA.
BEWARE: This field defaults to 0.00 (if no GPA is available). There is, therefore, NO WAY TO DISTINGUISH DEFAULTED GPAs FROM F GPAs (i.e., GPAs reflecting a student's failing every course). Therefore it is critical, if you are reporting on this field, to filter out cases where the CarlGPARangeCumulativeAtoF equals 'Unknown.'
Note that this same data is also available in the form of letter grades (A, A-, B+, B, B-, etc.) in CarlGPARangeCumulativeAtoF. Some folks simply prefer letter grades to numeric ranges; and some reports use each. So in efforts to accommodate everyone, we provide both.
Just note that they are equivalent.
|
|