Footprints Data Model

Table
Table Name :item_shortname Event Sum
Table Definition The fact table for Ticket Event Summary. The date and time of lifecycle events is stored here, along with calculations for the elapsed work hours and clock hours. There are separate calculations that begin at ticket creation and ticket activation. Resolution Time and Closed Time are two different events. The data in this table is maintained by data mart stored procedures. The name of the table is qualified by the container schema and includes the item short name. In addition to the built-in reporting, this table can be used in any dimensional BI tool to produce useful reports.

Attribute
Attribute Name Attribute Definition Logical Datatype Is Required
Item Id The automatically generated primary key of the :item_shortname_event_sum table. LONG Yes
Date Hour Local A date time in UTC that cooresponds to a whole hour date in the local timezone for the Date Hour Local. The Minutes portion of the date will usually be 00 but can be 30, 15, 45 in the case of half hour or quarter hour time zones. UTC never has daylight savings is in effect, DATE Yes
Life Cycle Event Id A Foreign Key to the Life Cycle Event table. INTEGER Yes
Event DateTime The exact event date and time in UTC. This is used to calculate exact times and durations. It is within the hour that is bound by the local hour. DATE Yes
Creation Clock Hours Count The elapsed clock hours from creation to the activity date. This does not include the time spent in DOES NOT COUNT FOR TIME states. NUMBER(13,2) Yes
Creation Work Hours Count The elapsed work hours from creation to the activity date. This does not include the time spent in DOES NOT COUNT FOR TIME states. Work hours are defined by the schedule actual period table. NUMBER(13,2) Yes
All Creation Clock Hours Count The elapsed clock hours from creation to the activity date. The status flag DOES NOT COUNT FOR TIME is ignored here. NUMBER(13,2) Yes
All Creation Work Hours Count The elapsed work hours from creation to the activity date. Work hours are defined by the schedule actual period table. The status flag DOES NOT COUNT FOR TIME is ignored here. NUMBER(13,2) Yes
Activation Clock Hours Count The elapsed clock hours from activation to the activity date. This does not include the time spent in DOES NOT COUNT FOR TIME states. NUMBER(13,2,) Yes
Activation Work Hours Count The elapsed work hours from activation to the activity date. This does not include the time spent in DOES NOT COUNT FOR TIME states. Work hours are defined by the schedule actual period table. NUMBER(13,2) Yes
All Activation Clock Hours Count The elapsed clock hours from activation to the activity date. The status flag DOES NOT COUNT FOR TIME is ignored here. NUMBER(13,2) Yes
All Activation Work Hours Count The elapsed work hours from activation to the activity date. The status flag DOES NOT COUNT FOR TIME is ignored here. Work hours are defined by the schedule actual period table. NUMBER(13,2) Yes