Subject Area : 300 - Ticket Data Mart
Subject Area Name Definition
300 - Ticket Data Mart Tables that support the Ticket Data Mart used in reporting.
ER Diagram Name Physical Display Level
Logical Column
Physical Column
Physical Name Entity/Table Name Logical Only Do Not Generate Definition
:item_shortname_bal :Item_Shortname Balance false 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.
Physical Name Attribute/Column Name Definition Physical Data Type
item_id Item Id The ticket item id for this dynamic summary table that was generated for the item name. bigint
begin_date_hour_local Begin Date Hour Local 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. datetime
end_date_hour_local End Date Hour Local 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. datetime
activated_ct Activated Count 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. numeric(13,2)
resolved_ct Resolved Count 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. numeric(13,2)
unresolved_ct UnResolved Count 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. numeric(13,2)
:item_shortname_event_sum :item_shortname Event Sum false 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.
Physical Name Attribute/Column Name Definition Physical Data Type
item_id Item Id The automatically generated primary key of the :item_shortname_event_sum table. bigint
event_datetime 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. datetime
creatn_clock_hours_ct 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. numeric(13,2)
creatn_work_hours_ct 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. numeric(13,2)
all_creatn_clock_hours_ct 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. numeric(13,2)
all_creatn_work_hours_ct 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. numeric(13,2)
actvn_clock_hours_ct 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. numeric(13,2)
actvn_work_hours_ct 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. numeric(13,2)
all_actvn_clock_hours_ct 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. numeric(13,2)
all_actvn_work_hours_ct 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. numeric(13,2)
life_cycle_event_id Life Cycle Event Id A Foreign Key to the Life Cycle Event table. int
date_hour_local 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, datetime
:item_shortname_status :item_shortname Status false The configuration settings for the item status and item lifecycle flags are maintained in this table for performance and history reasons. They are replicated here at the beginning of the data mart load process. Even a retired status will be in this table in order to support historical entries. :item_shortname will be substituted at publish.
Physical Name Attribute/Column Name Definition Physical Data Type
is_active Is Active Indicates if the state is an Activated state, used only to calculate Activated event date. Do not confuse with UnClosed, UnResolved. bit
is_resolved Is Resolved Indicates if the state is an Resolved state, Used to calculate Resolved event date. bit
is_closed Is Closed Indicates if the state is an Closed state, Used to calculate Closed event date. Currently, only one status is allow to be the Closed state. bit
is_responded Is Responded Indicates if the state is an Responded state, Used to calculate Responded event date. bit
does_ct_for_time Does Count for Time Indicates that this state does not count for time expended. This is used for the measures that do not include the ALL designation. It is anticipated that WAITING ON CUSTOMER RESPONSE and similar states would use this flag to show agent metrics without such states included in the time. bit
item_status_name Item Status Name The name of the :item_shortname Status. varchar(255)
item_status_id Item Status Id The automatically generated primary key of the :item_shortname_status table. int
:item_shortname_transn :item_shortname Transition false A dimension table that records the details of a state transition, including the from and to state names, and the number of hops that are taken in the transition. This allows the precalculation of transitive states, so the transition from A to D via the path A>b>c>D would be recorded with a hopCt of 3.
Physical Name Attribute/Column Name Definition Physical Data Type
item_transn_id Item Transition Id The automatically generated primary key of the :item_shortname_transn table. bigint
hop_ct Hop Count The number of transitions. Open to Active to Closed will be stored additionally as Open to Closed with a hop count of 2. int
is_longest_hop Is Longest Hop Indicates that this transition is a 'longest hop' transition. Many reports will want to find the longest transition between two states, and ignore cycles within the two. bit
to_item_status_id To Item Status Id A Foreign Key to the :item_shortname Status table. int
from_item_status_id From Item Status Id A Foreign Key to the :item_shortname Status table. int
from_item_status_name From Item Status Name The name of the From Status. varchar(255)
to_item_status_name To Item Status Name The name of the To Status. varchar(255)
:item_shortname_transn_sum :item_shortname Transition Sum false 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.
Physical Name Attribute/Column Name Definition Physical Data Type
item_id Item Id The automatically generated primary key of the :item_shortname_transn_sum table. bigint
transn_datetime Transition 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. datetime
clock_hours_ct Clock Hours Count 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. numeric(13,2)
work_hours_ct Work Hours Count 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. numeric(13,2)
all_clock_hours_ct All Clock Hours Count The elapsed clock hours for the transistion between the from and to states. The status flag DOES NOT COUNT FOR TIME is ignored here. numeric(13,2)
all_work_hours_ct All Work Hours Count 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. numeric(13,2)
item_transn_id Item Transition Id A Foreign Key to the :item_shortname Transition table. bigint
date_hour_local 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, datetime
life_cycle_event Life Cycle Event false This may come into play for the "First Contact" which is defined as:
 
From Ursula Strahm (3/23/2012)
First Call from the Executive Dashboard SRS:
 
  
 
First Call.- The elapsed time between the From and To Event is 0 for the current Widget configuration. Examples of this are:
 
i. The Issue is created in the “To Event” status. In this case, the Issue will always show in the First Call bucket.
 
ii. Issue went from Created to “To Event” status outside of working hours and the Widget is configured to display “Work Hours”
 
iii. Issue was Created in a “does not count for time” status and it stay in “does not count for time status” until it reached the to “To Event” and the Widget is configured to “only account for time spent in statuses included in the Issue Lifecycle Time Measurements”

Physical Name Attribute/Column Name Definition Physical Data Type
life_cycle_event_id Life Cycle Event Id The automatically generated primary key of the life_cycle_event table. int
life_cycle_event_name Life Cycle Event Name The name of the Life Cycle Event. varchar(255)
life_cycle_event_descr Life Cycle Event Description A descriptive name of the Life Cycle Event. This descriptive name may be localized. varchar(512)
prev_timing Previous Timing false A table with a generic timestamp. The timestamp is used to give the timestamps of previous and second previous timestamps according to a step as specified in the choices the user has for comparison reports.
Physical Name Attribute/Column Name Definition Physical Data Type
prev_day Previous Day The date for the start of the previous day for the start of the day that contains the current date hour local time. datetime
prev_day_of_week Previous Day of Week The date for the start of the day one week back from the current date hour local time. datetime
prev_day_of_month Previous Day of Month The date for the start of the day one month back from the current date hour local time. datetime
prev_day_of_quarter Previous Day of Quarter The date for the start of the day one quarter back from the current date hour local time. datetime
prev_day_of_year Previous Day of Year The date for the start of the day one year back from the current date hour local time. datetime
prev_week Previous Week The date for the start of the week one week back from the start of the week that contains the current date hour local time. datetime
prev_week_of_month Previous Week of Month The date for the start of the week whose number in the previous month corresponds to the week number in the current month of the current date hour local time. datetime
prev_week_of_quarter Previous Week of Quarter The date for the start of the week whose number in the previous quarter corresponds to the week number in the current quarter of the current date hour local time. datetime
prev_week_of_year Previous Week of Year The date for the start of the week whose number in the previous year corresponds to the week number in the current year of the current date hour local time. datetime
prev_month Previous Month The start of the month one month before the month of the current date hour local time. datetime
prev_month_of_quarter Previous Month of Quarter The start of the month three months before the month of the current date hour local time. datetime
prev_month_of_year Previous Month of Year The start of the month twelve months before the month of the current date hour local time. datetime
prev_quarter Previous Quarter The start of the quarter three months before the quarter of the current date hour local time. datetime
prev_quarter_of_year Previous Quarter of Year The start of the quarter twelve months before the quarter of the current date hour local time. datetime
prev_year Previous Year The start of the year one year before the year of the current date hour local time. datetime
date_hour_local 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,
 
This ties the previous and second previous dates to the date hour local of the time dimesnion table.
datetime
second_prev_day Second Previous Day The date for the start of the second previous day for the start of the day that contains the current date hour local time. datetime
second_prev_day_of_week Second Previous Day of Week The date for the start of the day two weeks back from the current date hour local time. datetime
second_prev_day_of_month Second Previous Day of Month The date for the start of the day two months back from the current date hour local time. datetime
second_prev_day_of_quarter Second Previous Day of Quarter The date for the start of the day two quarters back from the current date hour local time. datetime
second_prev_day_of_year Second Previous Day of Year The date for the start of the day two years back from the current date hour local time. datetime
second_prev_week Second Previous Week The date for the start of the week two weeks back from the start of the week that contains the current date hour local time. datetime
second_prev_week_of_month Second Previous Week of Month The date for the start of the week whose number in the second previous month corresponds to the week number in the current month of the current date hour local time. datetime
second_prev_week_of_quarter Second Previous Week of Quarter The date for the start of the week whose number in the second previous quarter corresponds to the week number in the current quarter of the current date hour local time. datetime
second_prev_week_of_year Second Previous Week of Year The date for the start of the week whose number in the second previous year corresponds to the week number in the current year of the current date hour local time. datetime
second_prev_month Second Previous Month The start of the month two months before the month of the current date hour local time. datetime
second_prev_month_of_quarter Second Previous Month of Quarter The start of the month six months before the month of the current date hour local time. datetime
second_prev_month_of_year Second Previous Month of Year The start of the month 24 months before the month of the current date hour local time. datetime
second_prev_quarter Second Previous Quarter The start of the quarter six months before the quarter of the current date hour local time. datetime
second_prev_quarter_of_year Second Previous Quarter of Year The start of the quarter 24 months before the quarter of the current date hour local time. datetime
second_prev_year Second Previous Year The start of the year two years before the year of the current date hour local time. datetime
time_dim Time Dimension false A table with a generic timestamp. The timestamp is broken up into the specific pieces that the reports need to slice the information according to the user specifications.
Physical Name Attribute/Column Name Definition Physical Data Type
day_of_week Day of Week Integer Day of Week. int
td_day TD Day Integer value for the day portion of the date hour local. int
td_week TD Week Integer value for the week of the year based on the ISO 8601 system used for calculating the week of the year.
 
The first week of the year corresponds to the first week in the year of the Gregorian calendar that contains 4 days starting on a Monday. Weeks are then incremented each Monday, until the next years count starts
int
td_month TD Month Integer value for the month portion of the date hour local. int
td_quarter TD Quarter Integer value for the quarter of the year. This is based on divding 12 months into 4 quarters of three consecutive months. int
td_year TD Year Integer value for the year portion of the date hour local. int
day_date Day Date Today's date with time truncated. datetime
start_of_week_date Start of Week Date The start of the week that contains the current date hour local time. datetime
start_of_month_date Start of Month Date The start of the month that contains the current date hour local time. datetime
start_of_quarter_date Start of Quarter Date The start of the quarter that contains the current date hour local time. datetime
start_of_year_date Start of Year Date The start of the year that contains the current date hour local time. datetime
td_hour TD Hour Integer value for the hour portion of the date hour local. int
day_of_month Day of Month Integer Day of Month. int
day_of_quarter Day of Quarter Integer Day of Quarter. int
day_of_year Day of Year Integer Day of Year int
week_of_month Week of Month Integer Week of month based on the ISO 8601 system used for calculating the week of the year.
 
The first week of the month corresponds to the first week in the month of the Gregorian calendar that contains 4 days starting on a Monday.
 

int
week_of_quarter Week of Quarter Integer Week of quarter based on the ISO 8601 system used for calculating the week of the year.
 
The first week of the quarter corresponds to the first week in the first quarter month of the Gregorian calendar that contains 4 days starting on a Monday.
int
date_hour_local 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, datetime
end_of_hour_date End of Hour Date The next hour increment of the current date hour local time. datetime
end_of_day_date End of Day Date The next day increment of the current date hour local time. datetime
end_of_week_date End of Week Date The next week increment of the current date hour local time. datetime
end_of_month_date End of Month Date The next month increment of the current date hour local time. datetime
end_of_quarter_date End of Quarter Date The next quarter increment of the current date hour local time. datetime
end_of_year_date End of Year Date The next year increment of the current date hour local time. datetime
time_zone_local_hour Time Zone Local Hour false A bridge table that provides the UTC begin and End Hour datetimes for a local hour in an Olson timezone region. This table is used in Trend Reporting to give a high performance date calculation so that local hours and days can be used on graph axes and report heading with calculated totals. It is also used to load the summary tables using the local time dimension. High speed correct database-side time zone conversions are made by joining to this table.
Physical Name Attribute/Column Name Definition Physical Data Type
begin_date_hour_utc Begin Date Hour UTC A date whole hour in the local timezone for the Begin Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect. datetime
end_date_hour_utc End Date Hour UTC A date whole hour in the local timezone for the End Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect. datetime
begin_date_hour_local Begin Date Hour Local A date time in UTC that cooresponds to a whole hour date in the local timezone for the Begin 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, datetime
end_date_hour_local End Date Hour Local A date time in UTC that cooresponds to a whole hour date in the local timezone for the End 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, datetime
begin_is_daylight_savings Begin Is Daylight Savings Flag indicating if daylight savings is in effect for the begin time. This helps identify the special hours that are missing or duplicated during daylight savings days. bit
time_zone_region_id Time Zone Region Id An alternate Key to the Time Zone Region table, which is arbitrarily calculated and assigned by FootPrints. The reason for this is performance of the Foreign Key join to large numbers of rows. There is no suitable short data length key available in a published standard data source. bigint
time_zone_local_hour_id Time Zone Local Hour Id The automatically generated primary key of the time_zone_local_hour table. bigint
begin_date_hour_local_s Begin Date Hour Local S A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
end_date_hour_local_s End Date Hour Local S A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
begin_date_hour_utc_s Begin Date Hour UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
end_date_hour_utc_s End Date Hour UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
end_is_daylight_savings End Is Daylight Savings Flag indicating if daylight savings is in effect for the end time. This helps identify the special hours that are missing or duplicated during daylight savings days. bit
time_zone_region Time Zone Region false A region of the world defined in the Olson time zone database. Only the entries actually used by a FootPrints instance will be stored. The localized name of the region is provided by the Java.Util.Timezone getDisplayName() method. Only the java class can be used to determine the time zone offset from GMT, which varies historically and in the future. See the Time Zone Local Hour table. Take aspirin as needed.
Physical Name Attribute/Column Name Definition Physical Data Type
time_zone_region_name Time Zone Region Name The name of the Time Zone Region. This is 2. The 'primary key' of the java timezone is the string region identifier in English, example 'America/Phoenix'. There is no numeric identifier. As the JRE is updated, or specific classes are updated, the list could change to include new entries, and the detail data behind the regions could change. varchar(255)
time_zone_region_abbr Time Zone Region Abbreviation A well-known string identifier used as a code to identify the Time Zone Region. This could be provided by user entry, but it is not available from the Timezone class. It should probably not be used. varchar(255)
time_zone_region_id Time Zone Region Id An alternate Key to the Time Zone Region table, which is arbitrarily calculated and assigned by FootPrints. The reason for this is performance of the Foreign Key join to large numbers of rows. There is no suitable short data length key available in a published standard data source. bigint