Column / Alternative

Data Type / Comment

Description  /  Examples  /  Notes

Date_Key

DateKey

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

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

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

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.