|
-- This view is intended for use by individual faculty. It does no weighting of grades by credits attempted/completed.
-- It utilizes row-level security to limit rows returned to those classes that the logged-in user actually taught.
--
SELECT
mde.MD_Pers_Commonname MD_Inst_CommonName,
mct.MD_CarlTerm_FiscalOrAidYear,
mct.MD_CarlTerm_ShortName,
SUBSTRING(ISNULL(mct.MD_CarlTerm_ShortName, '??/??'), 4, 2) AS MF_SectGrade_TermWithoutYear,
mds.MD_Sect_Level,
mds.MD_Sect_PrimaryDepartment,
mds.MD_Sect_PrimarySubject,
mds.MD_Sect_Name,
mds.MD_Sect_Title,
mdd.MD_Pers_CommonName,
mdd.MD_DStu_GraduatingCohortYear,
mdd.MD_DStu_Major1,
mdd.MD_DStu_Major2,
mdg.MD_Grade_Letter,
-- Averages for these figures will be unusable for real audit purposes; they are unweighted, for one thing
mfs.MF_SectGrade_AssignedGrade,
mfs.MF_SectGrade_TranscriptedGrade,
mfs.MF_SectGrade_AttemptedCredit,
mfs.MF_SectGrade_CompletedCredit,
-- The following two values (CASE...) are usable for calculating weighted average grades
CASE
WHEN mfs.MF_SectGrade_AttemptedCredit IS NULL THEN NULL
WHEN mfs.MF_SectGrade_TranscriptedGrade IS NULL THEN NULL
ELSE mfs.MF_SectGrade_TranscriptedGrade * mfs.MF_SectGrade_AttemptedCredit
END AS MF_SectGrade_GradeTimesCreditsAttempted,
CASE
WHEN mfs.MF_SectGrade_TranscriptedGrade IS NULL THEN NULL
WHEN mfs.MF_SectGrade_AttemptedCredit IS NULL THEN NULL
ELSE mfs.MF_SectGrade_AttemptedCredit
END AS MF_SectGrade_GradedCreditsAttempted,
1 AS MF_SectGrade_Count
FROM
MF_SectionGrade mfs
INNER JOIN VMD_SECTION mds
ON mfs.MD_SECT_SURROGATEKEY = mds.MD_SECT_SURROGATEKEY
INNER JOIN MD_CARLTERM mct
ON mfs.MD_CARLTERM_SURROGATEKEY = mct.MD_CARLTERM_SURROGATEKEY
INNER JOIN MD_DEGREESTUDENT mdd
ON mfs.MD_COLLPERS_SURROGATEKEY = mdd.MD_COLLPERS_SURROGATEKEY
INNER JOIN MD_GRADE mdg
ON mfs.MD_GRADE_SURROGATEKEY = mdg.MD_GRADE_SURROGATEKEY
INNER JOIN MF_TEACHSECTION mft
ON mds.MD_SECT_SURROGATEKEY = mft.MD_SECT_SURROGATEKEY
AND mfs.MD_CARLTERM_SURROGATEKEY = mft.MD_CARLTERM_SURROGATEKEY
INNER JOIN MD_EMPLOYEE mde
ON mft.MD_COLLPERS_SURROGATEKEY = mde.MD_COLLPERS_SURROGATEKEY
-- UtilityTables.TB_NetIDEmpID maps the user's login ID to a Colleague ID
INNER JOIN UtilityTables.TB_NetIDEmpID utne
ON mde.MD_COLPERS_COLLEAGUEID = utne.TB_NetEmp_ColleagueID
-- Join to current employee information
INNER JOIN MD_EMPLOYEE mde2
ON mft.MF_TEACHSECT_COLLEAGUEID = mde2.MD_COLPERS_COLLEAGUEID
AND mde2.MD_EMP_ROWISCURRENT = 1
WHERE
-- Make sure instructor is a current Carleton employee
mde2.MD_EMP_STATUS = 'Carleton Employee'
-- Perform row-level security checks, using SUSER_SNAME() (the user's AD login)
AND LOWER(utne.TB_NetEmp_NetID) =
CASE
-- Goerwitz (as an administrator) sees just one prof's grades (for testing)
WHEN SUSER_SNAME() = 'ADS\adminrgoerwit' THEN 'sottaway'
-- SSIS1 gets all data for all faculty, as do key data warehouse users
WHEN IS_MEMBER('ADS\dw_ReadAll') = 1 THEN utne.TB_NetEmp_NetID
WHEN IS_MEMBER('ADS\dw_AcademicRecord') = 1 THEN utne.TB_NetEmp_NetID
WHEN SUSER_SNAME() = 'ADS\SSIS1' THEN utne.TB_NetEmp_NetID
-- Faculty get to see only grades for classes they taught
WHEN IS_MEMBER('ADS\Faculty') = 1 THEN SUSER_SNAME()
-- Everyone else gets nothing
ELSE 'NobodyKnowsTheTroubleIveSeen'
END
|
|
MD_Pers_CommonName
|
nvarchar(64)
|
Full legal name, with middle name or initial. Does not include honorifics unless these are part of the legal name and are available in the system from which the data is derived. This field is mandatory and has no default.
Note that this field is 'calculated' for prospective students (i.e., put together out of the given name, middle name, and surname). Because the middle name is not well maintained for prospective students, the common name will be "dirty" as well. It is recommended that this field be avoided, therefore, for prospective students. Also, note: If you believe names are needed for prospective students, then consider also whether the report you are creating should be generated off of Recruitment Plus, and not the data warehouse.
Common names in Colleague should be very clean, and as a result common names in the data warehouse for students, faculty, and staff may be relied upon. If you need names for a report, however, consider reporting directly off of Colleague. Typically the data warehouse is best for aggregate analysis, and if you are using individual names, you may find other sources of information to be better.
Common names in Advance are also relatively clean. Hence also are common names in the warehouse for alumni and other constituencies maintained by External Relations.
|
|
|
MD_CarlTerm_FiscalOrAidYear
|
char(7)
|
Fiscal/financial aid year (the so-called INSTANCE in Colleague financial aid files). The year here is in YYYY-YY format. Defaults to 'Unknown'.
Note that although the financial aid year begins in July, the academic year begins in either July or September, depending on what report you are generating. Traditionally, fall is seen as the start of the academic year at Carleton. Also all 'AY' (academic year) terms in Colleague begin in September. Also, most students, professors, and regular folk all think of the fall term as term 1.
Choose whether to use the AidYear or the AcademicYear, depending on what you are reporting on and who your audience is.
|
|
|
MD_CarlTerm_ShortName
|
char(7)
|
Term name, like 07/FA, 08/WI, 08/SP, 08/SU, etc. Colleague naming conventions apply. Others should be converted to this format.
|
|
|
MF_SECTGRADE_TERMWITHOUTYEAR
|
|
|
|
|
MD_Sect_Level
|
varchar(32)
|
Text or description corresponding to the level code, e.g., "OCS Non-Carleton Program," "300 lvl non-comps/non-ind," "Credit Lab."
Typically this attribute is used as a filter. It is used, e.g., to remove all but 100, 200, and 300 level non-comp courses, leaving us with regular classes we need to take into account when predicting seat counts in regular classrooms and number of sections needed. This attribute may also be used to filter out levels not associated with normal letter grades, e.g., if we're analyzing grade inflation or grading patterns.
To use this field properly may require the help of the Registrar.
Unless this field is too large, use this in preference to the level codes (two-digit numbers that only a retricted range of individuals on campus know the meaning of).
|
|
|
MD_Sect_PrimaryDepartment
|
varchar(64)
|
The long name of the primary department that this course is listed under (DEPARTMENT.1.DESC).
|
|
|
MD_Sect_PrimarySubject
|
varchar(64)
|
Courses have not only departments, but subjects. This field holds the full primary subject name for a given course (not the subject "code").
|
|
|
MD_Sect_Name
|
varchar(32)
|
Course section name, i.e., human-readable unique identifier for section (e.g., "OCP.142.00"). Taken from COURSE.SECTIONS->SEC.NAME in Colleague.
The section ID has in recent Colleague revisions become an opaque identifier, so use the section name (not the ID) for most reporting.
|
|
|
MD_Sect_Title
|
varchar(64)
|
Title of a section, e.g., "Equilibrium and Analysis Lab." This is to be distinguished from the section "name" (e.g., CHEM.230L.54).
Corresponds to SEC.SHORT.TITLE in Colleague.
|
|
|
MD_Pers_CommonName
|
nvarchar(64)
|
Full legal name, with middle name or initial. Does not include honorifics unless these are part of the legal name and are available in the system from which the data is derived. This field is mandatory and has no default.
Note that this field is 'calculated' for prospective students (i.e., put together out of the given name, middle name, and surname). Because the middle name is not well maintained for prospective students, the common name will be "dirty" as well. It is recommended that this field be avoided, therefore, for prospective students. Also, note: If you believe names are needed for prospective students, then consider also whether the report you are creating should be generated off of Recruitment Plus, and not the data warehouse.
Common names in Colleague should be very clean, and as a result common names in the data warehouse for students, faculty, and staff may be relied upon. If you need names for a report, however, consider reporting directly off of Colleague. Typically the data warehouse is best for aggregate analysis, and if you are using individual names, you may find other sources of information to be better.
Common names in Advance are also relatively clean. Hence also are common names in the warehouse for alumni and other constituencies maintained by External Relations.
|
|
|
MD_DStu_GraduatingCohortYear
|
int
|
Actual, or projected, graduating class year for a given student. The procedure for assigning a value to this field is complex, and if a student makes slower or faster than anticipated academic progress, their class year may change periodically.
This attribute is similar to, but not quite the same, as the pref_class_year in Advance (which denotes the class an alum prefers to be associated with, and can be virtually anything; for alumni relations purposes we just don't care what class year a person says they want to be associated with).
Students without a graduation year get assigned a nonsense year of 9999 here, in lieu of NULL or an empty string.
Note that having a graduating cohort year does not mean that a given student has actually graduated. The value here is just the group they should (have) graduate(d) as part of. Also, note that lacking a graduating cohort year may not indicate that a student hasn't graduated. For example, for about a decade (up until 2011) Carleton routinely removed the class year when a student was reported to have died. This practice ceased during the 2010-11 academic year, but the data cleanup may not have been complete in its aftermath, and there is always the possibility of other such practices being introduced in the future. There are also a number of cases where a student receives a graduating cohort year that does not reflect the actual year in which they graduated. For example, a student that entered in the fall of 2003 and graduated in 2010 may in some cases keep their original anticipated class year of 2007.
Do not use this field to limit results to graduates. To be sure to get only graduates in a given query, filter on the MD_DStu_EnrollmentStatus field. Select only students who have graduated. As long as this field is being proprly mainained (and not, e.g., converted to a different code if a student dies), this will limit results, correctly, to graduates.
|
|
|
MD_DStu_Major1
|
varchar(64)
|
First student major. To search for all students who have a given major or set of majors, use the MajorList attribute and a SQL LIKE statement. If you're not sure how to do this, ask for help from the DW team.
Defaults to 'Not Applicable' (if the major is unavailable, e.g., because the student hasn't yet started, or has not declared a major, although typically we code students who have not yet declared a major as 'undecided' for major1).
|
|
|
MD_DStu_Major2
|
varchar(64)
|
First student major. To search for all students who have a given major or set of majors, use the MajorList attribute and a SQL LIKE statement. If you're not sure how to do this, ask for help from the DW team.
Defaults to 'Not Applicable' (if the major is unavailable, e.g., because the student hasn't yet started, or has not declared a second major).
|
|
|
MD_Grade_Letter
|
varchar(3)
|
Letter grade, e.g., A, A-, B, B+, F, S, NC, CR, etc. Also, grades like EXT, DRP, CNT, and so on. Typically grades other than A, A-, B, etc. and S/CR/NC should be filtered out. Note that starred grades like S* indicate that the student requested that the grade (a pass/fail grade typically) be given in place of a standard A, A-, B, etc. letter grade. The un-starred S, CR, NC, etc. grades indicate that the pass/fail grade was mandated, and not requested by the student.
|
3
|
|
MF_SectGrade_AssignedGrade
|
float
|
|
|
|
MF_SectGrade_TranscriptedGrade
|
float
|
|
|
|
MF_SectGrade_AttemptedCredit
|
float
|
|
|
|
MF_SectGrade_CompletedCredit
|
float
|
|
|
|
MF_SECTGRADE_GRADETIMESCREDITSATTEMPTED
|
|
|
|
|
MF_SECTGRADE_GRADEDCREDITSATTEMPTED
|
|
|
|
|
MF_SECTGRADE_COUNT
|
|
|
|