Dict-Physical

Table MD_Room

Card of table MD_Room

Name	MD_Room
Dimensional Type	Dimension
Comment	Used for classroom, lab, etc. usage reporting. Has campus, building, and room information (building and room form a hierarchy). For the moment, the campus will always be "Carleton," seeing as we have only one campus. Can be aggregated as MD_Building (rooms aggregate as buildings for purposes of classroom usage reporting, etc.). The existence of an 'Unknown' room should make it possible to avoid NULL foreign keys. This data comes from Colleague. Information here may not be updated as frequently as other data, because room (like building) data doesn't change rapidly.

Check constraint name of the table MD_Room

CKT_MD_ROOM

List of incoming references of the table MD_Room

Relationship_79

MF_SectionMeetsAt

MD_Room_SurrogateKey

Relationship_94

MF_ReadingByHourNoAggregations

MD_Room_SurrogateKey

Relationship_109

MF_ReadingByHourSnapshot

MD_Room_SurrogateKey

Relationship_121

MF_IssueTrackingTicket

MD_Room_SurrogateKey

Relationship_129

MF_IssueTrackingAssetValue

MD_Room_SurrogateKey

Relationship_135

MF_Room

MD_Room_SurrogateKey

Relationship_139

MF_RoomAssignment

MD_Room_SurrogateKey

Relationship_143

MF_Booking

MD_Room_SurrogateKey

Relationship_202

MF_ReadingByHourAccumulating

MD_Room_SurrogateKey

Relationship_236

MF_CardTransaction

MD_Room_SurrogateKey

Relationship_257

MF_KeyedComputerInUse

MD_Room_SurrogateKey

Relationship_258

MF_KeyedComputerSoftwareUsage

MD_Room_SurrogateKey

Relationship_267

MF_KeyedComputerEvent

MD_Room_SurrogateKey

List of diagrams containing the table MD_Room

DimsLocationDiagram

List of columns of the table MD_Room

MD_Room_SurrogateKey

<None>

numeric

MD_Room_ID

Unique identifier associated with a room in Colleague. Do not expose to users.

DW_CodeDescShort

varchar(16)

MD_Room_BuildingID

Unique building identifier in Colleague. Do not expose to users.

DW_CodeDescShort

varchar(16)

MD_Room_Building

Building name (full - not a code). Note that the type of this attribute is multibyte Unicode, because we want to be able to record real building names, which may possibly contain non-ASCII characters. Taken from ROOM.BLDG.DESC in Colleague.

DW_Building

nvarchar(32)

MD_Room_Longitude

<None>

float

MD_Room_Latitude

<None>

float

MD_Room_Elevation

<None>

float

MD_Room_Shape

Geometric shape/configuration of room. Typically this information will have been exported from a CAD program of some kind.

Not currently used.

<None>

geography

MD_Room_Number

Unique identifier for a room within a building, like 001, 218, or 345. Typically, though not always, an actual number (as in a string of digits). Often ends in a letter like 'A'.

DW_CodeDescShort

varchar(16)

MD_Room_Name

Occasionally, rooms will (in addition to their numeric designation) have an actual name.

DW_CommentShort

nvarchar(128)

128

MD_Room_Floor

Floor of the building where a room is situated. Usually a number like 1, 2, or 3. Sometime a letter, like B. Often 00 (two zeroes) instead of 0.

Not to be confused with MD_Room_FloorType, which refers to the construction and material-type of the room's physical floor.

DW_CodeDescShort

varchar(16)

MD_Room_FloorType

The construction and material-type of the room's physical floor.

This is not to be confused with the MD_Room_Floor, which will typically be a number (e.g., 1, for 'first floor').

This attribute, rather, lets us know how an individual room is constructed.

DW_CodeDesc

varchar(32)

MD_Room_Accessibility

DW_CodeDesc

varchar(32)

MD_Room_AccessibilityCode

Listed in other reports as the "access code" (which can be confusing, since this field is about accessibility for people with disabilities, not about codes needed in order to access a location).

DW_CodeDesc

varchar(32)

MD_Room_PrimaryUse

What a given room is primarily used for (e.g., Student Housing). Cf. MD_Room_SecondaryUse. Taken from XROOM.PRIMARY.USE.DESC in Colleague.

DW_CodeDesc

varchar(32)

MD_Room_SecondaryUse

What a given room is secondarily used for. Cf. MD_Room_PrimaryUse.

DW_CodeDesc

varchar(32)

MD_Room_Type

Specific classification for a room, e.g., "Closet" or "Dining Room." Comes from XROOM.TYPE.DESC in Colleague.

In a hierarchy, the type belongs below (i.e., is more specific than) the room category, MD_Room_Category.

Note that this is not a 'code.'

DW_CodeDesc

varchar(32)

MD_Room_Category

General room usage category, e.g., "Student Housing" or "Public Space." In a hierarchy, the category would occupy the top level, while the room usage or type would occupy the next level down. This field is the description corresponding to ROOM.CATEGORY.CODE in Colleague.

DW_CodeDesc

varchar(32)

MD_Room_SCUPCode

Not well maintained as of 2010. These correspond to 'room facility use' codes in Colleague.

Formerly was used to hold SCUP (Society of College and University Planners) codes for spaces, which are typically numeric (315 for "Office Service", 610 for "Assembly," etc). The codes are still present, but as noted are not well maintained.

DW_CodeDesc

varchar(32)

MD_Room_SCUPCategory

Not well maintained as of 2010.

Formerly was used to hold SCUP (Society of College and University Planners) classifications for spaces. These use a slightly different vocabulary than what we normally use at Carleton (e.g., in MD_Room_PrimaryUse, MD_Room_Type, and MD_Room_Category).

DW_CodeDesc

varchar(32)

MD_Room_ResponsibleDept

Department responsible for a given room in a building. Note that this may not be the same thing as the department primarily responsible for coordinating use of a room. Based on XROOM.RESP.DEPT.DESC in Colleague.

Typically the people responsible for a room on campus are either Residential Life or Facilities.

DW_Department

varchar(64)

MD_Room_CoordinatingDept

Department responsible for coordinating use of a given room in a building. Note that this may not be the same thing as the department primarily responsible a room. That is, the coordinating department may differ from the responsible department(s). Based on XROOM.COORD.DEPT.DESC.

Not currently, as of 2010, used a great deal at Carleton.

DW_Department

varchar(64)

MD_Room_CoordinatingDept2

See MD_Room_CoordinatingDept. Sometimes multiple departments coordinate or control a single space. Not currently used.

DW_Department

varchar(64)

MD_Room_CoordinatingDept3

See MD_Room_CoordinatingDept. Sometimes multiple departments coordinate or control a single space. Not currently used.

DW_Department

varchar(64)

MD_Room_Coordinator

Not currently utilized. Do not expose to users.

DW_CommentShort

nvarchar(128)

128

MD_Room_Coordinator2

Not currently used. See MD_Room_Coordinator. This attribute exists in case there are multiple coordinators per room.

DW_CommentShort

nvarchar(128)

128

MD_Room_Coordinator3

Not currently used. See MD_Room_Coordinator. This attribute exists in case there are multiple coordinators per room.

DW_CommentShort

nvarchar(128)

128

MD_Room_StudentsAssigned

Count of students assigned to a given room, where the status of the assignment = "Assigned" (not, e.g., "Canceled").

The value given here is for the current term, and will therefore change relatively frequently.

Will be "Not Applicable" for rooms that are not of type BDRM (bedroom).

DW_CodeDesc

varchar(32)

MD_Room_AreaRange

Square footage of a room, discretized into ranges for easy use in reports.

The actual dimensions of a room are recorded in Colleague, but are not available in the data warehouse.

DW_CodeDesc

varchar(32)

MD_Room_CeilingHeight

Height in feet of ceiling (discretized or "banded" into ranges, to make grouping and reporting easier).

Sometimes used in conjunction with the room's area to get an approximate wall square footage, for renovation or painting purposes.

As of 2010 this is not stored in Colleague.

DW_CodeDesc

varchar(32)

MD_Room_WallAreaRange

Square footage of walls.

Not clear where it should be stored in Colleague as of 2010, and therefore not maintained (will always be 'Unknown').

DW_CodeDesc

varchar(32)

MD_Room_WindowCountRange

DW_CodeDesc

varchar(32)

MD_Room_MainPaintColor

DW_CodeDesc

varchar(32)

MD_Room_Wing

Rarely used. Do not expose to users unless this pattern changes. Note that this is a wing 'description' in Colleague, not a code. Codes should not be used in reports, if this can be avoided.

DW_CodeDesc

varchar(32)

MD_Room_Campus

DW_Campus

varchar(16)

MD_Room_FirecodeCapacity

Capacity, as recorded for fire code compliance purposes (different from how many people we'd reasonably seat there).

DW_CodeDesc

varchar(32)

MD_Room_CapacityExact

Do not expose to users.

This field containst the exact capacity of a given room, rather than a discretized or 'banded' version of it.

May be NULL.

<None>

int

MD_Room_Capacity

Number of seats (hence people) a given room is set up to accommodate, or, in the case of student housing, how many beds, i.e., how people a room can sleep. Different from fire code capacity, which is the maximum capacity we could seat and still remain within fire code compliance.

Also available in Colleague, but not here, is a text designation of the room's student capacity, i.e., whether a given room is a single, double, quad, etc.

DW_CodeDesc

varchar(32)

MD_Room_ExtraCapacity

Not clear what this is for or where it comes from in Colleague.

DW_CodeDesc

varchar(32)

MD_Room_DormStaffType

Fills out part of the picture of how a room in a residence hall is being used: RA Room, RA Connecting Room, RA Double, Medical

DW_CodeDesc

varchar(32)

MD_Room_DormSpaceClass

DW_CodeDesc

varchar(32)

MD_Room_IsQuietArea

'Yes' if room is designated as part of a quiet area in a residence hall. 'No' otherwise.

The value 'No' for non-dorm-rooms is not terribly useful. Just make a note of this.

DW_YesOrNoNOTNULL

char(3)

MD_Room_BathroomType

DW_CodeDesc

varchar(32)

MD_Room_IsSubstanceFree

'Yes' if room is designated as part of a "substance free" area in a residence hall. 'No' otherwise.

The term "substance free" is not intended to be humorous. Rather, it indicates that the room lies in an area of a residence hall where, by agreement, no illicit substances are used.

The value 'No' for non-dorm-rooms is not terribly useful. Just make a note of this.

DW_YesOrNoNOTNULL

char(3)

MD_Room_IsVacant

"Yes" if a room has no residents currently. "No" otherwise.

Note that a "No" here is only meanginful if the room is a dorm room. That is, if a classroom has no residents, this information is generally not useful.

DW_YesOrNoNOTNULL

char(3)

MD_Room_State

Active or inactive. Typically, one will want to filter out inactive rooms (as well as inactive buildings).

DW_CodeDescShort

varchar(16)

MD_Room_AuditKey

DW_AuditKey

bigint

MD_Room_Timestamp

<None>

timestamp