Sample Date Dimension Table Date - Date dimensionColumn / Alternative | Data Type / Comment | Description / Examples / Notes | Date_Key DateKey | MediumInt | Surrogate key sequential number that uniquely identifies a date. ... 292200, 292201, 292203, ... and 0 (for not applicable). This key lists dates in correct order when sorted. Since
MySQL dates begin with 1000-01-01, we suggest using the number of days
since the day before 1000-01-01 as the Date_Key for a date, plus 0 for
not applicable. This
key, often with a qualifying prefix, is also one or more columns in
fact tables where each Date_Key column relates a fact to a date. For
example, Effective_Date_Key and Posted_Date_Key. | Academic_Period_End_ Date_ID AcademicPeriodEndDateID | Char (10) 'lookup' or Date | Text
that identifies the end date of an academic period using its calendar
year number, calendar month number, and day of calendar month number,
with leading zeroes, separated by hyphens, in the format yyyy-mm-dd. ... 2011-12-31, 2012-01-31, 2012-05-31, 2012-08-31, ... and NA. If preferred, we could make this column a Date data type named AcademicPeriodEndDate. | Academic_Period_ID AcademicPeriodID | Char or VarChar (?) 'lookup' | Text that identifies an academic period, without regard to the year. 1-Fall Semester, 2-Winter Intersession, 3-Spring Semester, 4-Summer Session, ... and NA. | Academic_Year_Day_ID AcademicYearDayID | Char (9) | Text
that identifies a day of an academic year using the number of the
calendar year in which the academic year ends and the number of the day
of the academic year, with leading zeroes, in the format Ayyyy.ddd. ... A2011.001, A2011.002, ... A2011.366, ... and NA. | Academic_Year_ID AcademicYearID | | Text that identifies an academic year using the number of the calendar year in which the academic year ends. ... 2010, 2011, 2012, ... and NA. If
preferred, we could add an A, or AY, before each number, making the
examples ... A2010, A2011, A2012, ... and NA; or ... AY2010, AY2011,
AY2012, ... and NA. |
| Academic_Year_Period_ ID AcademicYearPeriodID | Char or VarChar (?) 'lookup' | Text
that identifies an academic period of an academic year using the number
of the calendar year in which the academic year ends and the academic
period identifier. A2011.1-Fall Semester, A2011.2-Winter Intersession, A2011.3-Spring Semester, A2011.4-Summer Session, ... and NA. | Calendar_Half_Year_ID HalfID | Char (1) | Text that identifies a half of a calendar year using its number, without regard to the year. 1, 2, and N (for not applicable). If preferred, we could add an H before each number, making the examples H1, H2, and NA. | Calendar_Month_ID MonthID | Char (3) 'lookup' | Text that identifies a calendar month using the first three letters of the month's name. Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, and NA. | Calendar_Month_Name MonthName | VarChar (8) 'lookup' | Name of a calendar month. January, February, March, ... November, December, and NA. | Calendar_Month_Number MonthNumber | TinyInt | Sequential number of a month of a calendar year, without regard to the year. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, and 0 (for not applicable). | Calendar_Month_Short_ID MonthShortID | Char (2) 'lookup' | Text that identifies a calendar month using one or two of the first three letters of the month's name. Ja, F, Mr, Ap, My, Jn, Jl, Au, S, O, N, D, and NA. | Calendar_Quarter_Year_ID QuarterID | Char (1) | Text that identifies a quarter of a calendar year using its number, without regard to the year. 1, 2, 3, 4, and N (for not applicable). If preferred, we could add a Q before each number, making the values Q1, Q2, Q3, Q4, and NA. | Calendar_Week_End_Date WeekEndDate | Date | Date a calendar week ends, in the format yyyy-mm-dd. ... 2011-01-01, 2011-01-08, ... 2011-12-24, 2011-12-31, 2012-01-07, ... and 0000-00-00 for not applicable. | Calendar_Week_ID WeekID | Char (2) 'lookup' | Text
that identifies a calendar week using the number of the Saturday on
which the week of the calendar year ends, without identifying the year. 1, 2, 3, ... 51, 52, 53, and NA. If preferred, we could add a W before each number, making the examples W1, W2, W3, ... W51, W52, W53, and NA. Sorting
on this ID, which does not include a leading zero or space, does not
list weeks in correct order; order by Calendar_Week_Number to list weeks
in chronological order within a calendar year. Alternatively,
if we added a leading zero or space to the IDs for weeks 1 through 9,
making those IDs 01 through 09 or 1 through 9, ordering by this ID
would list weeks in correct order and we might not need the column
Calendar_Week_Number. When January 1 is not a Sunday, the first week of a calendar year starts during the previous year. When December 31 is not a Saturday, the last week of the calendar year ends before the year ends. | Calendar_Week_Number WeekNumber | TinyInt | Sequential number of the Saturday on which a week of a calendar week ends, without identifying the year. 1, 2, 3, ... 51, 52, 53, and 0 (for not applicable). When January 1 is not a Sunday, the first week of a calendar year starts during the previous year. When December 31 is not a Saturday, the last week of a calendar year ends before the year ends. | Calendar_Year_Day_ID YearDayID | Char (8) | Text
that identifies a day of a specific calendar year using the number of
the calendar year and the number of the day of the calendar year, with
leading zeroes, in the format yyyy.ddd. ... 2011.001, 2011.002, ... 2011.366, ... and NA. | Calendar_Year_Half_ID YearHalfID | Char (7) 'lookup' | Text
that identifies a half of a specific calendar year using the number of
the calendar year and the number of the half of the calendar year, in
the format yyyy.Hh. ... 2011.H1, 2011.H2, ... 2012.H1, ... and NA. | Calendar_Year_ID YearID | Char (4) 'lookup' | Text that identifies a calendar year using its number in the common era. ... 2010, 2011, 2012, ... and NA. | Calendar_Year_Month_ID YearMonthID | Char (10) 'lookup' | Text
that identifies a month of a specific calendar year using the number of
the calendar year, the number of the month of the calendar year, with
leading zeroes, and the first three letters of the month's name, in the
format yyyy-mmMon. ... 2011-01Jan, 2011-02Feb, ... 2011-12Dec, ... and NA. If
preferred, we could omit the first three letters of the month's name,
making the examples ... 2011-01, 2011-02 ... 2011-12, and NA. | Calendar_Year_Number YearNumber | SmallInt | Sequential number of a calendar year in the common era. ... 2010, 2011, 2012, ... and 0 (for not applicable). | Calendar_Year_Quarter_ID YearQuarterID | Char (7) 'lookup' | Text
that identifies a quarter of a specific calendar year using the number
of the calendar year and the number of the quarter of the calendar year,
in the format yyyy.Qq. ... 2011.Q1, 2011.Q2, 2011.Q3, 2011.Q4, ... 2012.Q1, ... and NA. | Calendar_Year_Week_ID YearWeekID | Char (8) | Text
that identifies a week of a specific calendar year using the number of
the calendar year and the number of the Sunday on which the week of the
calendar year ends, with leading zeroes, in the format yyyy.Www ... 2011.W01, 2011.W02, ... 2011.W52, 2011.W53, 2012.W01, and NA. When January 1 is not a Sunday, the first week of a calendar year starts during the previous year. When December 31 is not a Saturday, the last week of a calendar year ends before the year ends. | Civil_Holiday_Flag IsCivilHoliday | Char (3) 'lookup' | Text that indicates whether a date is a civil, i.e., public, holiday. No, Yes, and NA. http://en.wikipedia.org/wiki/Civic_holiday says: "A
civil holiday, civic holiday, legal holiday, or work holiday is a day
that is legally recognized and celebrated as a holiday in a particular
sovereign state or jurisdictional subdivision of such, e.g., a state or a
province and may be known as a public holiday. It is usually a day that
the legislature, parliament, congress, or sovereign has declared by
statute, edict, or decree as a non-working day when the official arms of
government such as the court system are closed." | Civil_Holiday_Name CivilHolidayName | VarChar (30) 'lookup' | Name of a civil holiday. ... Independence Day, Memorial Day, New Year's Day, None, Thanksgiving Day, ... and NA. | Date | Date | Date, in the format yyyy-mm-dd, to which date functions, including date_format, can be applied. ... 2011-01-01, 2011-01-02, ... 2011-12-31, 2012-01-01, ... and 0000-00-00 for not applicable. | Date_ID DateID | Char (10) | Text
that identifies a date using its calendar year number, month number,
and day of month number, with leading zeroes, separated by hyphens, in
the format yyyy-mm-dd. ... 2011-01-01, 2011-01-02, ... 2011-12-31, 2012-01-01, ... and NA. | Date_Name DateName | VarChar (17) | Name
of a date using its calendar month name, day of month number, without
leading zeroes, and year number, in the format Month d, yyyy. ... January 1, 2011, January 2, 2011, ... December 31, 2011, January 1, 2012, ... and NA. | Date_Type DateType | Char (4) 'lookup' | Type of date, i.e., whether the row is an actual date or is not applicable. Date and NA. | Day_of_Academic_Period_ ID DayOfAcademicPeriodID | Char (3) 'lookup' | Text
that identifies a day of an academic period using the number of the day
of the academic period, without identifying the period. 1, 2, 3, ... and NA. Sorting
on this ID, which does not include leading zeroes or spaces, does not
list days in correct order if the period has more than 9 days; order by
Day_of_Academic_Period_Number to list days in chronological order within
an academic period. Alternatively,
if we added leading zeroes or spaces to the IDs for any days 1 through
99, making those IDs 001 through 099 or 1 through 99, ordering by
this ID would list days in correct order and we might not need the
column Day_of_Academic_Period_Number. | Day_of_Academic_Period_ Number DayOfAcademicPeriod Number | SmallInt | Sequential number of a day of an academic period, without identifying the period. 1, 2, 3, ... and 0 (for not applicable). | Day_of_Academic_Year_ID DayOfAcademicYearID | Char (3) 'lookup' | Text that identifies a day of an academic year using the number of the day of the academic year, without identifying the year. 1, 2, 3, ... 99, 100, ... 364, 365, 366, and NA. Sorting
on this ID, which does not include leading zeroes or spaces, does not
list days in correct order; order by Day_of_Academic_Year_Number to list
days in chronological order within an academic year. Alternatively,
if we added leading zeroes or spaces to the IDs for days 1 through 99,
making those IDs 001 through 099 or 1 through 99, ordering by this ID
would list days in correct order and we might not need the column
Day_of_Academic_Year_Number. | Day_of_Academic_Year_ Number DayOfAcademicYear Number | SmallInt | Sequential number of a day of an academic year, without identifying the year. 1, 2, 3, ... 99, 100, ... 364, 365, 366, and 0 (for not applicable). | Day_of_Calendar_Month_ ID DayOfMonthID | Char (2) 'lookup' | Text that identifies a day of a calendar month using the number of the day of the calendar month, without identifying the month. 1, 2, 3, ... 9, 10, ... 28, 29, 30, 31, and NA. Sorting
on this ID, which does not include leading zeroes or spaces, does not
list days in correct order; order by Day_of_Calendar_Month_Number to
list days in chronological order within a calendar month. Alternatively,
if we added leading zeroes or spaces to the IDs for days 1 through 9,
making those IDs 01 through 09 or 1 through 9, ordering by this ID
would list days in correct order and we might not need the column
Day_of_Calendar_Month_Number. | Day_of_Calendar_Month_ Number DayOfMonthNumber | TinylInt | Sequential number of a day of a calendar month, without identifying the month. 1, 2, 3, ... 9, 10, ... 28, 29, 30, 31, and 0 (for not applicable). | Day_of_Calendar_Year_ID DayOfYearID | Char (3) 'lookup' | Text that identifies a day of a calendar year using the number of the day of the calendar year, without identifying the year. 1, 2, 3, ... 99, 100, ... 364, 365, 366, and NA. Sorting
on this ID, which does not include leading zeroes or spaces, does not
list days in correct order; order by Day_of_Calendar_Year_Number to list
days in chronological order within a calendar year. Alternatively,
if we added leading zeroes or spaces to the IDs for days 1 through 99,
making those IDs 001 through 099 or 1 through 99, ordering by this ID
would list days in correct order and we might not need the column
Day_of_Calendar_Year_Number. | Day_of_Calendar_Year_ Number DayOfYearNumber | SmallInt | Sequential number of a day of a calendar year, without identifying the year. 1, 2, 3, ... 99, 100, ... 364, 365, 366, and 0 (for not applicable). | Day_of_Fiscal_Month_ Number DayOfFiscalMonthNumber | TinyInt | Sequential number of a day of a fiscal month, without identifying the month. 1, 2, 3, ... 9, 10, ... 28, 29, 30, 31, and 0 (for not applicable). | Day_of_Fiscal_Year_ID DayOfFiscalYearID | Char (3) 'lookup' | Text that identifies a day of a fiscal year using the number of the day of the fiscal year, without identifying the year. 1, 2, 3, ... 99, 100, ... 364, 365, 366, and NA. Sorting
on this ID, which does not include leading zeroes or spaces, does not
list days in correct order; order by Day_of_Fiscal_Year_Number to list
days in chronological order within a fiscal year. Alternatively,
if we added leading zeroes, or spaces, to the IDs for days 1 through
99, making those IDs 001 through 099, or 1 through 99, ordering by
this ID would list days in correct order and we might not need the
column Day_of_Fiscal_Year_Number. | Day_of_Fiscal_Year_ Number DayOfFiscalYearNumber | SmallInt | Sequential number of a day of a fiscal year, without identifying the year. 1, 2, 3, ... 99, 100, ... 364, 365, 366, and 0 (for not applicable). | Day_of_Week_ID DayOfWeekID | Char (3) 'lookup' | Text that identifies a day of the week using the first three letters of the day's name. Sun, Mon, Tue, Wed, Thu, Fri, Sat, and NA. | Day_of_Week_Name DayOfWeekName | VarChar (9) 'lookup' | Name of a day of the week. Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and NA. | Day_of_Week_Number DayOfWeekNumber | TinyInt | Sequential number of a day of a week that starts on Sunday, where Saturday is 7. 1, 2, 3, 4, 5, 6, 7, and 0 (for not applicable). | Day_of_Week_Short_ID DayOfWeekShortID | Char (2) 'lookup' | Text that identifies a day of the week using the first one or two letters of the day's name. Su, M, Tu, W, Th, F, Sa, and NA. | Fiscal_Half_Year_ID FiscalHalfID | Char (1) | Text that identifies a fiscal half of a fiscal year using its number, without identifying the year. 1, 2, and N (for not applicable). If preferred, we could add an H, or FH, before each number, making the examples H1, H2, and NA; or FH1, FH2, and NA. | Fiscal_Month_ID FiscalMonthID | Char or VarChar (?) 'lookup' | Text that identifies a fiscal month of a fiscal year, without identifying the year. 01JulAug, 02Sep, 03Oct, ... 11Jun, 12Closing, and NA. 01Oct, 02Nov, 03Dec, ... 11Aug, 12Sep, and NA. | Fiscal_Month_Number FiscalMonthNumber | TinyInt | Sequential number of a fiscal month of a fiscal year, without identifying the year. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, and 0 (for not applicable). | Fiscal_Quarter_Year_ID FiscalQuarterID | Char (1) | Text that identifies a fiscal quarter of a fiscal year using its number, without identifying the year. 1, 2, 3, 4, and N (for not applicable). If
preferred, we could add a Q, or FQ, before each number, making the
examples Q1, Q2, Q3, Q4, and NA; or FQ1, FQ2, FQ3, FQ4, and NA. | Fiscal_Week_ID FiscalWeekID | Char (2) 'lookup' | Text that identifies a fiscal week of a fiscal year using its number, without identifying the year. 1, 2, 3, ... 51, 52, 53, and NA. If
preferred, we could add a W or FW before each number, making the
examples W1, W2, W3, ... W51, W52, W53, and NA; or FW1, FW2, FW3, ...
FW51, FW52, FW53, and NA. Sorting
on this ID, which does not include a leading zero or space, does not
list weeks in correct order; order by Fiscal_Week_Number to list weeks
in chronological order within a fiscal year. Alternatively,
if we added a leading zero or space to the IDs for weeks 1 through 9,
making those IDs 01 through 09 or 1 through 9, ordering by this ID
would list weeks in correct order and we might not need the column
Fiscal_Week_Number. | Fiscal_Week_Number FiscalWeekNumber | TinyInt | Sequential number of a fiscal week of a fiscal year, without identifying the year. 1, 2, 3, ... 51, 52, 53, and 0 (for not applicable). | Fiscal_Year_Day_ID FiscalYearDayID | Char (8) | Text
that identifies a day of a specific fiscal year using the number of the
fiscal year and the number of the day of the fiscal year, with leading
zeroes, in the format Fyyyy.ddd. ... F2011.001, F2011.002, ... F2011.366, ... and NA. | Fiscal_Year_Half_ID FiscalYearHalfID | Char (8) 'lookup' | Text
that identifies a half of a specific fiscal year using the number of
the fiscal year and the number of the half of the fiscal year, in the
format Fyyyy.Hh. ... F2011.H1, F2011.H2, ... F2012.H1, ... and NA. | Fiscal_Year_ID FiscalYearID | Char (4) 'lookup' | Text that identifies a fiscal year using the number of the calendar year in which the fiscal year ends. ... 2010, 2011, 2012, ... and NA. If
preferred, we could add an F, or FY, before each number, making the
examples ... F2010, F2011, F2012, ... and NA; or ... FY2010, FY2011,
FY2012, ... and NA. | Fiscal_Year_Month_ID FiscalYearMonthID | Char or VarChar (?) 'lookup' | Text that identifies a fiscal month of a specific fiscal year. ... F2011-01JulAug, F2011-02Sep, F2011-03Oct, ... F2011-11Jun, F2011-12Closing, F2012-01JulAug, ... and NA. | Fiscal_Year_Number FiscalYearNumber | SmallInt | Sequential number of a fiscal year using the number of the calendar year in which the fiscal year ends. ... 2011, 2012, 2013, ... and 0 (for not applicable). | Fiscal_Year_Quarter_ID FiscalYearQuarterID | Char (8) 'lookup' | Text
that identifies a quarter of a specific fiscal year using the number of
the fiscal year and the number of the fiscal quarter of the fiscal
year, in the format Fyyyy.Qq. ... F2011.Q1, F2011.Q2, F2011.Q3, F2011.Q4, F2012.Q1, ... and NA. | Four_Day_Weekend_Flag IsFourDayWeekend | Char (3) 'lookup' | Text that indicates whether a date is in a four-day weekend. No, Yes, and NA. | Holiday_Type HolidayType | VarChar (9) 'lookup' | Text that identifies a type of holiday, when applicable and relevant. Civil, Both, Neither, Religious, and NA. Civil Holiday, Civil and Religious Holiday, No Holiday, Religious Holiday, and NA. | Last_Day_of_Month_Flag IsLastDayofMonth | Char (3) 'lookup' | Text that indicates whether a date is the last day of a calendar month. No, Yes, and NA. | Religious_Holiday_Flag IsReligiousHoliday | Char (3) 'lookup' | Text that indicates whether a date is a relevant religious holiday. No, Yes, and NA. | Religious_Holiday_Name ReligiousHolidayName | VarChar (30) 'lookup' | Name of a relevant religious holiday. ... Eid Al-Adha, ... Good Friday, None, ... and NA. | Three_Day_Weekend_Flag IsThreeDayWeek | Char (3) 'lookup' | Text that indicates whether a date is in a three-day weekend. No, Yes, and NA. | Weekend_Flag IsWeekend | Char (3) 'lookup' | Text that indicates whether a date is in a weekend. No, Yes, and NA. | Weekend_Type WeekendType | Char (13) 'lookup' | Text that identifies a type of weekend, including none. 2-Day Weekend, 3-Day Weekend, 4-Day Weekend, Not Weekend, and NA. 2-Day, 3-Day, 4-Day, None, and NA. | Workday_Flag IsWorkday | Char (3) 'lookup' | Text that indicates whether a date is a workday. No, Yes, and NA. | Loaded_Date_Time LoadedDateTime | TimeStamp | Date and time, in the current time zone, a row was first loaded into the data warehouse staging area. ... 2011-05-20 23:59:59, 2011-05-21 00:00:00, .... The
date and time retrieved from this column are those that were in effect
in the time zone of the retrieval when the statement that loaded the row
began to execute, even if the row was loaded in a different time zone. | Modified_Date_Time ModifiedDateTime | TimeStamp | Date and time, in the current time zone, a row was last modified in the data warehouse staging area. 0000-00-00 00:00:00, ... 2011-05-20 23:59:59, 2011-05-21 00:00:00, .... The
date and time retrieved from this column are those that were in effect
in the time zone of the retrieval when the statement that last modified
the row began to execute, even if the row was modified in a different
time zone. |
- On
page 41: "Data warehouses always need an explicit date dimension table.
There are many date attributes not supported by the SQL date function,
including fiscal periods, seasons, holidays, and weekends."
- On page 60: "The date dimension is the one dimension where surrogate keys should be assigned in a meaningful, sequential order.
- On
page 239: "If the data warehouse serves multinational needs, we must
standardize the general date dimension to handle multinational calendars
in an open-ended number of countries. The primary date dimension
contains generic attributes about the date, regardless of the country.
If your multinational business spans Gregorian, Hebrew, Islamic, and
Chinese calendars, then we would include four sets of days, months, and
years in this primary dimension. Country-specific date dimensions
supplement the primary date table."
-
Usually, we suggest initially populating the Date table only with dates
that seem possible to be used in a fact table, such as 1990-01-01, or
2000-01-01, or 2010-01-01 through 2019-12-31, or 2029-12-31, or
2039-12-31. Earlier or later dates would be added later if needed, with a
Date_Key equal to the number of days since the day before 1000-01-01.
This would keep Date_Keys in sequential order, even if new ones were
added later.
- If
preferred, we could populate the Date table with dates from 1800-01-01
through 2149-12-31, or an earlier date. In Infobright, this would create
two data packs for each column, with dates after around 1979 in the
second data pack. Thus, the first data pack will rarely need to be
uncompressed if the Date_Key for not applicable were 999999 rather than
0.
|