Footprints Data Model

Table
Name :item_shortname_event_sum
Comment
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.

Column
Name Datatype Comment Is PK Is FK
item_id bigint The automatically generated primary key of the :item_shortname_event_sum table. Yes No
date_hour_local datetime
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,
Yes Yes
life_cycle_event_id int A Foreign Key to the Life Cycle Event table. Yes Yes
event_datetime 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.
No No
creatn_clock_hours_ct numeric(13,2)
The elapsed clock hours from creation to the activity date. This does not
include the time spent in DOES NOT COUNT FOR TIME states.
No No
creatn_work_hours_ct numeric(13,2)
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.
No No
all_creatn_clock_hours_ct numeric(13,2)
The elapsed clock hours from creation to the activity date. The status flag
DOES NOT COUNT FOR TIME is ignored here.
No No
all_creatn_work_hours_ct numeric(13,2)
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.
No No
actvn_clock_hours_ct numeric(13,2)
The elapsed clock hours from activation to the activity date. This does not
include the time spent in DOES NOT COUNT FOR TIME states.
No No
actvn_work_hours_ct numeric(13,2)
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.
No No
all_actvn_clock_hours_ct numeric(13,2)
The elapsed clock hours from activation to the activity date. The status flag
DOES NOT COUNT FOR TIME is ignored here.
No No
all_actvn_work_hours_ct numeric(13,2)
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.
No No