Table MF_TeachSection

Card of table MF_TeachSection

Name

MF_TeachSection

Dimensional Type

Fact

Comment

Records instances of employees (mainly faculty) teaching sections (i.e., course instances).

Normally if you use this table in a join YOU WILL TYPICALLY BE JOINING ON THE COLLEAGUE ID, not the surrogate key link to MD_Employee or VMD_Instructor, and you'll probably just want those rows in MD_EMPLOYEE or MD_INSTRUCTOR where MD_Emp_RowIsCurrent = 1. But it all depends on whether you want current or historical information on the instructor.

Grain is one row per employee (instructor), per section, per single term (SU, FA, WB, WI, SP, SB). Most sections are one regular term in duration, so in general the term coincides with the section's term. But in cases where a section occupies a multi-term term (e.g., AY [all year], WS [winter and spring], etc.), the data here is re-broken-up into multiple terms, so that our analyses can go by regular, non-overlapping SU, FA, etc. terms.

It should be emphasized that this fact table *does* include WB and SB terms, unlike most of the registration-related fact tables (which exist primarily for calculating whether we have enough seats on campus during regular terms).

MIscellaneous other info: This table does not include waitlist sections (the "WLs"). Also, all instructor workloads are summed across all primary/cross-listed sections (because of our normal business practice, which is to divide the workload by the number of primary/cross listings for a given section). But the link here is always to the primary course, not the cross listing. We do this because cross lists make it hard to get right numbers when analyzing seat counts. The negative consequence is that sometimes cross-lists have different primary subjects than the primary section, and those are not available here.

This table is mainly used as a linking or bridge table, since the relationship between employee and section is, unfortunately (but necessarily) many-to-many.

Check constraint name of the table MF_TeachSection

CKT_MF_TEACHSECTION


List of outgoing references of the table MF_TeachSection

Name

Parent Table

Foreign Key Columns

Relationship_28

MD_Employee (Shortcut)

MD_CollPers_SurrogateKey

Relationship_28

MD_Employee (Shortcut)

MD_CollPers_SurrogateKey

Relationship_70

MD_Section (Shortcut)

MD_Sect_SurrogateKey

Relationship_70

MD_Section (Shortcut)

MD_Sect_SurrogateKey

Relationship_73

MD_Course (Shortcut)

MD_Course_SurrogateKey

Relationship_73

MD_Course (Shortcut)

MD_Course_SurrogateKey

Relationship_74

MD_CarlTerm (Shortcut)

MD_CarlTerm_SurrogateKey

Relationship_74

MD_CarlTerm (Shortcut)

MD_CarlTerm_SurrogateKey


List of referencing views of the table MF_TeachSection

Name

Code

VW_GradesByInstructorWithDynamicSecurity

VW_GRADESBYINSTRUCTORWITHDYNAMICSECURITY


List of diagrams containing the table MF_TeachSection

Name

StarsAcademicRecordDiagram

StarsFacultyDiagram


List of columns of the table MF_TeachSection

Name

Comment

Domain

Data Type

Length

MD_Sect_SurrogateKey

 

<None>

numeric

 

MD_CarlTerm_SurrogateKey

 

<None>

numeric

 

MF_TeachSect_ColleagueID

Used for internal load/bookeeping purposes, by data warehouse ETL processes. Do not expose to users, except possibly in the case of Analysis Services cubes, where we're looking for a unique faculty count.

DW_ColleagueID

char(7)

7

MD_CollPers_SurrogateKey

 

<None>

numeric

 

MD_Course_SurrogateKey

 

<None>

numeric

 

MF_TeachSect_Load

This field contains the value used by the Dean of the College and other to determine what percentage of their contractually obligated service/teaching load a given faculty member is fulfilling by teaching a given class. Typically the value here will be 1 (meaning 1 course equivalent). If an instructor is teaching a half-term course, or the course is being team taught, that value will naturally be less than 1.

Workload serves as the sole basis for calculating credit towards completion of teaching obligations. Conterintuitively, the "percent" value attached to instructor + section combinations (instructor X teaches section Y) is not used for these calculations.

This data, i.e., the workload has not been kept consistently and accurately enough over time, before 2010-11, to make it truly useful for longitudinal analysis, although we do the best we can here to make it consistent (on which, see below).

Part of the reason for the inconsistency is that the workload value has been implemented in different ways at different times. In 2008-09 and before it was a decimal value, typically 16.67. Since most faculty were at that point on a six-course load, this number was contrived so that if multiplied by six, it would equal 100, i.e., a full load. When we moved to a mixed five/six-course load (2009-10 and later), the scale of values entered was changed to simple integers. In order to calculate what portion of his/her contractually obligated load a given instructor is fulfilling one must, in the 2009-10 and later world, divide the aggregated load, across all his/her courses and releases for the year, by the instructor's contractual load (e.g., if the contractual load is 5, and the instructor is teaching 4 courses plus a big committee assignment [= 1 course], then we get 5/5, i.e., a full load).

Here we convert old values (8.33, 16.67, etc) to new values (.5, 1, etc.), but the conversion process is imperfect.

Note that workload here means something different from what it means to HR staff (who use workloads the way HR staffs in other institutions use FTE values). This difference becomes particularly important when it comes to folks who are both faculty and staff. The workload value here will only reflect the academic work load, not the FTE value across all appointments/positions, as maintained by HR.

<None>

float

 

MF_TeachSect_CreditsTaught

Calculated as *MaxCredits (section) / course duration in terms) * percent value assigned instructor for this course (e.g., .5 if they were teaching 50% of the course). For multi-term courses, credits are allocated evenly across all terms, even if this is not what actually happened in the course.

<None>

float

 

MF_TeachSect_Percent

Floating point number between 0 and 1 indicating what percentage of a particular class is being taught by a particular instructor.

This data is not kept consistently and accurately enough over time to make it truly useful. Instead we attach workloads to specific instructor and course pairs. Credit towards completion of teaching obligations is actually given according to these workloads, not on the basis of any calculations involving the percentage.

Do not expose to users therefore.

<None>

float

 

MF_TeachSect_Method

Degenerate dimension that distinguishes lab instruction from lecture, etc. Since 1996 this data is more or less reliable.

Note that during the 90s (the precise end date is unclear, probably before 1996) we used to combine lecture and lab sections often, but mark the faculty role in those sections multiple times, each one with a different instructional method and another associated workload.

Current practice is to create separate sections for labs, so that the relationship between instructor and method is 1 to 1. In cases where this is not so, as described above, we select one method arbitrarily.

DW_CodeDesc

varchar(32)

32

MF_TeachSect_Count

 

<None>

int

 

MF_TeachSect_AuditKey

 

DW_AuditKey

bigint

 

MF_TeachSect_Timestamp

 

<None>

timestamp

 

MD_Sect_SurrogateKey

 

<None>

numeric

 

MD_CarlTerm_SurrogateKey

 

<None>

numeric

 

MF_TeachSect_ColleagueID

Used for internal load/bookeeping purposes, by data warehouse ETL processes. Do not expose to users, except possibly in the case of Analysis Services cubes, where we're looking for a unique faculty count.

DW_ColleagueID

char(7)

7

MD_CollPers_SurrogateKey

 

<None>

numeric

 

MD_Course_SurrogateKey

 

<None>

numeric

 

MF_TeachSect_Load

This field contains the value used by the Dean of the College and other to determine what percentage of their contractually obligated service/teaching load a given faculty member is fulfilling by teaching a given class. Typically the value here will be 1 (meaning 1 course equivalent). If an instructor is teaching a half-term course, or the course is being team taught, that value will naturally be less than 1.

Workload serves as the sole basis for calculating credit towards completion of teaching obligations. Conterintuitively, the "percent" value attached to instructor + section combinations (instructor X teaches section Y) is not used for these calculations.

This data, i.e., the workload has not been kept consistently and accurately enough over time, before 2010-11, to make it truly useful for longitudinal analysis, although we do the best we can here to make it consistent (on which, see below).

Part of the reason for the inconsistency is that the workload value has been implemented in different ways at different times. In 2008-09 and before it was a decimal value, typically 16.67. Since most faculty were at that point on a six-course load, this number was contrived so that if multiplied by six, it would equal 100, i.e., a full load. When we moved to a mixed five/six-course load (2009-10 and later), the scale of values entered was changed to simple integers. In order to calculate what portion of his/her contractually obligated load a given instructor is fulfilling one must, in the 2009-10 and later world, divide the aggregated load, across all his/her courses and releases for the year, by the instructor's contractual load (e.g., if the contractual load is 5, and the instructor is teaching 4 courses plus a big committee assignment [= 1 course], then we get 5/5, i.e., a full load).

Here we convert old values (8.33, 16.67, etc) to new values (.5, 1, etc.), but the conversion process is imperfect.

Note that workload here means something different from what it means to HR staff (who use workloads the way HR staffs in other institutions use FTE values). This difference becomes particularly important when it comes to folks who are both faculty and staff. The workload value here will only reflect the academic work load, not the FTE value across all appointments/positions, as maintained by HR.

<None>

float

 

MF_TeachSect_CreditsTaught

Calculated as *MaxCredits (section) / course duration in terms) * percent value assigned instructor for this course (e.g., .5 if they were teaching 50% of the course). For multi-term courses, credits are allocated evenly across all terms, even if this is not what actually happened in the course.

<None>

float

 

MF_TeachSect_Percent

Floating point number between 0 and 1 indicating what percentage of a particular class is being taught by a particular instructor.

This data is not kept consistently and accurately enough over time to make it truly useful. Instead we attach workloads to specific instructor and course pairs. Credit towards completion of teaching obligations is actually given according to these workloads, not on the basis of any calculations involving the percentage.

Do not expose to users therefore.

<None>

float

 

MF_TeachSect_Method

Degenerate dimension that distinguishes lab instruction from lecture, etc. Since 1996 this data is more or less reliable.

Note that during the 90s (the precise end date is unclear, probably before 1996) we used to combine lecture and lab sections often, but mark the faculty role in those sections multiple times, each one with a different instructional method and another associated workload.

Current practice is to create separate sections for labs, so that the relationship between instructor and method is 1 to 1. In cases where this is not so, as described above, we select one method arbitrarily.

DW_CodeDesc

varchar(32)

32

MF_TeachSect_Count

 

<None>

int

 

MF_TeachSect_AuditKey

 

DW_AuditKey

bigint

 

MF_TeachSect_Timestamp

 

<None>

timestamp

 


List of indexes of the table MF_TeachSection

Name

Unique

Cluster

Primary

Foreign Key

Alternate Key

Table

MF_TEACHSECTION_PK

TRUE

TRUE

TRUE

FALSE

FALSE

MF_TeachSection

MF_TEACHSECT_COLLID_IDX

FALSE

FALSE

FALSE

FALSE

FALSE

MF_TeachSection

RELATIONSHIP_28_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_TeachSection

RELATIONSHIP_73_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_TeachSection

MF_TEACHSECTION_PK

TRUE

TRUE

TRUE

FALSE

FALSE

MF_TeachSection

MF_TEACHSECT_COLLID_IDX

FALSE

FALSE

FALSE

FALSE

FALSE

MF_TeachSection

RELATIONSHIP_28_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_TeachSection

RELATIONSHIP_73_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_TeachSection


List of keys of the table MF_TeachSection

Name

Identifier_1

Identifier_1


Previous
Home
Next