Jump to: navigation, search

DATE_TIME Dimension

The DATE_TIME dimension allows facts to be described by attributes of calendar date and 15-minute time interval. This is a static dimension.

The DATE_TIME Dimension
Field Data Type Description
DATE_TIME_KEY int 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 int 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 int 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 int 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 int 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 int 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 int 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 int 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 int Points to the next record of this table. This value is DATE_TIME_KEY+1.
DATE_TIME_NEXT_30MIN_KEY int Points to the DATE_TIME_30MIN_KEY record that represents the next 30-minute period.
DATE_TIME_NEXT_HOUR_KEY int Points to the DATE_TIME_HOUR_KEY record that represents the next hour.
DATE_TIME_NEXT_DAY_KEY int Points to the DATE_TIME_DAY_KEY record that represents the next calendar day.
DATE_TIME_NEXT_WEEK_KEY int Points to the DATE_TIME_WEEK_KEY record that represents the next calendar week.
DATE_TIME_NEXT_MONTH_KEY int Points to the DATE_TIME_MONTH_KEY record that represents the next calendar month.
DATE_TIME_NEXT_QUARTER_KEY int Points to the DATE_TIME_QUARTER_KEY record that represents the next calendar quarter.
DATE_TIME_NEXT_YEAR_KEY int Points to the DATE_TIME_YEAR_KEY record that represents the next year.
CREATE_AUDIT_KEY int The surrogate key used to join to the ETL_AUDIT System dimension. 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 int The surrogate key used to join to the ETL_AUDIT System dimension. Specifies the lineage for data update. This value can be useful for aggregation, EAI, and ETL tools—that is, applications that need to identify recently modified data.
CAL_DATE datetime The date/time data type for a calendar date that is specific for this RDBMS.
CAL_DAY_NAME varchar(32) The calendar day name—for example, "Sunday".
CAL_MONTH_NAME varchar(32) The calendar month name—for example, "January".
CAL_DAY_NUM_IN_WEEK smallint The day number in a week. By default, the values start with 1 for Sunday and end with 7 for Saturday.
CAL_DAY_NUM_IN_MONTH smallint 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 smallint 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 tinyint 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 tinyint 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 smallint 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 smallint The year number for the week to which this day belongs.
CAL_WEEK_START_DATE 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, this value is March 6, 2011 for all dates between March 6, 2011 and March 12, 2011.
CAL_WEEK_END_DATE 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, this value is March 6, 2011 for all dates between March 6, 2011 and March 12, 2011.
CAL_MONTH_NUM_IN_YEAR smallint The month number in the calendar year, starting with 1 for January and ending with 12 for December.
CAL_QUARTER_NUM_IN_YEAR smallint 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 smallint 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 smallint The Gregorian calendar year, expressed as a four-digit integer—for example, 2011.
CAL_HOUR_NUM_IN_DAY smallint 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 smallint The hour of the day, as an integer from 00 to 23.
CAL_MINUTE_NUM_IN_HOUR smallint The 15-minute number of the hour. This value is one of the following:

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 smallint The 30-minute number of the hour. This value is one of the following:

0: for 0 <= min < 30
30: for 30 <= min < 60

LABEL_YYYY varchar(32) 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, 2011, at 15:45 is "2011".
LABEL_YYYY_QQ varchar(32) 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, 2011, at 15:45 is "2011 1Q".
LABEL_YYYY_MM varchar(32) 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, 2011, at 15:45 is "2011-01".
LABEL_YYYY_WE varchar(32) 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, 2011, at 15:45 is "2011-W05" (January 30, 2011 fell in the fifth week of the year).
LABEL_YYYY_WE_D varchar(32) 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, 2011, at 15:45 is "2011-05-1" (January 30, 2011 fell in the fifth week of the year, and Sunday is the first day of the week).
LABEL_YYYY_MM_DD varchar(32) 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, 2011, at 15:45 is "2011-01-30".
LABEL_YYYY_MM_DD_HH varchar(32) 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 3, 2011, at 15:45 is "2011-01-30 03".
LABEL_YYYY_MM_DD_HH24 varchar(32) 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, 2011, at 15:45 is "2011-01-30 15".
LABEL_YYYY_MM_DD_HH_30MI varchar(32) 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, 2011, at 15:45 is "2011-01-30 03:30".
LABEL_YYYY_MM_DD_HH24_30MI varchar(32) 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, 2011, at 15:45 is "2011-01-30 15:30".
LABEL_YYYY_MM_DD_HH_MI varchar(32) 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, 2011, at 15:45 is "2011-01-30 03:45".
LABEL_YYYY_MM_DD_HH24_MI varchar(32) 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, 2011, at 15:45 is "2011-01-30 15:45".
LABEL_YYYY_MM_DD_HH_15INT varchar(32) 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, 2011, at 15:45 is "2011-01-30 03:45-04:00".
LABEL_YYYY_MM_DD_HH24_15INT varchar(32) 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, 2011, at 15:45 is "2011-01-30 15:45-16:00".
LABEL_YYYY_MM_DD_HH_30INT varchar(32) 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, 2011, at 15:45 is "2011-01-30 03:30-04:00".
LABEL_YYYY_MM_DD_HH24_30INT varchar(32) 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, 2011, at 15:45 is "2011-01-30 15:30-16:00".
LABEL_QQ varchar(32) 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, 2011, at 15:45 is "1Q".
LABEL_MM varchar(32) 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, 2011, at 15:45 is "01".
LABEL_WE varchar(32) 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, 2011, at 15:45 is "W05". (January 30, 2011 falls in the fifth week of the year.)
LABEL_DD varchar(32) 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, 2011, at 15:45 is "30".
LABEL_HH varchar(32) 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, 2011, at 15:45 is "03".
LABEL_HH24 varchar(32) 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, 2011, at 15:45 is "15".
LABEL_30MI varchar(32) 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, 2011, at 15:45 is "30".
LABEL_MI varchar(32) 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, 2011, at 15:45 is "45".
LABEL_TZ varchar(32) 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 varchar(4) Indicates the period between midnight and noon ("AM") or between noon and midnight ("PM").
RUNNING_YEAR_NUM int The running year number, starting with 1 for the year that is populated as the first year in this calendar. By default, the calendar starts with the year that precedes the DATE_TIME table initialization. For example, if the iWD Data Mart is initiated in year 2011, this field stores the value of 2 for rows that are generated for 2011 dates.
RUNNING_QUARTER_NUM int 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, 2011); 5–8, respectively, for the four quarters of the second populated year (in this example, 2011); and so forth.
RUNNING_MONTH_NUM int 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, 2011); 13–24, respectively, for the 12 months of the second populated year (in this example, 2012); and so forth.
RUNNING_WEEK_NUM int 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, this value is 1–53, respectively, for the 53 weeks of the first populated year (for example, 2011); 54–107, respectively, for the 53 weeks of the second populated year (in this example, 2012); and so forth.
RUNNING_DAY_NUM int 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, 2011); 366–730, respectively, for the 365 days of the second populated year (in this example, 2012); and so forth.
RUNNING_HOUR_NUM int 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/2011); 25–48, respectively, for the 24 hours of the second populated day (in this example, 1/2/2011); and so forth.
RUNNING_30MIN_NUM int 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/2011, if 2011 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.
EVENT_DATE_KEY int Key to the EVENT_DATE dimension, describing the date.
EVENT_TIME_KEY int Key to the EVENT_TIME dimension, describing the time.

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on June 27, 2018, at 02:22.