Dict-Physical

View VMD_Section

Card of view VMD_Section

Name	VMD_Section
Comment	See MD_Section in the StarsAcademicRecord package. This view is very important for reporting on class seat availability vs. filled seats (i.e., enrolled students).
Dimensional Type	Dimension

SQL query of the view VMD_Section

select
MD_Sect_SurrogateKey,
MD_Sect_ID,
MD_Sect_CurrentStatus,
MD_Sect_PrimarySectID,
MD_Sect_PrimarySectSurrogateKey,
MD_Sect_CourseID,
MD_Sect_IsPrimaryListing,
MD_Sect_IsCrossListing,
CASE WHEN MD_Sect_Name LIKE '%.WL_' OR MD_Sect_Name LIKE '%.WL__' THEN 'Yes' ELSE 'No' END AS MD_Sect_IsWaitList,
MD_Sect_Name,
MD_Sect_Title,
MD_Sect_CIPCode,
MD_Sect_CourseName,
MD_Sect_SectionNumber,
CASE
WHEN ISNUMERIC (SUBSTRING(MD_Sect_SectionNumber,1,1)) <> 1 THEN 'Unknown' -- First char should be a digit
WHEN ISNUMERIC (MD_Sect_SectionNumber) = 1 AND MD_Sect_SectionNumber >= 1000 THEN 'Senior' -- Summer graduate programs
WHEN SUBSTRING (MD_Sect_SectionNumber,1,1) = '0' THEN 'Intro'
WHEN SUBSTRING (MD_Sect_SectionNumber,1,1) = '1' THEN 'Intro'
WHEN SUBSTRING (MD_Sect_SectionNumber,1,1) = '2' THEN 'Upper'
ELSE 'Senior'
END as MD_Sect_IntroUpperOrSenior,
CASE
WHEN ISNUMERIC (SUBSTRING(MD_Sect_SectionNumber,1,1)) <> 1 THEN 9 -- First char should be a digit
WHEN ISNUMERIC (MD_Sect_SectionNumber) = 1 AND MD_Sect_SectionNumber >= 1000 THEN 3 -- Summer graduate programs
WHEN SUBSTRING (MD_Sect_SectionNumber,1,1) = '0' THEN 1
WHEN SUBSTRING (MD_Sect_SectionNumber,1,1) = '1' THEN 1
WHEN SUBSTRING (MD_Sect_SectionNumber,1,1) = '2' THEN 2
ELSE 3
END as MD_Sect_IntroUpperOrSeniorCode,
MD_Sect_Level,
MD_Sect_LevelCode,
MD_Sect_AcademicLevel,
MD_Sect_AcademicLevelCode,
MD_Sect_ReportingYear,
MD_Sect_ReportingTerm,
MD_Sect_StartDate,
MD_Sect_EndDate,
MD_Sect_RegularStartTerm,
MD_Sect_DurationInTerms,
MD_Sect_Location,
MD_Sect_TimeSlot,
MD_Sect_PrimarySubject,
MD_Sect_PrimaryDepartment,
MD_Sect_MinCredit,
MD_Sect_MaxCredit,
MD_Sect_CreditType,
MD_Sect_Load,
MD_Sect_Campus,
MD_Sect_CapacityRange,
MD_Sect_EnrollmentRange,
CASE
WHEN MD_Sect_EnrollmentRange = 'Unknown' THEN 'Unknown'
WHEN MD_Sect_EnrollmentRange >= ' 20 - 29' THEN 'Yes'
ELSE 'No'
END AS MD_Sect_EnrollmentIs20OrOver,
MD_Sect_GlobalCapacityRange,
MD_Sect_DroppedStuCountRange,
MD_Sect_EnrolledStuCountRange,
MD_Sect_FroshPriority,
MD_Sect_SophomorePriority,
MD_Sect_NewStudentPriority,
MD_Sect_MainInstructionalMethod,
MD_Sect_IsLabSection,
MD_Sect_IsPartialTerm,
MD_Sect_IsMultiTerm,
MD_Sect_IsNoExploration,
MD_Sect_IsLabScience,
MD_Sect_IsFormalStatReasoning,
MD_Sect_IsSocialInquiry,
MD_Sect_IsArtsPractice,
MD_Sect_IsHumanisticInquiry,
MD_Sect_IsLitOrArtsAnalysis,
MD_Sect_IsArgumentAndInquiry,
MD_Sect_IsWritingRequirement,
MD_Sect_IsWritingRequirement2,
MD_Sect_IsQuantitativelyRich,
MD_Sect_IsInternationalStudies,
MD_Sect_IsInterculturalDomestic,
MD_Sect_IsPhysicalEducation,
MD_Sect_IsPEIntercollegiate,
MD_Sect_IsPEClubCourse,
MD_Sect_IsIntegrativeExercise,
MD_Sect_IsLanguageProficiency,
MD_Sect_IsOldArtsAndLiterature,
MD_Sect_IsOldHumanities,
MD_Sect_IsOldSocialScience,
MD_Sect_IsOldNoDistributionCredit,
MD_Sect_IsOldMathAndScience,
MD_Sect_IsOldRecognitionOfDifference,
MD_Sect_IsOldCultureOrLinguistics,
MD_Sect_IsOldWritingRich,
MD_Sect_Types,
MD_Sect_IsOCS,
MD_Sect_IsOWB,
CASE
WHEN MD_Sect_IsOCS = 'Yes' THEN 'Yes'
WHEN MD_Sect_IsOWB = 'Yes' THEN 'Yes'
ELSE 'No'
END AS MD_Sect_IsOWBOrOCS,
MD_Sect_IsStOlaf,
MD_Sect_IsCarletonPlacement
from
MD_Section

List of incoming view references of the view VMD_Section

ViewReference_31

VMF_SectionSeatsByTerm

ViewReference_33

VMF_SectionEnrollmentByTerm

ViewReference_64

VMF_SectionGradeByTerm

ViewReference_67

VMF_DistroGroup

ViewReference_70

VMF_TeachSection

ViewReference_137

VMF_Booking

ViewReference_155

VMF_SectionEnrollment

ViewReference_156

VMF_SectionSeats

ViewReference_165

VMF_SectionGrade

List of referencing views of the view VMD_Section

VW_GradesByInstructorWithDynamicSecurity

List of shortcuts of the view VMD_Section

View

View

View

List of diagrams containing the view VMD_Section

ViewsAcademicRecordDiagram

List of permissions of the view VMD_Section

SELECT,VIEW DEFINITION

AcadRecordReader

SELECT,VIEW DEFINITION

FacultyReader

SELECT,VIEW DEFINITION

SpaceReader

List of view columns of the view VMD_Section

MD_Sect_SurrogateKey

numeric

MD_Sect_ID

varchar(24)

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.

MD_Sect_CurrentStatus

varchar(32)

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

MD_Sect_PrimarySectID

varchar(24)

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.

MD_Sect_PrimarySectSurrogateKey

numeric

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.

MD_Sect_CourseID

varchar(16)

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.

MD_Sect_IsPrimaryListing

char(3)

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.

MD_Sect_IsCrossListing

char(3)

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.

MD_SECT_ISWAITLIST

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_Sect_CIPCode

varchar(12)

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.

MD_Sect_CourseName

varchar(16)

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.

MD_Sect_SectionNumber

varchar(16)

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.

MD_SECT_INTROUPPERORSENIOR

Naive classifier, which throws sections into one of three baskets (Intro. Upper, or Senior) depending on the first digit of the course number. THIS IS NOT THE SAME THING AS THE COURSE LEVEL.

MD_SECT_INTROUPPERORSENIORCODE

Naive classifier, which throws sections into one of three baskets (1, 2, or 3) depending on the first digit of the course number (1 = intro, 2 = upper, 3 = senior, i.e., 300 or above course numbers). THIS IS NOT THE SAME THING AS THE COURSE LEVEL.

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_LevelCode

char(5)

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.

MD_Sect_AcademicLevel

varchar(30)

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.

MD_Sect_AcademicLevelCode

char(5)

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

MD_Sect_ReportingYear

int

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.

MD_Sect_ReportingTerm

char(7)

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.

MD_Sect_StartDate

datetime

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.

MD_Sect_EndDate

datetime

MD_Sect_RegularStartTerm

char(7)

"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"

MD_Sect_DurationInTerms

int

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.

MD_Sect_Location

nvarchar(64)

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.

MD_Sect_TimeSlot

varchar(7)

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.

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_PrimaryDepartment

varchar(64)

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

MD_Sect_MinCredit

float

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.

MD_Sect_MaxCredit

float

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.

MD_Sect_CreditType

varchar(32)

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

MD_Sect_Load

float

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.

MD_Sect_Campus

varchar(16)

Not used; see MD_Sect_Location.

MD_Sect_CapacityRange

varchar(32)

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.

MD_Sect_EnrollmentRange

varchar(32)

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

MD_SECT_ENROLLMENTIS20OROVER

'Y' if a course's enrollment is 20 or more. 'No' otherwise - with the occasional 'Unknown' if in fact the enrollment is unknown.

This field is calculated based on MD_Sect_Enrollment, and would not ordinarily be needed, really, except that the proportion of classes with enrollments of less than 20 turns out to be a key cross-institutional metric, and is cited often in US News rankings literature. Staff in the Office of the Dean of the College at Carleton therefore need quick access to a binary yes/no field that allows them to see this proportion.

MD_Sect_GlobalCapacityRange

varchar(32)

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.

MD_Sect_DroppedStuCountRange

varchar(16)

Count of students who have dropped a section.

MD_Sect_EnrolledStuCountRange

varchar(16)

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

MD_Sect_FroshPriority

char(3)

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

MD_Sect_SophomorePriority

char(3)

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

MD_Sect_NewStudentPriority

char(3)

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

MD_Sect_MainInstructionalMethod

varchar(32)

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.

MD_Sect_IsLabSection

char(3)

MD_Sect_IsPartialTerm

char(3)

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

MD_Sect_IsMultiTerm

char(3)

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.

MD_Sect_IsNoExploration

char(3)

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

MD_Sect_IsLabScience

char(3)

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

1 2

Home