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 |