Dict-Physical

View VMF_RoomUtilization

Card of view VMF_RoomUtilization

Name	VMF_RoomUtilization
Comment	Used to measure room and building utilization (relative to capacity) by date and time of day, or rather, hour of the day. This fact table only includes data about 'bookable' rooms, such as classrooms and meeting space. As of 2010 it takes data mainly from EMS (course sizes are taken, on the fly, from the DW, as are room capacities). The data in this table needs to be fed into an OLAP cube to be useful. Performance of the raw relational table is not optimized. Data only extends 7 days into the future. See also VMF_SlotUtilization, which uses Carleton 'slots' instead of hours. The VMF_SlotUtilization view is less useful, because slots vary by day and over time, and they therefore make consistent historical reporting impossible.
Dimensional Type	Fact

SQL query of the view VMF_RoomUtilization

select
   mfbc.MD_Date_SnapshotDate,
   mfbc.MD_Time_SnapshotTime,
mfbc.MD_Room_SurrogateKey,
mfbc.MD_Building_SurrogateKey,
ISNULL(mfb.MD_Event_SurrogateKey, (SELECT MD_Event_SurrogateKey FROM MD_Event WHERE MD_Event_ID = 'None')) AS MD_Event_SurrogateKey,
ISNULL(mfb.MD_Sect_SurrogateKey, (SELECT MD_Sect_SurrogateKey FROM MD_Section WHERE MD_Sect_Name = 'UNKN.9999.99')) AS MD_Sect_SurrogateKey,
ISNULL(mfbc.MD_Room_CapacityExact, 0) AS MF_RoomUtil_Capacity,
CASE
WHEN mfb.MAX_ExpectedAttendees IS NULL THEN 0
WHEN mfbc.MD_Room_CapacityExact IS NULL THEN mfb.MAX_ExpectedAttendees
-- Fix some of the more ridiculous SetupCount figures in EMS
WHEN mfb.MAX_ExpectedAttendees > (2 * mfbc.MD_Room_CapacityExact) THEN (2 * mfbc.MD_Room_CapacityExact)
ELSE mfb.MAX_ExpectedAttendees
END AS MF_RoomUtil_Usage
from
-- Only takes us 7 days into the future
dbo.VMF_BookingSnapshotCoverage mfbc
LEFT OUTER JOIN (
      SELECT
         MD_Date_SnapshotDate,
         MD_Time_SnapshotTime,
         MD_Room_SurrogateKey,
         MD_Event_SurrogateKey,
         MD_Sect_SurrogateKey,
         MAX(MF_Booking_ExpectedAttendees) AS MAX_ExpectedAttendees
      FROM
         dbo.VMF_BookingSnapshot
      GROUP BY
         MD_Date_SnapshotDate,
         MD_Time_SnapshotTime,
         MD_Room_SurrogateKey,
         MD_Event_SurrogateKey,
         MD_Sect_SurrogateKey) mfb
ON mfbc.MD_Date_SnapshotDate = mfb.MD_Date_SnapshotDate
AND mfbc.MD_Time_SnapshotTime = mfb.MD_Time_SnapshotTime
AND mfbc.MD_Room_SurrogateKey = mfb.MD_Room_SurrogateKey