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.
| 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 |
| 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 |
| 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. |
This page was last edited on June 27, 2018, at 10:22.
Comments or questions about this documentation? Contact us for support!