| Oracle® Healthcare Data Model Reference 11g Release 2 (11.2) Part Number E18026-02 |
|
|
View PDF |
| Oracle® Healthcare Data Model Reference 11g Release 2 (11.2) Part Number E18026-02 |
|
|
View PDF |
This chapter describes the Data Flow between fact tables and dimension tables of Oracle Healthcare Data Model relational part to target materialize views and cubes to support the module Oracle Healthcare Data Model OLAP.
This chapter includes the following section:
For more information, see Chapter 9, "Oracle Healthcare Data Model OLAP Dimensions".
For each cube, each section includes the following cube information:
Description
Dimensions (leaf load level and load sequence)
Base Measures with Physical Mapping and Description
Derived Measure with the Logical Name and the Calculations
Table 10-1 lists the Oracle Healthcare Data Model OLAP cubes.
Table 10-1 Oracle Healthcare Data Model OLAP Cubes
| Cubes | Description |
|---|---|
|
Encounter Cube |
|
|
Patient Cube |
|
|
Case Cube |
|
|
Observation Cube |
Summary of Case KPIs (from case fact table).
Table 10-2 Case Cube Dimensions
| Dimension | Leaf Level | Aggregate Method |
|---|---|---|
|
KPI |
KPI |
MAX |
|
Facility |
Facility |
MAX |
|
Organization |
Business Unit |
MAX |
|
Service Provider Practice |
Service Provider Practice |
MAX |
|
Age Band |
Age Band |
MAX |
|
Shift |
Shift |
MAX |
|
Time of Day |
Second |
MAX |
|
Time |
Day |
MAX |
|
Encounter |
Encounter |
MAX |
|
Patient |
Patient |
MAX |
|
Case |
Case |
SUM |
Stored Measures
KPI_DEN_CC: Maps to KPI Denominator column in KPI Case Fact table. This measure can be used for KPIs involving Count of Cases.
KPI_NUM_CC: Maps to KPI Numerator column in KPI Case Fact table. This measure can be used for KPIs involving Count of Cases.
Calculated Measures
KPI_CC = ( KPI_NUM_CC/ KPI_DEN_CC):KPI Denominator / KPI Numerator (Cases)
KPI_DEN_CC_LY:KPI Denominator(Cases) Last Year
KPI_NUM_CC_LY:KPI Numerator(Cases) Last Year
KPI_CC_LY = (KPI_NUM_CC_LY/KPI_DEN_CC_LY):KPI (Cases) last year
KPI_CC_LY_PCT_CHG=((KPI_CC - KPI_CC_LY)/KPI_CC_LY)*100: KPI(Cases) percentage change from last year
KPI_DEN_CC_LP:KPI Denominator(Cases) Last Period
KPI_NUM_CC_LP:KPI Numerator(Cases) Last Period
KPI_CC_LP = (KPI_NUM_CC_LP/KPI_DEN_CC_LP):KPI (Cases) last period
KPI_CC_LP_PCT_CHG = ((KPI_CC - KPI_CC_LP)/KPI_CC_LP)*100:KPI(Cases) percentage change from last period
KPI_CC_FAC_RNK:Ranking of KPI(Cases) within facility dimension
KPI_CC_ORG_RNK:Ranking of KPI(Cases) within organization dimension
Special Cases/Calculation
Note: Put restriction on KPI dimension, and select the appropriate measure for each KPI
In this release there is only one case KPI
KPI #3:
Set KPI dimension appropriately
Expression:KPI_CC
Create a new calculated measure KPI_MEAS which will be used in the reports.
The other measures will be available as needed to showcase additional measures/calculations for the KPIs.
KPI_MEAS:
If KPI(KPI KPI) in ('KPI_3') then KPI_CC else NA
Summary of encounter KPIs (from encounter fact table).
Cube level Aggregation Setting
Table 10-3 Encounter Cube Dimensions
| Dimension | Leaf Level | Aggregate Method |
|---|---|---|
|
KPI |
KPI |
SUM |
|
Facility |
Facility |
SUM |
|
Organization |
Business Unit |
SUM |
|
Service Provider Practice |
Service Provider Practice |
SUM |
|
Age Band |
Age Band |
SUM |
|
Shift |
Shift |
SUM |
|
Time of Day |
Second |
SUM |
|
Time |
Day/Encounter |
SUM |
|
Patient |
Patient |
SUM |
Stored Measures
"CEP" stands for Count of Encounters/Patients.
Table 10-4 Encounter Stored Measures
| Sr. No | Name | Description |
|---|---|---|
|
1 |
KPI_NUM_VAL_CEP |
KPI Numerator Value |
|
2 |
KPI_DEN_VAL_CEP |
KPI Denominator Value |
|
3 |
KPI_DEN_CE |
KPI Denominator |
|
4 |
KPI_NUM_CE |
KPI Numerator |
For the KPIs which depend on the Value columns (that is, KPIs 31,32 and 36, 37) the calculations do not differ based on Count of Encounters/Patients.
KPI_DEN_CE: Maps to KPI Denominator column in KPI Encounter Fact table. This measure can be used for KPIs involving Count of Encounters
KPI_NUM_CE: Maps to KPI Numerator column in KPI Encounter Fact table. This measure can be used for KPIs involving Count of Encounters.
Calculated Measures
Table 10-5 Encounter Calculated Measures
| Sr. No | Name | Description | Expression |
|---|---|---|---|
|
1 |
KPI_CE |
KPI Denominator / KPI Numerator (Encounters) |
( KPI_NUM_CE/ KPI_DEN_CE) |
|
2 |
KPI_DEN_CE_LY |
KPI Denominator(Encounters) Last Year |
LAG(ENC_KPI.KPI_DEN_CE, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
3 |
KPI_NUM_CE_LY |
KPI Numerator(Encounters) Last Year |
LAG(ENC_KPI.KPI_NUM_CE, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
4 |
KPI_CE_LY |
KPI (Encounters) last year |
ENC_KPI.KPI_NUM_CE_LY / ENC_KPI.KPI_DEN_CE_LY |
|
5 |
KPI_CE_LY_PC |
KPI(Encounters) percentage change from last year |
((ENC_KPI.KPI_CE - ENC_KPI.KPI_CE_LY) / ENC_KPI.KPI_CE_LY) * 100 |
|
6 |
KPI_DEN_CE_LP |
KPI Denominator(Encounters) last period |
LAG(ENC_KPI.KPI_DEN_CE, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
7 |
KPI_CE_LP |
KPI (Encounters) last period |
ENC_KPI.KPI_NUM_CE_LP / ENC_KPI.KPI_DEN_CE_LP |
|
8 |
KPI_CE_LP_PC |
KPI(Encounters) percentage change from last period |
((ENC_KPI.KPI_CE - ENC_KPI.KPI_CE_LP) / ENC_KPI.KPI_CE_LP) * 100 |
|
9 |
KPI_CE_FAC_RNK |
Ranking of KPI(Encounters) within facility dimension |
RANK() OVER HIERARCHY (FAC.HFAC ORDER BY ENC_KPI.KPI_CE DESC NULLS LAST WITHIN PARENT) |
|
10 |
KPI_CE_ORG_RNK |
Ranking of KPI(Encounters) within organization dimension |
RANK() OVER HIERARCHY (ORG.HORG ORDER BY ENC_KPI.KPI_CE DESC NULLS LAST WITHIN PARENT) |
|
11 |
KPI_CE_SPP_RNK |
Ranking of KPI(Encounters) within service provider practice dimension |
RANK() OVER HIERARCHY (SPP.HSPP ORDER BY ENC_KPI.KPI_CE DESC NULLS LAST WITHIN PARENT) |
|
12 |
KPI_NUM_VAL_CEP_LY |
KPI numerator value last year |
LAG(ENC_KPI.KPI_NUM_VAL_CEP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
13 |
KPI_NUM_VAL_CEP_LYC |
KPI numerator value percent change of last year |
((ENC_KPI.KPI_NUM_VAL_CEP - ENC_KPI.KPI_NUM_VAL_CEP_LY) / ENC_KPI.KPI_NUM_VAL_CEP_LY) * 100 |
|
14 |
KPI_NUM_VAL_CEP_LP |
KPI numerator value percent change of last period |
LAG(ENC_KPI.KPI_NUM_VAL_CEP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
15 |
KPI_NUM_VAL_CEP_LPC |
KPI numerator value percent change of last period |
((ENC_KPI.KPI_NUM_VAL_CEP - ENC_KPI.KPI_NUM_VAL_CEP_LP) / ENC_KPI.KPI_NUM_VAL_CEP_LP) * 100 |
|
16 |
KPI_NUM_VAL_CEP_FR |
Ranking of KPI numerator value within facility dimension |
RANK() OVER HIERARCHY (FAC.HFAC ORDER BY ENC_KPI.KPI_NUM_VAL_CEP DESC NULLS LAST WITHIN PARENT) |
|
17 |
KPI_NUM_VAL_CEP_OR |
Ranking of KPI numerator value within organization dimension |
RANK() OVER HIERARCHY (ORG.HORG ORDER BY ENC_KPI.KPI_NUM_VAL_CEP DESC NULLS LAST WITHIN PARENT) |
|
18 |
KPI_DEN_VAL_CEP_AVG |
Average of KPI Denominator value |
ENC_KPI.KPI_DEN_VAL_CEP / ENC_KPI.KPI_DEN_CE |
|
19 |
KPI_MEAS |
Represent the correct calculation needed for each of the KPIs |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_1'') or (KPI(KPI KPI) EQ ''KPI_4'') or (KPI(KPI KPI) EQ ''KPI_12'') or (KPI(KPI KPI) EQ ''KPI_22'') or (KPI(KPI KPI) EQ ''KPI_27'') or (KPI(KPI KPI) EQ ''KPI_28'') or (KPI(KPI KPI) EQ ''KPI_34'') or (KPI(KPI KPI) EQ ''KPI_35'') THEN ENC_KPI_KPI_CE ELSE IF (KPI(KPI KPI) EQ ''KPI_31'') or (KPI(KPI KPI) EQ ''KPI_32'') THEN ENC_KPI_KPI_MEDIAN ELSE IF (KPI(KPI KPI) EQ ''KPI_36'') or (KPI(KPI KPI) EQ ''KPI_37'') THEN ENC_KPI_KPI_NUM_VAL_CEP ELSE NA', NUMBER) |
|
20 |
KPI_MEAS_LP |
Represent the correct calculation needed for each of the KPIs LP |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_1'') or (KPI(KPI KPI) EQ ''KPI_4'') or (KPI(KPI KPI) EQ ''KPI_12'') or (KPI(KPI KPI) EQ ''KPI_22'') or (KPI(KPI KPI) EQ ''KPI_27'') or (KPI(KPI KPI) EQ ''KPI_28'') or (KPI(KPI KPI) EQ ''KPI_34'') or (KPI(KPI KPI) EQ ''KPI_35'') THEN ENC_KPI_KPI_CE_LP ELSE IF (KPI(KPI KPI) EQ ''KPI_31'') or (KPI(KPI KPI) EQ ''KPI_32'') THEN ENC_KPI_KPI_MEDIAN_LP ELSE IF (KPI(KPI KPI) EQ ''KPI_36'') or (KPI(KPI KPI) EQ ''KPI_37'') THEN ENC_KPI_KPI_NUM_VAL_CEP_LP ELSE NA', NUMBER) |
|
21 |
KPI_MEAS_LP_PCT_CHG |
Represent the correct calculation needed for each of the KPIs % change LP |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_1'') or (KPI(KPI KPI) EQ ''KPI_4'') or (KPI(KPI KPI) EQ ''KPI_12'') or (KPI(KPI KPI) EQ ''KPI_22'') or (KPI(KPI KPI) EQ ''KPI_27'') or (KPI(KPI KPI) EQ ''KPI_28'') or (KPI(KPI KPI) EQ ''KPI_34'') or (KPI(KPI KPI) EQ ''KPI_35'') THEN ENC_KPI_KPI_CE_LP_PC ELSE IF (KPI(KPI KPI) EQ ''KPI_31'') or (KPI(KPI KPI) EQ ''KPI_32'') THEN ENC_KPI_KPI_MEDIAN_LP_PC ELSE IF (KPI(KPI KPI) EQ ''KPI_36'') or (KPI(KPI KPI) EQ ''KPI_37'') THEN ENC_KPI_KPI_NUM_VAL_CEP_LPC ELSE NA', NUMBER) |
|
22 |
KPI_MEAS_LY |
Represent the correct calculation needed for each of the KPIs LY |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_1'') or (KPI(KPI KPI) EQ ''KPI_4'') or (KPI(KPI KPI) EQ ''KPI_12'') or (KPI(KPI KPI) EQ ''KPI_22'') or (KPI(KPI KPI) EQ ''KPI_27'') or (KPI(KPI KPI) EQ ''KPI_28'') or (KPI(KPI KPI) EQ ''KPI_34'') or (KPI(KPI KPI) EQ ''KPI_35'') THEN ENC_KPI_KPI_CE_LY ELSE IF (KPI(KPI KPI) EQ ''KPI_31'') or (KPI(KPI KPI) EQ ''KPI_32'') THEN ENC_KPI_KPI_MEDIAN_LY ELSE IF (KPI(KPI KPI) EQ ''KPI_36'') or (KPI(KPI KPI) EQ ''KPI_37'') THEN ENC_KPI_KPI_NUM_VAL_CEP_LY ELSE NA', NUMBER) |
|
23 |
KPI_MEAS_LY_PCT_CHG |
Represent the correct calculation needed for each of the KPIs % change LY |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_1'') or (KPI(KPI KPI) EQ ''KPI_4'') or (KPI(KPI KPI) EQ ''KPI_12'') or (KPI(KPI KPI) EQ ''KPI_22'') or (KPI(KPI KPI) EQ ''KPI_27'') or (KPI(KPI KPI) EQ ''KPI_28'') or (KPI(KPI KPI) EQ ''KPI_34'') or (KPI(KPI KPI) EQ ''KPI_35'') THEN ENC_KPI_KPI_CE_LY_PC ELSE IF (KPI(KPI KPI) EQ ''KPI_31'') or (KPI(KPI KPI) EQ ''KPI_32'') THEN ENC_KPI_KPI_MEDIAN_LY_PC ELSE IF (KPI(KPI KPI) EQ ''KPI_36'') or (KPI(KPI KPI) EQ ''KPI_37'') THEN ENC_KPI_KPI_NUM_VAL_CEP_LYC ELSE NA', NUMBER) |
|
24 |
KPI_MEDIAN |
The median of KPI value |
ENC_KPI_MEDIAN(kpi(kpi kpi) fac(fac fac) shft(shft shft) org(org org) spp(spp spp) agebnd(agebnd agebnd) tod(tod tod) pt(pt pt) time(time time)) |
|
25 |
KPI_MEDIAN_LP |
The median of KPI value LP |
ENC_KPI_MEDIAN_LP(kpi(kpi kpi) fac(fac fac) shft(shft shft) org(org org) spp(spp spp) agebnd(agebnd agebnd) tod(tod tod) pt(pt pt) time(time time)) |
|
26 |
KPI_MEDIAN_LP_PC |
The median of KPI value % change of LP |
((ENC_KPI.KPI_MEDIAN - ENC_KPI.KPI_MEDIAN_LP) / ENC_KPI.KPI_MEDIAN_LP) * 100 |
|
27 |
KPI_MEDIAN_LY |
The median of KPI value LY |
ENC_KPI_MEDIAN_LY(kpi(kpi kpi) fac(fac fac) shft(shft shft) org(org org) spp(spp spp) agebnd(agebnd agebnd) tod(tod tod) pt(pt pt) time(time time)) |
|
28 |
KPI_MEDIAN_LY_PC |
The median of KPI value % change of LY |
((ENC_KPI.KPI_MEDIAN - ENC_KPI.KPI_MEDIAN_LY) / ENC_KPI.KPI_MEDIAN_LY) * 100 |
|
29 |
KPI_MEDIAN_ORG_RNK |
Ranking of KPI median within organization dimension |
RANK() OVER HIERARCHY (ORG.HORG ORDER BY ENC_KPI.KPI_MEDIAN DESC NULLS LAST WITHIN PARENT) |
This cube is used to store the summary of the observation KPIs (from observation fact table).
Table 10-6 Observation Cube Dimensions
| Dimension | Leaf Level | Aggregate Method |
|---|---|---|
|
Age Band |
Age Band |
MAX |
|
Encounter |
Encounter |
MAX |
|
Facility |
Facility |
MAX |
|
KPI |
KPI |
MAX |
|
Organization |
Business Unit |
MAX |
|
Patient |
Patient |
MAX |
|
Service Provider Practice |
Service Provider Practice |
MAX |
|
Shift |
Shift |
MAX |
|
Time |
Day |
MAX |
|
Time of Day |
Second |
MAX |
|
Observation |
Observation |
SUM |
Stored Measures:
KPI_DEN_CO: Maps to KPI Denominator column in KPI Case Fact table. This measure can be used for KPIs involving Count of Observation.
KPI_NUM_CO: Maps to KPI Numerator column in KPI Case Fact table. This measure can be used for KPIs involving Count of Observation.
Calculated Measures:
KPI_CO = ( KPI_NUM_CO/ KPI_DEN_CO):KPI Denominator / KPI Numerator (Observations)
KPI_DEN_CO_LY:KPI Denominator(Observations) Last Year
KPI_NUM_CO_LY:KPI Numerator(Observations) Last Year
KPI_CO_LY = (KPI_NUM_CO_LY/KPI_DEN_CO_LY):KPI (Observations) last year
KPI_CO_LY_PCT_CHG=((KPI_CO - KPI_CO_LY)/KPI_CO_LY)*100: KPI(Observations) percentage change from last year
KPI_DEN_CO_LP:KPI Denominator(Observations) Last Period
KPI_NUM_CO_LP:KPI Numerator(Observations) Last Period
KPI_CO_LP = (KPI_NUM_CO_LP/KPI_DEN_CO_LP):KPI (Observations) last period
KPI_CO_LP_PCT_CHG = ((KPI_CO - KPI_CO_LP)/KPI_CO_LP)*100:KPI(Observations) percentage change from last period
KPI_CO_FAC_RNK:Ranking of KPI(Observations) within facility dimension
KPI_CO_ORG_RNK:Ranking of KPI(Observations) within organization dimension
Special Cases/Calculation:
Note:Put restriction on KPI dimension, and select the appropriate measure for each KPI
In this release there is only one observation KPI
KPI #6:
Set KPI dimension appropriately
Expression:KPI_CO
Create a new calculated measure KPI_MEAS which will be used in the reports.
The other measures will be available as needed to showcase additional measures/calculations for the KPIs.
KPI_MEAS:
If KPI(KPI KPI) in ('KPI_6') then KPI_CO else NA
Summary of patient KPIs (from encounter fact table).
Cube level Aggregation Setting
Table 10-7 Patient Cube Dimensions
| Dimension | Leaf Level | Aggregate Method |
|---|---|---|
|
KPI |
KPI |
MAX |
|
Facility |
Facility |
MAX |
|
Organization |
Business Unit |
MAX |
|
Service Provider Practice |
Service Provider Practice |
MAX |
|
Age Band |
Age Band |
MAX |
|
Shift |
Shift |
MAX |
|
Time of Day |
Second |
MAX |
|
Time |
Day/ Encounter |
MAX |
|
Patient |
Patient |
SUM |
Stored Measures
Table 10-8 Patient Cube Stored Measures
| Sr. No | Name | Description |
|---|---|---|
|
1 |
KPI_DEN_CP |
KPI Denominator(Count of Patients) |
|
2 |
KPI_NUM_CP |
KPI Numerator(Count of Patients) |
KPI_DEN_CP: Also maps to KPI Denominator column in KPI Encounter Fact table. This measure can be used for KPIs involving Count of Patients.
KPI_NUM_CP: Also maps to KPI Denominator column in KPI Encounter Fact table. This measure can be used for KPIs involving Count of Patients.
Calculated Measures
Table 10-9 Patient Cube Calculated Measures
| Sr. No | Name | Description | Expression |
|---|---|---|---|
|
1 |
KPI_CP |
KPI Denominator / KPI Numerator (Patients) |
(KPI_NUM_CP/KPI_DEN_CP) |
|
2 |
KPI_DEN_CP_LY |
KPI Denominator(Patients) Last Year |
LAG(ENC_KPI.KPI_DEN_CP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
3 |
KPI_NUM_CP_LY |
KPI Numerator(Patients) Last Year |
LAG(ENC_KPI.KPI_NUM_CP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
4 |
KPI_NUM_CP_LP |
KPI Numerator(Patients) Last period |
LAG(PT_KPI.KPI_NUM_CP, 1) OVER HIERARCHY ("TIME".HTCLNDR) |
|
5 |
KPI_CP_LY |
KPI (Patients) last year |
ENC_KPI.KPI_NUM_CP_LY / ENC_KPI.KPI_DEN_CP_LY |
|
6 |
KPI_CP_LY_PC |
KPI(Patients) percentage change from last year |
((ENC_KPI.KPI_CP - ENC_KPI.KPI_CP_LY) / ENC_KPI.KPI_CP_LY) * 100 |
|
7 |
KPI_DEN_CP_LP |
KPI Denominator(Patients) last period |
LAG(ENC_KPI.KPI_DEN_CP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
8 |
KPI_CP_LP |
KPI (Patients) last period |
ENC_KPI.KPI_NUM_CP_LP / ENC_KPI.KPI_DEN_CP_LP |
|
9 |
KPI_CP_LP_PC |
KPI(Patients) percentage change from last period |
((ENC_KPI.KPI_CP - ENC_KPI.KPI_CP_LP) / ENC_KPI.KPI_CP_LP) * 100 |
|
10 |
KPI_CP_FAC_RNK |
Ranking of KPI(Patients) within facility dimension |
RANK() OVER HIERARCHY (FAC.HFAC ORDER BY ENC_KPI.KPI_CP DESC NULLS LAST WITHIN PARENT) |
|
11 |
KPI_CP_ORG_RNK |
Ranking of KPI(Patients) within organization dimension |
RANK() OVER HIERARCHY (ORG.HORG ORDER BY ENC_KPI.KPI_CP DESC NULLS LAST WITHIN PARENT) |
|
12 |
KPI_MEAS |
Represent the correct calculation needed for each of the KPIs |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_2'') or (KPI(KPI KPI) EQ ''KPI_10'') or (KPI(KPI KPI) EQ ''KPI_13'') or (KPI(KPI KPI) EQ ''KPI_16'') or (KPI(KPI KPI) EQ ''KPI_18'') or (KPI(KPI KPI) EQ ''KPI_19'') THEN PT_KPI_KPI_CP ELSE NA', NUMBER) |
|
13 |
KPI_MEAS_LP |
Represent the correct calculation needed for each of the KPIs LP |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_2'') or (KPI(KPI KPI) EQ ''KPI_10'') or (KPI(KPI KPI) EQ ''KPI_13'') or (KPI(KPI KPI) EQ ''KPI_16'') or (KPI(KPI KPI) EQ ''KPI_18'') or (KPI(KPI KPI) EQ ''KPI_19'') THEN PT_KPI_KPI_CP_LP ELSE NA', NUMBER) |
|
14 |
KPI_MEAS_LP_PCT_CHG |
Represent the correct calculation needed for each of the KPIs % change of LP |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_2'') or (KPI(KPI KPI) EQ ''KPI_10'') or (KPI(KPI KPI) EQ ''KPI_13'') or (KPI(KPI KPI) EQ ''KPI_16'') or (KPI(KPI KPI) EQ ''KPI_18'') or (KPI(KPI KPI) EQ ''KPI_19'') THEN PT_KPI_KPI_CP_LP_PC ELSE NA', NUMBER) |
|
15 |
KPI_MEAS_LY |
Represent the correct calculation needed for each of the KPIs LY |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_2'') or (KPI(KPI KPI) EQ ''KPI_10'') or (KPI(KPI KPI) EQ ''KPI_13'') or (KPI(KPI KPI) EQ ''KPI_16'') or (KPI(KPI KPI) EQ ''KPI_18'') or (KPI(KPI KPI) EQ ''KPI_19'') THEN PT_KPI_KPI_CP_LY ELSE NA', NUMBER) |
|
16 |
KPI_MEAS_LY_PCT_CHG |
Represent the correct calculation needed for each of the KPIs % change of LY |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_2'') or (KPI(KPI KPI) EQ ''KPI_10'') or (KPI(KPI KPI) EQ ''KPI_13'') or (KPI(KPI KPI) EQ ''KPI_16'') or (KPI(KPI KPI) EQ ''KPI_18'') or (KPI(KPI KPI) EQ ''KPI_19'') THEN PT_KPI_KPI_CP_LY_PC ELSE NA', NUMBER) |
|
17 |
KPI_MEAS_RNK |
Ranking of KPI |
OLAP_DML_EXPRESSION(' IF (KPI(KPI KPI) EQ ''KPI_2'') or (KPI(KPI KPI) EQ ''KPI_10'') or (KPI(KPI KPI) EQ ''KPI_13'') or (KPI(KPI KPI) EQ ''KPI_16'') or (KPI(KPI KPI) EQ ''KPI_18'') or (KPI(KPI KPI) EQ ''KPI_19'') THEN PT_KPI_KPI_CP_ORG_RNK ELSE NA', NUMBER) |
For each fact, each section includes the following information:
Description
Physical Name
Stored Measures
Calculated Measures
Table 10-10 lists the Oracle Healthcare Data Model OLAP cubes.
Table 10-10 Oracle Healthcare Data Model OLAP Facts
| Fact | Description |
|---|---|
|
KPI Case Fact |
|
|
KPI Encounter Fact |
|
|
KPI Observation Fact |
Provides a summary of Case KPIs, from case fact table.
Physical Name: DWD_KPI_CASE_FCT
Figure 10-1 shows the Case KPI fact organization.
Table 10-11 shows the Case KPI Fact stored measures.
Table 10-11 Case KPI Fact Stored Measures
| Name | Description | Comments |
|---|---|---|
|
1 |
KPI_DEN_CC |
KPI Denominator (This measure can be used for KPIs involving Count of Cases. |
|
2 |
KPI_DEN_VAL_CEP |
KPI Numerator (This measure can be used for KPIs involving Count of Cases. |
Table 10-12 shows the Case KPI Fact calculated measures.
Table 10-12 Case KPI Fact Calculated Measures
| Sr. No | Name | Description | Expression |
|---|---|---|---|
|
1 |
KPI_CC |
KPI Denominator / KPI Numerator (Cases) |
CASE_KPI.KPI_NUM_CC / CASE_KPI.KPI_DEN_CC |
|
2 |
KPI_DEN_CC_LY |
KPI Denominator(Cases) Last Year |
LAG(CASE_KPI.KPI_DEN_CC, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
3 |
KPI_NUM_CC_LY |
KPI Numerator(Cases) Last Year |
LAG(CASE_KPI.KPI_NUM_CC, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
4 |
KPI_CC_LY |
KPI (Cases) last year |
CASE_KPI.KPI_NUM_CC_LY / CASE_KPI.KPI_DEN_CC_LY |
|
5 |
KPI_CC_LY_PCT_CHG |
KPI(Cases) percentage change from last year |
((CASE_KPI.KPI_CC - CASE_KPI.KPI_CC_LY) / CASE_KPI.KPI_CC_LY) * 100 |
|
6 |
KPI_DEN_CC_LP |
KPI Denominator(Cases) Last Period |
LAG(CASE_KPI.KPI_DEN_CC, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
7 |
KPI_NUM_CC_LP |
KPI Numerator(Cases) Last Period |
LAG(CASE_KPI.KPI_NUM_CC, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
8 |
KPI_CC_LP |
KPI (Cases) last period |
CASE_KPI.KPI_NUM_CC_LP / CASE_KPI.KPI_DEN_CC_LP |
|
9 |
KPI_CC_LP_PCT_CHG |
KPI(Cases) percentage change from last period |
((CASE_KPI.KPI_CC - CASE_KPI.KPI_CC_LP) / CASE_KPI.KPI_CC_LP) * 100 |
|
10 |
KPI_CC_FAC_RNK |
Ranking of KPI(Cases) within facility dimension |
RANK() OVER HIERARCHY (FAC.HFAC ORDER BY CASE_KPI.KPI_CC DESC NULLS LAST WITHIN PARENT) |
|
11 |
KPI_CC_ORG_RNK |
Ranking of KPI(Cases) within organization dimension |
RANK() OVER HIERARCHY (ORG.HORG ORDER BY CASE_KPI.KPI_CC DESC NULLS LAST WITHIN PARENT) |
|
12 |
KPI_MEAS |
Represent the correct calculation needed for each of the KPIs |
Summary of encounter KPIs, from encounter fact table.
Physical Name: DWD_KPI_ENC_FCT
Figure 10-2 shows the Encounter KPI fact organization.
Figure 10-2 Encounter KPI Fact Organization
Table 10-13 shows the Encounter KPI Fact stored measures.
Table 10-13 Encounter KPI Fact Stored Measures
| Sr. No | Name | Description |
|---|---|---|
|
1 |
KPI_NUM_VAL_CEP |
KPI Numerator Value |
|
2 |
KPI_DEN_VAL_CEP |
KPI Denominator Value |
|
3 |
KPI_DEN_CE |
KPI Denominator |
|
4 |
KPI_NUM_CE |
KPI Numerator |
|
5 |
KPI_DEN_CP |
KPI Denominator(Count of Patients) |
|
6 |
KPI_NUM_CP |
KPI Numerator(Count of Patients) |
Table 10-14 shows the Encounter KPI Fact calculated measures.
Table 10-14 Encounter KPI Fact Calculated Measures
| Sr. No | Name | Description | Expression |
|---|---|---|---|
|
1 |
KPI_CE |
KPI Denominator / KPI Numerator (Encounters) |
( KPI_NUM_CE/ KPI_DEN_CE) |
|
2 |
KPI_CP |
KPI Denominator / KPI Numerator (Patients) |
(KPI_NUM_CP/KPI_DEN_CP) |
|
3 |
KPI_DEN_CE_LY |
KPI Denominator(Encounters) Last Year |
LAG(ENC_KPI.KPI_DEN_CE, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
4 |
KPI_DEN_CP_LY |
KPI Denominator(Patients) Last Year |
LAG(ENC_KPI.KPI_DEN_CP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
5 |
KPI_NUM_CE_LY |
KPI Numerator(Encounters) Last Year |
LAG(ENC_KPI.KPI_NUM_CE, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
6 |
KPI_NUM_CP_LY |
KPI Numerator(Patients) Last Year |
LAG(ENC_KPI.KPI_NUM_CP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
7 |
KPI_CE_LY |
KPI (Encounters) last year |
ENC_KPI.KPI_NUM_CE_LY / ENC_KPI.KPI_DEN_CE_LY |
|
8 |
KPI_CP_LY |
KPI (Patients) last year |
ENC_KPI.KPI_NUM_CP_LY / ENC_KPI.KPI_DEN_CP_LY |
|
9 |
KPI_CE_LY_PCT_CHG |
KPI(Encounters) percentage change from last year |
((ENC_KPI.KPI_CE - ENC_KPI.KPI_CE_LY) / ENC_KPI.KPI_CE_LY) * 100 |
|
10 |
KPI_CP_LY_PCT_CHG |
KPI(Patients) percentage change from last year |
((ENC_KPI.KPI_CP - ENC_KPI.KPI_CP_LY) / ENC_KPI.KPI_CP_LY) * 100 |
|
11 |
KPI_DEN_CE_LP |
KPI Denominator(Encounters) last period |
LAG(ENC_KPI.KPI_DEN_CE, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
12 |
KPI_DEN_CP_LP |
KPI Denominator(Patients) last period |
LAG(ENC_KPI.KPI_DEN_CP, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
13 |
KPI_CE_LP |
KPI (Encounters) last period |
ENC_KPI.KPI_NUM_CE_LP / ENC_KPI.KPI_DEN_CE_LP |
Observation is used to store the summary of the observation KPIs (from the observation fact table).
Physical Name: DWD_KPI_OBSV_FCT
Figure 10-3 shows the Observation KPI fact organization.
Figure 10-3 Observation KPI Fact Organization
Table 10-15 shows the Observation KPI Fact stored measures.
Table 10-15 Observation KPI Fact Stored Measures
| Sr. No | Name | Description |
|---|---|---|
|
1 |
KPI_DEN_CO |
KPI Denominator |
|
2 |
KPI_NUM_CO |
KPI Numerator |
Table 10-16 shows the Observation KPI Fact calculated measures.
Table 10-16 Observation KPI Fact Calculated Measures
| Sr. No | Name | Description | Expression |
|---|---|---|---|
|
1 |
KPI_DEN_CO_LY |
KPI Denominator(Observations) Last Year |
LAG(OBSV_KPI.KPI_DEN_CO, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
2 |
KPI_NUM_CO_LY |
KPI Numerator(Observations) Last Year |
LAG(OBSV_KPI.KPI_NUM_CO, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR BY ANCESTOR AT LEVEL TIME_ENC.HTCLNDR.YR POSITION FROM BEGINNING) |
|
3 |
KPI_CO_LY |
KPI (Observations) last year |
OBSV_KPI.KPI_NUM_CO_LY / OBSV_KPI.KPI_DEN_CO_LY |
|
4 |
KPI_CO_LY_PCT_CHG |
KPI(Observations) percentage change from last year |
((OBSV_KPI.KPI_CO - OBSV_KPI.KPI_CO_LY) / OBSV_KPI.KPI_CO_LY) * 100 |
|
5 |
KPI_DEN_CO_LP |
KPI Denominator(Observations) Last Period |
LAG(OBSV_KPI.KPI_DEN_CO, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
6 |
KPI_NUM_CO_LP |
KPI Numerator(Observations) Last Period |
LAG(OBSV_KPI.KPI_NUM_CO, 1) OVER HIERARCHY (TIME_ENC.HTCLNDR) |
|
7 |
KPI_CO_LP |
KPI (Observations) last period |
OBSV_KPI.KPI_NUM_CO_LP / OBSV_KPI.KPI_DEN_CO_LP |
|
8 |
KPI_CO_LP_PCT_CHG |
KPI(Observations) percentage change from last period |
((OBSV_KPI.KPI_CO - OBSV_KPI.KPI_CO_LP) / OBSV_KPI.KPI_CO_LP) * 100 |
|
9 |
KPI_CO_FAC_RNK |
Ranking of KPI(Observations) within facility dimension |
RANK() OVER HIERARCHY (FAC.HFAC ORDER BY OBSV_KPI.KPI_CO DESC NULLS LAST WITHIN PARENT) |
|
10 |
KPI_CO_ORG_RNK |
Ranking of KPI(Observations) within organization dimension |
RANK() OVER HIERARCHY (ORG.HORG ORDER BY OBSV_KPI.KPI_CO DESC NULLS LAST WITHIN PARENT) |
|
11 |
KPI_MEAS |
Represent the correct calculation needed for each of the KPIs |
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
