Dict-Physical

View VMF_StudentDemographics

Card of view VMF_StudentDemographics

Name	VMF_StudentDemographics
Comment	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.
Dimensional Type	Fact

SQL query of the view VMF_StudentDemographics

select distinct
mds.MD_COLLPERS_SURROGATEKEY,
ISNULL(mdf.MD_COLLPERS_SURROGATEKEYFAIDPERS, mdf2.MD_COLLPERS_SURROGATEKEYFAIDPERS)
    AS MD_COLLPERS_SURROGATEKEYFAIDPERS,
MD_DATE_DATEINTEGER,
mds.MD_COLPERS_COLLEAGUEID,
1 as MD_STUDDEM_COUNT
from
-- Only look at dates around census time
DBO.VMD_DATESONORAROUNDCENSUSTIME mdd
-- 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'
where
-- 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

List of outgoing view references of the view VMF_StudentDemographics

ViewReference_172

VMD_DatesOnOrAroundCensusTime

ViewReference_173

VMD_DegreeStudent

ViewReference_174

VMD_FinancialAidPerson (Shortcut)

List of referenced objects of the view VMF_StudentDemographics

MD_FinancialAidPerson

VMD_DatesOnOrAroundCensusTime

VMD_DegreeStudent

List of diagrams containing the view VMF_StudentDemographics

ViewsStudentDiagram

List of permissions of the view VMF_StudentDemographics

SELECT,VIEW DEFINITION

AcadRecordReader

List of view columns of the view VMF_StudentDemographics

MD_CollPers_SurrogateKey

numeric

MD_COLLPERS_SURROGATEKEYFAIDPERS

MD_DATE_DATEINTEGER

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_STUDDEM_COUNT

Home