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

varchar(32)

Federal student contribution. Pulled from ISIR that comes in.

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

MD_FAidPers_ProfFamTotIncome

varchar(32)

Family total income, as used in various aid calculations. This number (like all the "Prof" numbers) comes over from the College Board via CSS Profile. Compare the "Fed" (Federal) and "Inst" (institutional) numbers.

MD_FAidPers_ProfParentNetWorth

varchar(32)

Net worth of parents' investments. The College Board does a calculation of the parents' net worth and enters it. We receive it as part of CSS Profile. This number is not entered by the parents.

MD_FAidPers_IProParHomeEquity

varchar(32)

Amount of equity parents hold in their home.

MD_FAidPers_ProfStuContrib

varchar(32)

Student contribution, i.e., amount student is expected to pay, as used in aid calculations. This number (like all the "Prof" numbers) comes over from the College Board via CSS Profile. Compare the "Fed" (Federal) and "Inst" (institutional) numbers.

This field is based on XSA.PROF.S.CONT in Colleague, and is marked as "new" as of 2007 in Student Financial Services' notes. As a result it may be of limited use in longitudinal analysis.

MD_FAidPers_InstParentAGI

varchar(32)

Parent adjusted gross income, as calculated for institutional aid purposes. Compare this with, e.g., "Fed" (federal) and "Prof" (CSS Profile/College Board) numbers.

Note that students don't always spend all four years at the same "parent AGI" income band, so if you break up your debt reports by parent AGI, your average debt across the AGI bands will be smaller than you'd expect, based on the overall average debt across all AGI bands. Again, this is because students are moving from one AGI to another. So if you break up a debt report by AGI, please break it up by fiscal/aid year, or calcuate averages on a per-student, per-year basis.

MD_FAidPers_InstStuContrib

varchar(32)

Student contribution, as calculated for institutional aid purposes. Compare this with, e.g., "Fed" (federal) and "Prof" (CSS Profile/College Board) numbers.

This number will not be valid before 2005-06. Its place in Colleague changed.

MD_FAidPers_InstFamContrib

varchar(32)

Family contribution, as calculated for institutional aid purposes. Compare this with, e.g., "Fed" (federal) and "Prof" (CSS Profile/College Board) numbers. Note that the "Inst" family contribution includes the non-custodial parent contribution, as well as parent and student contributions.

This field is based on SA.INST.FC in Colleague, and only goes back a few years prior to the initial data warehouse deployment (2009). Previously this data was housed in XSA.COLL.FC.1.

MD_FAidPers_NumberInFamily

varchar(32)

Count of family members (i.e., number in parent's family). Used for TRIO reporting and other such things. Always the same as XSA_IFAF_P_NO_EXEMPT in the Colleague feed as of 2009.

Note that Colleague also contains a count of people in the student's family, if the student is not a dependent, but this value is not typically useful at Carleton and therefore is not included in the data warehouse.

MD_FAidPers_IsNonPellSubsRecipient

char(3)

"Yes" if a student received a federally subsidized (Stafford) loan for the current aid year AND was not a Pell recipient. The value of this attribute is not to be relied on before August of 2011, because it was only added, and verified as correct, at that point. Before then it may be accurate as of the most recent row for a given student in a given aid year. But it does not keep history before August of 2011. Before then it is to be treated as a type-I SCD. This will not harm reporting, because we did not need this attribute before 2011-12.

Note well: Unlike MD_FAIDPERS_HASINTITUTIONALAIDNEED, this field has been implemented as a type-II SCD, meaning that it keeps history within a given financial aid year. Reason: IRA must typically report based on an as-of date. Hence we need to preserve values for this attribute historically.

Graduation data must now be disaggregated, for certain reporting purposes, by things like gender, race/ethnicity, etc., and whether a student received a federally subsidized grant (not including Pell Grants). This yes/no attribute will be "yes" for students who received such non-Pell subsidized grants, and "no" otherwise. This attribute is valid for the current academic year, so watch the RowStart and RowEnd attributes - i.e., make sure you have the 'as of' date you need for reporting purposes.

Note that this attribute is 'Yes' only if a given student actually received the loan(s) in question. To receive a loan, according to IPEDS, means, at least for the purposes of the IPEDS Student Financial Aid (SFA) component, that the loan was awarded to, and accepted by, a student.

MD_FAIDSPERS_IPEDSAIDSTATUS

IPEDS requires that we report graduation rates, by number of years required, for people receiving Pells, non-Pell unsubsidized Stafford recipients, and others. This attribute facilitates that reporting.

Generally this attribute should be used in conjunction with Student Demographic cubes and the VMF_StudentDemographics fact table. It may be used in conjunction with Student Financial Services/Financial Aid cubes and star schemas as well, but in that case they will not yield the IPEDS numbers needed for graduation rate reporting.

MD_FAidPers_RowStart

datetime

Date when a given row in this dimension begins to be valid. Use this attribute if you want to reconstruct history. See also the RowEnd attribute. Talk to the data warehousing team if you have questions.

MD_FAidPers_RowIsCurrent

bit

Flag indicating whether a given row is the most current one for a given financial aid applicant. Will be 1 if so; 0 otherwise. Typically users will not need to use this attribute. In technical terms, this attribute is not needed when joining data through a fact table, unless one is reconstructing history. If one is using this dimension without a join through a fact table, this attribute may be needed - but in that case, use caution and be sure talk with the data warehouse team if you have questions.