Dict-Physical

View VMF_KeyedComputerEvent

Card of view VMF_KeyedComputerEvent

Name	VMF_KeyedComputerEvent
Comment	Not to be confused with (V)MD_KeyedComputerEvent (note the MD there, rather than MF). For more information, see the documentation on MF_KeyedComputerEvent in the StarsLabUsage package. This table has been aggregated up to the hour level (zeroing out minutes and seconds on the time dimension), so as to arrive at the same grain as that of VMF_KeyedComputerInUse. We remove the 'program or app' dimension from this view, although it's in the parent VMF_KeyedComputerInUse dimension, because we throw away so many launch events when constructing VMF_KeyedComputerInUse. Basically we keep only one event of a given type (up, down, logon, logoff, program launch, etc.) per second. We do this because we aren't really interested in the particulars of launches, which can be obtained from the K2 database directly, if we're getting down to that fine level of detail. The goal here is to obtain relative usage stats, and sampling one launch per second is plenty for that.
Dimensional Type	Fact

SQL query of the view VMF_KeyedComputerEvent

select
MD_KeyedComp_SurrogateKey,
MD_KCEvent_SurrogateKey,
MD_Date_DateInteger,
-- Zero out minutes and seconds part of time key
(MIN(mfk.MD_Time_TimeInteger) / 65536) * 65536 AS MD_Time_TimeInteger,
-- MD_Date_DateIntegerOther,
-- MD_Time_TimeIntegerOther,
-- MD_Program_SurrogateKey,
(MD_Date_DateInteger / 10000) AS VMF_KCEvent_Year,
MAX(MD_ITUser_SurrogateKey) AS MD_ITUser_SurrogateKey,
MAX(MD_CarlDept_SurrogateKey) AS MD_CarlDept_SurrogateKey,
MAX(MD_CollPers_SurrogateKey) AS MD_CollPers_SurrogateKey,
MAX(MD_Room_SurrogateKey) AS MD_Room_SurrogateKey,
MAX(MD_Building_SurrogateKey) AS MD_Building_SurrogateKey,
MAX(MD_ITAsset_SurrogateKey) AS MD_ITAsset_SurrogateKey,
MAX(MF_KCEvent_UniqueID) AS MF_KCEvent_UniqueID,
SUM(MF_KCEvent_Duration) AS MF_KCEvent_Duration,
SUM(MF_KCEvent_Count) AS MF_KCEvent_Count
from
MF_KeyedComputerEvent mfk
INNER JOIN MD_Time mdt
ON mfk.MD_Time_TimeInteger = mdt.MD_Time_TimeInteger
group by
MD_KeyedComp_SurrogateKey,
MD_KCEvent_SurrogateKey,
MD_Date_DateInteger,
mdt.MD_Time_HourOfDay24

List of outgoing view references of the view VMF_KeyedComputerEvent

ViewReference_224

VMD_IssueTrackingAsset (Shortcut)

ViewReference_225

VMD_KeyedComputerEvent

ViewReference_226

VMD_ProgramOrApp

ViewReference_227

VMD_KeyedComputer

ViewReference_228

VMD_CarletonDepartment (Shortcut)

ViewReference_229

VMD_Date (Shortcut)

ViewReference_230

VMD_Room (Shortcut)

ViewReference_231

VMD_Building (Shortcut)

ViewReference_232

VMD_Time (Shortcut)

ViewReference_233

VMD_IssueTrackingUser (Shortcut)

ViewReference_235

VMD_Employee (Shortcut)

List of referenced objects of the view VMF_KeyedComputerEvent

MD_Time

MF_KeyedComputerEvent

List of diagrams containing the view VMF_KeyedComputerEvent

ViewsLabUsageDiagram

List of permissions of the view VMF_KeyedComputerEvent

SELECT,VIEW DEFINITION

IssueTrackingReader

List of view columns of the view VMF_KeyedComputerEvent

MD_KeyedComp_SurrogateKey

numeric

MD_KCEvent_SurrogateKey

numeric

MD_Date_DateInteger

int

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

MD_TIME_TIMEINTEGER

VMF_KCEVENT_YEAR

MD_ITUser_SurrogateKey

MD_CarlDept_SurrogateKey

MD_CollPers_SurrogateKey

MD_Room_SurrogateKey

MD_Building_SurrogateKey

MD_ITAsset_SurrogateKey

MF_KCEvent_UniqueID

MF_KCEvent_Duration

Duration of event(s). Note that not all events (specifically launch events) are counted here; rather, we just count one a second.

MF_KCEvent_Count

Simple integer count. Note that not all events (specifically launch events) are counted here; rather, we just count one a second.

Home