In Oracle Utilities Data Model, reference and lookup tables store master, reference, and dimensional data; and the base, derived, and aggregate tables store transaction and fact data at different granularities.
The base tables store the transaction data at the lowest level of granularity, while the derived and aggregate tables store consolidated and summary transaction data.
Two types of Extract, Transform, and Load (ETL) operations populate the tables with data. The source-ETL operations populate the reference, lookup, and base tables with data from the source On-Line Transaction Processing (OTLP) applications. Additional Intra-ETL operations populate the derived and aggregate tables with the data in the base, reference, and lookup tables. While the source ETL operations are not a part of Oracle Utilities Data Model, the Intra-ETL operations are.
There are two categories of Intra-ETL operations (scripts):
Derived Population: A database package containing scripts that populate the derived tables based on the content of the base, reference, and lookup tables.
Aggregate Population: A database package containing scripts to refresh the Oracle Utilities Data Model aggregate tables, mostly Materialized Views, based on the content of the derived tables and some reference tables.
Derived tables are implemented using Oracle tables. Some of the Aggregate tables Oracle tables and others are implemented using Materialized Views.
Note:
Changes to intra-ETL cannot be supported. But it is expected that if the business needs require a change in the business logic of the intra-ETLs some customer adaptations could be necessary even if they are not be supported.
Shows the PL/SQL mapping packages to populate the derived tables.
Populate target table DWD_ACCT_ARRER_MO. For more information, see Account Arrears Month Drvd.
DWD_ACCT_ARRER_MO Package Source Table
DWB_INVC
DWB_INVC_PYMT_ASGN
DWR_ACCT_AGRMNT_RLTN
DWR_CLNDR_MO
DWR_CUST
DWR_CUST_ACCT_ASGN
DWR_SRVC_LOC
DWR_USG_PNT
Table 7-1 DWD_ACCT_ARRER_MO Package Business Rules
| Column | Description |
|---|---|
|
CURR_BAL_AMT |
Account Balance Before Payment |
|
PAYOFF_BAL_AMT |
Principal plus interest. |
|
AMT_0_TO_30_DAYS |
Amount 0 to 30 Days |
|
AMT_31_TO_60_DAYS |
Amount 31 to 60 Days |
|
AMT_61_TO_90_DAYS |
Amount 61 to 90 Days |
|
AMT_91_TO_120_DAYS |
Amount 91 to 120 Days |
|
AMT_121_TO_150_DAYS |
Amount 121 to 150 Days |
|
AMT_151_TO_180_DAYS |
Amount 151 to 180 Days |
|
AMT_181_PLUS_DAYS |
Amount 181 Plus Days |
Populate target table DWD_ACCT_BAL_MO. For more information, see Account Balance Month Drvd.
DWD_ACCT_BAL_MO Package Source Tables
DWB_ACCT_BAL_HIST
DWB_ACCT_BAL_IMPT
DWR_CUST_ACCT_ASGN
DWR_ACCT
DWR_PROD_OFRNG
Table 7-2 DWD_ACCT_BAL_MO Lookup Values
| Table | Column | Operator | Value |
|---|---|---|---|
|
DWB_ACCT_BAL_IMPT |
ACCT_BAL_TYP_CD |
LIKE |
%LYTY%' |
|
DWB_ACCT_BAL_IMPT |
ACCT_BAL_TYP_CD |
LIKE |
%LYTY BONUS% |
Loyalty balance should normally be stored in the specific subject area, to allow the LOYALTY derived to work. Nothing prevents storing loyalty account balance type in ACCOUNT BALANCE when the Loyalty Account and the Account are the same. But it will not be taken into account by the other derived.
Populate target table DWD_ACCT_DEBT_DAY. For more information, see Account Debt Day Drvd.
DWD_ACCT_DEBT_DAY Package Source Tables
DWB_ACCT_DEBT
DWB_COST
DWB_EVT_PRTY_INTRACN
DWB_INVC
DWB_INVC_PYMT_ASGN
DWB_INVC_ADJ
DWB_ACCT_PYMT
DWB_ACCT_BAL_HIST
DWB_ACCT_BAL_IMPT
DWR_ACCT
DWR_ADDR_LOC
DWR_CUST
DWR_CUST_ACCT_ASGN
DWB_INVC_PYMT_ASGN
DWB_ACCT_DEBT
DWB_EVT_PRTY_INTRACN
DWB_COST
DWD_ACCT_DEBT_DAY Package Business Rules
Table 7-3 DWD_ACCT_DEBT_DAY Package Business Rules
| Column | Description |
|---|---|
|
MIN_INVC_DEBT_AGE |
Minimum invoice aging debt (Date - lowest DUE_DATE) in days. |
|
DEBT_CNT |
Calucation: Σ(Recovered Amount) Grouped By Account / Customer / Organization Business Unit / Collection Agency (via Account Payment) / Debt Aging Band (via Account Credit Limit) for a day derived from Balance Date |
|
RCV_AMT |
No value |
|
ADJ_AMT |
Calculation: Σ(Adjustment Amount) Grouped By Account / Customer / Organization Business Unit / Collection Agency (via Account Payment) / Debt Aging Band (via Account Credit Limit) for a day derived from Balance Date |
|
DEBT_AMT |
No value |
|
PNLTY_AMT |
No value |
|
WRTOFF_AMT |
Calculation: Σ(Writeoff Amount) Grouped By Account / Customer / Organization Business Unit / Collection Agency (via Account Payment) / Debt Aging Band (via Account Credit Limit) for a day derived from Balance Date |
|
AVG_DEBT_AGE |
No value |
|
AVG_INVC_DEBT_AGE |
Average of invoice aging (date minus due date). If only one invoice, this should be equal to debt age. |
|
CLCTR_CMISN_AMT |
Amount paid to the collection agency or to the employee in charge of collection. |
|
CURR_INVC_AMT |
Latest available invoice amount. |
|
CUST3MO_CNT |
No value |
|
CUST_CNT_3MO |
No value |
|
DEBT_AGE |
Age of debt of this account in days. |
|
INVC_IN_DEBT_CNT |
Number of invoices in debt (not yet paid and date>DUE_DATE, independently of extended due date). |
|
NB_AGNT_CMNTS |
Count the total number of comments agents wrote in the CRM system. |
|
NB_OF_AGRMNT_FAILED |
Number of agreed payment extension that was not fulfilled by customer in (extended) due date. |
|
NB_OF_AGRMNT_SUCCESS |
Number of agreed payment extension that was fulfilled by customer in (extended) due date. |
|
NB_OF_CNTCT |
Total number of (successful) contacts (any direction, any mean) with the customer. |
|
NB_OF_EMP_INVLVD |
Total number of employee from the CSP that have been directly involved with this debt. It helps estimating the cost and also the efficiency of debt tracking. |
|
NEW_CUST_CNT |
Similar to 3 Month old customer count but for 1 month old customer. |
|
OUTSTNDNG_DRTN |
Total unpaid invoice aging in days (over all unpaid invoices to date). |
|
PNDNG_COLLCTN_CNT |
Total number of invoices within a collection process. |
|
PRMS_PYMT_CNT |
Number of times the customer promised to pay (or an agreement has been put in place). This is a custom field. It is not filled by default in Oracle Utilities Data Model. |
|
PYMT_COLCTD_CNT |
No value |
|
TOT_BILLUNIT_CNT |
This is at the moment one to one with the number of invoices. Ignore this field. |
|
TOT_CNTCT_DRTN |
Total duration of the contacts (any direction, any mean) with the customer. Letter and emails cannot be considered as having a contact duration. |
|
TOT_DEBT_AGE |
Sum of all unpaid invoices (date minus due date). If only one invoice, this should be equal to debt age. |
|
TOT_LENGTH_AGNT_CMNTS |
No value |
|
TOT_WORK_DRTN |
No value |
|
WVNG_CNT |
No value |
|
MAX_INVC_AMT_IN_DEBT |
For a given invoice, maximum due amount available. |
|
MAX_INVC_DEBT_AGE |
Maximum invoice aging debt (Date - lowest DUE_DATE) in days. |
|
MIN_INVC_AMT_IN_DEBT |
For a given invoice, minimum due amount available. |
|
TOT_DSPT_AMT |
Original amount deposit available (if any). |
|
TOT_FRAUD_COST |
Sum of all cost due to fraud (specific debt case). |
|
TOT_LEGAL_PRCS_COST |
No value |
|
TOT_PYMT_COLCTD_AMT |
No value |
|
TOT_TRNSFRD_AMT |
Amount transferred to this account by another (as payment) since the beginning of the debt. |
|
TOT_WVNG_AMT |
Total amount waived to the customer (not written-off). |
Table 7-4 DWD_ACCT_DEBT_DAY Lookup Values
| Table | Column | Operator | Value |
|---|---|---|---|
|
DWB_INVC_ADJ |
INVC_ADJ_RSN_CD |
= |
'3000' |
|
DWB_INVC |
FULL_PAY_RCVD_IND |
= |
Y' |
|
DWB_INVC |
FULL_PAY_RCVD_IND |
<> |
Y' |
|
DWB_INVC |
INVC_STAT_CD |
NOT LIKE |
5%' |
|
DWB_ACCT_PYMT |
PYMT_RSLT_CD |
= |
SUCCESS' |
|
DWB_ACCT_PYMT |
PYMT_MTHD_TYP_CD |
= |
'2' |
|
DWB_COST |
COST_SUBTYP_CD |
= |
3800' |
|
DWB_ACCT_DEBT |
LEGAL_IND |
IS |
NULL |
|
DWB_EVT_PRTY_INTRACN |
INTRACN_RSN_CD |
LIKE |
LIKE '6%' |
|
DWB_EVT_PRTY_INTRACN |
INTRACN_EVT_RMRK |
IS |
NOT NULL |
Populate target table DWD_ACCT_PYMT_MTHD_STAT_HIST. For more information, see Account Payment Method Status Hist Drvd.
DWD_ACCT_PMT_MTD_STAT_HST Package Source Tables
DWB_ACCT_CRDT_LMT
DWB_ACCT_PYMT_MTHD_STAT
DWR_ACCT_PREF_PYMT_MTHDDWR_ACCT
DWR_CLNDR_MO
DWR_CUST
DWR_CUST_ACCT_ASGN
Populate target table DWD_ACCT_PYMT_DAY. For more information, see Account Payment Day Drvd.
DWD_ACCT_PMT_DAY Package Source Tables
DWB_ACCT_PYMT
DWB_INVC
DWB_INVC_PYMT_ASGN
DWR_ACCT
Table 7-5 DWD_ACCT_PMT_DAY Business Rules
| Column | Description | Calculation |
|---|---|---|
|
PYMT_CNT |
Count of payment transactions. |
No value |
|
PYMT_AMT |
Total payment amount. |
Σ(Payment Amount) Grouped By Customer / Account / Payment Method Type / Employee / Collection Agency / Bank Direct Debit Channel (via Account Preferred Payment Method) for a day derived from Payment Date |
|
TOT_RFND_CNT |
Count of refund transactions |
No value |
|
TOT_RFND_AMT |
Total amount of money refunded to this account on this day. |
No value |
|
PYMT_SUCC_CNT |
Count of payment transactions that succeeded. |
No value |
|
TOT_SUCC_PYMT_CNT |
No value |
No value |
|
TOT_TRNSFR_CNT |
Count of transfer transactions. |
No value |
|
TOT_TRNSFR_AMT |
Amount of money transferred to this account. |
No value |
|
MAX_NB_DAYS_TO_DUE_DT |
The furthest due date associated with all payments of this type for this account. It is in the past in most cases. |
No value |
Populate target table DWD_ACCT_STAT_MO. For more information, see Account Status Month Drvd.
DWD_ACCT_STAT_MO Package Source Tables
DWB_ACCT_PYMT
DWB_INVC
DWB_INVC_PYMT_ASGN
DWR_ACCT
Table 7-6 PKG_DWD_ACCT_STAT_MO Business Rules
| Column | Description | Calculation |
|---|---|---|
|
ACTVTNS_CNT |
1 if it is a new activation account. |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
DISCNCTNS_CNT |
1 if it is a disconnected account. |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
INVL_DEACTV_CNT |
Number of involuntary deactivation of service |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
INVL_SUSPND_CNT |
Number of involuntary suspensions of service |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
REACTV_FROM_INVL_SUSPND_CNT |
Number of reactivations from involuntary suspensions of service |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
DEACTV_CNT |
Number of total deactivations of service |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
RECNCT_CNT |
Number of Reconnects |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
SSPNSN_CNT |
Number of Suspensions |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
SUSPND_CNT |
Number of total suspensions of service |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
REACTV_FROM_SUSPND_CNT |
Number of total reactivations from suspensions of service |
Number of instance with Status Code = corresponding status such as activation Grouped By Account / Organization Business Unit / Service Location (via Usage Point) / Customer Segment (via Customer) / Loyalty Program for a day derived from Effective From/To Date |
|
TOT_RFND |
Cash Refund Amount |
No value |
Populate target table DWD_DR_PROG_LD_RDCTN_RGN_DAY. For more information, see DR Program Load Reduction By Region Day Drvd.
PKG_DR_PROG_LD_RDCTN_RGN_DAY Package Source Tables
DWD_MTR_RDNG_DAY
DWR_DAY
DWR_DEMAND_RESPN_PROG
DWR_USG_PNT_GRP
DWR_USG_PNT_GRP_ASGN
DWR_USG_PNT_GRP_DR_PROG_ASGN
DWV_REGIONAL_ZONES_DIM
Table 7-7 PKG_DR_PROG_LD_RDCTN_RGN_DAY Business Rules
| Column | Description | Calculation |
|---|---|---|
|
AVG_RDCTN_AMT |
Derived table on demand response program resulted load reduction by region by day. |
"Meter Reading Day Drvd (via common dimension Usage Point) / Total kWh - baseline kWh" Grouped By Demand Response Program / Geography Region / Geography Sub Region (through Usage Point Location) / Day |
Populate target table DWD_END_DVC_EVT_CUST_DAY. For more information, see End Device Event By Customer Day Drvd.
PKG_DWD_END_DVC_EVT_CUST_DAY Package Source Tables
DWB_END_DVC_EVT
DWR_ACCT
DWR_CLNDR_MO
DWR_CUST
DWR_CUST_ACCT_ASGN
DWR_DAY
DWR_USG_PNT
Table 7-8 PKG_DWD_END_DVC_EVT_CUST_DAY Package Business Rules
| Column | Calculations |
|---|---|
|
RVS_MTR_CNT |
# of instances Where EndDeviceEventType is type of reversedMeter Grouped By Day / Calendar Month / Customer / Usage Point |
|
MTR_TMPR_EVT_CNT |
# of instances Where EndDeviceEventType is type of meterTamper (for example = 3.12.43.257 for <ElectricMeter>.<Security>.<Event>.<TamperDetected>) Grouped By Day / Calendar Month / Customer / Usage Point |
|
STPD_MTR_CNT |
# of instances Where EndDeviceEventType is type of stoppedMeter Grouped By Day / Calendar Month / Customer / Usage Point |
Populate target table DWD_END_DVC_EVT_DVC_DAY. For more information, see End Device Event By Device Day Drvd.
PKG_DWD_END_DVC_EVT_DVC_DAY Package Source Tables
DWB_END_DVC_EVT
DWR_CLNDR_MO
DWR_CUST_ACCT_ASGN
DWR_DAY
DWR_MNFCTR
DWR_MTR
DWR_PROD_ASST_MDL
DWR_USG_PNT
Table 7-9 PKG_DWD_END_DVC_EVT_DVC_DAY Business Rules
| Column | Description | Calculation |
|---|---|---|
|
RVS_MTR_CNT |
Reversed Meter Count |
# of instances Where EndDeviceEventType is type of reversedMeter Grouped By Day / Calendar Month / Meter / Product Asset Model / Manufacturer |
|
MTR_TMPR_EVT_CNT |
Meter Tamper Event Count |
# of instances Where EndDeviceEventType is type of meterTamper (e.g. = 3.12.43.257 for <ElectricMeter>.<Security>.<Event>.<TamperDetected>) Grouped By Day / Calendar Month / Meter / Product Asset Model / Manufacturer |
|
STPD_MTR_CNT |
Stopped Meter Count |
# of instances Where EndDeviceEventType is type of stoppedMeter Grouped By Day / Calendar Month / Meter / Product Asset Model / Manufacturer |
Table 7-10 DWD_END_DVC_EVT_DVC_DAY Lookup Values
| Table | Column | Operator | Value |
|---|---|---|---|
|
DWB_END_DVC_EVT |
TYP |
= |
Tamper Event' |
|
DWB_END_DVC_EVT |
TYP |
= |
Reversed Meter Event' |
|
DWB_END_DVC_EVT |
TYP |
= |
'Stopped Meter Event' |
Populate target table DWD_MTR_RDNG_DAY. For more information, see Meter Reading Day Drvd.
PKG_DWD_MTR_RDNG_DAY Package Source Tables
DWB_FNL_RDNG
DWB_MTR_RDNG
DWR_ACCT
DWR_CLNDR_MO
DWR_CUST
DWR_CUST_ACCT_ASGN
DWR_DAY
DWR_MTR
DWR_RDNG_TYP
DWR_SRVC_LOC
DWR_USG_PNT
DWV_OPERATIONAL_DIM
Table 7-11 PKG_DWD_MTR_RDNG_DAY Business Rules
| Column | Description | Calculation |
|---|---|---|
|
TOT_KWH |
Total kWh |
Value Where ReadingType is type of daily kWh (for example = 0.0.0.1.1.1.12.0.0.0.0.0.0.0.0.3.72.0 for bulkQuantity forward electricitySecondaryMetered energy (kWh) Grouped by Meter / Usage Point / Customer / Account / Service Location / Day |
|
MAX_KW |
Max kW |
Value Where ReadingType is type of daily kW (for example = 0.8.4.6.1.1.8.0.0.0.0.0.0.0.0.3.38.0 for maximum sixtyMinute indicating forward electricitySecondaryMetered demand (kW) Grouped by Meter / Usage Point / Customer / Account / Service Location / Day |
Table 7-12 PKG_DWD_MTR_RDNG_DAY Lookup Values
| Table | Column | Operator | Value |
|---|---|---|---|
|
DWR_RDNG_TYP |
RDNG_TYP_CD |
= |
0.8.4.6.1.1.8.0.0.0.0.0.0.0.0.3.38.0' |
|
DWR_RDNG_TYP |
RDNG_TYP_CD |
= |
'0.0.0.1.1.1.12.0.0.0.0.0.0.0.0.3.72.0' |
Populate target table DWD_MTR_RDNG_HR. For more information, see Meter Reading Hour Drvd.
PKG_DWD_MTR_RDNG_HR Package Source Tables
DWB_FNL_RDNG
DWB_MTR_RDNG
DWL_TIME_OF_USE
DWR_ACCT
DWR_CLNDR_MO
DWR_CUST
DWR_CUST_ACCT_ASGN
DWR_DAY
DWR_HR
DWR_HR_TIME_OF_USE_ASGN
DWR_MTR
DWR_RDNG_TYP
DWR_SRVC_LOC
DWR_USG_PNT
DWV_OPERATIONAL_DIM
Table 7-13 DWD_MTR_RDNG_HR Business Rules
| Column | Description | Calculation |
|---|---|---|
|
TOT_KWH |
Total kWh |
Value Where ReadingType is type of interval kWh (e.g. = 0.0.7.4.1.1.12.0.0.0.0.0.0.0.0.3.72.0 for sixtyMinute deltaData forward electricitySecondaryMetered energy (kWh) Grouped By Hour of a Day in a particular Month (and Usage Point / Meter / Customer / Account / Time Of Use) |
|
MAX_KW |
Max kW |
Value Where ReadingType is type of interval kW (for example = 0.8.7.6.1.1.8.0.0.0.0.0.0.0.0.3.38.0 for maximum sixtyMinute indicating forward electricitySecondaryMetered demand (kW) Grouped By Hour of a Day in a particular Month (and Usage Point /Meter / Customer / Account / Time Of Use) |
Table 7-14 DWD_MTR_RDNG_HR Lookup Values
| Table | Columns | Operator | Value |
|---|---|---|---|
|
DWR_RDNG_TYP |
RDNG_TYP_CD |
= |
0.8.4.6.1.1.8.0.0.0.0.0.0.0.0.3.38.0' |
|
DWR_RDNG_TYP |
RDNG_TYP_CD |
= |
'0.0.0.1.1.1.12.0.0.0.0.0.0.0.0.3.72.0' |
Populate target table DWD_OUTG_DAY. For more information, see Outage By Day Drvd.
PKG_DWD_OUTG_DAY Package Source Tables
DWB_OUTG_REC
DWR_ADDR_LOC
DWR_DAY
DWR_ORG_BSNS_UNIT
DWR_OUT_USG_PNT_ASGN
DWR_OUTG_RPT
DWV_REGIONAL_ZONES_DIM
Table 7-15 PKG_DWD_OUTG_DAY Package Business Rules
| Column | Description | Calculation |
|---|---|---|
|
OUTG_DRTN |
Outage Duration |
Outage Report.Outage Duration Grouped By Outage Report (via Outage Record) / Organization Business Unit (via UsagePoint, Geo Region and Sub Region) / Day |
|
CUST_MNTS_LOST |
Customer Minutes Lost |
Outage Report.Total Cml Grouped By Outage Report (via Outage Record) (via UsagePoint, Geo Region and Sub Region) / Organization Business Unit / Day |
|
OUTG_CNT |
Outage Count |
# of outage instance |
|
CUST_OUT_CNT |
Customer Out Count |
Outage Report.Customer Count Grouped By Outage Report (via Outage Record) / Organization Business Unit (via UsagePoint, Geo Region and Sub Region) / Day |
Populate target table DWD_OUTG_USG_PNT. For more information, see Outage By Usage Point Drvd.
PKG_DWD_OUTG_USG_PNT Package Source Tables
DWB_OUTG_REC
DWR_ACCT
DWR_CUST
DWR_CUST_ACCT_ASGN
DWR_DAY
DWR_MTR
DWR_OPERATIONAL_DIM
DWR_ORG_BSNS_UNIT
DWR_OUTG_RPT
DWR_OUTG_USG_PNT_ASGN
DWR_USG_PNT
DWR_ZN
DWV_GEOGRAPHY_ZONES_DIM
Table 7-16 DWD_END_DVC_EVT_DVC_DAY Business Rules
| Column | Description | Calculation |
|---|---|---|
|
OUTG_DRTN |
Outage Duration |
Outage Report.Outage Duration Grouped By Outage Report (via Outage Record) / Organization Business Unit / Day |
|
OUTG_CNT |
Outage Count |
# of outage instance |
|
CUST_OUT_CNT |
Customer Out Count |
Outage Report.Customer Count Grouped By Outage Report (via Outage Record) / Organization Business Unit / Day |
Populate target table DWD_RLBLTY_IND_CITY_MO. For more information, see Reliability Indices By City Month Drvd.
PKG_DWD_RLBLTY_IND_CITY_MO Package Source Tables
DWB_GEOGRAPHY_ZONES_DIM
DWB_OUTG_REC
DWD_OUTG_USG_PNT
DWR_CLNDR_MO
DWR_CLNDR_YR
DWR_DAY
DWR_USG_PNT
DWV_REGIONAL_ZONES_DIM
Table 7-17 DWD_RLBLTY_IND_CITY_MO Business Rules
| Column | Description | Calculation |
|---|---|---|
|
NBR_CUST_IMPT |
Number Customer Impacted |
sum(oup.outg_cnt) group by geo_city_key, substr(day_key,1,6); |
|
NBR_CUST_SRV |
Number Customer Served |
count(usg_pnt_key) group by geo_city_key; |
|
NBR_EVT |
Number Events |
count(oup.outg_rec_key) group by geo_city_key, substr(day_key,1,6); |
|
NBR_TRBL_CALLS |
Number Trouble Calls |
No value |
|
SAIDI |
System Average Interruption Duration Index (SAIDI) |
SAIDI = Σ(ri * Ni ) / Ns Where, ri = Restoration time, minutes Ni = Total number of customer interrupted Ns = Total number of customer served |
|
CAIDI |
Customer Average Interruption Duration Index (CAIDI) |
CAIDI = Σ(ri * Ni ) / Σ( Ni ) Where, ri = Restoration time, minutes Ni = Total number of customer interrupted Note: CAIDI = SAIDI / SAIFI |
|
SAIFI |
System Average Interruption Frequency Index (SAIFI) |
SAIFI = Σ(Ni ) / Ns Where, Ni = Total number of customer interrupted Ns = Total number of customer served |
|
CAIFI |
Customer Average Interruption Frequency Index (CAIFI) |
CAIFI = Σ(No ) / Ni Where, No = Number of interruptions Ni = Total number of customer interrupted |
|
MAIFI |
Momentary Average Interruption Frequency Index (MAIFI) |
MAIFI = Σ(IDi*Ni ) / Ns Where, IDi = Number if interrupting device operations Ni = Total number of customer interrupted Ns = Total number of customer served |
|
ASAI |
Average Service Availability Index (ASAI) |
ASAI = [1 - (Σ(ri * Ni ) / (NT * T))] * 100 Where, T = Time period under study, hours. ri = Restoration time, hours Ni = Total number of customers interrupted NT = Total number of customers served The ASAIusually calculated on either a monthly basis (730 hours) or a yearly basis (8,760 hours) For yearly value, = [1-(Σ(SAIDImonth)/8760)]*100 |
|
CMI |
Customer Minutes of Interruption (CMI) |
No value |
Populate target table DWD_RLBLTY_IND_FEDR_MO. For more information, see Reliability Indices By Feeder Month Drvd
PKG_DWD_RLBLTY_IND_FEDR_MO Package Source Tables
DWB_OUTG_REC
DWD_OUTG_USG_PNT
DWR_CLNDR_MO
DWR_CLNDR_YR
DWR_DAY
DWR_USG_PNT
DWV_OPERATIONAL_DIM
Table 7-18 DWD_RLBLTY_IND_CITY_MO Business Rules
| Column | Description | Calculation |
|---|---|---|
|
NBR_CUST_IMPT |
Number Customer Impacted |
sum(oup.outg_cnt) group by fedr_key, substr(day_key,1,6) |
|
NBR_CUST_SRV |
Number Customer Served |
count(usg_pnt_key) group by fedr_key |
|
NBR_EVT |
Number Events |
count(oup.outg_rec_key) group by fedr_key, substr(day_key,1,6) |
|
NBR_TRBL_CALLS |
Number Trouble Calls |
No value |
|
SAIDI |
System Average Interruption Duration Index (SAIDI) |
SAIDI = Σ(ri * Ni ) / Ns Where, ri = Restoration time, minutes Ni = Total number of customer interrupted Ns = Total number of customer served |
|
CAIDI |
Customer Average Interruption Duration Index (CAIDI) |
CAIDI = Σ(ri * Ni ) / Σ( Ni ) Where, ri = Restoration time, minutes Ni = Total number of customer interrupted Note: CAIDI = SAIDI / SAIFI |
|
SAIFI |
System Average Interruption Frequency Index (SAIFI) |
SAIFI = Σ(Ni ) / Ns Where, Ni = Total number of customer interrupted Ns = Total number of customer served |
|
CAIFI |
Customer Average Interruption Frequency Index (CAIFI) |
CAIFI = Σ(No ) / Ni Where, No = Number of interruptions Ni = Total number of customer interrupted |
|
MAIFI |
Momentary Average Interruption Frequency Index (MAIFI) |
MAIFI = Σ(IDi*Ni ) / Ns Where, IDi = Number if interrupting device operations Ni = Total number of customer interrupted Ns = Total number of customer served |
|
ASAI |
Average Service Availability Index (ASAI) |
ASAI = [1 - (Σ(ri * Ni ) / (NT * T))] * 100 Where, T = Time period under study, hours. ri = Restoration time, hours Ni = Total number of customers interrupted NT = Total number of customers served The ASAIusually calculated on either a monthly basis (730 hours) or a yearly basis (8,760 hours) For yearly value, = [1-(Σ(SAIDImonth)/8760)]*100 |
|
CMI |
Customer Minutes of Interruption (CMI) |
No value |