Table MF_FacultyNonTeachingAssignment

Card of table MF_FacultyNonTeachingAssignment

Name

MF_FacultyNonTeachingAssignment

Dimensional Type

Fact

Comment

This dimension, MF_FacultyNonTeachingAssignment, records faculty membership on boards, committees, etc., as well as 'membership' in pseudo-commmittees. The grain is one row per faculty member, per non-teaching assignment, per term.

This dimension is particularly useful for analyzing faculty service on committees, oversight boards, etc. Sabbaticals and department-head stints are also included here (they are, that is, treated the same way as committee assignments in Colleague).

Yes, regular staff also participate on comittees, but because this work isn't built into teaching load calculations the way it is for faculty, and because workload reporting is normally confined to faculty members, we as an institution only care about faculty pariticipation.

Because assignments can cross multiple terms, workloads must be allocated across all terms covered by a given assignement, including WB and SB terms. Allocation is by term, and not proportional to the length of the term. So, say, if a faculty member gets a course release for the entire academic year for acting as a department head, and this is coded as a single assigment in Colleague, the assignment will be re-split here into five rows, one for each term: FA, WB, WI, SB, SP (and possibly SU). Workload will be divided up amongst the terms evenly.

There is no other (practical) way to do this.

Although this dimension is based on the Colleague campus organization membership file, we include only rows where the role = 'FMEMB' (i.e., 'Faculty Membership'), again because only memberships marked with this role are used in workload calculations.


Check constraint name of the table MF_FacultyNonTeachingAssignment

CKT_MF_FACULTYNONTEACHINGASSIG


List of outgoing references of the table MF_FacultyNonTeachingAssignment

Name

Parent Table

Foreign Key Columns

Reference_5

MD_Employee (Shortcut)

MD_CollPers_SurrogateKey

Relationship_4

MD_CarletonOrganization (Shortcut)

MD_CarlOrg_SurrogateKey

Relationship_5

MD_Employee (Shortcut)

MD_CollPers_SurrogateKey

Relationship_40

MD_CarlTerm (Shortcut)

MD_CarlTerm_SurrogateKey


List of referencing views of the table MF_FacultyNonTeachingAssignment

Name

Code

VMF_FacultyNonTeachingAssignment

VMF_FACULTYNONTEACHINGASSIGNMENT

VMF_FacultyNonTeachingAssignment

VMF_FACULTYNONTEACHINGASSIGNMENT


List of diagrams containing the table MF_FacultyNonTeachingAssignment

Name

StarsFacultyDiagram


List of columns of the table MF_FacultyNonTeachingAssignment

Name

Comment

Domain

Data Type

Length

MD_CarlOrg_SurrogateKey

 

<None>

numeric

 

MD_CollPers_SurrogateKey

 

<None>

numeric

 

MD_CarlTerm_SurrogateKey

 

<None>

numeric

 

MF_NonTeachAsgn_PersCollID

Personal Colleague ID (not the same as the Colleague ID, i.e., the unique identifier, designating a relationship between an activity and a person).

Compare MF_NonTeachAsgn_PersCollID.

DW_ColleagueID

char(7)

7

MF_NonTeachAsgn_ColleagueID

Key tying a person to an organization in Colleague. It comes in at the wrong grain for us to use it as an alternate key here, though, since this value remains the same if a person serves, e.g., on the same committee multiple times.

Do not expose to users.

DW_CodeDescShort

varchar(16)

16

MF_NonTeachAsgn_Workload

Name of committee.

<None>

float

 

MF_NonTeachAsgn_StartDate

Do not expose to users. Maintained purely for checking/auditing purposes, carried over from the underlying Colleague file.

<None>

datetime

 

MF_NonTeachAsgn_EndDate

Do not expose to users. Maintained purely for checking/auditing purposes, carried over from the underlying Colleague file.

<None>

datetime

 

MF_NonTeachAsgn_IsLeave

 

DW_YesOrNoNOTNULL

char(3)

3

MF_NonTeachAsgn_IsPaid

 

DW_YesOrNoNOTNULL

char(3)

3

MF_NonTeachAsgn_AuditKey

 

DW_AuditKey

bigint

 

MF_NonTeachAsgn_Timestamp

 

<None>

timestamp

 

MD_CarlOrg_SurrogateKey

 

<None>

numeric

 

MD_CollPers_SurrogateKey

 

<None>

numeric

 

MD_CarlTerm_SurrogateKey

 

<None>

numeric

 

MF_NonTeachAsgn_PersCollID

Personal Colleague ID (not the same as the Colleague ID, i.e., the unique identifier, designating a relationship between an activity and a person).

Compare MF_NonTeachAsgn_PersCollID.

DW_ColleagueID

char(7)

7

MF_NonTeachAsgn_ColleagueID

Key tying a person to an organization in Colleague. It comes in at the wrong grain for us to use it as an alternate key here, though, since this value remains the same if a person serves, e.g., on the same committee multiple times.

Do not expose to users.

DW_CodeDescShort

varchar(16)

16

MF_NonTeachAsgn_Workload

Name of committee.

<None>

float

 

MF_NonTeachAsgn_StartDate

Do not expose to users. Maintained purely for checking/auditing purposes, carried over from the underlying Colleague file.

<None>

datetime

 

MF_NonTeachAsgn_EndDate

Do not expose to users. Maintained purely for checking/auditing purposes, carried over from the underlying Colleague file.

<None>

datetime

 

MF_NonTeachAsgn_IsLeave

 

DW_YesOrNoNOTNULL

char(3)

3

MF_NonTeachAsgn_IsPaid

 

DW_YesOrNoNOTNULL

char(3)

3

MF_NonTeachAsgn_AuditKey

 

DW_AuditKey

bigint

 

MF_NonTeachAsgn_Timestamp

 

<None>

timestamp

 


List of indexes of the table MF_FacultyNonTeachingAssignment

Name

Unique

Cluster

Primary

Foreign Key

Alternate Key

Table

MF_FACULTYNONTEACHINGASSIGNMENT_PK

TRUE

FALSE

TRUE

FALSE

FALSE

MF_FacultyNonTeachingAssignment

MF_FACULTYNONTEACHINGASSIGNMENT_PK

TRUE

FALSE

TRUE

FALSE

FALSE

MF_FacultyNonTeachingAssignment


List of keys of the table MF_FacultyNonTeachingAssignment

Name

Identifier_1

Identifier_1