DIM_DATE
This table stores a list of dates.
Foreign Keys
This table does not have any foreign keys.
Column Descriptions
Column Name |
Null? |
Data Type |
Description |
---|---|---|---|
DATE |
NOT NULL |
DATE |
Date in the format of yyyy-mm-dd. |
DAY |
NOT NULL |
INT |
Ordinal number to identify the day of the month. For example, 1 means the first day in the month while 30 means the thirtieth day in the month. |
DAY_NAME |
NOT NULL |
VARCHAR(10) |
Name for the day. For example, Monday. |
DAY_SHORT_NAME |
NOT NULL |
VARCHAR(5) |
Abbreviated name for the day. For example, Mon. |
DAY_OF_WEEK |
NOT NULL |
INT |
Ordinal number to identify the day of the week. For example, 1 means the first day in the week while 7 means the last day in the month. |
DAY_OF_MONTH |
NOT NULL |
INT |
Same as the column DAY. |
DAY_OF_QUARTER |
NOT NULL |
INT |
Ordinal number to identify the day of the quarter. For example, 1 means the first day in the quarter. |
DAY_OF_HALF_YEAR |
NOT NULL |
INT |
Ordinal number to identify the day of the first or second half year. For example, 1 means the first day in the first or second half year. |
DAY_OF_YEAR |
NOT NULL |
INT |
Ordinal number to identify the day of the year. For example, 1 means the first day in the year. |
LAST_DAY_OF_WEEK_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the week
|
LAST_DAY_OF_MONTH_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the month.
|
LAST_DAY_OF_QUARTER_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the quarter.
|
LAST_DAY_OF_HALF_YEAR_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the first or second half year.
|
LAST_DAY_OF_YEAR_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the year.
|
WEEK_OF_YEAR |
NOT NULL |
INT |
Ordinal number to identify the week of the year. For example, 1 means the first week of the year |
MONTH |
NOT NULL |
INT |
Ordinal number to identify the month of the year. For example, 1 means the first month of the year |
MONTH_NAME |
NOT NULL |
VARCHAR(10) |
Name for the month. For example, March. |
MONTH_SHORT_NAME |
NOT NULL |
VARCHAR(5) |
Short name for the month. For example, Mar. |
QUARTER |
NOT NULL |
INT |
Ordinal number to identify the quarter of the year. For example, 1 means the first quarter of the year. |
HALF_YEAR |
NOT NULL |
INT |
Ordinal number to identify the half year. For example, 1 means the first half year. |
YEAR |
NOT NULL |
INT |
Year in the format of yyyy. |
FICAL_WEEK_OF_YEAR |
NOT NULL |
INT |
Ordinal number to identify the weekly fiscal period. |
FISCAL_WEEK_START_DATE |
NOT NULL |
DATE |
Start date of the weekly fiscal period. |
FISCAL_WEEK_END_DATE |
NOT NULL |
DATE |
End date of the weekly fiscal period. |
FISCAL_MONTH_OF_YEAR |
NOT NULL |
INT |
Ordinal number to identify the monthly fiscal period. |
FISCAL_MONTH_START_DATE |
NOT NULL |
DATE |
Start date of the monthly fiscal period. |
FISCAL_MONTH_END_DATE |
NOT NULL |
DATE |
End date of the monthly fiscal period. |
FISCAL_QUARTER |
NOT NULL |
INT |
Ordinal number to identify the quarterly fiscal period. |
FISCAL_QUARTER_START_DATE |
NOT NULL |
DATE |
Start date of the quarterly fiscal period. |
FISCAL_QUARTER_END_DATE |
NOT NULL |
DATE |
End date of the quarterly fiscal period. |
FISCAL_HALF_YEAR |
NOT NULL |
INT |
Ordinal number to identify the half-yearly fiscal period. |
FISCAL_HALF_YEAR_START_DATE |
NOT NULL |
DATE |
Start date of the half-yearly fiscal period. |
FISCAL_HALF_YEAR_END_DATE |
NOT NULL |
DATE |
End date of the half-yearly fiscal period. |
FISCAL_YEAR |
NOT NULL |
INT |
Ordinal number to identify the yearly fiscal period. |
FISCAL_YEAR_START_DATE |
NOT NULL |
DATE |
Start date of the yearly fiscal period. |
FISCAL_YEAR_END_DATE |
NOT NULL |
DATE |
End date of the yearly fiscal period. |
FISCAL_YEAR_MONTH |
NOT NULL |
VARCHAR(10) |
Name for the month in the fiscal year. For example, Aug 2014. |
FISCAL_YEAR_QUARTER |
NOT NULL |
VARCHAR(10) |
Name for the quarter in the fiscal year. For example, Q1 2014. |
LAST_DAY_OF_FISCAL_MONTH_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the monthly fiscal period.
|
LAST_DAY_OF_FISCAL_QUARTER_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the quarterly fiscal period.
|
LAST_DAY_OF_FISCAL_HALF_YEAR_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the half-yearly fiscal period.
|
LAST_DAY_OF_FISCAL_YEAR_INDICATOR |
NOT NULL |
VARCHAR(1) |
Flags whether or not it is the last day of the yearly fiscal period.
|
MD_BATCH_ID |
NULLABLE |
INT |
ID of data batch. |
MD_PROCESS_ID |
NULLABLE |
INT |
ID of ETL process running on a specific task. |
MD_SOURCE_INSTANCE_ID |
NULLABLE |
INT |
Contnet pack ID of source instance. |
MD_CREATEDDATE |
NULLABLE |
DATETIME |
Date of the record being created. |
MD_DELETEDDATE |
NULLABLE |
DATETIME |
Date of the record being deleted. |
MD_LASTMODDATE |
NULLABLE |
DATETIME |
Date of the record being last updated. |
MD_TRANSLASTIND |
NULLABLE |
VARCHAR(1) |
Flags whether it is the latest record after updated by SCD2. |
MD_TRANSENDDATE |
NULLABLE |
DATETIME |
End date of the record being effective. |
MD_ACTIVESTATUSIND |
NULLABLE |
VARCHAR(1) |
Flags whether or not the old record is deleted. |
MD_HASH_CODE_SCD1 |
NULLABLE |
VARCHAR(40) |
(Only for dimension table) Hash code that is generated by SCD1 columns. |
MD_HASH_CODE_SCD2 |
NULLABLE |
VARCHAR(40) |
(Only for dimension table) Hash code that is generated by SCD2 columns. |
Projections
This table uses no projections.
Sequences
This table uses no sequences.