Footprints Data Model

Table
Name :item_shortname_transn_sum
Comment
The fact table for state transitions.  It holds all transitions, whether they
are transitive or not.  Transitions with a hop count of 1 are traditional
transitions.  There can be multiple cycles between two states for a single
ticket.  The Is Longest Hop can be used to identify the distinct longest paths. 
Work Hours and Clock Hours are calculated for all transitions.  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_transn_sum table. Yes No
item_transn_id bigint A Foreign Key to the :item_shortname Transition table. Yes Yes
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
transn_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
clock_hours_ct numeric(13,2)
The elapsed clock hours for the transistion between the from and to states.
This does not include the time spent in DOES NOT COUNT FOR TIME states.
No No
work_hours_ct numeric(13,2)
The elapsed work hours for the transistion between the from and to states. 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_clock_hours_ct numeric(13,2)
The elapsed clock hours for the transistion between the from and to states. The
status flag DOES NOT COUNT FOR TIME is ignored here.
No No
all_work_hours_ct numeric(13,2)
The elapsed clock hours for the transistion between the from and to states. The
status flag DOES NOT COUNT FOR TIME is ignored here. Work hours are defined by
the schedule actual period table.
No No