Difference between revisions of "SH.TIMES (table)"
Line 5: | Line 5: | ||
| '''Table | | '''Table | ||
| SH.TIMES | | SH.TIMES | ||
+ | |- valign="top" | ||
+ | | '''Description | ||
+ | | Time dimension table to support multiple hierarchies and materialized views | ||
|- | |- | ||
|} | |} | ||
+ | === Columns === | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
Line 15: | Line 19: | ||
| '''Nullable | | '''Nullable | ||
| '''Default | | '''Default | ||
− | | ''' | + | | '''Description |
− | |||
|- | |- | ||
Line 23: | Line 26: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | primary key; day date, finest granularity, CORRECT ORDER |
− | |||
|- | |- | ||
| DAY_NAME | | DAY_NAME | ||
Line 30: | Line 32: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | Monday to Sunday, repeating |
− | |||
|- | |- | ||
| DAY_NUMBER_IN_WEEK | | DAY_NUMBER_IN_WEEK | ||
Line 37: | Line 38: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 7, repeating |
− | |||
|- | |- | ||
| DAY_NUMBER_IN_MONTH | | DAY_NUMBER_IN_MONTH | ||
Line 44: | Line 44: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 31, repeating |
− | |||
|- | |- | ||
| CALENDAR_WEEK_NUMBER | | CALENDAR_WEEK_NUMBER | ||
Line 51: | Line 50: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 53, repeating |
− | |||
|- | |- | ||
| FISCAL_WEEK_NUMBER | | FISCAL_WEEK_NUMBER | ||
Line 58: | Line 56: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 53, repeating |
− | |||
|- | |- | ||
| WEEK_ENDING_DAY | | WEEK_ENDING_DAY | ||
Line 65: | Line 62: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | date of last day in week, CORRECT ORDER |
− | |||
|- | |- | ||
| WEEK_ENDING_DAY_ID | | WEEK_ENDING_DAY_ID | ||
| NUMBER | | NUMBER | ||
| not null | | not null | ||
− | |||
| | | | ||
| | | | ||
Line 79: | Line 74: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 12, repeating |
− | |||
|- | |- | ||
| FISCAL_MONTH_NUMBER | | FISCAL_MONTH_NUMBER | ||
Line 86: | Line 80: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 12, repeating |
− | |||
|- | |- | ||
| CALENDAR_MONTH_DESC | | CALENDAR_MONTH_DESC | ||
Line 93: | Line 86: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 1998-01, CORRECT ORDER |
− | |||
|- | |- | ||
| CALENDAR_MONTH_ID | | CALENDAR_MONTH_ID | ||
| NUMBER | | NUMBER | ||
| not null | | not null | ||
− | |||
| | | | ||
| | | | ||
Line 107: | Line 98: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 1998-01, CORRECT ORDER |
− | |||
|- | |- | ||
| FISCAL_MONTH_ID | | FISCAL_MONTH_ID | ||
| NUMBER | | NUMBER | ||
| not null | | not null | ||
− | |||
| | | | ||
| | | | ||
Line 121: | Line 110: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 28,31, repeating |
− | |||
|- | |- | ||
| DAYS_IN_FIS_MONTH | | DAYS_IN_FIS_MONTH | ||
Line 128: | Line 116: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 25,32, repeating |
− | |||
|- | |- | ||
| END_OF_CAL_MONTH | | END_OF_CAL_MONTH | ||
Line 135: | Line 122: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | last day of calendar month |
− | |||
|- | |- | ||
| END_OF_FIS_MONTH | | END_OF_FIS_MONTH | ||
Line 142: | Line 128: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | last day of fiscal month |
− | |||
|- | |- | ||
| CALENDAR_MONTH_NAME | | CALENDAR_MONTH_NAME | ||
Line 149: | Line 134: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | January to December, repeating |
− | |||
|- | |- | ||
| FISCAL_MONTH_NAME | | FISCAL_MONTH_NAME | ||
Line 156: | Line 140: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | January to December, repeating |
− | |||
|- | |- | ||
| CALENDAR_QUARTER_DESC | | CALENDAR_QUARTER_DESC | ||
Line 163: | Line 146: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 1998-Q1, CORRECT ORDER |
− | |||
|- | |- | ||
| CALENDAR_QUARTER_ID | | CALENDAR_QUARTER_ID | ||
| NUMBER | | NUMBER | ||
| not null | | not null | ||
− | |||
| | | | ||
| | | | ||
Line 177: | Line 158: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 1999-Q3, CORRECT ORDER |
− | |||
|- | |- | ||
| FISCAL_QUARTER_ID | | FISCAL_QUARTER_ID | ||
| NUMBER | | NUMBER | ||
| not null | | not null | ||
− | |||
| | | | ||
| | | | ||
Line 191: | Line 170: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 88,90, repeating |
− | |||
|- | |- | ||
| DAYS_IN_FIS_QUARTER | | DAYS_IN_FIS_QUARTER | ||
Line 198: | Line 176: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 88,90, repeating |
− | |||
|- | |- | ||
| END_OF_CAL_QUARTER | | END_OF_CAL_QUARTER | ||
Line 205: | Line 182: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | last day of calendar quarter |
− | |||
|- | |- | ||
| END_OF_FIS_QUARTER | | END_OF_FIS_QUARTER | ||
Line 212: | Line 188: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | last day of fiscal quarter |
− | |||
|- | |- | ||
| CALENDAR_QUARTER_NUMBER | | CALENDAR_QUARTER_NUMBER | ||
Line 219: | Line 194: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 4, repeating |
− | |||
|- | |- | ||
| FISCAL_QUARTER_NUMBER | | FISCAL_QUARTER_NUMBER | ||
Line 226: | Line 200: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 1 to 4, repeating |
− | |||
|- | |- | ||
| CALENDAR_YEAR | | CALENDAR_YEAR | ||
Line 233: | Line 206: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 1999, CORRECT ORDER |
− | |||
|- | |- | ||
| CALENDAR_YEAR_ID | | CALENDAR_YEAR_ID | ||
| NUMBER | | NUMBER | ||
| not null | | not null | ||
− | |||
| | | | ||
| | | | ||
Line 247: | Line 218: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 1999, CORRECT ORDER |
− | |||
|- | |- | ||
| FISCAL_YEAR_ID | | FISCAL_YEAR_ID | ||
| NUMBER | | NUMBER | ||
| not null | | not null | ||
− | |||
| | | | ||
| | | | ||
Line 261: | Line 230: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | 365,366 repeating |
− | |||
|- | |- | ||
| DAYS_IN_FIS_YEAR | | DAYS_IN_FIS_YEAR | ||
Line 268: | Line 236: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | e.g. 355,364, repeating |
− | |||
|- | |- | ||
| END_OF_CAL_YEAR | | END_OF_CAL_YEAR | ||
Line 275: | Line 242: | ||
| not null | | not null | ||
| | | | ||
− | | | + | | last day of cal year |
− | |||
|- | |- | ||
| END_OF_FIS_YEAR | | END_OF_FIS_YEAR | ||
Line 282: | Line 248: | ||
| not null | | not null | ||
| | | | ||
+ | | last day of fiscal year | ||
+ | |} | ||
+ | |||
+ | === Primary Key === | ||
+ | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
+ | |- style="background:silver" | ||
+ | | '''Primary Key | ||
+ | | '''Columns | ||
+ | |- | ||
+ | | TIMES_PK | ||
+ | | TIME_ID | ||
+ | |} | ||
+ | |||
+ | === Check Constraints === | ||
+ | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
+ | |- style="background:silver" | ||
+ | | '''Check Constraint | ||
+ | | '''Expression | ||
+ | | '''Description | ||
+ | |||
+ | |- | ||
+ | | SYS_C005088 | ||
+ | | "TIME_ID" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005089 | ||
+ | | "DAY_NAME" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005090 | ||
+ | | "DAY_NUMBER_IN_WEEK" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005091 | ||
+ | | "DAY_NUMBER_IN_MONTH" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005092 | ||
+ | | "CALENDAR_WEEK_NUMBER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005093 | ||
+ | | "FISCAL_WEEK_NUMBER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005094 | ||
+ | | "WEEK_ENDING_DAY" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005095 | ||
+ | | "WEEK_ENDING_DAY_ID" IS NOT NULL | ||
| | | | ||
+ | |- | ||
+ | | SYS_C005096 | ||
+ | | "CALENDAR_MONTH_NUMBER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005097 | ||
+ | | "FISCAL_MONTH_NUMBER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005098 | ||
+ | | "CALENDAR_MONTH_DESC" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005099 | ||
+ | | "CALENDAR_MONTH_ID" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005100 | ||
+ | | "FISCAL_MONTH_DESC" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005101 | ||
+ | | "FISCAL_MONTH_ID" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005102 | ||
+ | | "DAYS_IN_CAL_MONTH" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005103 | ||
+ | | "DAYS_IN_FIS_MONTH" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005104 | ||
+ | | "END_OF_CAL_MONTH" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005105 | ||
+ | | "END_OF_FIS_MONTH" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005106 | ||
+ | | "CALENDAR_MONTH_NAME" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005107 | ||
+ | | "FISCAL_MONTH_NAME" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005108 | ||
+ | | "CALENDAR_QUARTER_DESC" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005109 | ||
+ | | "CALENDAR_QUARTER_ID" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005110 | ||
+ | | "FISCAL_QUARTER_DESC" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005111 | ||
+ | | "FISCAL_QUARTER_ID" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005112 | ||
+ | | "DAYS_IN_CAL_QUARTER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005113 | ||
+ | | "DAYS_IN_FIS_QUARTER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005114 | ||
+ | | "END_OF_CAL_QUARTER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005115 | ||
+ | | "END_OF_FIS_QUARTER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005116 | ||
+ | | "CALENDAR_QUARTER_NUMBER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005117 | ||
+ | | "FISCAL_QUARTER_NUMBER" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005118 | ||
+ | | "CALENDAR_YEAR" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005119 | ||
+ | | "CALENDAR_YEAR_ID" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005120 | ||
+ | | "FISCAL_YEAR" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005121 | ||
+ | | "FISCAL_YEAR_ID" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005122 | ||
+ | | "DAYS_IN_CAL_YEAR" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005123 | ||
+ | | "DAYS_IN_FIS_YEAR" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005124 | ||
+ | | "END_OF_CAL_YEAR" IS NOT NULL | ||
+ | | | ||
+ | |- | ||
+ | | SYS_C005125 | ||
+ | | "END_OF_FIS_YEAR" IS NOT NULL | ||
| | | | ||
|} | |} | ||
− | + | === Detail Tables === | |
− | |||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" |
Revision as of 08:29, 11 February 2010
wikibot
Table | SH.TIMES |
Description | Time dimension table to support multiple hierarchies and materialized views |
Columns
Column | Data Type | Nullable | Default | Description |
TIME_ID | DATE(7) | not null | primary key; day date, finest granularity, CORRECT ORDER | |
DAY_NAME | VARCHAR2(9) | not null | Monday to Sunday, repeating | |
DAY_NUMBER_IN_WEEK | NUMBER(1, 0) | not null | 1 to 7, repeating | |
DAY_NUMBER_IN_MONTH | NUMBER(2, 0) | not null | 1 to 31, repeating | |
CALENDAR_WEEK_NUMBER | NUMBER(2, 0) | not null | 1 to 53, repeating | |
FISCAL_WEEK_NUMBER | NUMBER(2, 0) | not null | 1 to 53, repeating | |
WEEK_ENDING_DAY | DATE(7) | not null | date of last day in week, CORRECT ORDER | |
WEEK_ENDING_DAY_ID | NUMBER | not null | ||
CALENDAR_MONTH_NUMBER | NUMBER(2, 0) | not null | 1 to 12, repeating | |
FISCAL_MONTH_NUMBER | NUMBER(2, 0) | not null | 1 to 12, repeating | |
CALENDAR_MONTH_DESC | VARCHAR2(8) | not null | e.g. 1998-01, CORRECT ORDER | |
CALENDAR_MONTH_ID | NUMBER | not null | ||
FISCAL_MONTH_DESC | VARCHAR2(8) | not null | e.g. 1998-01, CORRECT ORDER | |
FISCAL_MONTH_ID | NUMBER | not null | ||
DAYS_IN_CAL_MONTH | NUMBER | not null | e.g. 28,31, repeating | |
DAYS_IN_FIS_MONTH | NUMBER | not null | e.g. 25,32, repeating | |
END_OF_CAL_MONTH | DATE(7) | not null | last day of calendar month | |
END_OF_FIS_MONTH | DATE(7) | not null | last day of fiscal month | |
CALENDAR_MONTH_NAME | VARCHAR2(9) | not null | January to December, repeating | |
FISCAL_MONTH_NAME | VARCHAR2(9) | not null | January to December, repeating | |
CALENDAR_QUARTER_DESC | CHAR(7) | not null | e.g. 1998-Q1, CORRECT ORDER | |
CALENDAR_QUARTER_ID | NUMBER | not null | ||
FISCAL_QUARTER_DESC | CHAR(7) | not null | e.g. 1999-Q3, CORRECT ORDER | |
FISCAL_QUARTER_ID | NUMBER | not null | ||
DAYS_IN_CAL_QUARTER | NUMBER | not null | e.g. 88,90, repeating | |
DAYS_IN_FIS_QUARTER | NUMBER | not null | e.g. 88,90, repeating | |
END_OF_CAL_QUARTER | DATE(7) | not null | last day of calendar quarter | |
END_OF_FIS_QUARTER | DATE(7) | not null | last day of fiscal quarter | |
CALENDAR_QUARTER_NUMBER | NUMBER(1, 0) | not null | 1 to 4, repeating | |
FISCAL_QUARTER_NUMBER | NUMBER(1, 0) | not null | 1 to 4, repeating | |
CALENDAR_YEAR | NUMBER(4, 0) | not null | e.g. 1999, CORRECT ORDER | |
CALENDAR_YEAR_ID | NUMBER | not null | ||
FISCAL_YEAR | NUMBER(4, 0) | not null | e.g. 1999, CORRECT ORDER | |
FISCAL_YEAR_ID | NUMBER | not null | ||
DAYS_IN_CAL_YEAR | NUMBER | not null | 365,366 repeating | |
DAYS_IN_FIS_YEAR | NUMBER | not null | e.g. 355,364, repeating | |
END_OF_CAL_YEAR | DATE(7) | not null | last day of cal year | |
END_OF_FIS_YEAR | DATE(7) | not null | last day of fiscal year |
Primary Key
Primary Key | Columns |
TIMES_PK | TIME_ID |
Check Constraints
Check Constraint | Expression | Description |
SYS_C005088 | "TIME_ID" IS NOT NULL | |
SYS_C005089 | "DAY_NAME" IS NOT NULL | |
SYS_C005090 | "DAY_NUMBER_IN_WEEK" IS NOT NULL | |
SYS_C005091 | "DAY_NUMBER_IN_MONTH" IS NOT NULL | |
SYS_C005092 | "CALENDAR_WEEK_NUMBER" IS NOT NULL | |
SYS_C005093 | "FISCAL_WEEK_NUMBER" IS NOT NULL | |
SYS_C005094 | "WEEK_ENDING_DAY" IS NOT NULL | |
SYS_C005095 | "WEEK_ENDING_DAY_ID" IS NOT NULL | |
SYS_C005096 | "CALENDAR_MONTH_NUMBER" IS NOT NULL | |
SYS_C005097 | "FISCAL_MONTH_NUMBER" IS NOT NULL | |
SYS_C005098 | "CALENDAR_MONTH_DESC" IS NOT NULL | |
SYS_C005099 | "CALENDAR_MONTH_ID" IS NOT NULL | |
SYS_C005100 | "FISCAL_MONTH_DESC" IS NOT NULL | |
SYS_C005101 | "FISCAL_MONTH_ID" IS NOT NULL | |
SYS_C005102 | "DAYS_IN_CAL_MONTH" IS NOT NULL | |
SYS_C005103 | "DAYS_IN_FIS_MONTH" IS NOT NULL | |
SYS_C005104 | "END_OF_CAL_MONTH" IS NOT NULL | |
SYS_C005105 | "END_OF_FIS_MONTH" IS NOT NULL | |
SYS_C005106 | "CALENDAR_MONTH_NAME" IS NOT NULL | |
SYS_C005107 | "FISCAL_MONTH_NAME" IS NOT NULL | |
SYS_C005108 | "CALENDAR_QUARTER_DESC" IS NOT NULL | |
SYS_C005109 | "CALENDAR_QUARTER_ID" IS NOT NULL | |
SYS_C005110 | "FISCAL_QUARTER_DESC" IS NOT NULL | |
SYS_C005111 | "FISCAL_QUARTER_ID" IS NOT NULL | |
SYS_C005112 | "DAYS_IN_CAL_QUARTER" IS NOT NULL | |
SYS_C005113 | "DAYS_IN_FIS_QUARTER" IS NOT NULL | |
SYS_C005114 | "END_OF_CAL_QUARTER" IS NOT NULL | |
SYS_C005115 | "END_OF_FIS_QUARTER" IS NOT NULL | |
SYS_C005116 | "CALENDAR_QUARTER_NUMBER" IS NOT NULL | |
SYS_C005117 | "FISCAL_QUARTER_NUMBER" IS NOT NULL | |
SYS_C005118 | "CALENDAR_YEAR" IS NOT NULL | |
SYS_C005119 | "CALENDAR_YEAR_ID" IS NOT NULL | |
SYS_C005120 | "FISCAL_YEAR" IS NOT NULL | |
SYS_C005121 | "FISCAL_YEAR_ID" IS NOT NULL | |
SYS_C005122 | "DAYS_IN_CAL_YEAR" IS NOT NULL | |
SYS_C005123 | "DAYS_IN_FIS_YEAR" IS NOT NULL | |
SYS_C005124 | "END_OF_CAL_YEAR" IS NOT NULL | |
SYS_C005125 | "END_OF_FIS_YEAR" IS NOT NULL |
Detail Tables
Detail Table | Column | Referencing Column |
SH.COSTS | TIME_ID | TIME_ID |
SH.SALES | TIME_ID | TIME_ID |