7 Oracle Utilities Data Model Intra-ETL

This chapter includes the following sections:

About Oracle Utilities Data Model Intra-ETL

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.

Intra-ETL PL/SQL Packages Business Rules and Source Tables

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_".

PKG_DWD_ACCT_ARRER_MO Package

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

PKG_DWD_ACCT_BAL_MO Package

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.

PKG_DWD_ACCT_DEBT_DAY

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

PKG_DWD_ACCT_PMT_MTD_STAT_HST

Populate target table DWD_ACCT_PYMT_MTHD_STAT_HIST. For more information, see Account Payment Method Status Hist Drvd.

Table 7-9 DWD_ACCT_PMT_MTD_STAT_HST Package Source Tables

Source Table Name

DWB_ACCT_CRDT_LMT

DWB_ACCT_PYMT_MTHD_STAT

DWR_ACCT

DWR_ACCT_PREF_PYMT_MTHD

DWR_CLNDR_MO

DWR_CUST

DWR_CUST_ACCT_ASGN

PKG_DWD_ACCT_PYMT_DAY

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.

  

PKG_DWD_ACCT_STAT_MO

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

  

PKG_DR_PROG_LD_RDCTN_RGN_DAY

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

PKG_DWD_END_DVC_EVT_CUST_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

PKG_DWD_END_DVC_EVT_DVC_DAY

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

Table 7-20 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'

PKG_DWD_MTR_RDNG_DAY

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

Table 7-23 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'

PKG_DWD_MTR_RDNG_HR

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)

Table 7-26 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'

PKG_DWD_OUTG_DAY

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

PKG_DWD_OUTG_USG_PNT

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

PKG_DWD_RLBLTY_IND_CITY_MO

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)

  

PKG_DWD_RLBLTY_IND_FEDR_MO

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)