MF_SectionMeetsAt

Fact

Holds days/times when sections meet, and places where they meet.

This fact table is organized as a periodic snapshot, with rows representing ten minute increments, going on throughout the day.

It is based on data in Colleague about course meeting times and section begin/end dates. Since that data does not include information on days when classes are suspended (e.g., reading days), that information is not captured, and reading - and other such - days are treated as regular class days.

The key field in this table is percent of capacity for a given physical space. This value is snapshotted because a given room may potentially vary in capacity over time, depending on what renovation work is going on.

Technical details on populating MF_SectionMeetsAt fact table:

0) Look at the existing data in this MF_SectionMeetsAt table; find the date/time of the most recent row
1) Set our start datetime for extraction from ODS to the next time evenly divisible by ten minutes after that

2) Join section meeting dimension in ODS with X_DW_NIGHTLY_CRS_SECTION
3) Filter out non-active and non-primary sections
4) Take only sections whose start date is <= start datetime and whose end date is >= our start datetime
5) For each Room + start time + section combination:

a) Look up current section enrollment
b) Look up room capacity (not section capacity)
c) Calculate percent utilization (a / b)

6) Using the above data (a-c), starting with our start datetime and ending with the current datetime
as of the start of package execution,

a) Enter 3a-3c above into appropriate rows
b) Look up and store surrogate keys for
- Date
- Time
- Section
- Room
- Building
c) Add the audit key

7) At the end, delete rows from fact table where section is no longer active and/or primary

On potential uses of this fact table, and other tables associated with room usage, see:

http://www.provost.umich.edu/space/resources/classroom.html