3 Logical Data Model Dimensions

This chapter describes the logical data model dimensions shown in Table 3-1.

Table 3-1 Logical Data Model Dimensions

Dimension
Age Band
Facility
KPI
Organization
Patient
Service Provider Practice
Shift
Time
Time of Day

Logical Data Model Dimensions

This section lists the logical data model dimensions.

Age Band

Description: AGE BAND

Age Band Hierarchies

Standard Age Band Hierarchy

Description of the illustration hdmrf_age1.gif

Age Band Levels

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-2 Age Band Total

Sr. Number	Attribute	Description
1.	TAGEBND	Total Age Band

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

Facility

Description: FACILITY

Facility Hierarchies

Standard Facility Hierarchy:

Description of the illustration hdmrf_fac1.gif

Facility Levels

Table 3-4 shows Total Facility. Total Facility is the most aggregate level of the dimension.

Table 3-4 Facility Total

Sr. No.	Attribute	Description
1	TFAC	Total Facility

Table 3-5 shows facility Campus level.

Table 3-5 Facility Campus Level

Sr. No.	Attribute	Description
1	L6 - Campus	L6 - Campus

Table 3-6 shows facility Building level.

Table 3-6 Facility Building Level

Sr. No.	Attribute	Description
1	L5 - Building	L5 - Building

Table 3-7 shows facility Floor level.

Table 3-7 Facility Floor Level

Sr. No.	Attribute	Description
1	L4 - Floor	L4 - Floor

Table 3-8 shows facility Suite level.

Table 3-8 Facility Suite Level

Sr. No.	Attribute	Description
1	L3 - Suite	L3 - Suite

Table 3-9 shows facility Care Site level.

Table 3-9 Facility Care Site Level

Sr. No.	Attribute	Description
1	L2 - Care site	L2 - Care site

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

KPI

Description: KPI DIMENSION TABLE

KPI Hierarchies

Standard KPI Hierarchy

Description of the illustration hdmrf_kpi1.gif

KPI Levels

Table 3-11 shows KPI Total. Total KPI is the most aggregate level of the dimension.

Table 3-11 KPI Total

Sr. Number	Attribute	Description
1.	TKPI	Total KPI

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

Organization

Description: ORGANIZATION DIMENSION

Organization Hierarchies

Standard Organization Hierarchy.

Description of the illustration hdmrf_org1.gif

Organization Levels

Table 3-13 shows Total Organization.

Table 3-13 Organization Total

Sr. Number	Attribute	Description
1.	TORG	Total Organization

Table 3-14 shows Enterprise level.

Table 3-14 Organization Enterprise

Sr. Number	Attribute	Description
1.	ENTRPRS	Enterprise

Table 3-15 shows Regional BU level

Table 3-15 Organization Regional BU Total

Sr. Number	Attribute	Description
1.	RGNBU	Regional BU

Table 3-16 shows Facility BU level.

Table 3-16 Organization Facility BU

Sr. Number	Attribute	Description
1.	FCLBU	Facility BU

Table 3-17 shows Department level.

Table 3-17 Organization Department

Sr. Number	Attribute	Description
1.	DEP	Department

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

Patient

Description: PATIENT DIMENSION

Patient Hierarchy

Standard Patient Hierarchy

Description of the illustration hdmrf_pat1.gif

Patient Levels

Table 3-19 shows Total Patient. All Total Patient is the aggregate level of the dimension.

Table 3-19 Patient Total

Sr. Number	Attribute	Description
1.	TPT	Total Patient

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

Service Provider Practice

Description: SERVICE PROVIDER PRACTICE SNOWFLAKE

Service Provider Practice Hierarchy

Standard Service Provider Practice Hierarchy.

Description of the illustration hdmrf_servicepp1.gif

Service Provider Practice Levels

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-22 Service Provider Practice Practice

Sr. Number	Attribute	Description
1.	PRCT	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

Shift

Description: SHIFT DIMENSION

Shift Hierarchy

Standard Shift Hierarchy:

Description of the illustration hdmrf_shift1.gif

Shift Levels

Table 3-24 shows Shift Total. All Shift is the most aggregate level of the dimension.

Table 3-24 Shift Total

Sr. Number	Attribute	Description
1.	TSHFT	Total Shift

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

Time

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.

Time Hierarchy

Standard Time Calendar Hierarchy:

Description of the illustration hdmrf_timecal1.gif

Time Calendar Week Hierarchy:

Description of the illustration hdmrf_timewk1.gif

Time Levels

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

Time of Day

Description: Time of Day

Time of Day Hierarchy

Standard Time of Day Hierarchy:

Description of the illustration hdmrf_tod1.gif

Time of Day Levels

Table 3-32 shows Hour, the most aggregate level of the Time of Day dimension.

Table 3-32 Time of Day: Hour

Sr. Number	Attribute	Description
1.	HR	Hour

Table 3-33 shows Half_Hr that captures information relating to half hour in Time of Day.

Table 3-33 Time of Day: Half Hour

Sr. Number	Attribute	Description
1	HLFHR	Half Hour

Table 3-34 shows Qtr_Hr that captures information relating to Quarter hour in Time of Day.

Table 3-34 Time of Day: Quarter Hour

Sr. Number	Attribute	Description
1	QTRHR	Quarter Hour

Table 3-35 shows Minutes that captures information relating to Minutes in Time of Day.

Table 3-35 Time of Day: Minutes

Sr. Number	Attribute	Description
1	MIN	Minute

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