Dict-Physical

View VMF_RoomUtilizationByDay

Card of view VMF_RoomUtilizationByDay

Name	VMF_RoomUtilizationByDay
Comment	Used to measure room and building utilization (relative to capacity) by date. Excludes canceled assignments. This fact table only includes data about office and dorm room assignments. And it only contains data about rooms whose usage is best analyzed at the day level (rather than by hour of the day, as would be the case for, e.g., classrooms, conference rooms, and other volatile 'bookable' spaces). 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. Dorm assignments are only made for students during terms. Summer bookings, like reunion, are not available here.
Dimensional Type	Fact

SQL query of the view VMF_RoomUtilizationByDay

select
   rabc.MD_Date_SnapshotDate,
rabc.MD_Room_SurrogateKey,
rabc.MD_Building_SurrogateKey,
rabc.MD_Room_CapacityExact AS MF_RoomUtilBD_Capacity,
ISNULL(mfb.Sum_Count, 0) AS MF_RoomUtilBD_Usage
from
-- Only takes us 7 days into the future
dbo.VMF_RoomAssignmentSnapshotCoverage rabc
LEFT OUTER JOIN (
      SELECT
         MD_Date_SnapshotDate,
         MD_Room_SurrogateKey,
         SUM(MF_RoomAsgn_Count) AS Sum_Count
      FROM
         dbo.VMF_RoomAssignmentSnapshot
      GROUP BY
   MD_Date_SnapshotDate,
MD_Room_SurrogateKey) mfb
ON rabc.MD_Date_SnapshotDate = mfb.MD_Date_SnapshotDate
AND rabc.MD_Room_SurrogateKey = mfb.MD_Room_SurrogateKey