Dict-Physical

View VMF_KeyedComputerInUseWithRoomUtilization

Card of view VMF_KeyedComputerInUseWithRoomUtilization

Name	VMF_KeyedComputerInUseWithRoomUtilization
Comment	Not actually used, for various practical reasons, but available. VERY SLOW. Based on VMF_KeyedComputerInUse, but even slower, because it adds two fields based on room utilization: VMF_KCINUSE_ROOMISFULLYUTILIZED - 'Yes' if the room a computer is in is being used fully, defined as (workstations used / workstation count) > (.9 * workstation count) VMF_KCINUSE_ROOMCOMPUTERCOUNT - Count of computers deployed in the same room Should only be used as a feeder for a multidimensional cube. Direct queries against this view will be slow.
Dimensional Type	Fact

SQL query of the view VMF_KeyedComputerInUseWithRoomUtilization

SELECT
vmk.MD_KeyedComp_SurrogateKey,
vmk.MD_Date_DateInteger,
vmk.MD_Time_TimeInteger,
vmk.VMF_KCInUse_Year,
vmk.MD_ITAsset_SurrogateKey,
vmk.MD_ITUser_SurrogateKey,
vmk.MD_Room_SurrogateKey,
vmk.MD_Building_SurrogateKey,
vmk.MD_CarlDept_SurrogateKey,
vmk.VMF_KCInUse_Used,
vmk.VMF_KCInUse_Available,
CASE
      WHEN fullRooms.MD_Room_SurrogateKey IS NULL
      THEN 'No'
      ELSE 'Yes'
   END AS VMF_KCInUse_RoomIsFullyUtilized,
   fullRooms.VMF_KCInUse_RoomComputerCount
FROM
   (SELECT
      MD_Room_SurrogateKey,
      MD_Date_DateInteger,
      MD_Time_TimeInteger,
      COUNT(MD_KeyedComp_SurrogateKey) AS VMF_KCInUse_RoomComputerCount
    FROM
      VMF_KEYEDCOMPUTERINUSE
    GROUP BY
      MD_Room_SurrogateKey,
      MD_Date_DateInteger,
      MD_Time_TimeInteger
    HAVING
      SUM(VMF_KCInuse_USED) >= (.9 * SUM(VMF_KCINUSE_AVAILABLE))
   ) fullRooms
   RIGHT OUTER JOIN VMF_KeyedComputerInUse vmk
   ON fullRooms.MD_Room_SurrogateKey = vmk.MD_Room_SurrogateKey
      AND fullRooms.MD_Date_DateInteger = vmk.MD_Date_DateInteger
      AND fullRooms.MD_Time_TimeInteger = vmk.MD_Time_TimeInteger

List of outgoing view references of the view VMF_KeyedComputerInUseWithRoomUtilization

ViewReference_241

VMD_TimeToHour (Shortcut)

ViewReference_242

VMD_Date (Shortcut)

ViewReference_243

VMD_Building (Shortcut)

ViewReference_244

VMD_Room (Shortcut)

ViewReference_245

VMD_CarletonDepartment (Shortcut)

ViewReference_246

VMD_IssueTrackingUser (Shortcut)

ViewReference_247

VMD_IssueTrackingAsset (Shortcut)

List of referenced objects of the view VMF_KeyedComputerInUseWithRoomUtilization

VMF_KeyedComputerInUse

List of diagrams containing the view VMF_KeyedComputerInUseWithRoomUtilization

ViewsLabUsageDiagram

List of permissions of the view VMF_KeyedComputerInUseWithRoomUtilization

SELECT,VIEW DEFINITION

IssueTrackingReader

List of view columns of the view VMF_KeyedComputerInUseWithRoomUtilization

MD_KeyedComp_SurrogateKey

MD_Date_DateInteger

MD_Time_TimeInteger

Time, to the granularity of one hour (minutes and seconds fields are always zero).

VMF_KCInUse_Year

Used solely for partitioning purposes; can be calculated from MD_Date_DateInteger.

MD_ITAsset_SurrogateKey

MD_ITUser_SurrogateKey

MD_Room_SurrogateKey

MD_Building_SurrogateKey

MD_CarlDept_SurrogateKey

VMF_KCInUse_Used

Count of how many, of the six available ten-minute chunks available during each hour, a given computer had an active login (i.e., a logon without, yet, a corresponding logout).

VMF_KCInUse_Available

Will always be 6 - unless, of course, we aggregate the total.

Used as a divisor for VMF_KCInUse_Used, which gives the count of how many, of the six available ten-minute chunks available during each hour, a given computer had an active login (i.e., a logon without, yet, a corresponding logout).

Together these figures give us a good % utilization figure.

VMF_KCINUSE_ROOMISFULLYUTILIZED

VMF_KCINUSE_ROOMCOMPUTERCOUNT

Home