Dict-Physical

Table MD_Section

Card of table MD_Section

Name	MD_Section
Dimensional Type	Dimension
Comment	Academic section dimension. Note that sections are defined per term, unlike courses, which have no particular term(s) associated with them. That is, a section is a unique instance of a course occurring in a particular term. Courses themselves have their own dimension, MD_Course, which generally should not be used (most metrics at Carleton are done based on sections). MD_Section MUST be used in place of MD_Course when analyzing distro requirements, because although it's possible to define distro requirements fullfilled (and overlays) at the course level, these can be overridden (and frequently are overridden) at the section level. For this reason, the data warehouse does not even store distros at the course level, even though they can be assigned there in Colleague, as a kind of default. Please note that although sections are defined per term, Carleton has multi-term terms. In other words, we have names for double terms, whole-year terms, etc. To regularize/normalize this situation, we've allocated information in this dimension down to the regular-term level (regular terms include Summer, Fall, Winter, and Spring). Hence a three-term off-campus studies program occuring in, say, AY/11, will be broken out here into three rows, one for each regular term during which the program is going on (FA, WI, SP). There is no other reasonable way to do this.

Check constraint name of the table MD_Section

CKT_MD_SECTION

List of incoming references of the table MD_Section

CrossListingToPrimaryListing

MD_Section

MD_Sect_PrimarySectSurrogateKey

Relationship_44

MF_SectionEnrollment

MD_Sect_SurrogateKey

Relationship_50

MF_SectionEnrollmentByTerm

MD_Sect_SurrogateKey

Relationship_52

MF_SectionSeatsByTerm

MD_Sect_SurrogateKey

Relationship_55

MF_SectionGradeByTerm

MD_Sect_SurrogateKey

Relationship_58

MF_DistroGroup

MD_Sect_SurrogateKey

Relationship_70

MF_TeachSection

MD_Sect_SurrogateKey

Relationship_77

MF_SectionMeetsAt

MD_Sect_SurrogateKey

Relationship_134

MF_Booking

MD_Sect_SurrogateKey

Relationship_157

MF_SectionSeats

MD_Sect_SurrogateKey

Relationship_159

MF_SectionGrade

MD_Sect_SurrogateKey

List of outgoing references of the table MD_Section

CrossListingToPrimaryListing

MD_Section

MD_Sect_PrimarySectSurrogateKey

List of diagrams containing the table MD_Section

DimsAcademicRecordDiagram

List of columns of the table MD_Section

MD_Sect_SurrogateKey

<None>

numeric

MD_Sect_ID

Unique identifier for a section. Taken from COURSE.SECTIONS.ID in Colleague. Treat this as an opaque identifier, even if in older Colleague revisions it was human readable (not just a numeric sequence).

Do not use in reports. Use the section name (or "description" as it's called in Colleague) instead.

DW_SectionID

varchar(24)

MD_Sect_CurrentStatus

Current section status: Active, Cancelled, Expired. If no status is available (which should never happen, except in the case of an anonymous, catch-all course) "Unknown."

'Expired' is not strictly a Colleague status. It occurs here when a Section that formerly appeared in Colleague is no longer visible to the data warehouse (e.g., because it is being filtered out).

DW_CodeDesc

varchar(32)

MD_Sect_PrimarySectID

If course is a cross-listing, this field contains the ID of the primary section (i.e., of a different section). For the primary section, the MD_Sect_PrimarySectID will match the MD_Sect_ID. This field should not be exposed to users. It is primarily useful in in constructing recursive relationships in OLAP cubes.

DW_SectionID

varchar(24)

MD_Sect_PrimarySectSurrogateKey

Circular link back to MD_Section. May be null. Links a cross-listed course back to the primary section it's affiliated with. Usually will be NULL, since most sections are not cross listings. Most rows in MD_Section, that is, are for the primary section.

<None>

numeric

MD_Sect_CourseID

The ID for a course (not section). Has become an opaque numeric identifier (in more recent Colleague revisions).

Do not use in reports. Use the course name (or "description" as it's called in Colleague) instead.

DW_CourseID

varchar(16)

MD_Sect_IsPrimaryListing

True if the course in question is a primary listing, and not a cross listing. The value of this attribute is always the inverse of the value of MD_Sect_IsCrossListing.

When used with MF_SectionEnrollmentByTerm and MF_SectionSeatsByTerm, this field is not needed, because those tables link only to rows in this dimension for primary listings.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsCrossListing

False if the course in question is a primary listing, and not a cross listing. The value of this attribute is always the inverse of the value of MD_Sect_IsPrimaryListing.

When used with MF_SectionEnrollmentByTerm and MF_SectionSeatsByTerm, this field is not needed, because those tables link only to rows in this dimension for primary listings.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_Name

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.

DW_SectionName

varchar(32)

MD_Sect_Title

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.

DW_CodeDescLong

varchar(64)

MD_Sect_CIPCode

Six-digit (xxx.xxxx) CIP identifier code for this course (note that the code is in fact assigned at the course, not section, level).

The purpose of the Classification of Instructional Programs (CIP) is to provide a taxonomic scheme that will support the accurate tracking, assessment, and reporting of fields of study and program completions activity. CIP was originally developed by the U.S. Department of Education's National Center for Education Statistics (NCES) in 1980, with revisions occurring in 1985 and 1990. The 2000 edition (CIP-2000) is the third revision of the taxonomy and presents an updated taxonomy of instructional program classifications and descriptions.

<None>

varchar(12)

MD_Sect_CourseName

This is the course-level "description," i.e., a human-readable version of the course ID.

Use this instead of the course ID in reports.

DW_CourseName

varchar(16)

MD_Sect_SectionNumber

Number of section, e.g., 1, 2, etc. Taken from COURSE.SECTIONS->SEC.COURSE.NO in Colleague. 9999 if unknown (should not occur in Colleague; will be used only for a generic/unknown course).

Note that the value here isn't strictly a 'number' since labs (for example) will have an 'L' at the end.

DW_CodeDescShort

varchar(16)

MD_Sect_Level

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).

DW_CodeDesc

varchar(32)

MD_Sect_LevelCode

Section level 01 - 20 (as of 2009). 99 if unknown or NULL in Colleague (should not happen).

More explanation on levels is available in the documentation on the MD_Sect_Level attribute.

DW_SectionLevelCode

char(5)

MD_Sect_AcademicLevelCode

UG, NG, SG: Don't use these. Use the academic level (description/name) instead, not these opaque codes.

DW_SectionLevelCode

char(5)

MD_Sect_AcademicLevel

Descriptions corresponding to internal Colleague academic-level codes UG (Undergraduate), SG (Summer Graduate), and ND (Non-Degree). Please use these instead of the codes. Not everyone looking at reports (now or in the future) will know what the codes mean.

DW_AcademicLevel

varchar(30)

MD_Sect_ReportingYear

Academic year to which a given section is reckoned. This only becomes a difficult question if we have, say, an Off-Campus Studies course that goes for multiple regular terms (i.e., a "long" term) that crosses academic year boundaries, as, e.g., sometimes happens with off-campus courses occurring in South America, where the academic year goes from our winter until our fall.

Normally the reporting year is easy to determine for a section, because sections occur in single terms that occur in single academic years.

DW_YearAtCarleton

int

MD_Sect_ReportingTerm

The term when the course officially occurs (and to which grades and other administrativa are tied).

The value here may be that of a multi-term "term," e.g., 09/WS (Winter/Spring 2009). The format here is the short term format, 00/XX.

DW_TermShortName

char(7)

MD_Sect_StartDate

Start date of course. This value may be NULL in Colleague, meaning that we have entered no value here. NULL values typically pose problems in queries and reports, so use of this field is discouraged.

<None>

datetime

MD_Sect_EndDate

<None>

datetime

MD_Sect_RegularStartTerm

"Regular" term in which section is taught (fall, winter break, winter, spring, spring break, or summer). Taken from COURSE.SECTIONS->SEC.TERM in Colleague.

For multi-term courses, this term is the "normal" (single-term) term in which the course started.

For courses that occur on breaks, the value here will be "Unknown"

DW_TermShortName

char(7)

MD_Sect_DurationInTerms

Count of regular FA, WI, SP, SU terms that a given section overlaps with. Will always be a positive integer.

This attribute allows us a very rough way to classify sections by length.

If the section is offered in a single standard term (like 09/FA) then the value here will be 1, as will be the case for most courses. If, however, a section begins in winter term, but ends in spring term, then it is a two-term course (with a reporting term of 00/WS, where 00 is the year), and the value here will be 2.

If a section begins in the middle of one term, and ends in the middle of the next, the value here will be 2.

For half-term and other such sections, the value here is 1. Break (e.g., WB) courses get 0. The value assigned to break courses is wholly arbitrary, and could just as easily have been a 1, like a half-term course.

Defaults to 1.

<None>

int

MD_Sect_Location

Campus or country in which section meets/is located. E.g., Carleton, St. Olaf, Denmark, Austria, Multi-Nation, etc. Defaults to Unknown (which corresponds to NULL in Colleague).

In the case of country names, the text here may or may not correspond to the exact strings used for countries in addresses. Do not join on this attribute, therefore.

DW_Addr_Country

nvarchar(64)

MD_Sect_TimeSlot

Traditional "slots" used at Carleton do not exist in Colleague per se, unfortunately, so we don't populate this field (yet). If we did populate it, this is how it would work:

This field holds the course time slot (2a, 2c, 4a, etc.; on the meaning of 'Other' and 'Unknown', see below).

Most Carleton courses fall into well-established time slots that meet at certain times of the day on MWF, and on another TTh. Not all courses fall into a single slot, and not all courses use the slots at all (Colleague uses times, not slots, internally). But many administrators like the shorthand of slots.

If a course does not fall into a traditional slot, we label it 'Other' here. If it falls into more than one slot, we take the primary one (or if this can't be determined, we pick one of the slots arbitrarily). If no time is listed at all, we label it 'Unknown'.

The vagueness of this field makes it unsuitable for really detailed or systematic reporting.

DW_CourseTimeSlot

varchar(7)

MD_Sect_PrimarySubject

Courses have not only departments, but subjects. This field holds the full primary subject name for a given course (not the subject "code").

DW_CodeDescLong

varchar(64)

MD_Sect_PrimaryDepartment

The long name of the primary department that this course is listed under (DEPARTMENT.1.DESC).

DW_Department

varchar(64)

MD_Sect_MinCredit

Some courses can be taken for variable credits. This attribute stores the minimum credits for a given course. Cf. MaxCredit. Taken from COURSE.SECTIONS->SEC.MIN.CRED in Colleague.

Should be a whole number from 1 to 6, but allows for fractional amounts, should we need these.

<None>

float

MD_Sect_MaxCredit

Some courses can be taken for variable credits. This attribute stores the maximum credits for a given course. Cf. MinCredit. Taken from COURSE.SECTIONS->SEC.MAX.CRED in Colleague.

Should be a whole number from 1 to 6, but allows for fractional amounts, should we need these.

<None>

float

MD_Sect_CreditType

One of: INSTITUTIONAL, Quarter hours, Off-Campus Study, or Unknown (the last of which corresponds to NULL in Colleague).

DW_CodeDesc

varchar(32)

MD_Sect_Load

Load values for courses are based on the idea that a typical load for a typical 1-trimester course = 1, and that if a faculty member is on a full teaching load, the total of load values for all their courses for a given academic year should = 5 (or 6 for those on a six-course load).

Note that the way in which loads are entered changed in 2009. This field reflects the new system.

<None>

float

MD_Sect_Campus

Not used; see MD_Sect_Location.

DW_Campus

varchar(16)

MD_Sect_CapacityRange

Maximum enrollment or "capacity" of a class. Note that this max is only a "soft" max. Empirically it is clear that students may be registered for a course in numbers greater than the max theoretically allows.

Note that this value has been discretized or "banded" into ranges, to make it useful in reports. We use the US News ranges/buckets.

This is the capacity for a given section, NOT the global capacity across cross-listed and non-cross-listed version of the same section. As such it will sometimes (practically speaking) be wrong, because of Carleton business practice, which (as of 2009) dictates setting the capacity for a freshman-sophomore-only course to a blank/NULL until we know if it's full or not (at which point we move juniors and seniors off a separate waitlist). The bottom line is that we'll get a zero here, or 'Unknown', when in reality we should have a genuine capacity.

<None>

varchar(32)

MD_Sect_EnrollmentRange

Like the MD_EnrolledStuCountRange, but uses a slightly different discretization method (this one goes with the US News ranges/buckets).

DW_CodeDesc

varchar(32)

MD_Sect_GlobalCapacityRange

This attribute is just like the section capacity range, except that it gives us the global capacity, across all cross-listings for a given section.

It should only be used in conjunction with a filter that removes cross-listed courses (MD_Sect_IsCrossListing = 'No' or, alternatively, MD_Sect_IsPrimaryListing = 'Yes').

If you don't add the filter, you'll get artificial inflation of actual capacities.

DW_CodeDesc

varchar(32)

MD_Sect_DroppedStuCountRange

Count of students who have dropped a section.

DW_DropCountRange

varchar(16)

MD_Sect_EnrolledStuCountRange

Ranges used for reporting class sizes, as specified by the Registrar (" 0 - 9," "10 - 19," "20 - 29," and so on, padded on the left with a space, if need be, to ensure correct sorting). Exact student counts should be taken from a fact table. These are ranges for reporting.

Taken from COURSE.SECTIONS->SEC.ACTIVE.STUDENTS in Colleague.

Cf. the MD_Sect_Enrollment_Range, which uses the US News ranges (which are slightly different from the ones used here).

DW_DropCountRange

varchar(16)

MD_Sect_FroshPriority

"Yes" if freshmen are given priority for enrollment in a given section (typically this means that other students are waitlisted until freshmen have had their chance to register for it). "No" otherwise.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_SophomorePriority

"Yes" if sophomores are given priority for enrollment in a given section (typically this means that other students are waitlisted until sophomores have had their chance to register for it). "No" otherwise.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_NewStudentPriority

"Yes" if new students are given priority for enrollment in a given section (typically this means that other students are waitlisted until new students have had their chance to register for it). "No" otherwise.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_MainInstructionalMethod

One of several designations indicating how a course is taught, e.g.: Dance, Off Campus Studies, Intercollegiate Sport, Integrative Exercise, etc.

Technically, this attribute comes from COURSE.SEC.FACULTY, and there can be more than one method per course, because there can be more than one instructor per course. In such cases, we rate methods by credit hours by the faculty load associated with the course. The method associated with the highest load figure wins. There is no other good way to do this, other than to construct a "sidecar" dimension. This attribute isn't widely enough used to warrant such extremes.

DW_CodeDesc

varchar(32)

MD_Sect_IsLabSection

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsPartialTerm

"Yes" if a course is a half-term course or a winter/spring break course. Not all courses at Carleton have a traditional start and/or end date. Some start in what we'd think of as the middle of the term. See also MD_Sect_MaxCredits (normally the number of credits is a better measure to be using for analytical purposes).

"No" otherwise.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsMultiTerm

Some courses span multiple terms, particularly certain off-campus studies programs. Note that in Colleague there are special names for multi-term terms (AY, SW, WS, etc.). In other words, terms don't have a fixed length in Colleague, and they may overlap.

If a course overlaps multiple 'regular' (FA, WI, SP, SU) terms, then this field will be set to 'Yes'. 'No' otherwise.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsStOlaf

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsNoExploration

This is a post-2009/10 curriculum requirement. It is the new equivalent of "No distribution credit" in the old system.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsLabScience

"Yes" if a section fulfills the post-2009/10 "Science with Lab" distro requirement. "No" otherwise.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsFormalStatReasoning

"Yes" if a section fulfills the Formal Statistical Reasoning distribution requirement. "No" otherwise.

This is post-2009/10 curriculum requirement.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsSocialInquiry

"Yes" if a section fulfills the Social Inquiry distribution requirement. "No" otherwise.

This is a post-2009/10 curriculum requirement.

DW_YesOrNoNOTNULL

char(3)

MD_Sect_IsArtsPractice

"Yes" if a section fulfills the Arts Practice distribution requirement. "No" otherwise.

This is a post-2009/10 curriculum requirement.

DW_YesOrNoNOTNULL

char(3)

1 2

Home