Difference between revisions of "SH.TIMES (table)"

From dbscript Online Help
Jump to: navigation, search
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
−
| '''PK
+
| '''Description
−
| '''Index
 
 
 
 
|-
 
|-
Line 23: Line 26:
 
| not null
 
| not null
 
|  
 
|  
−
| TIMES_PK
+
| 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 09: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
Retrieved from "http://dbshelp.devio.at/index.php?title=SH.TIMES_(table)&oldid=2332"