DW_EB_X_EBS_COMMON_W_DAY_D

This is the base calendar dimension for the Oracle BI data warehouse. It stores the date related information at the individual calendar day level. The range of data is determined by the BEGIN_DATE and END_DATE parameters defined in the Data Warehouse Administration Console. This dimension stores both calendar and fiscal date attributes. The fiscal information is loaded from either the fiscal_week.csv or the fiscal_month.csv file depending on whether the customer has chosen to define their fiscal calendar at a week level or a month level. This dimension table supports only one fiscal calendar for the entire data warehouse.

Details

Module: Common Tables (EBS_COMMON)

Business Name: Day Dimension

Key Columns

Key column information is not documented in the supplied metadata.

Columns

Columns
Name	Datatype	Length	Precision	Not Null	Comments	Referred Table
ROW_WID	NUMBER		38	True	This uniquely identifies a day record in this table. The ROW_WID is generated by formatting date in YYYYMMDD format.	DW_EB_X_EBS_COMMON_W_DAY_D
CALENDAR_DATE	TIMESTAMP		6		Identifies the calendar date.
CAL_HALF	NUMBER		2		Identifies the calendar half-year this day belongs to. Possible values are 1 and 2.
CAL_MONTH	NUMBER		2		Identifies the calendar month in MM format.
CAL_MONTH_END_DT	TIMESTAMP		6
CAL_MONTH_START_DT	TIMESTAMP		6
CAL_MONTH_WID	NUMBER		38		Unique Warehouse idenitifier for gregorian month
CAL_QTR	NUMBER		1		Identifies the calendar quarter this day belongs to. Possible values are 1, 2, 3, and 4.
CAL_QTR_END_DT	TIMESTAMP		6
CAL_QTR_END_DT_WID	NUMBER		38		Identifies the last day, in YYYYMMDD format, of the quarter this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
CAL_QTR_START_DT	TIMESTAMP		6
CAL_QTR_START_DT_WID	NUMBER		38		Identifies the first day, in YYYYMMDD format, of the quarter this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
CAL_QTR_WID	NUMBER		38		Unique Warehouse idenitifier for gregorian quarter
CAL_TRIMESTER	NUMBER		10		Identifies the calendar trimester this day belongs to. Possible values are 1, 2 and 3.
CAL_WEEK	NUMBER		2		Identifies the calendar week this day belongs to. Possible values are 1 through 53.
CAL_WEEK_END_DT	TIMESTAMP		6
CAL_WEEK_END_DT_WID	NUMBER		38		Identifies the last day, in YYYYMMDD format, of the week this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
CAL_WEEK_START_DT	TIMESTAMP		6
CAL_WEEK_START_DT_WID	NUMBER		38		Identifies the first day, in YYYYMMDD format, of the week this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
CAL_YEAR	NUMBER		4		Identifies the calendar year in YYYY format.
CAL_YEAR_END_DT	TIMESTAMP		6
CAL_YEAR_END_DT_WID	NUMBER		38		Identifies the last day, in YYYYMMDD format, of the year this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
CAL_YEAR_START_DT	TIMESTAMP		6
CAL_YEAR_START_DT_WID	NUMBER		38		Identifies the first day, in YYYYMMDD format, of the Year this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
CAL_YEAR_WID	NUMBER		38
DATASOURCE_NUM_ID	NUMBER		38		This column is the unique identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, Oracle recommends that you define separate unique source IDs for each of your different source instances.
DATE_KEY	NUMBER		38		Identifies Date in Julian Format
DAY_AGO_DT	TIMESTAMP		6		Previous Day Date
DAY_AGO_KEY	NUMBER		10		Date Key of Previous Day
DAY_AGO_WID	NUMBER		38		Surrogate key of previous day	DW_EB_X_EBS_COMMON_W_DAY_D
DAY_DT	TIMESTAMP		6		Calendar date of the day
DAY_OF_MONTH	NUMBER		2		Identifies the day of month. Possible values are 1 through 31.
DAY_OF_WEEK	NUMBER		1		Identifies the day of week. Possible values are 1 through 7.
DAY_OF_YEAR	NUMBER		3		Identifies the day of the Year. Possible values are 1 through 366.
ENT_DAY_OF_PERIOD	NUMBER		10		Identifies the day of the fiscal month. For e.g. 1, 2, ...25, ...28 etc
ENT_DAY_OF_WEEK	NUMBER		10		Identifies the day of the fiscal week. For e.g. 1, 2, ...7, etc.
ENT_DAY_OF_YEAR	NUMBER		10		Identifies the day of the fiscal year. For e.g. 1, 2, ...300, etc.
ENT_DIM_PERIOD_NUM	NUMBER		5		It is a cumulative number starting from 1 for the first Fiscal Month of the first Fiscal Year and keeps on adding up through the years.
ENT_DIM_QTR_NUM	NUMBER		3		It is a cumulative number starting from 1 for the first Fiscal Quarter of the first Fiscal Year and keeps on adding up through the years
ENT_DIM_WEEK_NUM	NUMBER		6		It is a cumulative number starting from 1 for the first Fiscal Week of the first Fiscal Year and keeps on adding up through the years.
ENT_DIM_YEAR_NUM	NUMBER		4		It is a cumulative number starting from 1 for the first first Fiscal Year and keeps on adding up through the years.
ENT_FST_DAY_KEY	NUMBER		10		Identifies the Julian date of the first fiscal day of the year
ENT_HALF	NUMBER		1		Identifies the half of fiscal year this day belongs to. Possible values are 1 and 2.
ENT_PERIOD	NUMBER		2		Identifies the fiscal month this day belongs to. Possible values are 1 through 12.
ENT_PERIOD_AGO_WID	NUMBER		38		Period Ago Warehouse identifier for enterrpise period
ENT_PERIOD_END_DT	TIMESTAMP		6		Identifies the end date of the fiscal month.
ENT_PERIOD_END_DT_WID	NUMBER		38		Identifies the last day, in YYYYMMDD format, of the fiscal month.
ENT_PERIOD_START_DT	TIMESTAMP		6		Identifies the start date of the fiscal month.
ENT_PERIOD_START_DT_WID	NUMBER		38		Identifies the first day, in YYYYMMDD format, of the fiscal month.
ENT_PERIOD_WEEK_NUM	NUMBER		2		Identifies the week of the Fiscal Month this day belongs to. Possible values are 1 through 52.
ENT_PERIOD_WID	NUMBER		38
ENT_PRIOR_PERIOD_WID	NUMBER		38
ENT_PRIOR_QTR_WID	NUMBER		38
ENT_PRIOR_WEEK_WID	NUMBER		38
ENT_PRIOR_YEAR_WID	NUMBER		38
ENT_QTR	NUMBER		1		Identifies the fiscal quarter this day belongs to. Possible values are 1,2, 3 and 4.
ENT_QTR_AGO_WID	NUMBER		38
ENT_QTR_END_DT	TIMESTAMP		6		Identifies the last date of the fiscal auarter.
ENT_QTR_END_DT_WID	NUMBER		38		Identifies the last day, in YYYYMMDD format, of the fiscal quarter.
ENT_QTR_START_DT	TIMESTAMP		6		Identifies the first date of the fiscal quarter.
ENT_QTR_START_DT_WID	NUMBER		38		Identifies the first day, in YYYYMMDD format, of the fiscal quarter.
ENT_QTR_WID	NUMBER		38
ENT_TRIMESTER	NUMBER		10		Identifies the fiscal trimester this day belongs to. Possible values are 1, 2 and 3.
ENT_WEEK	NUMBER		2		Identifies the fiscal week this day belongs to. Possible values are 1 through 52.
ENT_WEEK_AGO_WID	NUMBER		38
ENT_WEEK_END_DT	TIMESTAMP		6		Identifies the end date of the Fiscal Week.
ENT_WEEK_END_DT_WID	NUMBER		38		Identifies the last day, in YYYYMMDD format, of the fiscal week.
ENT_WEEK_START_DT	TIMESTAMP		6		Identifies the start date of the fiscal week.
ENT_WEEK_START_DT_WID	NUMBER		38		Identifies the first day, in YYYYMMDD format, of the fiscal week.
ENT_WEEK_WID	NUMBER		38
ENT_YEAR	NUMBER		4		Identifies the fiscal year this day belongs to.
ENT_YEAR_END_DT	TIMESTAMP		6		Identifies the last date of the fiscal year.
ENT_YEAR_END_DT_WID	NUMBER		38		Identifies the last day,in YYYYMMDD format, of the fiscal year.
ENT_YEAR_START_DT	TIMESTAMP		6		Identifies the first date of the fiscal year.
ENT_YEAR_START_DT_WID	NUMBER		38		Identifies the first day,in YYYYMMDD format, of the fiscal year.
ENT_YEAR_WID	NUMBER		38
FST_DAY_CAL_MNTH_FLG	VARCHAR2	1 CHAR			Identifies if this day is the first day of the calendar month.
FST_DAY_CAL_QTR_FLG	VARCHAR2	1 CHAR			Identifies if this day is the first day of the calendar quarter.
FST_DAY_CAL_WK_FLG	VARCHAR2	1 CHAR			Identifies if this day is the first day of the calendar week.
FST_DAY_CAL_YEAR_FLG	VARCHAR2	1 CHAR			Identifies if this day is the first day of the Calendar Year
FST_DAY_ENT_PERIOD_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is First Day of the Fiscal Month or not.
FST_DAY_ENT_QTR_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is First Day of the Fiscal Quarter or not.
FST_DAY_ENT_WEEK_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is First Day of the Fiscal Week or not.
FST_DAY_ENT_YEAR_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is First Day of the Fiscal Year or not.
HALF_AGO_DT	TIMESTAMP		6		Identifies the Date of the day half-a year ago.
HALF_AGO_KEY	NUMBER		10		Identifies the date of the day half a year ago.
HALF_AGO_WID	NUMBER		38		Identifies the Julian date of the day half a year ago	DW_EB_X_EBS_COMMON_W_DAY_D
INTEGRATION_ID	VARCHAR2	30 CHAR			Identifier used for integration with external system
JULIAN_DAY_NUM	NUMBER		10		Identifies the date in Julian format
JULIAN_MONTH_NUM	NUMBER		10		Identifies the Julian month number this day belongs to
JULIAN_QTR_NUM	NUMBER		10		Identifies the Julian quarter Number this day belongs to
JULIAN_TER_NUM	NUMBER		10		Identifies the Julian trimester number this day belongs to
JULIAN_WEEK_NUM	NUMBER		10		Identifies the Julian week number this day belongs to
JULIAN_YEAR_NUM	NUMBER		10		Identifies the Julian year this day belongs to.
LAST_DAY_CAL_MNTH_FLG	VARCHAR2	1 CHAR			Identifies if this day is the last day of the calendar month.
LAST_DAY_CAL_QTR_FLG	VARCHAR2	1 CHAR			Identifies if this day is the last day of the calendar quarter.
LAST_DAY_CAL_WK_FLG	VARCHAR2	1 CHAR			Identifies if this day is the last day of the calendar week.
LAST_DAY_CAL_YEAR_FLG	VARCHAR2	1 CHAR			Identifies if this day is the last day of the calendar year.
LAST_DAY_ENT_PERIOD_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is Last Day of the Fiscal Month or not.
LAST_DAY_ENT_QTR_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is Last Day of the Fiscal Quarter or not.
LAST_DAY_ENT_WEEK_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is Last Day of the Fiscal Week or not.
LAST_DAY_ENT_YEAR_FLG	VARCHAR2	1 CHAR			This flag indicates if the day is Last Day of the Fiscal Year or not.
MONTH_AGO_DT	TIMESTAMP		6		Identifies the date of the day a month ago
MONTH_AGO_KEY	NUMBER		10		Identifies the Julian Date of the day a month ago
MONTH_AGO_WID	NUMBER		38		Identifies the date, in YYYMMDD format, of the day a month ago	DW_EB_X_EBS_COMMON_W_DAY_D
M_END_CAL_DT_WID	NUMBER		38		Identifies the last day, in YYYYMMDD format, of the month this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
M_STRT_CAL_DT_WID	NUMBER		38		Identifies the first day, in YYYYMMDD format, of the month this day belongs to	DW_EB_X_EBS_COMMON_W_DAY_D
PERIOD_KEY	NUMBER		10		Identifies the period key of this day in YYYYMMDD format.
PER_NAME_ENT_HALF	VARCHAR2	50 CHAR			Identifies the fiscal half-year period name. For e.g. 1980 Half1, 1980 Half2, etc.
PER_NAME_ENT_PERIOD	VARCHAR2	50 CHAR			Identifies the fiscal month Period Name in YYYY/MM format. For e.g. "1980 / 01", "1980 / 10", etc.
PER_NAME_ENT_QTR	VARCHAR2	50 CHAR			Identifies the fiscal quarter period name. For e.g. "1980 Q 1"
PER_NAME_ENT_TER	VARCHAR2	50 CHAR			Identifies the fiscal trimester period name. For e.g. "1980T1"
PER_NAME_ENT_WEEK	VARCHAR2	50 CHAR			Identifies the fiscal week period name. For e.g. "1980 Week01"
PER_NAME_ENT_YEAR	VARCHAR2	50 CHAR			Identifies the Fiscal year period name in YYYY format. For e.g. "1980"
PER_NAME_HALF	VARCHAR2	50 CHAR			Identifies the calendar half year period name. For e.g. "1979 Half2".
PER_NAME_MONTH	VARCHAR2	50 CHAR			Identifies the calendar month period name. For e.g. "1979 / 12".
PER_NAME_OFFSET_WK	VARCHAR2	50 CHAR			Offset Week
PER_NAME_QTR	VARCHAR2	50 CHAR			Identifies the calendar quarter period name. For e.g. "1979 Q 4".
PER_NAME_TER	VARCHAR2	50 CHAR			Identifies the calendar trimester period name. For e.g. "1979T3"
PER_NAME_WEEK	VARCHAR2	50 CHAR			Identifies the calendar week period Name. For e.g. "1979 Week53"
PER_NAME_YEAR	VARCHAR2	50 CHAR			Identifies the calendar year period name in YYYY format. For e.g. "1979".
QUARTER_AGO_DT	TIMESTAMP		6		Identifies the date of the day a quarter ago ( same day three months before).
QUARTER_AGO_KEY	NUMBER		10		Identifies the Julian date of the day a quarter ago( same day three months before).
QUARTER_AGO_WID	NUMBER		38		Identifies the date, in YYYYMMDD format, of the day a quarter ago ( same day three months before).	DW_EB_X_EBS_COMMON_W_DAY_D
TENANT_ID	VARCHAR2	80 CHAR			This column is the unique identifier for a Tenant in a multi-tenant environment. This would typically be used in an Application Service Provider (ASP) / Software As a Service (SOAS) model.
TRIMESTER_AGO_DT	TIMESTAMP		6		Identifies the date of the day a trimester ago( same day four months before).
TRIMESTER_AGO_KEY	NUMBER		10		Identifies the Julian date of the day, a trimester ago (same day four months before).
TRIMESTER_AGO_WID	NUMBER		38		Identifies the date, in YYYYMMDD format, of the day a trimester ago (same day four months before).	DW_EB_X_EBS_COMMON_W_DAY_D
WEEK_AGO_DT	TIMESTAMP		6		Identifies the date of the day a week ago.
WEEK_AGO_KEY	NUMBER		10		Identifies the Julian Date of the day a week ago.
WEEK_AGO_WID	NUMBER		38		Identifies the date, in YYYYMMDD format, of the day a week ago.	DW_EB_X_EBS_COMMON_W_DAY_D
W_CURRENT_CAL_DAY_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Day is Current or Next or Previous to the current day. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_CAL_MONTH_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Month is Current or Next or Previous to the current month. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_CAL_QTR_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Quarter is Current or Next or Previous to the current quarter. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_CAL_WEEK_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Week is Current or Next or Previous to the current week. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_CAL_YEAR_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Year is Current or Next or Previous to the current year. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_ENT_PERIOD_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Fisacl Month is Current or Next or Previous to the current Fiscal month. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_ENT_QTR_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Fiscal Quarter is Current or Next or Previous to the current Fiscal Quarter. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_ENT_WEEK_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Fiscal Week is Current or Next or Previous to the current Fiscal week. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_CURRENT_ENT_YEAR_CODE	VARCHAR2	50 CHAR			This is the code which indicates whether the Fiscal Year is Current or Next or Previous to the current Fiscal Year. This code gets updated everyday and the default value is '__NOT_APPLICABLE__'.
W_DAY_CODE	VARCHAR2	50 CHAR			Identifies the name of the day. Sunday, Monday, Tuesday, etc.
W_MONTH_CODE	VARCHAR2	50 CHAR			Identifies the name of the month this day belongs to. Possible values are January through December.
X_CUSTOM	VARCHAR2	10 CHAR			This column is used as a generic field for customer extensions.
YEAR_AGO_DT	TIMESTAMP		6		Identifies the date, a year ago (same day the previous year).
YEAR_AGO_KEY	NUMBER		10		Identifies the Julian date a year ago (same day the previous year).
YEAR_AGO_WID	NUMBER		38		Identifies the date, in YYYYMMDD format, of the day a year ago.	DW_EB_X_EBS_COMMON_W_DAY_D
W$_INSERT_DT	TIMESTAMP		6
W$_UPDATE_DT	TIMESTAMP		6