Dict-Physical

View VMD_Room

Card of view VMD_Room

Name	VMD_Room
Comment	See documentation on MD_Room. This view contains basic Colleague information on campus rooms. Note that virtually any space can be a 'room' (e.g., an alcove, closet, roof, etc.).
Dimensional Type	Dimension

SQL query of the view VMD_Room

select
MD_Room_SurrogateKey,
MD_Room_ID,
MD_Room_BuildingID,
MD_Room_Building,
MD_Room_Floor,
MD_Room_Number,
MD_Room_Name,
CASE
/* Res Life groups rooms with A, B, C, etc. extensions together for coed living petition/permission purposes */
WHEN MD_ROOM_NAME LIKE '%*%[0-9][A-J]' AND MD_ROOM_TYPE <> 'Single'
THEN SUBSTRING(MD_ROOM_NAME, 1, LEN(MD_ROOM_NAME) - 1)
ELSE MD_ROOM_NAME
END AS MD_Room_CoedGroupingName,
MD_Room_FloorType,
MD_Room_Accessibility,
MD_Room_AccessibilityCode,
MD_Room_PrimaryUse,
MD_Room_SecondaryUse,
MD_Room_Type,
MD_Room_Category,
MD_Room_SCUPCode,
MD_Room_SCUPCategory,
MD_Room_ResponsibleDept,
MD_Room_CoordinatingDept,
MD_Room_CoordinatingDept2,
MD_Room_CoordinatingDept3,
MD_Room_StudentsAssigned,
MD_Room_AreaRange,
MD_Room_CeilingHeight,
MD_Room_WallAreaRange,
MD_Room_WindowCountRange,
MD_Room_MainPaintColor,
MD_Room_Wing,
MD_Room_Campus,
MD_Room_Capacity,
MD_Room_ExtraCapacity,
MD_Room_IsSubstanceFree,
MD_Room_IsQuietArea,
CASE
/* Used to find bookable classrooms; utilization rates for classrooms matter
* for climate action plans and strategic plans. Labs, studios, and other less
* flexibly used spaces are not included here, because they cannot easily be
* factored into utilization targets. Ditto for auditoriums, which are big and
* hard to keep filled, but are necessary for performances of various kinds.
*/
WHEN MD_ROOM_TYPE = 'Lecture Hall' THEN 'Yes'
WHEN MD_ROOM_TYPE LIKE '% Classroom' THEN 'Yes'
WHEN MD_ROOM_TYPE LIKE 'Sem% Room%' THEN 'Yes'
ELSE 'No'
END AS MD_Room_IsBookableClassroom,
MD_Room_BathroomType,
MD_Room_DormStaffType,
MD_Room_DormSpaceClass,
MD_Room_State
from
MD_Room

List of incoming view references of the view VMD_Room

ViewReference_1

VMF_CardTransaction

ViewReference_109

VMF_IssueTrackingTicket

ViewReference_127

VMF_IssueTrackingAssetValue

ViewReference_136

ViewReference_140

ViewReference_143

ViewReference_148

ViewReference_152

VMF_RoomUtilizationByDay

ViewReference_186

VMF_ReadingByHourAccumulating

ViewReference_192

VMF_ReadingByHourSnapshot

ViewReference_207

VMF_ReadingByHourNoAggregations

ViewReference_230

VMF_KeyedComputerEvent

ViewReference_230

VMF_KeyedComputerEvent

ViewReference_244

VMF_KeyedComputerInUseWithRoomUtilization

ViewReference_244

VMF_KeyedComputerInUseWithRoomUtilization

ViewReference_249

VMF_KeyedComputerInUse

ViewReference_249

VMF_KeyedComputerInUse

List of shortcuts of the view VMD_Room

View

View

View

View

List of diagrams containing the view VMD_Room

ViewsIssueTrackingDiagram

ViewsLabUsageDiagram

List of permissions of the view VMD_Room

SELECT,VIEW DEFINITION

CardAccessReader

SELECT,VIEW DEFINITION

CardAccessReader

SELECT,VIEW DEFINITION

EnergyReader

SELECT,VIEW DEFINITION

EnergyReader

SELECT,VIEW DEFINITION

IssueTrackingReader

SELECT,VIEW DEFINITION

IssueTrackingReader

SELECT,VIEW DEFINITION

SpaceReader

SELECT,VIEW DEFINITION

SpaceReader

List of view columns of the view VMD_Room

MD_Room_SurrogateKey

numeric

MD_Room_ID

varchar(16)

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

MD_Room_BuildingID

varchar(16)

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

MD_Room_Building

nvarchar(32)

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.

MD_Room_Floor

varchar(16)

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.

MD_Room_Number

varchar(16)

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

MD_Room_Name

nvarchar(128)

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

MD_Room_CoedGroupingName

Name of room, for the purposes of determining whether (if it contains people of multiple genders) this would require a petition.

At Carleton it's perfectly OK for men and women to sleep in the same room, as long as the room is in coed space, and the inhabitants petition for it, and the petition is granted.

MD_Room_FloorType

varchar(32)

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.

MD_Room_Accessibility

varchar(32)

MD_Room_AccessibilityCode

varchar(32)

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

MD_Room_PrimaryUse

varchar(32)

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

MD_Room_SecondaryUse

varchar(32)

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

MD_Room_Type

varchar(32)

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

MD_Room_Category

varchar(32)

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.

MD_Room_SCUPCode

varchar(32)

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.

MD_Room_SCUPCategory

varchar(32)

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

MD_Room_ResponsibleDept

varchar(64)

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.

MD_Room_CoordinatingDept

varchar(64)

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.

MD_Room_CoordinatingDept2

varchar(64)

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

MD_Room_CoordinatingDept3

varchar(64)

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

MD_Room_StudentsAssigned

varchar(32)

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

MD_Room_AreaRange

varchar(32)

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.

MD_Room_CeilingHeight

varchar(32)

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.

MD_Room_WallAreaRange

varchar(32)

Square footage of walls.

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

MD_Room_WindowCountRange

varchar(32)

MD_Room_MainPaintColor

varchar(32)

MD_Room_Wing

varchar(32)

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.

MD_Room_Campus

varchar(16)

MD_Room_Capacity

varchar(32)

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.

MD_Room_ExtraCapacity

varchar(32)

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

MD_Room_IsSubstanceFree

char(3)

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

MD_Room_IsQuietArea

char(3)

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

MD_ROOM_ISBOOKABLECLASSROOM

MD_Room_BathroomType

varchar(32)

MD_Room_DormStaffType

varchar(32)

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

MD_Room_DormSpaceClass

varchar(32)

MD_Room_State

varchar(16)

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

MD_Room_SurrogateKey

numeric

MD_Room_ID

varchar(16)

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

MD_Room_BuildingID

varchar(16)

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

MD_Room_Building

nvarchar(32)

MD_Room_Floor

varchar(16)

MD_Room_Number

varchar(16)

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

MD_Room_Name

nvarchar(128)

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

MD_Room_CoedGroupingName

MD_Room_FloorType

varchar(32)

MD_Room_Accessibility

varchar(32)

MD_Room_AccessibilityCode

varchar(32)

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

MD_Room_PrimaryUse

varchar(32)

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

1 2

Home