|
select
MD_COLLPERS_SURROGATEKEYFAIDPERS,
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_PRIMARYAFFILIATION,
MD_PERS_CITIZENSHIP,
CASE
-- Unknown here (which should not occur) defaults to US
WHEN MD_PERS_CITIZENSHIP = 'Unknown' THEN 'Yes'
-- Covers 'United States of America and 'United States/dual citizenship'
WHEN SUBSTRING(MD_PERS_CITIZENSHIP, 1, 13) = 'United States' THEN 'Yes'
ELSE 'No'
END AS MD_Pers_IsUSCitizen,
MD_PERS_AGERANGE,
MD_PERS_HOMEADDRCITY,
MD_PERS_HOMEADDRUSSTATE,
MD_PERS_HOMEADDR5DIGITUSZIP,
MD_PERS_HOMEADDRUSREGIONFIPS,
MD_PERS_HOMEADDRUSREGIONCARLETON,
MD_PERS_HOMEADDRCOUNTRY,
MD_PERS_HOMEADDRLATITUDE,
MD_PERS_HOMEADDRLONGITUDE,
MD_PERS_HOMEADDRNONUSPROVINCE,
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_STU_ACADLEVEL,
MD_FAIDPERS_INSTANCENAME,
MD_FAIDPERS_APPSTATUS,
MD_FAIDPERS_ENTRYDECISIONTYPE,
MD_FAIDPERS_PARENTAGI,
CASE MD_FAIDPERS_PARENTAGI
WHEN 'Unknown' THEN 'Unknown'
WHEN ' $0 - $19,999' THEN ' $0 - $39,999'
WHEN ' $20,000 - $39,999' THEN ' $0 - $39,999'
WHEN ' $40,000 - $59,999' THEN ' $40,000 - $79,999'
WHEN ' $60,000 - $79,999' THEN ' $40,000 - $79,999'
WHEN ' $80,000 - $99,999' THEN ' $80,000 - $119,999'
WHEN '$100,000 - $119,999' THEN ' $80,000 - $119,999'
WHEN '$120,000 - $139,999' THEN '$120,000 - $159,999'
WHEN '$140,000 - $159,999' THEN '$120,000 - $159,999'
WHEN '$160,000 - $179,999' THEN '$160,000 - $199,999'
WHEN '$180,000 - $199,999' THEN '$160,000 - $199,999'
WHEN '$200,000 - $219,999' THEN '$200,000 - $239,999'
WHEN '$220,000 - $239,999' THEN '$200,000 - $239,999'
WHEN '$240,000 - $259,999' THEN '$240,000 - $279,999'
WHEN '$260,000 - $279,999' THEN '$240,000 - $279,999'
ELSE '$300,000 or More'
END AS MD_FAIDPERS_ParentAGIRod,
MD_FAIDPERS_NEED,
MD_FAIDPERS_RECRUITMENTPLUSPKID,
MD_FAIDPERS_ENTERINGCOHORTYEAR,
MD_FAIDPERS_GRADUATINGCOHORTYEAR,
CASE
WHEN MD_FAIDPERS_GRADUATINGCOHORTYEAR = 9999 THEN 'Unknown'
WHEN (MD_FAIDPERS_GRADUATINGCOHORTYEAR - MD_FAIDPERS_INSTANCENAME) = 1 THEN '4 - Senior'
WHEN (MD_FAIDPERS_GRADUATINGCOHORTYEAR - MD_FAIDPERS_INSTANCENAME) = 2 THEN '3 - Junior'
WHEN (MD_FAIDPERS_GRADUATINGCOHORTYEAR - MD_FAIDPERS_INSTANCENAME) = 3 THEN '2 - Sophomore'
WHEN (MD_FAIDPERS_GRADUATINGCOHORTYEAR - MD_FAIDPERS_INSTANCENAME) >= 4 THEN
CASE
WHEN MD_FAIDPERS_ENTERINGCOHORTYEAR < MD_FAIDPERS_INSTANCENAME THEN '1 - Freshman (Administrative)'
ELSE '1 - Freshman (True)'
END
ELSE 'Not Applicable'
END AS MD_FAidPers_YearAtCarleton,
MD_FAIDPERS_CARLETONTOTALEXPENSES,
MD_FAIDPERS_FEDFAMTOTINCOME,
MD_FAIDPERS_FEDPARENTAGI,
MD_FAIDPERS_FEDPARENTCONTRIB,
MD_FAIDPERS_FEDSTUCONTRIB,
MD_FAIDPERS_FEDFAMCONTRIB,
MD_FAIDPERS_FEDNEED,
MD_FAIDPERS_PROFPARTOTALINCOME,
MD_FAIDPERS_PROFFAMTOTINCOME,
MD_FAIDPERS_PROFPARENTAGI,
MD_FAIDPERS_PROFPARENTNETWORTH,
MD_FAIDPERS_PROFSTUNETWORTH,
MD_FAIDPERS_IPROPARHOMEEQUITY,
MD_FAIDPERS_PROFPARENTCONTRIB,
MD_FAIDPERS_PROFSTUCONTRIB,
MD_FAIDPERS_PROFFAMCONTRIB,
MD_FAIDPERS_INSTPARENTAGI,
MD_FAIDPERS_INSTPARENTCONTRIB,
MD_FAIDPERS_INSTSTUCONTRIB,
MD_FAIDPERS_NONCUSTODPARCONTRIB,
MD_FAIDPERS_INSTFAMCONTRIB,
MD_FAIDPERS_INSTNEED,
MD_FAIDPERS_NUMBERINFAMILY,
MD_FAIDPERS_PARENTDEDUCTIONS,
MD_FAIDPERS_ISNONPELLSUBSRECIPIENT,
MD_FAIDPERS_ISPELLGRANTRECIPIENT,
--
-- IPEDS requires that we divide students up into three categories, Pell recipients, non-Pell subsidized federal aid recipients, and neither
-- Note that if a student receives a Pell, they go into basked 1, even if they also received a subsidized Stafford loan
CASE
WHEN MD_FAIDPERS_ISPELLGRANTRECIPIENT = 'Yes' THEN 'Pell Recipient'
WHEN MD_FAIDPERS_ISNONPELLSUBSRECIPIENT = 'Yes' THEN 'Non-Pell Fed Subsidized Recipient'
-- Dummy 'Unknown' aid applicant has an Unknown status (not 'Neither', because in that case we know the person's status)
WHEN MD_COLPERS_COLLEAGUEID = '0000000' THEN 'Unknown'
ELSE 'Neither'
END AS MD_FAIDSPERS_IPEDSAIDSTATUS,
MD_FAIDPERS_HASINSTITUTIONALAIDNEED,
MD_FAIDPERS_ROWSTART,
MD_FAIDPERS_ROWEND,
MD_FAIDPERS_ROWISCURRENT
FROM
MD_FinancialAidPerson
|
|
MD_CollPers_SurrogateKeyFAidPers
|
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_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_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_ISUSCITIZEN
|
|
|
|
|
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_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 '0000'.
|
|
|
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_HomeAddrNonUSProvince
|
nvarchar(64)
|
|
|
|
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.
|
|
|
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
|
|
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_IPEDSETHNICITY
|
|
|
|
|
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_Stu_AcadLevel
|
varchar(30)
|
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.
|
|
|
MD_FAidPers_InstanceName
|
int
|
Used internally, by the warehouse. If you are looking to classify awards by financial aid year, DO NOT USE THIS FIELD. Use instead the MD_CarlTerm dimension, which should be linked to the facts you are summarizing.
If you don't understand what's being said here, consult the data warehouse team. You probably need a quick refresher on the dimensional model and how it's properly used in reports.
|
|
|
MD_FAidPers_AppStatus
|
varchar(32)
|
Current application/applicant status. The applicant goes through a series of status values, until, ultimately, the status changes to "moved to student" (at least for those students that end up matriculating and becoming full-fledged Carleton students). These status values are different from those used by Admissions staff. The values, though, are supplied by Admissions, and whatever massaging or changing occurs is done on their end, other than the "move to student" (which - check on this - may be done in Colleague).
Possible values include: Refused, Unknown, Paid, Yes to Waitlist, Accepted, Paid Def From Prev Year, Withdrew After Decision, Paid/Withdrew, Complete Application, Withdrew Pre Decision, Paid/Deferred, Not Applied, Wait List, Moved to Student, Part 1 Received, Deferred ED, Part 2 Received, Applied, No to Waitlist, WL Closeout. Older Colleague reports exclude 'Not Applied' rows in this field, although our data entry practice seems, of late, not to have been to enter people who would need to be marked 'Not Applied.' As a result, specific exclusion of this value is typically not necessary.
This field corresponds to APPL.CURRENT.STATUS in Colleague (which corresponds to the student status in Recruitment Plus). Compare APPL.ADMIT.STAT, which corresponds to the 'decision type' in Recruitment Plus, and is available here as MD_FAidPers_EntryDecisionType.
The value of this field defaults to 'Unknown' although this value should not normally be encountered.
|
|
|
MD_FAidPers_EntryDecisionType
|
varchar(32)
|
Application "decision type" of a student when he/she applied (e.g., fall early decision, winter early decision, regular decision, fall transfer, winter transfer). This field corresponds, roughly, to the 'requirement rule' in Recruitment Plus. The values are supplied by Admissions, and whatever massaging or changing of values in R+ that occurs is done on their end before being placed in Colleague (where this field's data is taken from).
This field corresponds to APPL.ADMIT.STAT in Colleague, but ultimately the source of this data is Recruitment Plus.
The value of this attribute defaults to 'Unknown' although this value should not normally be encountered.
For reports that list only fall-entry freshmen, and exclude (for example) transfers, this attribute offers a useful way of filtering out unwanted data.
|
|
|
MD_FAidPers_ParentAGI
|
varchar(32)
|
Adjusted gross income of parents. Note that there are two AGI values used at Carleton, one the 'institutional' and the other the 'federal' (the latter is received from the feds via ISIR). Anyway, what we do here is use the 'institutional' AGI if it's available; otherwise the federal. If neither is available, we mark it as 'Unknown'.
|
|
|
MD_FAidPers_ParentAGIRod
|
|
Same as MD_FAidPers_ParentAGI, except discretized into wider ($40,000) groups.
Created at the request of Jim Fergerson, on behalf of Rod Oto, to make certain statistical analyses easier.
|
|
|
MD_FAidPers_Need
|
varchar(32)
|
Overall financial aid need of student. Note that there are two 'need' values used at Carleton, one the 'institutional,' another 'federal' (received from the feds via ISIR). Anyway, what we do here is use the 'institutional' need if it's available; if that's not available, we use the federal. If neither is available, we mark it as 'Unknown'.
This is not the same as SA.NEED in Colleague.
|
|
|
MD_FAidPers_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_FAidPers_EnteringCohortYear
|
int
|
Anticipated entry cohort (for applicants); actual cohort for existing students.
Because not all applicants are actual students, and because they therefore may not have started (yet) at Carleton - and also just because of the general vicissitudes of human life - the cohort year here may change, or may not even be valid, strictly speaking (e.g., in the case of an applicant offered aid who does not matriculate).
|
|
|
MD_FAidPers_GraduatingCohortYear
|
int
|
Anticipated year of graduation (class year). This number is provisional for many aid applicants, who may, e.g., not even have matriculated yet.
If a class year is not available in Colleague (in either the student or applicant files), we simply add five years to the anticipated start date. This enables us to get complete finanancial aid numbers, even when a class year is missing.
What this means, practically speaking, is that if you use this field to calculate, e.g., total indebtedness for a given class, you will get wrong numbers unless you also remove 'E' awards (tentative awards) and keep the A, C, G, and P awards. Why? Because if you take out the E's, and include the A (accepted), C (certified), G (guaranteed), and P (provisional) awards, you'll get only people who matriculated, and the matriculants are the ones you typically want to calculate total indebtedness for.
To be sure you actually report only on those who have actually graduated, do not use this dimension. Use the degree student dimension and filter on a field that is only populated for graduates.
|
|
|
MD_FAIDPERS_YEARATCARLETON
|
|
|
|
|
MD_FAidPers_CarletonTotalExpenses
|
varchar(32)
|
Carleton cost of attendance. This number comes from the Colleague financial aid files. It is not reflective of federal reporting rules, but rather is used internally by SFS for its reporting purposes. The method of calculating this figure may change from year to year, as business process changes, so it would be unwise to use it for any critical, strategic longitudinal reporting.
|
|
|
MD_FAidPers_FedFamTotIncome
|
varchar(32)
|
Total income for parents, as reported to us through ISIR.
|
|
|
MD_FAidPers_FedParentAGI
|
varchar(32)
|
Federal parent adjusted gross income. Pulled from ISIR that comes in.
Use the InstParentAGI (parent AGI) in preference to this field, unless you specifically want to analyze data from the feds (e.g., FAFSA form data).
|
|
|
MD_FAidPers_FedParentContrib
|
varchar(32)
|
Federal parent contribution. Pulled from ISIR that comes in.
|
|
|
MD_FAidPers_FedStuContrib
|
varchar(32)
|
Federal student contribution. Pulled from ISIR that comes in.
|
|
|
MD_FAidPers_FedFamContrib
|
varchar(32)
|
Federal parent + federal student contribution.
This number is from the FAFSA form (federal), and comes from Colleague XSA.FED.FAM.CONT.CS. Student Financial Services' notes for 2007 label this value as "estimated."
This figure is also known as the EFC, or the Expected Family Contribution.
|
|
|
MD_FAidPers_FedNeed
|
varchar(32)
|
Federal need calculation for student financial aid.
In the underlying Colleague files, this field may be zero or NULL - and these two values mean different things. NULL means that no value is available (yet). Zero means that it has been determined that no aid is needed. Here the need is banded (zero gets thrown in with a range of values). NULL is turned into an explicit 'Unknown'.
|
|