This is what data warehousing specialists call a "factless fact table," and is intended only for taking snapshots of student demographic data on and around census day (the as-of day we reserve in each term for official reporting). It brings in financial aid data also, for the entering cohort of each student. This will help us with IPEDS graduation stats (in which case, an as-of date of Aug 31 would generally be used, not census date).
The graduation stats may be divided up by student demographic categories (e.g., IPEDS ethnicity). It may also be divided up by financial aid status and years-to-graduate. Note, though, that the foreign key used to link student demographic data here with aid information utilizes the entry term to do the join. This means, in practice, that if we are dividing up a given group of students by their Pell/Stafford/Neither status, that status will be calculated based on each student's first year at Carleton, as per IPEDS data collection specifications. To calculate values by student, across multiple aid years (like collective debt by class year), the data must be structured differently. See, e.g., Student Financial Services star schemas and cubes.
This view does not correspond to any real fact table.
The performance of this view is poor, and it is intended only to serve as a basis for building (better optimized) cubes.
1 as MD_STUDDEM_COUNT
-- Only look at dates around census time
-- Only look at degree students, specifically at rows valid during the above dates
INNER JOIN VMD_DEGREESTUDENT mds
ON mdd.MD_DATE_SQLDATE >= mds.MD_DSTU_ROWSTART
AND mdd.MD_DATE_SQLDATE <= mds.MD_DSTU_ROWEND
-- Only look at aid information for the students' entering cohort year (useful for IPEDS)
LEFT OUTER JOIN MD_FINANCIALAIDPERSON mdf
ON mds.MD_COLPERS_COLLEAGUEID = mdf.MD_COLPERS_COLLEAGUEID
AND mdd.MD_DATE_SQLDATE >= mdf.MD_FAIDPERS_ROWSTART
AND mdd.MD_DATE_SQLDATE <= mdf.MD_FAIDPERS_ROWEND
AND CAST(mdd.MD_DATE_FISCALYEAR - 1 AS CHAR(4)) = mdf.MD_FAIDPERS_INSTANCENAME
-- Fill in degree students with no aid information with the 'Unknown' aid applicant's info
LEFT OUTER JOIN MD_FINANCIALAIDPERSON mdf2
ON mdf.MD_COLLPERS_SURROGATEKEYFAIDPERS IS NULL
AND mdf2.MD_COLPERS_COLLEAGUEID = '0000000'
-- Exclude weird cases where someone appears to register, then defer, and is left with an active status for the year before they matriculated
mds.MD_DSTU_COHORTCODE NOT IN ('Unknown', '')
AND SUBSTRING(mds.MD_DSTU_COHORTCODEACADYEAR, 1, 4) < mdd.MD_DATE_FISCALYEAR