In the Column List:
- P = Primary key
- M = Mandatory field
- F = Foreign key (where the term is used loosely to indicate a surrogate key reference to a field in another table, not a formal constraint)
- DV = Default value
Table DATE_TIME
Description
Allows facts to be described by attributes of calendar date and 15-minute time interval. This dimension is a calendar — either default or defined in configuration. The table is first populated for a configurable time period in which the schema is initialized and is subsequently populated for the next time period as part of maintenance. Configuration of a time zone and week-numbering rules affect the data population for this table. Each row describes a 15-minute time interval for one calendar date. A single row that contains a date in 2025 is included to serve a special purpose: this future date earmarks a tentative end time for active facts so that applications do not have to test for null. This table enables aggregation along an arbitrary time interval.
Custom DATE_TIME tables can be added to the schema at any point during or after the Genesys Info Mart deployment. These tables have the same structure as the DATE_TIME table, are controlled with dedicated configuration options, and are populated by using algorithms that are similar to those for the DATE_TIME table.
By default, the DATE_TIME calendar is a Gregorian, not a fiscal, calendar. Values that describe the weeks in which dates belong are fixed to begin on Sunday, with the exception of the first week of the year, which may contain fewer than seven days and may start on a day other than Sunday. The last week of a year may also contain fewer than seven days. This setting is referred to as "simple week numbering" because the calendar year and the week-numbering year coincide. By customizing settings in the date-time configuration section before Genesys Info Mart is initialized, you can change the week starting day, the minimum number of days in the first week of the year, and the time zone. Alternatively, by changing the fiscal-year-week-pattern setting, you can configure the calendar to be a fiscal one.
If you want to change any of the fundamental features of the DATE_TIME dimension during runtime, you must take special steps to avoid introducing inconsistencies into your calendar data and compromising your reporting results. For information about changing calendar settings during runtime, see the procedure about changing calendar options in the Genesys Info Mart Operations Guide.
Day and month designations (such as "Sunday" and "January") are localizable; other abbreviations, such as "Q" for quarter, are not.
The DATE_TIME_NEXT_* keys facilitate the retrieval of data for a defined reporting interval by identifying all of the rows in the table that define the upper boundary of the reporting interval.
The LABEL_* fields provide various string representations of a standard calendar date and/or 15-minute interval.
The RUNNING_* fields facilitate the search of facts for the last x number of years, quarters, months, weeks, days, hours, or subhours.
Hint: For easiest viewing, open the downloaded CSV file in Excel and adjust settings for column widths, text wrapping, and so on as desired. Depending on your browser and other system settings, you might need to save the file to your desktop first.
Column List
DATE_TIME_KEY
The primary key of this table. It is used to join a particular 15-minute interval in this table to the fact and aggregate tables. This field increases monotonically to facilitate the calculation of time interval ranges and is equal to the UTC-equivalent time at which the time interval started.
DATE_TIME_30MIN_KEY
The surrogate key that is used to join a particular 30-minute interval in this table to the fact and aggregate tables. Two rows in this table share the same value, which is the DATE_TIME_KEY that represents the start of the 30-minute interval.
DATE_TIME_HOUR_KEY
The surrogate key that is used to join a particular hour in this table to the fact and aggregate tables. Four rows in this table share the same value, which is the DATE_TIME_KEY that represents the start of the hour interval.
DATE_TIME_DAY_KEY
The surrogate key that is used to join a particular day in this table to the fact and aggregate tables. Ninety-six rows in this table share the same value, which is the DATE_TIME_KEY that represents the start of the day interval.
DATE_TIME_WEEK_KEY
The surrogate key that is used to join a particular week in this table to the fact and aggregate tables. Multiple rows in this table share the same value, which is the DATE_TIME_KEY that represents the start of the week interval.
DATE_TIME_MONTH_KEY
The surrogate key that is used to join a particular month in this table to the fact and aggregate tables. Multiple rows in this table share the same value, which is the DATE_TIME_KEY that represents the start of the month interval.
DATE_TIME_QUARTER_KEY
The surrogate key that is used to join a particular quarter in this table to the fact and aggregate tables. Multiple rows in this table share the same value, which is the DATE_TIME_KEY that represents the start of the quarter interval.
DATE_TIME_YEAR_KEY
The surrogate key that is used to join a particular year in this table to the fact and aggregate tables. Multiple rows in this table share the same value, which is the DATE_TIME_KEY that represents the start of the year interval.
DATE_TIME_NEXT_KEY
Points to the next record of this table. This value is DATE_TIME_KEY+1.
DATE_TIME_NEXT_30MIN_KEY
Points to the DATE_TIME_30MIN_KEY record that represents the next 30-minute period.
DATE_TIME_NEXT_HOUR_KEY
Points to the DATE_TIME_HOUR_KEY record that represents the next hour.
DATE_TIME_NEXT_DAY_KEY
Points to the DATE_TIME_DAY_KEY record that represents the next calendar day.
DATE_TIME_NEXT_WEEK_KEY
Points to the DATE_TIME_WEEK_KEY record that represents the next calendar week.
DATE_TIME_NEXT_MONTH_KEY
Points to the DATE_TIME_MONTH_KEY record that represents the next calendar month.
DATE_TIME_NEXT_QUARTER_KEY
Points to the DATE_TIME_QUARTER_KEY record that represents the next calendar quarter.
DATE_TIME_NEXT_YEAR_KEY
Points to the DATE_TIME_YEAR_KEY record that represents the next year.
CREATE_AUDIT_KEY
The surrogate key that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value can be useful for aggregation, enterprise application integration (EAI), and ETL tools — that is, applications that need to identify newly added data.
UPDATE_AUDIT_KEY
The surrogate key that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value can be useful for aggregation, enterprise application integration (EAI), and ETL tools — that is, applications that need to identify recently modified data.
CAL_DATE
The date/time data type for a calendar date that is specific for this RDBMS.
CAL_DAY_NAME
The calendar day name — for example, "Sunday".
CAL_MONTH_NAME
The calendar month name — for example, "January".
CAL_DAY_NUM_IN_WEEK
The day number in a week. By default, the values start with 1 for Sunday and end with 7 for Saturday. If another day is configured as the first day of the week, the value 1 is populated for that day, the value 2 is populated for the subsequent day, and so forth. For example, if Monday is configured as the first day of the week (that is, the first-day-of-week configuration option is set to 2), the CAL_DAY_NUM_IN_WEEK values start with 1 for Monday and end with 7 for Sunday.
CAL_DAY_NUM_IN_MONTH
The day number in the calendar month, starting with 1 and ending with 28, 29, 30, or 31, depending on the month.
CAL_DAY_NUM_IN_YEAR
The day number in the calendar year, starting with 1 for January 1 and ending with 365 or 366 for December 31.
CAL_LAST_DAY_IN_WEEK
The indicator for the last day of the calendar week: 0 = No, 1 = Yes. For example, this value may be 0 for Wednesday records and 1 for Saturday records.
CAL_LAST_DAY_IN_MONTH
The indicator for the last day of the calendar month: 0 = No, 1 = Yes. For example, this value is set to 0 for January 16 and 1 for January 31.
CAL_WEEK_NUM_IN_YEAR
The week number in the calendar year, starting with 1 and ending with 53. The first week begins on the first day of the calendar year and may contain fewer than seven days. Likewise, the last week, ending with the last day of the year, may contain fewer than seven days.
WEEK_YEAR
The year number for the week to which this day belongs. By default, the week year matches the calendar year. If the week numbering is configured to differ from the simple week numbering (for example, for the purpose of financial reports), the year number that is stored for the first and last weeks differs from the year number of the calendar year.
CAL_WEEK_START_DATE
The start date of the calendar week to which this date belongs. All dates in the same calendar week share the same calendar week start date. For example, if a week starts on Sunday, this value is March 7, 2010 for all dates between March 7, 2010 and March 13, 2010.
CAL_WEEK_END_DATE
The end date of the calendar week to which this date belongs. All dates in the same calendar week share the same calendar week end date. For example, if a week starts on Sunday, this value is March 13, 2010 for all dates between March 7, 2010 and March 13, 2010.
CAL_MONTH_NUM_IN_YEAR
The month number in the calendar year, starting with 1 for January and ending with 12 for December.
CAL_QUARTER_NUM_IN_YEAR
The number of the quarter in the calendar year, starting with 1 for the first quarter (January 1 through March 31) and ending with 4 for the fourth quarter (October 1 through December 31).
CAL_HALF_NUM_IN_YEAR
The number of the half of the calendar year, starting with 1 for January 1 through June 30 and ending with 2 for July 1 through December 31.
CAL_YEAR_NUM
The Gregorian calendar year, expressed as a four-digit integer — for example, 2010.
CAL_HOUR_NUM_IN_DAY
The hour of the day, expressed as an integer from 1-12. This field is intended to be used in conjunction with the AMPM_INDICATOR field.
CAL_HOUR_24_NUM_IN_DAY
The hour of the day, as an integer from 00 to 23.
CAL_MINUTE_NUM_IN_HOUR
The 15-minute number of the hour. This field is set to one of the following values:
- 0 — for 0 <= min < 15
- 15 — for 15 <= min < 30
- 30 — for 30 <= min < 45
- 45 — for 45 <= min < 60
CAL_30MINUTE_NUM_IN_HOUR
The 30-minute number of the hour. This field is set to one of the following values:
- 0 — for 0 <= min < 30
- 30 — for 30 <= min < 60
LABEL_YYYY
The current date expressed as a string in YYYY format, where YYYY represents a four-digit year. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010".
LABEL_YYYY_QQ
The current date, expressed as a string in YYYY QQ format, where QQ represents the number of the quarter (1-4), followed by the letter "Q", which is not localizable. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010 1Q".
LABEL_YYYY_MM
The current date, expressed as a string in YYYY-MM format, where MM represents the two-digit month. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01".
LABEL_YYYY_WE
The current date, expressed as a string in YYYY-Www format, where Www represents the two-digit week number of the year, preceded by the letter "W". This field is useful when it is used as a label in report headers. For example, with simple week numbering, the label that this field stores for January 30, 2010, at 15:45 is "2010-W05" (January 30, 2010 fell in the fifth week of the year).
LABEL_YYYY_WE_D
The current date expressed as a string in YYYY-Www-D format, where Www represents the two-digit week number of the year, preceded by the letter "W", and D represents the day number in the week. This field is useful when used as a label in report headers. For example, with simple week numbering, the label that this field stores for January 30, 2010, at 15:45 is "2010-05-1" (January 30, 2010 fell in the fifth week of the year, and Sunday is the first day of the week).
LABEL_YYYY_MM_DD
The current date, expressed as a string in YYYY-MM-DD format, where DD represents the two-digit day of the month. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30".
LABEL_YYYY_MM_DD_HH
The current date, expressed as a string in YYYY-MM-DD HH format, where hour (HH) values range from 01 to 12. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 03".
LABEL_YYYY_MM_DD_HH24
The current date, expressed as a string in YYYY-MM-DD HH format where hour (HH) values range from 01 to 24. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 15".
LABEL_YYYY_MM_DD_HH_30MI
The current date, expressed as a string in YYYY-MM-DD HH:mm format, where hour (HH) values range from 01 to 12 and mm represents the closest 30-minute period that is less than or equal to the actual minute. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 03:30".
LABEL_YYYY_MM_DD_HH24_30MI
The current date, expressed as a string in YYYY-MM-DD HH:mm format, where hour (HH) values range from 01 to 24 and mm represents the closest 30-minute period that is less than or equal to the actual minute. This field is useful when it is used as a label in report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 15:30".
LABEL_YYYY_MM_DD_HH_MI
The current date, expressed as a string in YYYY-MM-DD HH:mm format, where hour (HH) values range from 01 to 12 and mm represents the actual minute. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 03:45".
LABEL_YYYY_MM_DD_HH24_MI
The current date, expressed as a string in YYYY-MM-DD HH:mm format, where hour (HH) values range from 01 to 24 and mm represents the actual minute. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 15:45".
LABEL_YYYY_MM_DD_HH_15INT
The current date, expressed as a string in YYYY-MM-DD 15INT format, where 15INT represents the 15-minute interval within the day. Hour values range from 01 to 12. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 03:45-04:00".
LABEL_YYYY_MM_DD_HH24_15INT
The current date, expressed as a string in YYYY-MM-DD 15INT format, where 15INT represents the 15-minute interval within the day and includes the hour, in a range from 01 to 24. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 15:45-16:00".
LABEL_YYYY_MM_DD_HH_30INT
The current date, expressed as a string in YYYY-MM-DD 30INT format, where 30INT represents the 30-minute interval within the day and includes the hour, in a range from 01 to 12. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 03:30-04:00".
LABEL_YYYY_MM_DD_HH24_30INT
The current date, expressed as a string in YYYY-MM-DD 30INT format, where 30INT represents the 30-minute interval within the day and includes the hour, in a range from 01 to 24. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "2010-01-30 15:30-16:00".
LABEL_QQ
A string representation of the current date, expressed in QQ format, where QQ represents the number of the quarter (1-4), followed by the letter "Q", which is not localizable. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "1Q".
LABEL_MM
A string representation of the current date, expressed in MM format, where MM represents the two-digit month. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "01".
LABEL_WE
A string representation of the current date, expressed in Www format, where Www represents the two-digit week number of the year, preceded by the letter "W". This field is useful when it is used as a label for report headers. For example, with simple week numbering, the label that this field stores for January 30, 2010, at 15:45 is "W05". (January 30, 2010 falls in the fifth week of the year.)
LABEL_DD
A string representation of the current date, expressed in DD format, where DD represents the two-digit day of the month. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "30".
LABEL_HH
A string representation of the current date, expressed in HH format, where hour (HH) values range from 01 to 12. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "03".
LABEL_HH24
A string representation of the current date, expressed in HH format, where hour (HH) values range from 01 to 24. This field is useful when it is used as a label for report headers. For example, the label that this field stores for January 30, 2010, at 15:45 is "15".
LABEL_30MI
A string representation of the current date, expressed in mm format, where mm represents the closest 30-minute period that is less than or equal to the actual minute. For example, the label that this field stores for January 30, 2010, at 15:45 is "30".
LABEL_MI
A string representation of the current date, expressed in mm format, where mm represents the actual minute. For example, the label that this field stores for January 30, 2010, at 15:45 is "45".
LABEL_TZ
A string representation of the time zone designator, as defined in ISO 8601 standard. For the time zone in which the UTC offset is equal zero, the letter "Z" is stored as the time zone designator. The zone designator for other time zones is specified by the offset from UTC in the format ±HH:<mm>, where HH represents hours and mm represents minutes, if applicable. For example, if the time that is being described is one hour ahead of UTC, the stored value would be "+01".
AMPM_INDICATOR
Indicates the period between midnight and noon ("AM") or between noon and midnight ("PM").
RUNNING_YEAR_NUM
The running year number, starting with 1 for the year that is populated as the first year in this calendar. The date-time-start-year configuration option controls the starting year. By default, the calendar starts with the year that precedes the DATE_TIME table initialization. For example, if the Genesys Info Mart database is initiated in year 2010, this field stores the value of 2 for rows that are generated for 2010 dates.
RUNNING_QUARTER_NUM
The running quarter number, starting with 1 as the first quarter of the first year that is populated for this calendar. Running values do not reset at the beginning of each year, so that this value is 1-4, respectively, for the four quarters of the first populated year (for example, 2009); 5-8, respectively, for the four quarters of the second populated year (in this example, 2010); and so forth.
RUNNING_MONTH_NUM
The running month number, starting with 1 as the first month of the first year that is populated for this calendar. Running values do not reset at the beginning of each year, so that this value is 1-12, respectively, for the 12 months of the first populated year (for example, 2009); 13-24, respectively, for the 12 months of the second populated year (in this example, 2010); and so forth.
RUNNING_WEEK_NUM
The running week number, starting with 1 as the first week of the first year that is populated for this calendar. Running values do not reset at the beginning of each year, so that, with simple week numbering, this value is 1-53, respectively, for the 53 weeks of the first populated year (for example, 2009); 54-107, respectively, for the 53 weeks of the second populated year (in this example, 2010); and so forth.
RUNNING_DAY_NUM
The running day number, starting with 1 as the first day of the first year that is populated for this calendar. Running values do not reset at the beginning of each year, so that this value is 1-365, respectively, for the 365 days of the first populated year (for example, 2009); 366-730, respectively, for the 365 days of the second populated year (in this example, 2010); and so forth.
RUNNING_HOUR_NUM
The running hour number, starting with 1 as the first hour of the first day of the first year that is populated for this calendar. Running hours do not reset at the beginning of each day, so that this value is 1-24, respectively, for the 24 hours of the first populated day (for example, 1/1/2009); 25-48, respectively, for the 24 hours of the second populated day (in this example, 1/2/2009); and so forth.
RUNNING_30MIN_NUM
The running 30-minute number, starting with 1 as the first 30-minute interval of the first hour of the first day of the first year that is populated for this calendar. Running 30-minute periods do not reset at the beginning of each hour, so that this value is 1-2, respectively, for the two 30-minute intervals of the first hour of 1/1/2009, if 2009 is the first year populated for this calendar; 3-4, respectively, for the two 30-minute intervals in the second hour of this day; and so forth.
Index List
| CODE | U | C | Description |
|---|---|---|---|
| IDX_DT_30 | Improves access time, based on a 30-minute key. | ||
| IDX_DT_NEXT30 | Improves access time, based on the next 30-minute key. | ||
| IDX_DT_NEXT | Improves access time, based on the key of the next record. | ||
| IDX_DT_30_INT | Improves access time, based on the 30-minute key, the next 30-minute key, and the primary key. | ||
| IDX_DT_HOUR_INT | Improves access time, based on the hour key, the next hour key, and the primary key. | ||
| IDX_DT_DAY_INT | Improves access time, based on the day key, the next day key, and the primary key. | ||
| IDX_DT_MONTH_INT | Improves access time, based on the month key, the next month key, and the primary key. | ||
| IDX_DT_CAL_DATE | Improves access time, based on the calendar date. |
Index IDX_DT_30
| Field | Sort | Comment |
|---|---|---|
| DATE_TIME_30MIN_KEY | Ascending |
Index IDX_DT_NEXT30
| Field | Sort | Comment |
|---|---|---|
| DATE_TIME_NEXT_30MIN_KEY | Ascending |
Index IDX_DT_NEXT
| Field | Sort | Comment |
|---|---|---|
| DATE_TIME_NEXT_KEY | Ascending |
Index IDX_DT_30_INT
| Field | Sort | Comment |
|---|---|---|
| DATE_TIME_30MIN_KEY | Ascending | |
| DATE_TIME_NEXT_30MIN_KEY | Ascending | |
| DATE_TIME_KEY | Ascending |
Index IDX_DT_HOUR_INT
| Field | Sort | Comment |
|---|---|---|
| DATE_TIME_HOUR_KEY | Ascending | |
| DATE_TIME_NEXT_HOUR_KEY | Ascending | |
| DATE_TIME_KEY | Ascending |
Index IDX_DT_DAY_INT
| Field | Sort | Comment |
|---|---|---|
| DATE_TIME_DAY_KEY | Ascending | |
| DATE_TIME_NEXT_DAY_KEY | Ascending | |
| DATE_TIME_KEY | Ascending |
Index IDX_DT_MONTH_INT
| Field | Sort | Comment |
|---|---|---|
| DATE_TIME_MONTH_KEY | Ascending | |
| DATE_TIME_NEXT_MONTH_KEY | Ascending | |
| DATE_TIME_KEY | Ascending |
Index IDX_DT_CAL_DATE
| Field | Sort | Comment |
|---|---|---|
| CAL_DATE | Ascending |
Subject Areas
- Calling_List_Metric — Represents a snapshot of outbound campaign calling list metrics.
- Calling_List_To_Campaign — Represents the associations between calling lists and campaigns.
- Campaign_Group_Session — Represents campaign groups as they are being loaded and unloaded.
- Campaign_Group_State — Represents campaign groups from the perspective of states they go through, such as “Loaded", "Started", and "Unloading".
- Campaign_Group_To_Campaign — Represents the associations between agent groups or place groups and campaigns.
- Contact_Attempt — Represents outbound campaign contact record attempts. An attempt may or may not include dialing.
- Interaction — Represents interactions from the perspective of a customer experience.
- Interaction_Resource — Represents a summary of each attempt to handle an interaction. It encompasses the mediation process that is required to offer the interaction to a target handling resource, as well as the activities of that target handling resource.
- Interaction_Resource_State — Allows facts to be described by the state of the associated agent resource. Each row describes one distinct media-specific agent state.
- Mediation_Segment — Represents interaction activity from the perspective of contact center ACD queues, virtual queues, interaction queues, and interaction workbins, as well as groups thereof.
- Place_Group — Represents the membership of places among place groups.
- Resource_Group — Represents the membership of contact center resources among resource groups.
- Resource_Skill — Represents the skill resumes of agent resources.
- Summary_Resource_Session — Represents agent resource media sessions from login to logout, summarized to the media type.
- Summary_Resource_State — Represents agent resource states, summarized to the media type.
- Summary_Resource_State_Reason — Represents agent resource state reasons, summarized to the media type.