Dict-Physical

View VMD_Instructor

Card of view VMD_Instructor

Name	VMD_Instructor
Comment	See MD_Employee in the DimsPeople and DimsEmployee packages. This view/dimension is a cut-down version of MD_Employee, with sensitive HR-related information removed, and faculty/instruction-related attributes left intact, and without a lot of current rows (rows are kept based on whether they join to MF_TeachSection). Please note that this dimension is not well suited to direct querying, without a join to a fact table. Without a fact-table join, you'll get multiple rows per instructor, due to what are called type-II slowly changing dimensions, which facilitate the recording of history, to support longitudinal analysis. Please also be aware that people's departments (or primary departments) change periodically, and so reports divided up by instructors' primary department may not produce expected results. That is, faculty may appear in multiple places.
Dimensional Type	Dimension

SQL query of the view VMD_Instructor

select
mde.MD_CollPers_SurrogateKey,
MD_Colpers_ColleagueID,
MD_Pers_CommonName,
MD_Pers_Surname + ', ' + MD_Pers_GivenName AS MD_Pers_LastThenFirstName,
MD_Pers_GivenName,
MD_Pers_Surname,
MD_Pers_Gender,
MD_Pers_PrimaryAffiliation,
MD_Pers_Citizenship,
MD_Pers_Agerange,
MD_Pers_HomeAddrCity,
MD_Pers_HomeAddrUSState,
MD_Pers_HomeAddr5DigitUSZip,
MD_Pers_HomeAddrUSRegionFIPS,
MD_Pers_HomeAddrUSRegionCarleton,
MD_Pers_HomeAddrCountry,
MD_Pers_HomeAddrMilesFromCampus,
MD_Emp_AlienStatus,
MD_Pers_IsNonResidentAlien,
MD_Pers_IsOfUnknownRaceEthnicity,
MD_Pers_IsHispanicOrLatino,
MD_Pers_Race,
MD_Pers_OldPrimaryRace,
MD_Pers_OldIPEDSEthnicity,
MD_PERS_IPEDSEthnicity,
MD_Pers_IsVeteran,
MD_Emp_PrimaryDept,
MD_Emp_YearsOfServiceRange,
MD_Emp_Status,
MD_Emp_IPEDSPositionCategory,
MD_Emp_PrimaryPositionType,
MD_Emp_PrimaryPositionClass,
MD_Emp_StartYear,
MD_Emp_StartAcademicYear,
MD_Emp_IsFullTime,
MD_Emp_WorkloadRange,
MD_Emp_NativeLanguage,
MD_Emp_SecondLanguage,
MD_Emp_HighestDegree,
MD_Emp_HighestDegreeYear,
MD_Emp_HighestDegreeInstitution,
MD_Emp_HighestDegreeIsTerminal,
MD_Emp_HighestDegreeType,
MD_Fac_IsCurrentFaculty,
MD_Fac_FacultyWorkload,
MD_Fac_IsRegularFaculty,
MD_Fac_IsAdjunctFaculty,
MD_Fac_IsEmeritusFaculty,
MD_Fac_IsVisitingFaculty,
MD_Fac_IsPEARFaculty,
MD_Fac_IsOnLeave,
MD_Fac_LeaveType,
MD_Fac_TermsOnLeaveThisAcadYear,
MD_Fac_TermsTeachingForYear,
MD_Fac_NumberOfAdviseesRange,
MD_Fac_PaidCourseLoad,
MD_Fac_IsTenured,
MD_Fac_TenureStatus,
MD_Fac_IsTenureTrack,
MD_Fac_YearsTenuredRange,
MD_Fac_RankOfFirstHire,
MD_Fac_PrimaryPositionRank,
MD_Fac_IsLeaveReplacement,
MD_Emp_RowStart,
MD_Emp_RowEnd,
MD_Emp_RowIsCurrent
from
VMD_Employee mde
LEFT OUTER JOIN
(SELECT distinct MD_CollPers_SurrogateKey FROM MF_TeachSection) mft
ON mde.MD_CollPers_SurrogateKey = mft.MD_CollPers_SurrogateKey
LEFT OUTER JOIN
(SELECT distinct MD_CollPers_SurrogateKey FROM MF_FacultyNonTeachingAssignment) mfn
ON mde.MD_CollPers_SurrogateKey = mfn.MD_CollPers_SurrogateKey
where
(mft.MD_CollPers_SurrogateKey IS NOT NULL
OR mfn.MD_CollPers_SurrogateKey IS NOT NULL
OR mde.MD_ColPers_ColleagueID = '0000000'
OR mde.MD_Fac_IsCurrentFaculty = 'Yes')

List of incoming view references of the view VMD_Instructor

ViewReference_28

VMF_FacultyNonTeachingAssignment

ViewReference_71

VMF_TeachSection

ViewReference_98

VMF_FacultyWorkloadActivity

ViewReference_223

VMF_AdvisementCurrent

View

ViewsAcademicRecord

List of diagrams containing the view VMD_Instructor

ViewsAcademicRecordDiagram

List of permissions of the view VMD_Instructor

SELECT,VIEW DEFINITION

AcadRecordReader

SELECT,VIEW DEFINITION

FacultyReader

List of view columns of the view VMD_Instructor

MD_COLLPERS_SURROGATEKEY

MD_COLPERS_COLLEAGUEID

MD_PERS_COMMONNAME

MD_PERS_LASTTHENFIRSTNAME

MD_PERS_GIVENNAME

MD_PERS_SURNAME

MD_PERS_GENDER

MD_PERS_PRIMARYAFFILIATION

MD_PERS_CITIZENSHIP

MD_PERS_AGERANGE

MD_PERS_HOMEADDRCITY

MD_PERS_HOMEADDRUSSTATE

MD_PERS_HOMEADDR5DIGITUSZIP

MD_PERS_HOMEADDRUSREGIONFIPS

MD_PERS_HOMEADDRUSREGIONCARLETON

MD_PERS_HOMEADDRCOUNTRY

MD_PERS_HOMEADDRMILESFROMCAMPUS

MD_EMP_ALIENSTATUS

MD_PERS_ISNONRESIDENTALIEN

MD_PERS_ISOFUNKNOWNRACEETHNICITY

MD_PERS_ISHISPANICORLATINO

MD_PERS_RACE

MD_PERS_OLDPRIMARYRACE

MD_PERS_OLDIPEDSETHNICITY

Like MD_Pers_OLDPrimaryRace, but with alien status marked as a race. This is a standard IPEDS reporting (as opposed to "collection") category.

MD_PERS_IPEDSETHNICITY

Like MD_Pers_Race, but with alien status marked as a race, and with 'Hispanic' also marked as a race, counter to newer census bureau standards, and counter to best practice in data warehousing (which does not mix categories like alien status and race, particularly if one should be a type-II SCD and another a type-II). This attribute, however, instantiates a standard IPEDS reporting (as opposed to "collection") category.

Values here are unreliable before 2010. This attribute exists as a calculated field only in views.

MD_PERS_ISVETERAN

MD_EMP_PRIMARYDEPT

MD_EMP_YEARSOFSERVICERANGE

MD_EMP_STATUS

MD_EMP_IPEDSPOSITIONCATEGORY

MD_EMP_PRIMARYPOSITIONTYPE

MD_EMP_PRIMARYPOSITIONCLASS

MD_EMP_STARTYEAR

MD_EMP_STARTACADEMICYEAR

MD_EMP_ISFULLTIME

MD_EMP_WORKLOADRANGE

MD_EMP_NATIVELANGUAGE

MD_EMP_SECONDLANGUAGE

MD_EMP_HIGHESTDEGREE

MD_EMP_HIGHESTDEGREEYEAR

MD_EMP_HIGHESTDEGREEINSTITUTION

MD_EMP_HIGHESTDEGREEISTERMINAL

MD_EMP_HIGHESTDEGREETYPE

MD_FAC_ISCURRENTFACULTY

MD_FAC_FACULTYWORKLOAD

MD_FAC_ISREGULARFACULTY

MD_FAC_ISADJUNCTFACULTY

MD_FAC_ISEMERITUSFACULTY

MD_FAC_ISVISITINGFACULTY

MD_FAC_ISPEARFACULTY

1 2

Home