Oracle® Healthcare Data Model Reference 11g Release 2 (11.2) Part Number E18026-02 |
|
|
View PDF |
This chapter describes the logical data model dimensions shown in Table 3-1.
This section lists the logical data model dimensions.
Description: AGE BAND
Total Age Band: All Total Age Band is the most aggregate level of the dimension.
Age Band: Detail level of the dimension. Stores the Age Band Information.
Table 3-2 shows Total Age Band: All Total Age Band is the most aggregate level of the dimension.
Table 3-3 shows Age Band. Age Band is the detail level of the dimension that stores Age Band Information.
Table 3-3 Age Band Detail
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
AGEBAND _Id |
AgeBand Id … represents a record which uniquely identifies an Age or Time period. |
8,9,10,11 |
2 |
AGEBAND _Years |
'Number of Years elapsed.' |
0,1 |
3 |
AGEBAND _Mths |
'Number of Months elapsed.' |
0,6,5,2,3 |
4 |
AGEBAND _Weeks |
'Number of Weeks elapsed. |
0,1,26,12 |
5 |
AGEBAND _Days |
'Number of days elapsed. |
5,6,7,8,9 |
6 |
AGEBAND1_NM |
'Age Band 1 - Name' |
AB1 |
7 |
AGEBAND1_RNG_NM |
'Age Band 1 - Range Name' |
28 Days-23 Months, 0-27 Days |
8 |
AGEBAND2_NM |
'Age Band 2 - Name' |
AB2 |
9 |
AGEBAND2_RNG_NM |
'Age Band 2 - Range Name' |
<1 Years, 1-4 Years |
10 |
AGEBAND3_NM |
'Age Band 3 - Name' |
AB3 |
11 |
AGEBAND3_RNG_NM |
'Age Band 3 - Range Name' |
<1 Years, 1-4 Years |
12 |
AGEBAND4_NM |
'Age Band 4 - Name' |
AB4 |
13 |
AGEBAND4_RNG_NM |
'Age Band 4 - Range Name' |
<1 Years, 1-4 Years |
14 |
AGEBAND5_NM |
'Age Band 5 - Name' |
AB5 |
15 |
AGEBAND5_RNG_NM |
'Age Band 5 - Range Name' |
<1 Years, 1-4 Years |
16 |
AGEBAND6_NM |
'Age Band 6 - Name' |
AB6 |
17 |
AGEBND6_RNG_NM |
'Age Band 6 - Range Name' |
<1 Years, 1-4 Years |
Description: FACILITY
Table 3-4 shows Total Facility. Total Facility is the most aggregate level of the dimension.
Table 3-5 shows facility Campus level.
Table 3-6 shows facility Building level.
Table 3-7 shows facility Floor level.
Table 3-8 shows facility Suite level.
Table 3-9 shows facility Care Site level.
Table 3-9 shows facility Room level.
Table 3-10 Facility Room Level
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
FAC_MBR_ID |
Facility Member Id … This is a lowest level (L0) representation of any Facility Member. |
1, 2, 3 |
2 |
FAC_MBR_NM |
100-1, 100-2, 100-3 |
|
3 |
FAC_MBR_SUBTYP |
Facility Subtype - from Campus, Building, Floor, Suite, Caresite, Room and Bed |
8094,8095, 8098 |
FAC_MBR_SUBTYP_NM |
Bed, Room |
||
4 |
FAC_MBR_STATUS_CD |
Facility Status Code - Active, Inactive |
NULL |
5 |
FAC_MBR_STATUS_CD_NM |
Facility Status Code Name |
NULL |
6 |
FAC_MBR_SEIS_FLG |
Facility Seismic Flag - A flag to indicate the design of this facility followed seismic design guidelines |
NULL |
7 |
FAC_MBR_BED_TYP |
Facility Bed Type (if applicable) |
Null |
8 |
FAC_MBR_BED_TYP_NM |
||
9 |
L1_FAC_ID |
Id at Facility level L1 - Typically Room |
31 |
10 |
L1_FAC_NM |
1001 |
|
11 |
L1_FAC_SUBTYP |
8094,8095, 8098 |
|
12 |
L1_FAC_SUBTYP_NM |
Room |
|
13 |
L1_FAC_STATUS_CD |
NULL |
|
14 |
L1_FAC_STATUS_CD_NM |
NULL |
|
15 |
L1_FAC_SEIS_FLG |
NULL |
|
16 |
L2_FAC_ID |
Id at Facility level L2 - Typically Caresite |
19 |
17 |
L2_FAC_NM |
Cath Lab |
|
18 |
L2_FAC_SUBTYP |
8094,8095, 8098 |
|
19 |
L2_FAC_SUBTYP_NM |
Caresite |
|
20 |
L2_FAC_STATUS_CD |
NULL |
|
21 |
L2_FAC_STATUS_CD_NM |
NULL |
|
22 |
L2_FAC_SEIS_FLG |
NULL |
|
23 |
L3_FAC_ID |
Id at Facility level L3 - Typically Suite |
30 |
24 |
L3_FAC_NM |
Cardiac |
|
25 |
L3_FAC_SUBTYP |
8094,8095, 8098 |
|
26 |
L3_FAC_SUBTYP_NM |
Suite |
|
27 |
L3_FAC_STATUS_CD |
NULL |
|
28 |
L3_FAC_STATUS_CD_NM |
NULL |
|
29 |
L3_FAC_SEIS_FLG |
NULL |
|
30 |
L4_FAC_ID |
Id at Facility level L4 - Typically Floor |
15 |
31 |
L4_FAC_NM |
B1 |
|
32 |
L4_FAC_SUBTYP |
8094,8095, 8098 |
|
33 |
L4_FAC_SUBTYP_NM |
Floor |
|
34 |
L4_FAC_STATUS_CD |
NULL |
|
35 |
L4_FAC_STATUS_CD_NM |
NULL |
|
36 |
L4_FAC_SEIS_FLG |
NULL |
|
37 |
L5_FAC_ID |
Id at Facility level L5 - Typically Building |
29 |
38 |
L5_FAC_NM |
Tower B |
|
39 |
L5_FAC_SUBTYP |
8094,8095, 8098 |
|
40 |
L5_FAC_SUBTYP_NM |
Building |
|
41 |
L5_FAC_STATUS_CD |
NULL |
|
42 |
L5_FAC_STATUS_CD_NM |
NULL |
|
43 |
L5_FAC_SEIS_FLG |
NULL |
|
44 |
L6_FAC_ID |
Id at Facility level L6 - Typically Campus |
32 |
45 |
L6_FAC_NM |
South Campus |
|
46 |
L6_FAC_SUBTYP |
8094, 8095, 8098 |
|
47 |
L6_FAC_SUBTYP_NM |
Campus |
|
48 |
L6_FAC_STATUS_CD |
NULL |
|
49 |
L6_FAC_STATUS_CD_NM |
NULL |
|
50 |
L6_FAC_SEIS_FLG |
NULL |
Description: KPI DIMENSION TABLE
Table 3-11 shows KPI Total. Total KPI is the most aggregate level of the dimension.
Table 3-12 shows KPI. Detail level of the dimension. Stores the KPI Information.
Table 3-12 KPI Detail
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
KPI_ID |
KPI Id (Unique Key) |
28,31,32 |
2 |
KPI_NM |
KPI Name |
Diabetic Teaching, Median Time to Primary PCI |
3 |
KPI_DESC |
KPI Description |
Diabetic Teaching: Percent of diabetic patients or newly diagnosed diabetics receiving diabetes teaching at discharge |
4 |
KPI_MEAS_CD |
Short Term/Code representing the KPI (if present/applicable else the KPI_Nm itself). |
Diabetic Teaching, Median Time to Primary PCI |
5 |
KPI_MEAS_CALC_TYPE |
Details relating to the Grain of information used by the KPI Calculation (for audit/reference only). This field is not intended to be used for metadata driven calculation. One from following 5 choices - CE (Count of Encounters), CP (Count of Patients), CC (Count of Cases), CO (Count of Observations), Other (Median Time Calc, Hours of Use, and so on.) |
CE,CO,CP,OTHERS |
6 |
KPI_MEAS_CALC_RPT |
Details relating to the Reporting Calculation (for audit/reference only). This field is not intended to be used for metadata driven calculation. |
(Numerator / Denominator) reported as a percentage, (Sum of hours of Encounters in the numerator / No. of Reporting Days) reported as a ratio of hours/days |
Description: ORGANIZATION DIMENSION
Standard Organization Hierarchy.
Table 3-13 shows Total Organization.
Table 3-14 shows Enterprise level.
Table 3-15 shows Regional BU level
Table 3-16 shows Facility BU level.
Table 3-17 shows Department level.
Table 3-18 shows Business Unit level.
Table 3-18 Organization Business Unit
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
LEVEL_DEPTH |
Level of the record - level of the hierarchy node that the current record represents. |
4, 3, 2, |
2 |
LEVEL_DESC |
Level description of the record - level description of the hierarchy node that the current record represents. |
L0, L1, L2, ... |
3 |
ORG_MBR_ID |
Organization Member Id… This is a lowest level (L0) representation of any Organization Member. |
9, 10 |
4 |
ORG_MBR_NM |
Organization Name |
ED-A - Unit, ED- B - Unit |
5 |
ORG_MBR_BSNS_UNT_TYP |
Org Business Unit Type like Outpatient Laboratory Services |
-1 |
6 |
ORG_MBR_BSNS_UNT_SUBTYP |
Org Business Unit Subtype like Enterprise, Region BU, Facility BU, Department, Unit |
NULL |
7 |
ORG_MBR_ADDR_TYP |
Address Type from Party Address. Using "Current" Address as party can have more than 1 Address. |
NULL |
8 |
ORG_MBR_LOC_STRT_ADDR1 |
Location Street Address Line 1. |
NULL |
9 |
ORG_MBR_LOC_STRT_ADDR2 |
Location Street Address Line 2. |
NULL |
10 |
ORG_MBR_LOC_STRT_ADDR3 |
Location Street Address Line 3. |
NULL |
11 |
ORG_MBR_CIT |
City. |
NULL |
12 |
ORG_MBR_ST_CD |
State Code. |
NULL |
13 |
ORG_MBR_ST_NM |
State Name. |
NULL |
14 |
ORG_MBR_CTRY_CD |
Country Code. |
|
15 |
ORG_MBR_CTRY_NM |
Country Name. |
NULL |
16 |
ORG_MBR_ZP_CD |
Postal (ZIP) Code. |
NULL |
17 |
ORG_MBR_TELE_NBR_BSNS |
Telephone Number (Business) |
NULL |
18 |
ORG_MBR_FAX_NBR_BSNS |
Fax Number (Business) |
NULL |
19 |
ORG_MBR_EML_ADDR_BSNS |
Email Address (Business) |
NULL |
20 |
L1_ORG_ID |
ID at organization level L1: Typically Department. |
7,9,10,19 |
21 |
L1_ORG_ NM |
NO VALUE, ED-A - UNIT, CARDIAC STEP-DOWN - UNIT |
|
22 |
L1_ORG_TYP |
-1 |
|
23 |
L1_ORG_SUBTYP |
NULL |
|
24 |
L1_ORG_ADDR_TYP |
NULL |
|
25 |
L1_ORG_LOC_STRT_ADDR1 |
NULL |
|
26 |
L1_ORG_LOC_STRT_ADDR2 |
NULL |
|
27 |
L1_ORG_LOC_STRT_ADDR3 |
NULL |
|
28 |
L1_ORG_CIT |
NULL |
|
29 |
L1_ORG_ST_CD |
NULL |
|
30 |
L1_ORG_ST_NM |
NULL |
|
31 |
L1_ORG_CTRY_CD |
NULL |
|
31 |
L1_ORG_CTRY_NM |
NULL |
|
32 |
L1_ORG_ZP_CD |
Postal Code. |
NULL |
33 |
L1_ORG_TELE_NBR_BSNS |
NULL |
|
34 |
L1_ORG_FAX_NBR_BSNS |
NULL |
|
35 |
L1_ORG_EML_ADDR_BSNS |
NULL |
|
36 |
L2_ORG_ID |
ID at organization level L2: Typically Facility BU. |
7,8,17,3 |
37 |
L2_ORG_ NM |
NO VALUE, EMERGENCY - DEPARTMENT, TEACHING DEPARTMENT - DEPARTMENT |
|
38 |
L2_ORG_TYP |
-1 |
|
39 |
L2_ORG_SUBTYP |
NULL |
|
40 |
L2_ORG_ADDR_TYP |
NULL |
|
41 |
L2_ORG_LOC_STRT_ADDR1 |
NULL |
|
42 |
L2_ORG_LOC_STRT_ADDR2 |
NULL |
|
43 |
L2_ORG_LOC_STRT_ADDR3 |
NULL |
|
44 |
L2_ORG_CIT |
NULL |
|
45 |
L2_ORG_ST_CD |
NULL |
|
46 |
L2_ORG_ST_NM |
NULL |
|
47 |
L2_ORG_CTRY_CD |
NULL |
|
48 |
L2_ORG_CTRY_NM |
NULL |
|
49 |
L2_ORG_ZP_CD |
Postal Code |
NULL |
50 |
L2_ORG_TELE_NBR_BSNS |
NULL |
|
51 |
L2_ORG_FAX_NBR_BSNS |
NULL |
|
52 |
L2_ORG_EML_ADDR_BSNS |
NULL |
|
53 |
L3_ORG_ID |
Id at Organization level L3 Typically Regional BU |
7,3,4 |
54 |
L3_ORG_ NM |
South Campus - Facility BU, No Value, North Campus - Facility BU |
|
55 |
L3_ORG_TYP |
-1 |
|
56 |
L3_ORG_SUBTYP |
NULL |
|
57 |
L3_ORG_ADDR_TYP |
NULL |
|
58 |
L3_ORG_LOC_STRT_ADDR1 |
NULL |
|
59 |
L3_ORG_LOC_STRT_ADDR2 |
NULL |
|
60 |
L3_ORG_LOC_STRT_ADDR3 |
NULL |
|
61 |
L3_ORG_CIT |
NULL |
|
62 |
L3_ORG_ST_CD |
NULL |
|
63 |
L3_ORG_ST_NM |
NULL |
|
64 |
L3_ORG_CTRY_CD |
NULL |
|
65 |
L3_ORG_CTRY_NM |
NULL |
|
66 |
L3_ORG_ZP_CD |
postal code |
NULL |
67 |
L3_ORG_TELE_NBR_BSNS |
NULL |
|
68 |
L3_ORG_FAX_NBR_BSNS |
NULL |
|
69 |
L3_ORG_EML_ADDR_BSNS |
NULL |
|
70 |
L4_ORG_ID |
Id at Organization level L4 Typically Enterprise |
2,3 |
71 |
L4_ORG_ NM |
Western Region - Regional BU, Eastern Region - Regional BU |
|
72 |
L4_ORG_TYP |
-1 |
|
73 |
L4_ORG_SUBTYP |
NULL |
|
74 |
L4_ORG_ADDR_TYP |
NULL |
|
75 |
L4_ORG_LOC_STRT_ADDR1 |
NULL |
|
76 |
L4_ORG_LOC_STRT_ADDR2 |
NULL |
|
77 |
L4_ORG_LOC_STRT_ADDR3 |
NULL |
|
78 |
L4_ORG_CIT |
NULL |
|
79 |
L4_ORG_ST_CD |
NULL |
|
80 |
L4_ORG_ST_NM |
NULL |
|
81 |
L4_ORG_CTRY_CD |
NULL |
|
82 |
L4_ORG_CTRY_NM |
NULL |
|
83 |
L4_ORG_ZP_CD |
POSTAL CODE |
NULL |
84 |
L4_ORG_TELE_NBR_BSNS |
NULL |
|
85 |
L4_ORG_FAX_NBR_BSNS |
NULL |
|
86 |
L4_ORG_EML_ADDR_BSNS |
NULL |
Description: PATIENT DIMENSION
Table 3-19 shows Total Patient. All Total Patient is the aggregate level of the dimension.
Table 3-20 shows Patient Detail level of the dimension. This level stores Patient Information.
Table 3-20 Patient Detail
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
PT_ID |
Patient Id (Unique Key) |
21028, 21234 |
2 |
PT_BIR_PLC |
Patient Birth Place |
NULL |
3 |
PT_MULT_BIR_FLG |
Patient Multiple Birth Flag |
NULL |
4 |
PT_BIR_ORD |
Patient Birth Order (in relation to patient's siblings). Values: 1,2,3,4, and so on. |
NULL |
5 |
PRTY_BIR_DT |
Patient (Ind Party) Birth Date |
23-DEC-80, 12-JUN-61 |
6 |
PRTY_FRST_NM |
Patient (Ind Party) First Name |
Queenie, Todd |
7 |
PRTY_MID_NM |
Patient (Ind Party) Middle Name |
NULL, F, J, K |
8 |
PRTY_LST_NM |
Patient (Ind Party) Last Name |
Henkes, Tansey |
9 |
PRTY_DECD_FLG |
Patient (Ind Party) deceased flag |
NULL |
10 |
PRTY_DECD_DT |
Patient (Ind Party) deceased date |
NULL |
11 |
PRTY_MRTL_STCD_ID |
NULL |
|
12 |
PRTY_MRTL_STCD |
Patient (Ind Party) Marital Status Code details (required) |
NULL |
13 |
PRTY_MRTL_STCD_NM |
NULL |
|
14 |
PRTY_GNDR_CD_ID |
23, 24 |
|
15 |
PRTY_GNDR_CD |
Patient (Ind Party) Gender Code details (required) |
8011, 8012 |
16 |
PRTY_GNDR_CD_NM |
Female, Male |
|
17 |
PT_CLIN_TRIAL_STATUS_CD_ID |
NULL |
|
18 |
PT_CLIN_TRIAL_STATUS_CD |
Patient Clinical Trial Status Code details (required) |
NULL |
19 |
PT_CLIN_TRIAL_STATUS_CD_NM |
NULL |
|
20 |
PT_STATUS_CD_ID |
NULL |
|
21 |
PT_STATUS_CD |
Patient Status Code details (required) |
NULL |
22 |
PT_STATUS_CD_NM |
NULL |
|
23 |
PT_LIVG_DEPC_CD_ID |
NULL |
|
24 |
PT_LIVG_DEPC_CD |
Patient Living Dependency Code details (optional) |
NULL |
25 |
PT_LIVG_DEPC_CD_NM |
NULL |
|
26 |
PT_LIVG_ARNGMNT_CD_ID |
NULL |
|
27 |
PT_LIVG_ARNGMNT_CD |
Patient Living Arrangements Code details (optional) |
NULL |
28 |
PT_LIVG_ARNGMNT_CD_NM |
NULL |
|
29 |
PRTY_ID_NBR |
Party Identification Number |
NULL |
30 |
PRTY_ID_TYP_ID |
NULL |
|
31 |
PRTY_ID_TYP |
Party Identification Type (mandatory) |
NULL |
32 |
PRTY_ID_TYP_NM |
NULL |
|
33 |
PRTY_RELGN_CD_ID |
NULL |
|
34 |
PRTY_RELGN_CD |
Party Religion (optional) |
NULL |
35 |
PRTY_RELGN_CD_NM |
NULL |
|
36 |
PRTY_RC_CD_ID |
21,20,19 |
|
37 |
PRTY_RC_CD |
Party Race (optional) |
8009,8008,8007 |
38 |
PRTY_RC_CD_NM |
Black, Asian, Native Hawaiian |
|
39 |
PRTY_ETHN_CD_ID |
NULL |
|
40 |
PRTY_ETHN_CD |
Party Ethnicity (optional) |
NULL |
41 |
PRTY_ETHN_CD_NM |
NULL |
Description: SERVICE PROVIDER PRACTICE SNOWFLAKE
Standard Service Provider Practice Hierarchy.
Table 3-21 shows Total Service Provider. All Total Service Providers is the most aggregate level of the dimension.
Table 3-21 Service Provider Practice Total
Sr. Number | Attribute | Description |
---|---|---|
1. |
TSPP |
Total Service Provider Practice |
Practice: Next level of total service providers.
Table 3-22 shows Practice, the next level of service provider practice.
Table 3-23 shows Service Provider Practice Detail. The detail level of the dimension that stores the Service provider practice information.
Table 3-23 Service Provider Practice Detail
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
SVCPRVPRCT_ID |
Service Provider Practice Id… This is a representation of Service Provider within a Practice. This is a Surrogate Key as multiple records of Service Provider are possible under different Practices. |
2131, 2133, 2133 |
2 |
SVCPRV_PRCT_SVCPRV_ID |
Service Provider Id (Individual) Part of Natural Key comprising of SvcprvPrct_Svcprv_Id and SvcprvPrct_Prct_Id columns. |
3,7,16 |
3 |
SVCPRV_IND_FRST_NM |
First Name (Individual) |
Christine, Jessica |
4 |
SVCPRV_IND_MID_NM |
Middle Name (Individual) |
A,NULL |
5 |
SVCPRV_IND_LST_NM |
Last Name (Individual) |
Hastings, Lee |
6 |
SVCPRV_IND_PRTY_ID |
Party Id |
30,20,32 |
7 |
SVCPRV_IND_SRC_ID |
Integration Id |
600~30, 600~20, 600~32 |
8 |
SVCPRV_IND_TYP_ID |
Service Provider Type Id |
55 |
9 |
SVCPRV_IND_TYP |
Service Provider Type |
8017 |
10 |
SVCPRV_IND_TYP_NM |
Service Provider Type (Name) |
Attending Physician |
11 |
SVCPRV_IND_ID_NBR |
IdentificationnNumber (Individual) |
NULL |
12 |
SVCPRV_IND_ID_TYP_ID |
Identification Type Id |
|
13 |
SVCPRV_IND_ID_TYP |
Identification Type (Individual) - Mandatory |
NULL |
14 |
SVCPRV_IND_ID_TYP_NM |
Identification Type Name |
|
15 |
SVCPRV_IND_CLSFCTN_CD_ID |
Classification Code Id |
|
16 |
SVCPRV_IND_CLSFCTN_CD |
The Classification Class/Category to which the service provider belongs. For example, Patient Care, Non-Patient Care |
8438 |
17 |
SVCPRV_IND_CLSFCTN_CD_NM |
Classification Code (Name) |
Patient Care, Non-Patient Care |
18 |
SVCPRV_IND_ACPTG_NEW_PT_FLG |
Flag to indicate if Service Provider accepts new patients. (Y/N) |
NULL |
19 |
SVCPRVPRCT _PRCT_ID |
Service Provider Id (Organization) Part of Natural Key comprising of SvcprvPrct_Svcprv_Id and SvcprvPrct_Prct_Id columns. |
3,7,16 |
20 |
PRCT_ORG_NM |
Practice Name. This would correspond to Service Provider Type of Type: Organization Party. |
Christine Hastings, Jessica Lee |
21 |
PRCT_ORG_PRTY_ID |
Organization Party Id |
30,20,32 |
22 |
PRCT_ORG_PRTY_SRC_ID |
Organization Party Integration Id. |
600~30, 600~20, 600~32 |
23 |
PRCT_ORG_TYP_ID |
Practice Type attributes. Service Provider Type details |
55 |
24 |
PRCT_ORG_TYP |
Practice Type (Code) |
8017 |
25 |
PRCT_ORG_TYP_NM |
Practice Type (Name) |
Attending Physician, Physician, Emergency Physician |
26 |
PRCT_ORG_SVCPRV_GRP_NM |
Practice: Service Provider Group Name |
NULL |
27 |
PRCT_ORG_SVCPRV_GRP_TYP |
Practice - Service Provider Group Type (Service Provider Group Type = Practice) |
NULL |
Description: SHIFT DIMENSION
Table 3-24 shows Shift Total. All Shift is the most aggregate level of the dimension.
Table 3-25 shows Shift. The Detail level of the dimension stores the Shift Information.
Table 3-25 Shift Detail
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
SHFT_ID |
Shift Id (Unique Key) |
1,2,3,4,5 |
2 |
SHFT_NM |
Shift Name |
07:00 hrs ¿ 14:00 hrs, 14:00 hrs ¿ 22:00 hrs |
3 |
SHFT_DESC |
Shift Description |
Morning Shift: 07.00 am to 02.00 pm, Afternoon Shift: 02:00 pm to 10:00 pm |
4 |
SHFT_LOW_TOD_ID |
Time of Day Key of Lower Range of Shift |
25200, 50400, 79200 |
5 |
SHFT_HIGH_TOD_ID |
Time of Day Key of Higher Range of Shift |
50399,79199, 25199 |
6 |
SHFT_LOW_FMT |
Format HH24:MI hrs |
07:00 hrs, 14:00 hrs |
7 |
SHFT_HIGH_FMT |
Format hh24:mi hrs |
14:00 hrs, 22:00 hrs |
Description: TIME DAY SNOWFLAKE, WEEKDAY
The Time Dimension stores the Date component of the Date Time Stamp fields. Each record represents a day.
Calendar Time Hierarchy levels: All, Year, Half Year, Quarter, Month, and Day.
Calendar Week Time Hierarchy levels: All, Week, and Day.
This table is created and populated through a time dimension load script.
Standard Time Calendar Hierarchy:
Time Calendar Week Hierarchy:
Table 3-26 shows Calendar Year. Calendar Year captures information relating to a year in a Business Calendar.
Table 3-26 Time Calendar Year
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
CAL_YR_KY |
Unique warehouse key of the Year, in the Calendar. |
20010101, 20020101 |
2 |
YR_CD |
20010101, 20020101 |
|
3 |
YR_NBR |
Calendar year number. |
1,2,3 |
4 |
YR_DESC |
Calendar year description. |
CY 2001, CY 2002 |
5 |
CAL_NM |
Name of the Calendar. |
CALENDAR |
6 |
YR_STRT_DT |
Calendar year start date. |
01-JAN-01, 01-JAN-02 |
7 |
YR_END_DT |
Calendar year end date. |
31-DEC-01, 31-DEC-02 |
8 |
YR_TIMESPN |
The length, in terms of days, of this year in the Calendar. |
365 |
Table 3-27 shows Calendar Half Year that captures information relating to half year in a Business Calendar.
Table 3-27 Time Calendar Half Year
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
CAL_HALF_YR_KY |
Unique warehouse key of the half year, in the Calendar. |
20010101, 20010701 |
2 |
HALF_YR_CD |
20010101, 20010701 |
|
3 |
CAL_YR_KY |
Unique key of the year, in which this half year occurred in the Calendar. |
20010101,20020101 |
4 |
YR_CD |
Name of the Calendar. |
20010101,20020101 |
5 |
HALF_YR_NBR |
A numeric representation of half year number in the Calendar. It contains values 1 or 2. |
1,2,3,4 |
6 |
HALF_YR_DESC |
Calendar half year description. |
CY 2001 HY1, CY 2001 HY2, CY 2002 HY1 |
7 |
HALF_YR_STRT_DT |
Calendar half year start date. |
01-JAN-01, 01-JUL-01 |
8 |
HALF_YR_END_DT |
Calendar half year END date. |
30-JUN-01, 31-DEC-01 |
9 |
HALF_YR_TIMESPN |
The length, in terms of days, of this half year in the Calendar. |
181, 184 |
Table 3-28 shows Quarter that captures information relating to Quarter in a Business Calendar.
Table 3-28 Time Calendar Quarter
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
CAL_QTR_KY |
Unique warehouse key of the quarter, in the Calendar. |
20141001, 20150101, 20150401, 20150701 |
2 |
QTR_CD |
Code of the quarter, in the Calendar. |
20141001, 20150101, 20150401, 20150701 |
3 |
CAL_HALF_YR_KY |
Unique key of the half year, in which this quarter occurred in the Calendar. |
20140701, 20150101, 20010101 |
4 |
HALF_YR_CD |
Code of the half year, in which this quarter occurred in the Calendar |
20140701, 20150101, 20010101 |
5 |
QTR_NBR |
A numeric representation of the quarter number in the Calendar. It ranges from 1 to 4. |
56,57,60,1,2,3 |
6 |
QTR_DESC |
Calendar quarter description. |
CY 2014 Q4, CY 2015 Q1, CY 2015 Q2, CY 2015 Q3 |
7 |
QTR_STRT_DT |
Calendar quarter start date. |
01-OCT-14, 01-JAN-15, 01-APR-15, 01-JUL-15 |
8 |
QTR_END_DT |
Calendar quarter end date. |
31-DEC-14, 31-MAR-15, 30-JUN-15, 30-SEP-15 |
9 |
QTR_TIMESPN |
The length, in terms of days, of this quarter in the Calendar. |
92, 90, 91 |
Table 3-29 shows Calendar Month that captures information relating to a month in a Calendar.
Table 3-29 Time Calendar Month
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
CAL_MN_KY |
Unique warehouse key of the month, in the Calendar. |
20050801, 20050901 |
2 |
MN_CD |
Code of the month, in the Calendar. |
20050801, 20050901 |
3 |
CAL_QTR_KY |
Unique key of the quarter, in which this month occurred in the Calendar. |
20050701, 20060101 |
4 |
QTR_CD |
Code of the quarter, in which this month occurred in the Calendar. |
20050701, 20051001 |
5 |
MN_NBR |
A numeric representation of the month number in the Calendar. It ranges from 1 to 12. |
56,57,58,59 |
6 |
MN_DESC |
Calendar month description. |
CY 2005 M8, CY 2005 M9, CY 2005 M10, CY 2005 M11 |
7 |
MN_STRT_DT |
Calendar month start date. |
01-AUG-05, 01-SEP-05 |
8 |
MN_END_DT |
Calendar month end date. |
31-AUG-05, 30-SEP-05 |
9 |
MN_TIMESPN |
The length, in terms of days, of this month in the Calendar. |
31, 30 |
Table 3-30 shows Calendar Week that captures information relating to a week in a Calendar.
Table 3-30 Time Calendar Week
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
CAL_WK_KY |
Unique warehouse key of the week, in the Calendar. |
20020317, 20020324 |
2 |
WK_CD |
Code of the week, in the Calendar. |
20020317, 20020324 |
3 |
WK_NBR |
A numeric representation of the week number in the Calendar. It ranges from 1 to 53. |
63,64,65,66 |
4 |
WK_DESC |
Calendar week description. |
CY 2002 W11, CY 2002 W12 |
5 |
WK_STRT_DT |
Calendar week start date. |
17-MAR-02, 24-MAR-02 |
6 |
WK_END_DT |
Calendar week end date. |
23-MAR-02, 30-MAR-02 |
7 |
WK_TIMESPN |
The length, in terms of days, of this week in the Calendar. |
7 |
Table 3-31 shows Day that captures information relating to a Day level in a Calendar.
Table 3-31 Time Calendar Day
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
DATE_ID |
The unique identifier for a calendar date |
20010220, 20010221 |
2 |
CAL_WK_DY_KY |
Unique warehouse key of the week, in the Calendar. |
3,4,5,6,7 |
3 |
CAL_WK_DY |
A numeric representation of the week day. |
TUE, WED |
4 |
CAL_WK_DY_DESC |
A descriptive representation of the week day. |
TUESDAY, WEDNESDAY |
5 |
CAL_JULIAN_DY |
The numeric representation, according to the Julian calendar. |
2451961, 2451962 |
6 |
CAL_DY_TIMESPN |
The length, in terms of days, of this date in the Calendar. |
1 |
7 |
CAL_DY_OF_YR |
A numeric representation of the day of the year. |
51, 52, 53 |
8 |
CAL_WRKG_DY_IND |
The flag representing if the day is a working day or not. For example, 0 is not a working day and 1 is a working day. |
Y,N |
9 |
CAL_HOLIDY_IND |
The flag representing if the day is a holiday or not. For example, 0 is not a holiday and 1 is a holiday. |
N,Y |
10 |
CAL_WEEKEND_IND |
The flag representing if the day is a weekend or not. For example, 0 is a weekday and 1 is a weekend. |
N,Y |
11 |
CAL_DT |
Represents a calendar date. |
20-FEB-01, 21-FEB-01 |
12 |
CAL_DT_DESC |
20010220, 20010221 |
|
13 |
CAL_STRT_DT |
Start date of Calendar |
20-FEB-01, 21-FEB-01 |
14 |
CAL_END_DT |
End date of Calendar |
20-FEB-01, 21-FEB-01 |
15 |
CAL_LOAD_DT |
Null |
|
16 |
CAL_LST_UPDT_DT |
Null |
|
17 |
CAL_LST_UPDT_BY |
Null |
|
18 |
CAL_CRNT_IND |
Null |
|
19 |
CAL_WK_KY |
20010218, 20010225, 20010304 |
|
20 |
CAL_WK_CD |
20010218, 20010225, 20010304 |
|
21 |
CAL_WK_NBR |
7,8,9 |
|
22 |
CAL_WK_DESC |
CY 2001 W7, CY 2001 W8 |
|
23 |
CAL_WK_STRT_DT |
18-FEB-01, 25-FEB-01 |
|
24 |
CAL_WK_END_DT |
24-FEB-01, 03-MAR-01 |
|
25 |
CAL_WK_TIMESPN |
7 |
|
26 |
CAL_MN_KY |
20010201, 20010301 |
|
27 |
CAL_MN_CD |
20010201, 20010301 |
|
28 |
CAL_MN_NBR |
2,3,4,5 |
|
29 |
CAL_MN_DESC |
CY 2001 M2, CY 2001 M3, CY 2001 M4 |
|
30 |
CAL_MN_STRT_DT |
01-FEB-01, 01-MAR-01 |
|
31 |
CAL_MN_END_DT |
28-FEB-01, 31-MAR-01 |
|
32 |
CAL_MN_TIMESPN |
28,31 |
|
33 |
CAL_QTR_KY |
20010101, 20010401 |
|
34 |
CAL_QTR_CD |
20010101, 20010401 |
|
35 |
CAL_QTR_NBR |
1,2 |
|
36 |
CAL_QTR_DESC |
CY 2001 Q1, CY 2001 Q2 |
|
37 |
CAL_QTR_STRT_DT |
01-JAN-01, 01-APR-01 |
|
38 |
CAL_QTR_END_DT |
31-MAR-01, 30-JUN-01 |
|
39 |
CAL_QTR_TIMESPN |
90,91 |
|
40 |
CAL_HALF_YR_KY |
20010101, 20010701 |
|
41 |
CAL_HALF_YR_CD |
20010101, 20010701 |
|
42 |
CAL_HALF_YR_NBR |
1 |
|
43 |
CAL_HALF_YR_DESC |
CY 2001 HY1, CY 2001 HY2 |
|
44 |
CAL_HALF_YR_STRT_DT |
01-JAN-01, 01-JUL-01 |
|
45 |
CAL_HALF_YR_END_DT |
30-JUN-01, 31-DEC-01 |
|
46 |
CAL_HALF_YR_TIMESPN |
181 |
|
47 |
CAL_YR_KY |
20010101 |
|
48 |
CAL_YR_CD |
20010101 |
|
49 |
CAL_YR_NBR |
1 |
|
50 |
CAL_YR_DESC |
CY 2001 |
|
51 |
CAL_YR_STRT_DT |
01-JAN-01 |
|
52 |
CAL_YR_END_DT |
31-DEC-01 |
|
53 |
CAL_YR_TIMESPN |
365 |
Description: Time of Day
Standard Time of Day Hierarchy:
Table 3-32 shows Hour, the most aggregate level of the Time of Day dimension.
Table 3-33 shows Half_Hr that captures information relating to half hour in Time of Day.
Table 3-34 shows Qtr_Hr that captures information relating to Quarter hour in Time of Day.
Table 3-35 shows Minutes that captures information relating to Minutes in Time of Day.
Table 3-36 shows Seconds that captures information relating to Seconds in Time of Day.
Table 3-36 Time of Day: Seconds
Sr. Number | Attribute | Description | Sample Value |
---|---|---|---|
1 |
TOD_ID |
Time of Day Id … represents a second within a day |
64, 65,66 |
2 |
TOD_NM |
TOD Name |
64, 65,66 |
3 |
TOD_DESC |
TOD Description |
64, 65,66 |
4 |
TOD_HMISS_AM |
Format HH:MI:SS AM/PM |
12:00:03AM, 12:00:04AM, 12:00:05AM |
5 |
TOD_HH24MISS |
Format HH24:MI:SS |
00:01:03, 00:01:04 00:01:05 |
6 |
TOD_MINUTE |
TOD Minute information (in HH24:MI format) |
00:01 |
7 |
TOD_QTR_HR |
TOD Quarter Hour information (in HH24:MI format) |
00:00 |
8 |
TOD_HALF_HR |
TOD Half Hour information (in HH24:MI format) |
00:00 |
9 |
TOD_HR |
TOD Hour information (in HH24:MI format) |
00:00 |