Footprints Data Model

Table
Name :item_shortname_bal
Comment
A summary table to make calculation of hourly balances fast.  The date range is
a range of whole hours that identifies the period during which the ticket is
unclosed at the end of the whole hour.  The flags can be summed, and they
indicated further whether the ticket was activated or resolved at the end of the
hour.  This table is only used for unclosed balance metrics, and is designed to
be joined with the TIME DIMENSION. Summary higher than hour is possible just by
joining the higher granularity time dimension with the date range. 
Interpolation below the hour can be done for the first or  last hour of the
ranges using the hard date time in the TICKET EVENT SUMMARY table.

Column
Name Datatype Comment Is PK Is FK
item_id bigint
The ticket item id for this dynamic summary table that was generated for the
item name.
Yes No
begin_date_hour_local datetime
The begin whole hour of the time period for which this ticket is unclosed at
the last minute of the first hour through the last minute of the end hour. 
Joins to TIME DIM.
Yes No
activated_ct numeric(13,2)
Indicates if the Activation Event occured prior to the end of the period. A one
or zero integer count flag for the Item, designed to be summed in a query.  It
is important to identify the ONE ROW in this table which has the correct count
flag by date joining, so that summing the count flag will not double count. That
is the method of high performance aggregation.
No No
end_date_hour_local datetime
The end whole hour of the time period for which this ticket is unclosed at the
last minute of the first hour through the last minute of the end hour.  Joins to
TIME DIM.
No No
resolved_ct numeric(13,2)
Indicates if the Resolved Event occured prior to the end of the period. A one
or zero integer count flag for the Item, designed to be summed in a query.  It
is important to identify the ONE ROW in this table which has the correct count
flag by date joining, so that summing the count flag will not double count. That
is the method of high performance aggregation.
No No
unresolved_ct numeric(13,2)
Indicates if the Resolved Event had not occured prior to the end of the period.
This is the inverse of the Resolved Ct, and makes it easier to count UnResolved.
A one or zero integer count flag for the Item, designed to be summed in a query.
 It is important to identify the ONE ROW in this table which has the correct
count flag by date joining, so that summing the count flag will not double
count. That is the method of high performance aggregation.
No No