This fact table/view gives us a full transactional history of updates to award amounts, by award name, student, term, status, and last-updated date - and it does so for any given date, allowing us to compare financial aid amounts for the current year, as of a certain date, with those of past years, as of certain dates.
Its primary use is for helping Student Financial Services compare financial aid information (How much have we offered this year as opposed to last? How much will ultimately be accepted?) longitudinally across years, during the winter, spring, and summer.
This fact table/view is constructed so that if you ask for aid dollars as of, say, May 1, 2010, you will see aid offered/accepted/etc. for the 2010-11 fiscal/aid year, not for the then-current (2009-10) fiscal/aid year. Likewise, if you ask for aid dollars as of May 1, 2011, you will see aid offered/accepted/etc. for the 2011-12 fiscal/aid year, and not for the then-current 2010-11 fiscal/aid year. NOTE THAT THIS MAY BE VERY CONFUSING to people who are used to looking at similar fact tables used by the Business Office, where, if you ask for an account balance as of a certain date, you get it for the then-current fiscal year. The reason this table works the way it does is that in May 1 of 2010, Student Financial Services is, from a budgetary standpoint, more concerned about the following aid year than the current one (where virtually all the aid has been spent or accepted already, and we no longer need to guess about our year-end aid figures). In other words, on May 1 (or really any time from Jan through June), what Financial Aid really wants to know is where their aid expenditures/offers for the upcoming academic year stand relative to previous years, and how they will likely end up by the end of the following fiscal year. Aid can be accepted or rejected, or it may be offered to students who do or don't actually matriculate. It's therefore hard to predict. But because financial aid makes up such a large part of our budget, it's critical that we try to predict anyway. This table is built to facilitate such prediction.
This view, unlike VMF_AwardAmountCurrent and VMF_AwardAmountCurrentByYear, provides information on awards of any status, so be sure to filter on the award status if you only want the A's or some other combination of status values.
DO NOT USE THIS VIEW DIRECTLY IN QUERIES (on the reasons why, see below).
THE FIRST SNAPSHOT DATE THAT WILL GIVE VALID VALUES IS 10 June 2009. This is when the transactions started being captured every day, reliably. Before then only final amounts are available—i.e., the final outcome for every term/student/award combination (without intermediate status values and amounts).
This view is based on MF_AwardAmount, but is restructured so that it can serve as a kind of periodic snapshot. This view and its underlying table would have been implemented as a periodic snapshot, but Student Financial Services wanted the snapshot to be taken every day, and that would have meant a lot of storage - and a lot of rows being repeated that in fact have not changed. Rows would also have needed to be revisited, since award statuses change. As a result, taking a periodic snapshot would have been a loathsome task. So instead of doing a periodic snapshot, the underlying table has been implemented as a transactional table, but with a join to the date dimension that allows us (as noted) to emulate a periodic snapshot.
MF_AWAM_SNAPSHOTDATE holds the as-of date of the quasi-periodic-snapshot. It is a genuine SQL date, and may be used as-is, or as a foreign key linking it to the VMD_SnapshotDate view. This field reflects the as-of date, not the last-updated date of the award amount, which is in MD_DATE_DATEINTEGER and links to the standard date dimension-view, VMD_Date.
See also MD_AwardAmount in the StarsFinancialAid package.
Why do we use a view here, and not a genuine periodic snapshot table? Because transactions in the general ledger may be back-dated, and so rows in this table may be revisited and amounts frequently need to be recalculated. Rather than continually update rows here, we instead construct a view that does this on the fly. Doing so makes the implementation much easier, but renders the view inefficient.
Because this view is inefficiently implemented, it should ONLY be used via an Analysis Services cube.