This chapter includes the following sections:
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. The naming convention by default is the physical name of the target table plus the prefix, "PKG_
".
Populate target table DWD_ACCT_ARRER_MO
. For more information, see Account Arrears Month Drvd.
Table 7-2 DWD_ACCT_ARRER_MO Package Source Tables
Source Table Name |
---|
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-3 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.
Table 7-4 DWD_ACCT_BAL_MO Package Source Tables
Source Table Name |
---|
DWB_ACCT_BAL_HIST |
DWB_ACCT_BAL_IMPT |
DWR_CUST_ACCT_ASGN |
DWR_ACCT |
DWR_PROD_OFRNG |
Table 7-5 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.
Table 7-6 DWD_ACCT_DEBT_DAY Package Source Tables
Source Table Name |
---|
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 |
Table 7-7 DWD_ACCT_DEBT_DAY Package Business Rules
Column | Description | Calculation |
---|---|---|
MIN_INVC_DEBT_AGE |
Minimum invoice aging debt (Date - lowest DUE_DATE) in days. |
|
DEBT_CNT |
||
RCV_AMT |
Σ(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 |
|
ADJ_AMT |
Σ(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 |
||
PNLTY_AMT |
||
WRTOFF_AMT |
Σ(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 |
||
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 |
||
CUST_CNT_3MO |
||
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 |
||
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 |
||
TOT_WORK_DRTN |
||
WVNG_CNT |
||
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 |
||
TOT_PYMT_COLCTD_AMT |
||
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-8 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.
Populate target table DWD_ACCT_PYMT_DAY
. For more information, see Account Payment Day Drvd.
Table 7-10 DWD_ACCT_PMT_DAY Package Source Tables
Source Table Name |
---|
DWB_ACCT_PYMT |
DWB_INVC |
DWB_INVC_PYMT_ASGN |
DWR_ACCT |
Table 7-11 DWD_ACCT_PMT_DAY Business Rules
Column | Description | Calculation |
---|---|---|
PYMT_CNT |
Count of payment transactions. |
|
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 |
|
TOT_RFND_AMT |
Total amount of money refunded to this account on this day. |
|
PYMT_SUCC_CNT |
Count of payment transactions that succeeded. |
|
TOT_SUCC_PYMT_CNT |
||
TOT_TRNSFR_CNT |
Count of transfer transactions. |
|
TOT_TRNSFR_AMT |
Amount of money transferred to this account. |
|
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. |
Populate target table DWD_ACCT_STAT_MO
. For more information, see Account Status Month Drvd.
Table 7-12 DWD_ACCT_STAT_MO Package Source Tables
Source Table Name |
---|
DWB_ACCT_PYMT |
DWB_INVC |
DWB_INVC_PYMT_ASGN |
DWR_ACCT |
Table 7-13 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 |
Populate target table DWD_DR_PROG_LD_RDCTN_RGN_DAY
. For more information, see DR Program Load Reduction By Region Day Drvd.
Table 7-14 PKG_DR_PROG_LD_RDCTN_RGN_DAY Package Source Tables
Source Table Name |
---|
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-15 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.
Table 7-16 PKG_DWD_END_DVC_EVT_CUST_DAY Package Source Tables
Source Table Name |
---|
DWB_END_DVC_EVT |
DWR_ACCT |
DWR_CLNDR_MO |
DWR_CUST |
DWR_CUST_ACCT_ASGN |
DWR_DAY |
DWR_USG_PNT |
Table 7-17 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.
Table 7-18 PKG_DWD_END_DVC_EVT_DVC_DAY Package Source Tables
Source Table Name |
---|
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-19 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 |
Populate target table DWD_MTR_RDNG_DAY
. For more information, see Meter Reading Day Drvd.
Table 7-21 PKG_DWD_MTR_RDNG_DAY Package Source Tables
Source Table Name |
---|
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-22 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 |
Populate target table DWD_MTR_RDNG_HR
. For more information, see Meter Reading Hour Drvd.
Table 7-24 PKG_DWD_MTR_RDNG_HR Package Source Tables
Source Table Name |
---|
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-25 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) |
Populate target table DWD_OUTG_DAY
. For more information, see Outage By Day Drvd.
Table 7-27 PKG_DWD_OUTG_DAY Package Source Tables
Source Table Name |
---|
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-28 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.
Table 7-29 PKG_DWD_OUTG_USG_PNT Package Source Tables
Source Table Name |
---|
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-30 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.
Table 7-31 PKG_DWD_RLBLTY_IND_CITY_MO Package Source Tables
Source Table Name |
---|
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-32 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 |
|
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) |
Populate target table DWD_RLBLTY_IND_FEDR_MO
. For more information, see Reliability Indices By Feeder Month Drvd
Table 7-33 PKG_DWD_RLBLTY_IND_FEDR_MO Package Source Tables
Source Table Name |
---|
DWB_OUTG_REC |
DWD_OUTG_USG_PNT |
DWR_CLNDR_MO |
DWR_CLNDR_YR |
DWR_DAY |
DWR_USG_PNT |
DWV_OPERATIONAL_DIM |
Table 7-34 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 |
|
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) |