MF_AwardAmount

Fact

This is the award fact table. The grain of this table is one row per financial aid applicant per award per term per award status per last-updated date. This fact table should not be exposed directly to users (on the reasons why, see IMPORTANT NOTES below). Numbers in this table are not really usable before 10 June 2009, because this is when aid information started being snapshotted on a daily basis. Award numbers are altogether missing before the 1997-98 award year.

Awards are uniquely identified by student + term + award + status + last updated date. So, e.g., we may have a "Byrd Scholarship" going to student "John J. Doe" for term "09/SP" with a status of "Accepted" and a last updated date of 05 May 2010. That award may come out of a GL account, and if so it will have a code associated with it (e.g., 10000000001325). But the link to the GL account is optional (i.e., the foreign key may link to a row in the GL dimension with a dummy account code, '?????????????').

The grain of this fact table is per award, per student, per term, per status value. It will typically need to be aggregated up to the aid-year level for Student Financial Service reporting.

Note that if we are analyzing graduation rates for Pell/Stafford/Neither aid recipients (as per IPEDS), student demographic cubes/star schemas should be used instead. See, e..g, VMF_StudentDemographics. See also the 'Student Demographic' OLAP cubes.

Status values here are available through the MD_AwardStatus dimension (and not, e.g., via a field in this table).

IMPORTANT NOTES:

This table should be exposed directly to users only if the user has considerable knowledge of the data warehouse. Why? Because it's essentially a slowly changing fact table. That is, it's a cheat. It could have been a periodic snapshot table because the business requirement is to allow Student Financial Services to ask "where are we now with our financial aid numbers today vs. last year this same (or other) date?" and to do general longitudinal analysis. Such a requirement is best met with a periodic snapshot. But a periodic snapshot, especially a daily one, has lots of rows in it that never change. It gets very big very fast. So instead of using a periodic snapshot table, we keep this transactional table that tracks updates to financial aid by student, term, award, status, and last updated date (taking the last updated date into account lets us genuinely track changes).

Another reason this table should not be carelessly exposed to users is that it contains optional foreign keys, in particular foreign keys referencing the MD_DegreeStudent, MD_GLAccount, and MD_Prospect dimensions. These relationships are optional (and may link to dummy "Unknown" records in the above-mentioned three dimensions), because not all aid applicants are in fact degree students (some don't matriculate). Also, we don't always get a Recruitment Plus PKID for all our aid applicants, making it impossible to link reliably with MD_Prospect. Finally, not all grants, loans, etc. are associated with accounts in our general ledger. Hence the optional relationship there. If users try to make joins to these dimensions, they may get incomplete or misleading numbers. Reports must therefore be constructed for them - carefully - by IT staff. Or the users must be trained well.

A final reason why this table should be treated with care is that the data it contains becomes spotty before June, 2009. This is when the daily snapshots began. Before that, only "final" numbers are available for a given student/term/award (no status or update history). This will throw off reports that track where we are now vs. where we were at various dates in the past, with respect to awards - at least if the report goes back further than June 2009.

Note that this table links to the most recent row for a given degree student (MD_DegreeStudent) and a given prospective student (MD_Prospect). This conforms to institutional reporting practice. If a student switches class years, e.g., at Carleton, when we generate the cumulative debt reports, we reckon the student's debt with his or her new class year, not the old one. Links to the MD_FinancialAidPerson table, though, are done historically. I.e., rows in this fact table link to the row in MD_FinancialAidPerson that was current/active at the time when the row was added/updated. Because the grain of the MD_FinancialAidPerson table is per instance (i.e., per aid year), the most recent row will not necessarily be the one for the current financial aid year. The subtleties here are generally not important, and user generally shouldn't worry about them. The bottom line is that reports will come out the way mid-level and upper-level executives expect them, but not necessarily the way we would construct them if we were doing this work for the first time.