Time Dimension - Definition
Table:
| Table | Description |
| D_TIME | The Time Dimension (D_TIME) contains several navigable hierarchies relating to time - these represent different ways of viewing the calendar. The two major groups are Natural (i.e. the normal yearly calendar) and Financial (a calendar representing the Australian business year). |
Levels:
| Level | Prefix | Description |
| NAT_YEA | NYR | Natural Year. This describes the Natural Calendar Year. |
| NAT_QTR | NQT | Natural Quarter. This describes the Natural Calendar Quarter. |
| NAT_MTH | NMN | Natural Month. This describes the Natural Calendar Month. |
| NAT_WEK | NWK | Natural Week. This describes the Natural Calendar Week. |
| FIN_YEA | FYR | Financial Year. This describes the Financial Year. |
| FIN_QTR | FQT | Natural Quarter. This describes the Natural Calendar Quarter. |
| FIN_MTH | FMN | Financial Month. This describes the Financial Month. |
| FIN_WEK | FWK | Financial Week. This describes the Financial Week. |
| DAY | DAY | Day. This describes the Calendar Day. The Day description is common to both the Natural and Financial calendars. |
Level Attributes:
NAT_YEA Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| YEAR | NUMBER(4) | The year as a number type. e.g. 2005. |
| NAME | VARCHAR2(4) | The year as a character type. e.g. '2005'. |
| DAYS_IN_YEAR | NUMBER(3) | The number of days in the year. e.g. 365, 366. |
NAT_QTR Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| QUARTER | NUMBER(1) | The quarter as a number type. e.g. 1, 2, 3, 4. |
| NAME | VARCHAR2(11) | The full quarter name as a character type. e.g. '1st Quarter'. |
| SHORT_NAME | VARCHAR2(7) | The short quarter name as a character type. e.g. '1st Qtr'. |
| ORDERING | NUMBER(5) | A number that allows ordering to span years. e.g. 20054, 20061. |
| DAYS_IN_QUARTER | NUMBER(2) | The number of days in the quarter. e.g. 92. |
NAT_MTH Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| MONTH | NUMBER(2) | The month as a number type. e.g. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. |
| NAME | VARCHAR2(10) | The full month name as a character type. e.g. 'January', 'February', etc. |
| SHORT_NAME | VARCHAR2(3) | The short month name as a character type. e.g. 'Jan', 'Feb', etc. |
| ORDERING | NUMBER(6) | A number that allows ordering to span years. e.g. 200512, 200601. |
| DAYS_IN_MONTH | NUMBER(2) | The number of days in the month. e.g. 31. |
NAT_WEK Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| WEEK | NUMBER(2) | The week as a number type. e.g. 1, 2, 3, etc. Dates are assigned to weeks as follows: - 1st Jan is assigned to Week 1. - Each Monday, the week is incremented. |
| NAME | VARCHAR2(3) | The week name as a character type. e.g. '1st', '2nd', etc. |
| ORDERING | NUMBER(6) | A number that allows ordering to span years. e.g. 200552, 200601. |
FIN_YEA Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| YEAR | NUMBER(4) | The financial year as a number type. e.g. 2006. As the Financial Year spans 2 natural years, this is the number of the later year. e.g. for the financial year 2005/2006, this value is 2006. |
| NAME | VARCHAR2(9) | The financial year as a character type. e.g. '2005/2006'. |
| SHORT_NAME | VARCHAR2(20) | The financial year as a character type. e.g. '2005/2006 Fin Yr'. |
| FULL_NAME | VARCHAR2(25) | The financial year as a character type. e.g. '2005/2006 Financial Year'. |
| DAYS_IN_YEAR | NUMBER(3) | The number of days in the financial year. e.g. 365, 366. |
FIN_QTR Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| QUARTER | NUMBER(1) | The financial quarter as a number type. e.g. 1, 2, 3, 4. |
| NAME | VARCHAR2(3) | The financial quarter name as a character type. e.g. '1st'. |
| SHORT_NAME | VARCHAR2(11) | The short financial quarter name as a character type. e.g. '1st Fin Qtr'. |
| FULL_NAME | VARCHAR2(25) | The financial quarter name as a character type. e.g. '1st Financial Quarter'. |
| ORDERING | NUMBER(5) | A number that allows ordering to span years. e.g. 20054, 20061. |
FIN_MTH Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| MONTH | NUMBER(2) | The financial month as a number type. e.g. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. |
| NAME | VARCHAR2(10) | The financial month name as a character type. e.g. 'January', 'February', etc. |
| SHORT_NAME | VARCHAR2(9) | The short financial month name as a character type. e.g. '1st Fin Mth', 2nd Fin Mth', etc. |
| FULL_NAME | VARCHAR2(25) | The long month name as a character type. e.g. '1st Financial Month', etc. |
| ORDERING | NUMBER(6) | A number that allows ordering to span years. e.g. 200512, 200601. |
FIN_WEK Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| WEEK | NUMBER(2) | The week as a number type. e.g. 1, 2, 3, etc. Dates are assigned to weeks as follows: - 1st Jul is assigned to Week 1. - Each Monday, the week is incremented. |
| NAME | VARCHAR2(3) | The week name as a character type. e.g. '1st', '2nd', etc. |
| SHORT_NAME | VARCHAR2(10) | The week name as a character type. e.g. '1st Fin Wk', '2nd Fin Wk', etc. |
| FULL_NAME | VARCHAR2(25) | The week name as a character type. e.g. '1st Financial Week', etc. |
| ORDERING | NUMBER(6) | A number that allows ordering to span years. e.g. 200552, 200601. |
DAY Level:
| Attribute | Datatype | Description |
| ID | NUMBER | OWB assigned. |
| START_DATE | DATE | Timestamp of the start of the day. e.g. '01-JAN-2006 00:00:00'. |
| END_DATE | DATE | Timestamp of the end of the day. e.g. '01-JAN-2006 23:59:59'. |
| JULIAN_DATE | NUMBER(7) | Days since 1st January 4712 BC. |
| NAME | VARCHAR2(10) | The name of the day. e.g. 'Monday', 'Tuesday', etc. |
| SHORT_NAME | VARCHAR2(3) | The short name of the day. e.g. 'Mon', 'Tue', etc. |
| WEEKDAY | VARCHAR2(1) | Y/N Indicator. |
| LAST_DAY_IN_QUARTER | VARCHAR2(1) | Y/N indicator. |
| LAST_DAY_IN_MONTH | VARCHAR2(1) | Y/N indicator. |
OTHER - These are audit and control attributes. While not strictly belonging to a Level, they are assigned to the most granular so that OWB can maintain them - in this case; DAY:
| Attribute | Datatype | Description |
| RUN_NO | NUMBER | The Run Number that created this row. |
| INS_TSP | DATE | Insert timestamp. |
Hierarchies:
| Hierarchy | Description |
| NAT_CAL | A hierarchy that allows navigation from Natural Calendar Year to Day. |
| NAT_WEK | A hierarchy that allows navigation from Natural Calendar Week to Day. |
| FIN_CAL | A hierarchy that allows navigation from Financial Calendar Year to Day. |
| FIN_WEK | A hierarchy that allows navigation from Financial Calendar Week to Day. |
Level Relationships:
| Hierarchy | Level |
| NAT_CAL | NAT_YEA NAT_QTR NAT_MTH DAY |
| NAT_WEK | NAT_WEK DAY |
| FIN_CAL | FIN_YEA FIN_QTR FIN_MTH DAY |
| FIN_WEK | FIN_WEK DAY |
posted by Cat Play Crew at
8:38 pm
0 Comments:
Post a Comment
<< Home