Jump to: navigation, search

Changes to Time-Related Tables and Fields

DevGuide2.png Purpose: To provide a high-level description of changes to date- and time-related tables and fields in the Info Mart 8.x schema.

Major Highlights

Genesys Info Mart 8.x supports multiple, customizable calendars with flexible week-numbering rules that can be configured to conform to the ISO 8601 standard for the representation of dates and times. These calendars are added as custom dimension tables to the Info Mart database schema.

Genesys Info Mart 8.x stores time facts in UTC time. Scalable support for multiple calendars means that Genesys Info Mart can be configured to express time data in any Java time-zone format.

Genesys Info Mart 8.x provides one default calendar (DATE_TIME dimension). The default configuration expresses UTC time in the GMT time zone and conforms to legacy Genesys Info Mart week-numbering rules, which are not the ISO 8601 standard.

The number of columns in the DATE_TIME dimension table has been reduced.

Unified Date_Time Fields in Fact Tables

The implementation of the DATE_TIME dimension is simplified by reducing to four the number of DATE_TIME fields in fact tables. START_TS and END_TS fields in fact tables store time in the UTC format, while START_DATE_TIME_KEY and END_DATE_TIME_KEY reference the DATE_TIME dimension.

Column Description
START_DATE_TIME_KEY Identifies the start of a 15-minute interval in which the fact began. Use this value as a key to join the fact tables to any configured DATE_TIME dimensions, in order to group the facts that are related to the same interval and/or convert the START_TS timestamp to an appropriate time zone.
END_DATE_TIME_KEY Identifies the start of a 15-minute interval in which the fact ended. Use this value as a key to join the fact tables to any configured DATE_TIME dimensions, in order to group the facts that are related to the same interval and/or convert the END_TS timestamp to an appropriate time zone.
START_TS The date and time at which the fact began, as a Coordinated Universal Time (UTC) value--the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).
END_TS The date and time at which the fact ended, as a Coordinated Universal Time (UTC) value--the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).

Obsolete Date_Time Fields in Fact Tables

The following DATE_TIME fields have been removed from various fact tables:

  • GMT_END_TIME
  • GMT_ENTERPRISE_DATE_KEY
  • GMT_START_TIME
  • GMT_TENANT_DATE_KEY
  • GMT_TIME_OF_DAY_KEY
  • LOCAL_END_TIME
  • LOCAL_ENTERPRISE_DATE_KEY
  • LOCAL_START_TIME
  • LOCAL_TENANT_DATE_KEY
  • LOCAL_TIME_OF_DAY_KEY
  • STD_ENTERPRISE_DATE_KEY
  • STD_ENTERPRISE_END_TIME
  • STD_ENTERPRISE_START_TIME
  • STD_ENTERPRISE_TIME_OF_DAY_KEY
  • STD_TENANT_DATE_KEY
  • STD_TENANT_END_TIME
  • STD_TENANT_START_TIME
  • STD_TENANT_TIME_OF_DAY_KEY

Obsolete DATE_TIME Tables

The following date and time dimension tables have been retired in release 8.x:

  • ENTERPRISE_DATE
  • ENTERPRISE_MONTH
  • TENANT_DATE
  • TIME_OF_DAY

For compatibility information about the DATE_TIME dimension, see DATE_TIME.

This page was last edited on July 17, 2020, at 16:05.
blog comments powered by Disqus