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

Y means Yes while N means No.

LAST_DAY_OF_MONTH_INDICATOR

NOT NULL

VARCHAR(1)

Flags whether or not it is the last day of the month.

Y means Yes while N means No.

LAST_DAY_OF_QUARTER_INDICATOR

NOT NULL

VARCHAR(1)

Flags whether or not it is the last day of the quarter.

Y means Yes while N means No.

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.

Y means Yes while N means No.

LAST_DAY_OF_YEAR_INDICATOR

NOT NULL

VARCHAR(1)

Flags whether or not it is the last day of the year.

Y means Yes while N means No.

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.

Y means Yes while N means No.

LAST_DAY_OF_FISCAL_QUARTER_INDICATOR

NOT NULL

VARCHAR(1)

Flags whether or not it is the last day of the quarterly fiscal period.

Y means Yes while N means No.

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.

Y means Yes while N means No.

LAST_DAY_OF_FISCAL_YEAR_INDICATOR

NOT NULL

VARCHAR(1)

Flags whether or not it is the last day of the yearly fiscal period.

Y means Yes while N means No.

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.