Facts
Important! As of the v2.7.0.1 release, all materialized views are removed from Oracle Utilities Analytics Warehouse.
This section lists all the Oracle Utilities Network Management System star schemas.
• Call
• Crew
• Job
• Outage
Abnormal Device Log
Captures details of devices that go into an abnormal state and end up with an alarm log in the source system.
Note: This fact is designed to cater to various KPI requirements about abnormal devices in the Switching dashboard in Oracle Utilities Analytics Warehouse.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_ABNORMAL_DEVICE_LOG |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | DDS_ALARM_LOG |
ODI Package Name | B1_PKG_CF_ABNORMAL_DEVICE_LOG |
ETL View Name | B1_F_ABNORMAL_DEVICE_LOG_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
ABNORMAL_DEVICE_ LOG_KEY | Abnormal Device Log Surrogate Key | Populated with the sequence from B1_ABNORMAL_DEVICE_LOG_SEQ.NEXTVAL. | |
Natural Keys | |||
SRC_ALARM_CLS | Alarm Message Class | DDS_ALARM_LOG.ALARM_CLS | |
SRC_ALARM_IDX | Alarm Message Index | DDS_ALARM_LOG.ALARM_IDX | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
ABNORMAL_DTTM | Abnormal Date/Time | DDS_ALARM_LOG. USER_TIME | |
ACK_DTTM | Acknowledgment Date/Time | DDS_ALARM_LOG. TIME_ACK | |
DEACT_DTTM | Deactivation Date/Time | DDS_ALARM_LOG. WHEN_DEACTIVATED | |
SYSTEM_TIME | System Date/Time | DDS_ALARM_LOG.SYSTEM_TIME | |
Foreign Keys | |||
ABNORMAL_DATE_KEY | Abnormal Date Key | DDS_ALARM_LOG.USER_TIME | Populated based on the Date part of ABNORMAL_DTTM. |
ABNORMAL_TIME_KEY | Abnormal Time Key | DDS_ALARM_LOG.USER_TIME | Populated based on the Time of ABNORMAL_DTTM. |
ACK_DATE_KEY | Acknowledgment Date Key | DDS_ALARM_LOG.TIME_ACK | Populated based on the Date part of ACK_DTTM. |
ACK_TIME_KEY | Acknowledgment Time Key | DDS_ALARM_LOG.TIME_ACK | Populated based on the Time part of ACK_DTTM. |
DEACT _DATE_KEY | Deactivation Date Key | DDS_ALARM_LOG. WHEN_DEACTIVATED | Populated based on the Date part of DEACT_DTTM. |
DEACT _TIME_KEY | Deactivation Time Key | DDS_ALARM_LOG. WHEN_DEACTIVATED | Populated based on the Time part of DEACT_DTTM. |
ALARM_LOG_KEY | Alarm Log Key | DDS_ALARM_LOG.ALARM_CLS DDS_ALARM_LOG.ALARM_IDX | Populated based on ALARM_CLS and ALARM_IDX from DDS_ALARM_LOG table. If any (either alarm class or index) of the values is 0, then map to the zeroth key. |
DEVICE_KEY | Device Dimension Surrogate Key | DDS_ALARM_LOG.DEVICE_CLS DDS_ALARM_LOG.DEVICE_IDX | Populated based on DEVICE_CLS and DEVICE_IDX from DDS_ALARM_LOG table. If any (either device class or index) of the values is 0, then map to the zeroth key. In case of non-electrical devices, devices will not be available in the Device dimension. Facts referring to these devices will be populated with 0 instead of -99. |
CTRL_ZONE_KEY | Control Zone Dimension Surrogate Key | DDS_ALARM_LOG.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | DDS_ALARM_LOG.NCG | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
ABNORMAL_DEVICE_LOG_UDD1_KEY | Abnormal Device Log User Defined Dimension 1 Surrogate Key | ||
ABNORMAL_DEVICE_LOG_UDD2_KEY | Abnormal Device Log User Defined Dimension 2 Surrogate Key | ||
SYSTEM_DATE_KEY | System Date Key | DDS_ALARM_LOG.SYSTEM_TIME | Populated based on the SYSTEM_DTTM part from the CD_DATE table. |
SYSTEM_TIME_KEY | System Time Key | DDS_ALARM_LOG.SYSTEM_TIME | Populated based on the SYSTEM_DTTM part from the CD_TIME table. |
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Call
Stores details about the incidents created in the source system and also captures details about the associated jobs. The data is stored for all restored and un-restored jobs.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_RST_CALL |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | INCIDENTS |
ODI Package Name | B1_PKG_CF_RST_CALL |
ETL View Name | B1_F_RECENT_CALL_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
RST_CALL_KEY | Restored Call Fact Surrogate Key | Populated with the sequence from SPL_RST_CALL_SEQ. | |
Natural Keys | |||
SRC_INCIDENT_ID | Source Incident ID | INCIDENTS.DECODE(NVL(ASSOCIATE_IDX, 0), 0,NU=MB,ASSOCIATE_IDX) | Populated with NUMB if the Associate Index value is 0. Else, Associated Index value is populated. |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
PRIORITY_IND | Priority Indicator | INCIDENTS.DECODE(SUBSTR(INCIDENTS.COMPLAINT, 7, 1 ), '1', '1', '2', '1'= ,'0') | Populate from the extracted nth character of the COMPLAINT column of INCIDENTS table, where ‘n’ is defined by the configuration parameter B1_PRTY_BIT. If the extracted value is 1 or 2, populate as 1, else populate as 0. |
RST_IND | Restored Indicator | JOBS.RESTORE_TIME | If RESTORE_TIME is populated in the JOBS table, then this field is populated as 1; else as 0. |
Foreign Keys | |||
EVENT_KEY | Event Key | JOBS.NUMB | Populated from CD_EVENT's surrogate key using NUMB column JOBS table. |
ACCT_KEY | Account Key | CES_CUSTOMERS_HISTORY.ACCOUNT_ NUMBER | Populated based on the account in the CD_ACCOUNT dimension. |
CALL_DATE_KEY | Call Date Key | INCIDENTS.INPUT_ TIME | Populated based on the date part in the CD_DATE dimension. |
CALL_TIME_KEY | Call Time Key | INCIDENTS.INPUT_ TIME | Populated based on the Time part in the CD_TIME dimension. |
CTRL_ZONE_KEY | Control Zone Key | INCIDENTS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
USER_KEY | User Key | CES_USER.USER_NAME | Populated with user name in the CD_USER table. |
CALL_INFO_KEY | Call Info Key | INCIDENTS.DECODE(NVL(ASSOCIATE_IDX, 0),0,NUMB,ASSOCIATE_IDX) | If the Associate Index value is 0, this field is populated with NUMB. Else, it is populated with Associated Index value. |
PREM_KEY | Premise Key | CES_CUSTOMERS_HISTORY.SERV_LOC_ID | Populated with the service location ID in the CD_PREM table. |
ADDR_KEY | Address Key | CES_CUSTOMERS_HISTORY.SERV_LOC_ID | Populated with the service location ID in the CD_PREM table. |
DEVICE_KEY | Device Key | INCIDENTS.H_IDX INCIDENTS.H_CLS | Populated based on H_IDX and H_CLS in the CD_DEVICE table. |
ACCT_TYPE_KEY | Account Type Key | CES_CUSTOMERS_HISTORY.ACCOUNT_TYPE | Populated with the account type. |
METER_KEY | Meter Key | CES_CUSTOMERS_HISTORY.METER_ID | Populated with the meter ID in the CD_METER table. |
NETWORK_PREMISE_ KEY | Network Premise Key | CES_CUSTOMERS_HISTORY.SERV_LOC_ID | Populated with the service location ID in the CD_NETWORK_PREM table. |
CTRL_ZONE_SEC_KEY | Control Zone Secondary Key | INCIDENTS.NCG | Populate from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension. |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMAN_STORMS. STORM_NAME STORMAN_STORMS. STORM_DATE STORMMAN_STORMS. ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMAN_STORMS. STORM_NAME STORMAN_STORMS. STORM_DATE STORMMAN_STORMS. ZONE_IDX | Populated based on the storm name, date and control zone. |
CALL_UDD1_KEY | User Defined Dimension 1 Key | ||
CALL_UDD2_KEY | User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
City Outage
A monthly snapshot fact that stores various measures (such as CMI, SAIDI, SAIFI, number of customers, number of interruptions, etc) at the city and major event type level.
This fact is unusual in the sense that its data is not populated directly from the source tables in the replication schema. It is a calculated fact whose data is derived from the data in the Customer restored facts.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CITY_OUTG |
Table Type | Fact |
Fact Type | Snapshot |
Driver Table | CD_CITY |
ODI Package Name | B1_PKG_CF_CITY_OUTG |
ETL View Name | n/a |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
CITY_OUTG_KEY | City Outage Snapshot Fact Key | Populated with the sequence from SPL_CITY_OUTG_SEQ. | |
Natural Keys | |||
SRC_CITY | City | CD_CITY.SRC_CITY | Loaded with city information from the City dimension that is active during the slice period. |
SRC_STATE | State | CD_CITY.SRC_STATE | Loaded with state information from the City dimension that is active during the slice period. |
SRC_COUNTRY | Country | CD_CITY.SRC_COUNTRY | Loaded with country information from the City dimension that is active during the slice period. |
SNAP_TYPE_CD | Snapshot Type Code | B1_DSI_CONFIG_VW.SNAP_TYPE_CD | Populated with snapshot type from DSI config view based on the configuration of the snapshot defined in the target entity table. |
MAJ_EVT_SNAP_TYPE_ CD | Major Event Snapshot Type Code | CD_MJR_EVT_SNAP_TYPE.SNAP_TYPE_CD | Populated with all combinations of control zone. |
Measures | |||
NUM_CUST_SERVED | Number of Customers Served | CES_CUSTOMERS_HISTORY.NUM_CUST_SERVED | Populated with the number of active customers served within the City during the snapshot period. |
NUM_SUST_INTRPT | Number of Sustained Interruptions | CF_CUST_RST_OUTG.NUM_SUST_INTRPT | Populated with the count of sustained outages during the snapshot period. An outage is considered sustained if the difference between the beginning and ending time of the outage (in minutes) is greater than the configured momentary threshold in minutes. |
NUM_MOM_INTRPT | Numb. Mom. Interruptions | CF_CUST_RST_OUTG.NUM_MOM_INTRPT | Populated with the count of momentary outages during the snapshot period. An outage is considered momentary if the difference between the beginning and ending time of the outage (in minutes) is less than or equal to the configured momentary threshold in minutes. |
CMI | CMI | CF_CUST_RST_OUTG. CMI | Populated with the sum of customer minutes interrupted (in minutes) for sustained outages. |
NUM_MULT_SUST_ INTRPT | Number of Multiple Sustained Interruptions | CF_CUST_RST_OUTG. NUM_MULT_SUST_ INTRPT | Populated with the count of sustained outages greater than the configured CEMI count. |
NUM_MULT_CUST_ INTRPT | Number of Multiple Customer Interruptions | CF_CUST_RST_OUTG. NUM_MULT_CUST_ INTRPT | Populated with the count of sustained and momentary outages greater than the configured CEMI count. |
SAIDI | SAIDI | CF_CUST_RST_OUTG. BEGIN_DTTM CF_CUST_RST_OUTG. RST_DTTM | Populated with the customer minutes interrupted by number of customers served. |
CAIDI | CAIDI | CF_CUST_RST_OUTG. CAIDI | Populated with the customer minutes interrupted divided by the number of customers interrupted. |
SAIFI | SAIFI | CF_CUST_RST_OUTG. SAIFI | Populated with the number of customer interruptions divided by the number of customers served. |
MAIFIE | MAIFIE | CF_CUST_RST_OUTG. MAIFIE | Populated with the momentary customer interruptions divided by the number of customers served. Note: This metric is for momentary events - if there were multiple reclose attempts to clear a fault, it only counts as a single event. |
NUM_CUST_INTRPT | Customers Interrupted | CF_CUST_RST_OUTG. NUM_CUST_INTRPT | Populated with the number of sustained customer interruptions. Note: If a customer has had multiple sustained interruptions, each one is counted. |
NUM_MOM_E_INTRPT | Number of Momentary Events Preceding a Sustained Interruption | CF_CUST_RST_OUTG. NUM_MOM_E_INTRPT | Populated with the number of momentary events during the period, excluding the events that immediately precede a sustained interruption. |
NUM_EVENT | Number of Events | CF_CUST_RST_OUTG. NUM_EVENT | Populated with the number of events during the period. It is the sum of number of sustained interruptions and number of momentary interruptions. |
NUM_CUST_SUST_ INTRPT | Number of Customers who experienced a Sustained Interruption | CF_CUST_RST_OUTG. NUM_CUST_SUST_ INTRPT | Populated with the number of customers who experienced a sustained interruption during the period. |
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
SNAPSHOT_DT | Snapshot Date | B1_JOB_EXEC.SLICE_END_DTTM | Consider one second less than the slice end date (<date>23:59:59). |
Foreign Keys | |||
SNAPSHOT_DATE_KEY | Snapshot Date Surrogate Key | B1_JOB_EXEC.SLICE_END_DTTM | Populated with the last date of current snapshot period (populates only the date part). |
CITY_KEY | City Surrogate Key | CD_CITY.CITY_KEY | Populated based on the customers active during the snapshot period. |
BEGIN_DATE_KEY | Snapshot Begin Date Surrogate Key | B1_JOB_EXEC.SLICE_START_DTTM | Populated based on the slice start date from the B1_JOB_EXEC table. |
END_DATE_KEY | Snapshot End Date Surrogate Key | B1_JOB_EXEC.SLICE_END_DTTM | Populated based on the difference between slice end date and one second from the B1_JOB_EXEC table. |
CITY_OUTG_UDD1_KEY | City outage User Defined Dimension 1 Surrogate Key | ||
CITY_OUTG_UDD2_KEY | City outage User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Control Zone Outage
An accumulation fact that captures all reliability metrics for leaf level NCG daily.
This fact is unusual in the sense that its data is not populated directly from the source tables in the replication schema. It is a calculated fact whose data is derived from the data in the Customer restored facts.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CTRL_ZONE_OUTG |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CD_CTRL_ZONE |
ODI Package Name | B1_PKG_CF_CTRL_ZONE_OUTG |
ETL View Name | n/a |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
CTRL_ZONE_OUTG_ KEY | Control Zone Outage Fact Key | Populated with the sequence from SPL_CTRL_ZONE_OUTG_SEQ. | |
Natural Keys | |||
SRC_NCG_ID | NCG ID | CD_CTRL_ZONE.NCG_ ID | Loaded with control zone details from Control Zone dimension that are active during the slice period. |
Measures | |||
NUM_CUST_SERVED | Number of Customers Served | CES_CUSTOMERS_HISTORY | Populated with the number of active customers served in the control zone during the snapshot period. 1. Joins with CES_CUSTOMERS_HISTORY on NCG and the BIRTH and DEATH effective for that snapshot period. 2. Counts the number of records. |
NUM_SUST_EVENT | Number of Sustained Events | Populated with the count of events that are momentary within the statistics period. A sustained event can be identified when the difference between begin and end time of the outage (outage duration in minutes) is greater than the configured number of momentary minutes. | |
NUM_SUST_INTRPT | Number of Sustained Interruptions | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with the count of sustained outages during the period. An outage is considered sustained if the difference between the beginning and ending time of the outage (in minutes) is greater than the configured number of momentary minutes. |
NUM_MOM_EVENT | Number of Momentary Event | Populated with the count of momentary events during the period. A momentary event can be identified when the difference between begin and end time of the outage (the outage duration in minutes) is less than the configured number of momentary minutes. | |
NUM_MOM_INTRPT | Numb. Mom. Interruptions | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with the count of momentary outages during the snapshot period. An outage is considered momentary if the difference between the beginning and ending time of the outage (in minutes) is less than or equal to the configured number of momentary minutes. |
CMI | CMI | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with the sum of customer minutes interrupted (in minutes) for sustained outages. |
NUM_MULT_SUST_ INTRPT | Number of Multiple Sustained Interruptions | CF_CUST_RST_OUTG.PER.SRC_PER_ID CF_CUST_RST_OUTG.PREM.SRC_PREM_ID CF_CUST_RST_OUTG.METER.SRC_METER_ID | Populated with the count of sustained outages that are greater than the configured CEMI count. |
NUM_MULT_CUST_ INTRPT | Number of Multiple Customer Interruptions | CF_CUST_RST_OUTG.PER.SRC_PER_ID CF_CUST_RST_OUTG.PREM.SRC_PREM_ID CF_CUST_RST_OUTG.METER.SRC_METER_ID | Populated with the count of sustained and momentary outages that are greater than the configured CEMI count. |
SAIDI | SAIDI | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with the customer minutes interrupted by number of customers served. |
CAIDI | CAIDI | Populated with the number of customer minutes interrupted divided by the number of customers interrupted. | |
SAIFI | SAIFI | Populated with the number of customers interrupted divided by the number of customers served. | |
MAIFIE | MAIFIE | Populated with the number of momentary customer interruption events divided by the number of customers served. | |
NUM_CUST_INTRPT | Customers Interrupted | Populated with the sum of customer interruptions during the period. If a customer experiences multiple interruptions, each should be counted. | |
NUM_MOM_E_INTRPT | Number of Momentary Events | Populated with the number of momentary events during the period, excluding the events that immediately precede a sustained interruption. | |
NUM_EVENT | Number of Events | Populated with the number of events during the period. | |
NUM_CUST_SUST_ INTRPT | Number of Customers who experienced a Sustained Interruption | Populated with the count of customers who experienced more than P_CEMI_COUNT sustained interruptions during the period. | |
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
STATISTICS_DTTM | Statistics Date/Time | Populated with job processing date end time in the DD-MON-YYYY HH24:MI:SS format. | |
Foreign Keys | |||
CTRL_ZONE_KEY | Control Zone Surrogate Key | CD_CTRL_ZONE.CTRL_ZONE_KEY | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
CTRL_ZONE_SEC_KEY | Control Zone Secondary Surrogate Key | CD_CTRL_ZONE_SEC.CTRL_ZONE_SEC_KEY | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
BEGIN_DATE_KEY | Snapshot Begin Date Surrogate Key | B1_JOB_EXEC.SLICE_START_DTTM | Populated based on the slice start date from the B1_JOB_EXEC table. |
END_DATE_KEY | Snapshot End Date Surrogate Key | B1_JOB_EXEC.SLICE_END_DTTM | Populated based on the difference between slice end date and one second from the B1_JOB_EXEC table. |
CTRL_ZONE_OUTG_ UDD1_KEY | Control Zone outage User Defined Dimension 1 Surrogate Key | ||
CITY_OUTG_UDD2_KEY | City outage User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Crew
For all jobs created in the source system, several activities (such as assignment, un-assignment, dispatch, arrival and completion) are performed to complete them. Each of these activities are carried out by the corresponding crew.
This fact stores all such crew activity information for every job created in the source system. The data is stored for all restored and un-restored jobs.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_RST_CREW |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | CREW_ASSIGNMENTS CREW_DISPATCHES STORMMAN_STORMS |
ODI Package Name | B1_PKG_CF_RST_CREW |
ETL View Name | B1_F_RST_CREW_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
RST_CREW_KEY | Recent Crew Key | Populated with the sequence from SPL_RECENT_CREW_SEQ. | |
Natural Keys | |||
SRC_CREW_ID | Source Crew ID | CREW_ASSIGNMENTS.CREW_KEY CREW_DISPATCHES.CREW_KEY | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
INROUTE_DURATION | Inroute Duration | CREW_DISPATCHES.WHEN_HAPPENED | Populated with the total time spent by the crew in ‘Dispatch’ state for a particular event. |
WORK_DURATION | Work Duration | CREW_DISPATCHES.WHEN_HAPPENED | Populated with the total time spent by the crew in ‘Arrive’ state for a particular event. |
ASSIGN_DURATION | Assign Duration | CREW_ASSIGNMENT.WHEN_HAPPENED | Populated with the total time spent by the crew in ‘Assign’ state for a particular event. |
DISPATCH_DURATION | Dispatch Duration | CREW_DISPATCHES.WHEN_HAPPENED CREW_ASSIGNMENTS.WHEN_HAPPENED | Populated with the total time spent by the crew while assigned, en route and working on the event. Dispatch Duration = En Route Duration + Work Duration + Assign Duration |
Degenerate Dimensions | |||
SRC_EVENT_NBR | Source Event Number | INCIDENTS.DECODE(NVL(ASSOCIATE_IDX, 0), 0,NUMB,ASSOCIATE_IDX) | If the Associate Index value is 0, populate this field with NUMB. Else, populate Associated Index value. |
EXCL_PERIOD_START_DTTM | Exclusion Period End Date/Time | STORMMAN_STORMS. EX_STORM_START | If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period start date/time. |
EXCL_PERIOD_END_ DTTM | Exclusion Period Start Date/Time | STORMMAN_STORMS. EX_STORM_END | If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period end date/time. |
Foreign Keys | |||
EVENT_KEY | Event Key | JOBS.NUMB | Populated from CD_EVENT’s surrogate key using NUMB column in the JOBS table. |
ASSIGN_DATE_KEY | Last Assign Date Key | CREW_ASSIGNMENTS.WHEN_HAPPENED | Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0. |
ASSIGN_TIME_KEY | Last Assign Time Key | CREW_ASSIGNMENTS.WHEN_HAPPENED | Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0. |
CTRL_ZONE_KEY | Control Zone Key | JOBS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
ASSIGN_USER_KEY | Last Assign User Key | CREW_ASSIGNMENTS. CES_USER | Populated with the last user who assigned the crew. |
CREW_KEY | Crew Key | CREW_ASSIGNMENTS.CREW_KEY CREW_DISPATCHES.CREW_KEY | Populated with the crew details in the CREW_ASSIGNMENTS dimension. |
DEVICE_KEY | Device Key | JOBS.H_IDX JOBS.H_CLS | Populated based on H_IDX and H_CLS in the CD_DEVICE table. |
UNASSIGN_DATE_KEY | Unassign Date Key | CREW_ASSIGNMENTS.WHEN_HAPPENED | Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0. |
UNASSIGN_TIME_KEY | Unassign Time Key | CREW_ASSIGNMENTS.WHEN_HAPPENED | Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0. |
UNASSIGN_USER_KEY | Unassign User Key | CREW_ASSIGNMENTS.CES_USER | Look up the CD_USER dimension with the last user who unassigned the crew and populate with surrogate key. |
ACCEPT_DATE_KEY | Unassign Date Key | CREW_DISPATCHES.WHEN_HAPPENED | Populated based on the date part of the latest dispatch date of the crew. In case of no dispatches for the crew, populate as 0. |
ACCEPT_TIME_KEY | Accept Time Key | CREW_DISPATCHES.WHEN_HAPPENED | Populated based on the time part of the latest dispatch date of the crew and lookup the CD_TIME dimension. In case of no dispatches for the crew, populate as 0. |
ACCEPT_USER_KEY | Accept User Key | CREW_DISPATCHES.CES_USER | Populate with the last user who dispatched the crew, by looking up the CD_USER dimension. |
ARRIVE_DATE_KEY | Arrive Date Key | CREW_DISPATCHES.WHEN_HAPPENED | Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest arrival date of the crew. In case of no completions for the crew, populate as 0. |
ARRIVE_TIME_KEY | Arrive Time Key | CREW_DISPATCHES.WHEN_HAPPENED | Populated based on the time part of the latest arrival date of the crew and lookup the CD_TIME dimension. In case of no completions for the crew, populate as 0. |
ARRIVE_USER_KEY | Arrive User Key | CREW_DISPATCHES.CES_USER | Populate with the last user who marked the crew activity as arrived, by looking up the CD_USER dimension. |
CMPL_DATE_KEY | Completed Date Key | CREW_DISPATCHES.WHEN_HAPPENED | Extract the date part of the latest completion date of the crew and lookup the CD_DATE dimension. In case of no completions for the crew, populate as 0. |
CMPL_TIME_KEY | Completed Time Key | CREW_DISPATCHES.WHEN_HAPPENED | Extract the time part of the latest completion date of the crew and lookup the CD_TIME dimension. In case of no completions for the crew, populate as 0. |
CMPL_USER_KEY | Completed User Key | CREW_DISPATCHES.CES_USER | Populate with the last user who marked the crew activity as completed, by looking up the CD_USER dimension. |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL2_DETAIL_KEY | Excludable 2 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL3_DETAIL_KEY | Excludable 3 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM_OUTAGE_TYPE_KEY | Storm Outage Type Key | JOBS.STORMMAN_TYPE | Populated based on the storm outage type in the Jobs table. |
CTRL_ZONE_SEC_KEY | Control Zone Key | JOBS.NCG | Populated from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension. |
EXCL_DETAIL_KEY | Excludable Detail Key | STORMMAN_STORMS.ZONE_IDX STORMMAN _STORMS. STORM_NAME STORMMAN _STORMS. STORM_DATE | Populated based on the storm level NCG, storm name, and storm name suffix from the CD_EXCL_DETAIL table. |
EXCL_START_DATE_ KEY | Exclusion Period Start Date Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the date part in the CD_DATE table. |
EXCL _END_DATE_KEY | Exclusion Period End Date Key | STORMMAN_STORMS. EX_STORM_END | Populated based on the date part from the CD_DATE dimension. |
EXCL_START_TIME_ KEY | Exclusion Period Start Time Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the time part from the CD_TIME dimension. |
EXCL _END_TIME_KEY | Exclusion Period End Time Key | STORMMAN_STORMS. EX_STORM_END | Populated based on the time part from the CD_TIME dimension. |
CURR_ACCEPT_TIME_KEY | Current Crew Accept Time Key | CREW_DISPATCHES.WHEN_HAPPENED | If the accept time is before the assign time, populate null. Else, populate accept time in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_ACCEPT_DATE_KEY | Current Crew Accept Date Key | CREW_DISPATCHES.WHEN_HAPPENED | If the accept date is before the assign date, populate null. Else, populate accept date in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_ARRIVE_TIME_KEY | Current Crew Arrive Time Key | CREW_DISPATCHES.WHEN_HAPPENED | If the arrive time is before the assign time, populate null or if arrive time is before the accept time, populate null. Else, populate the arrive time in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_ARRIVE_DATE_KEY | Current Crew Arrive Date Key | CREW_DISPATCHES.WHEN_HAPPENED | If the arrive date is before the assign date, populate null or if arrive date is before the accept date, populate null. Else, populate the arrive date in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_CMPL_TIME_KEY | Current Crew Completed Time Key | CREW_DISPATCHES.WHEN_HAPPENED | If the complete time is before the assign time, populate null, or if complete time is before the accept time, populate null, or complete time is before the arrive time, then populate null. Else, populate the complete time in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_CMPL_DATE_KEY | Current Crew Completed Date Key | CREW_DISPATCHES.WHEN_HAPPENED | If the complete date is before the assign date, populate null, or if complete date is before the accept date, populate null, or complete date is before the arrive date, then populate null. Else, populate the complete date in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CREW_UDD1_KEY | User Defined Dimension 1 Key | ||
CREW_UDD2_KEY | User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
DATA_LOAD_DTTM | Data Load Date/Time | Populated with the load timestamp value. | |
Crew Activity
Captures the complete history of all the crews that worked on an event, including multiple assignments of the same crew to the same event.
Note: The data for this fact is captured from both the driver tables (CREW_ASSIGNMENTS and CREW_DISPATCHES) in a mutually exclusive manner. All columns of the table are separately populated from each of these driver tables.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CREW_ACTIVITY |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CREW_ASSIGNMENTS CREW_DISPATCHES STORMMAN_STORMS |
ODI Package Name | B1_PKG_CF_CREW_ACTIVITY |
ETL View Name | B1_F_CREW_ACTIVITY_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
CREW_ACTIVITY_KEY | Crew Activity Fact Surrogate Key | Populated with the sequence from B1_CREW_ACTIVITY_SEQ.NEXTVAL. | |
Natural Keys | |||
SRC_IND | Source Indicator | Populated based on the table the data is being loaded from. • ‘A’ if the source table is CREW_ASSIGNMENTS • ‘D’ if the source is CREW_DISPATCHES | |
SEQ_NUM | Sequence Number | CREW_ASSIGNMENTS. SEQ_NUM CREW_DISPATCHES. SEQ_NUM | |
ACTIVE_FLG | Active Flag | CREW_ASSIGNMENTS. ACTIVE CREW_DISPATCHES. ACTIVE | |
ACTION_CD | Action Code | CREW_ASSIGNMENTS. ACTION CREW_DISPATCHES. ACTION | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
ACTION_DTTM | Action Date/Time | CREW_ASSIGNMENTS.WHEN_HAPPENED CREW_DISPATCHES. WHEN_HAPPENED | |
Foreign Keys | |||
ACTION_DATE_KEY | Action Date Key | CREW_ASSIGNMENTS.WHEN_HAPPENED CREW_DISPATCHES. WHEN_HAPPENED | Populated based on the date part of ACTION_DTTM. |
ACTION _TIME_KEY | Action Time Key | CREW_ASSIGNMENTS.WHEN_HAPPENED CREW_DISPATCHES. WHEN_HAPPENED | Populated based on the time of ACTION_DTTM. |
CREW_KEY | Crew Key | CREW_ASSIGNMENTS.CREW_KEY CREW_DISPATCHES.CREW_KEY | Populated based on the CREW_KEY from the CREW_ASSIGNMENTS/CREW_DISPATCHES tables. |
EVENT_KEY | Event Key | JOBS.NUMB | Populated based on NUMB from the JOBS table. |
CTRL_ZONE_ KEY | Control Zone Dimension Surrogate Key | JOBS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | JOBS.NCG | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
STORM_KEY | Storm Dimension Surrogate Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM_OUTAGE_TYPE_KEY | Storm Outage Type Dimension Surrogate Key | JOBS.STORMMAN_TYPE | Populated based on jobs STORMMAN_TYP. |
DEVICE_KEY | Device Key | JOBS.H_IDX JOBS.H_CLS | Populated based on H_IDX and H_CLS from JOBS table. If any (either device class or index) value is 0, then map to the zeroth key. In case of non-electrical devices, devices are not available in Device dimension. Facts referring to these devices will be populated with 0 instead of -99. |
ASSIGN_USER_KEY | Assigned User Key | CREW_ASSIGNMENTS.CES_USER | Populated based on CES_USER from CREW_ASSIGNMENTS. |
CREW_ACTIVITY_UDD1_KEY | Crew Activity User Defined Dimension 1 Surrogate Key | ||
CREW_ACTIVITY_UDD2_KEY | Crew Activity User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Customer Outage
Stores the details of customers experiencing service interruptions based on the supply node log information from the source system. The supply node log data will be mapped to the Supply Node Lookup dimension to determine the actual list of customers experiencing the outage.
Few measures captured in this fact are outage duration, CMI, number of momentary outages, etc. The data is stored for all restored and un-restored jobs.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CUST_RST_OUTG |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | SUPPLY_NODE_LOG JOBS STORMMAN_STORMS |
ODI Package Name | B1_PKG_CF_CUST_RST_OUTG |
ETL View Name | B1_F_CUST_RECENT_OUTG_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
CUST_RST_OUTG_KEY | Customer Restored Outage Fact Surrogate Key | Populated with the sequence from SPL_CUST_RST_OUTG_SEQ. | |
Natural Keys | |||
SRC_ID | Source Outage ID | SUPPLY_NODE_LOG.ID | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
OUTG_DURATION | Outage Duration | SUPPLY_NODE_LOG.OUTAGE_TIME SUPPLY_NODE_LOG.RESTORE_TIME | Populated with the difference between restore time and outage time. |
NUM_MOMENTARY | Number of Momentaries Covered | JOBS.MOMENTARIES | Populated with the number of momentaries covered. |
CMI | Customer Minutes Interrupted | SUPPLY_NODE_LOG. OUTAGE_TIME SUPPLY_NODE_LOG. RESTORE_TIME | Populated with difference between restored time and outage time, in minutes. |
EST_RST_DUR | Estimated Restoration Duration | JOBS.BEGIN_TIME JOBS.EST_REST_TIME | Populated with the difference between estimated restore time and begin time, in minutes. |
DURATION_ DEVIATION | Duration Deviation | JOBS.RESTORE_TIME JOBS.EST_REST_TIME | Populated with the difference between restore time and estimated restore time, in minutes. |
Degenerate Dimensions | |||
BEGIN_DTTM | Begin Date Time | SUPPLY_NODE_LOG. OUTAGE_TIME | |
RST_DTTM | Restored Date Time | SUPPLY_NODE_LOG. RESTORE_TIME | |
RST_IND | Restored Indicator | SUPPLY_NODE_LOG.RESTORE_TIME | Populated based on JOBS.RESTORE_TIME. If the restored date/time exists, then this field is populated with 1. Else, with 0. |
PLANNED_IND | Planned Indicator | SWMAN_SHEET.SHEET_CATEGORY_NAME | Populated based on SHEET_CATEGORY_NAME. If the sheet category is planned, then this field is populated with 1. Else, 0. |
EXCLUDE_IND | Exclude Indicator | JOBS J.STATE_VALUE PICKLIST_INFO_UPD_TR P.NO_DTR_FLAG SWMAN_SHEET_CATEGORYSSCA.SHEET_CATEGORY_NAME | Populated based on STATE_VALUE '1024, NO_DTR_FLAG 'Y' and SHEET_CATEGORY_NAME 'Planned. |
OMS_EXCLUDE_IND | OMS Exclude Indicator | PICKLIST_INFO_UPD_ TR.NO_DTR_FLAG | Populated based on NO_DTR_FLAG. If it is set to 'Y', then this field is populated with 1. Else, with 0. |
CANCELLED_IND | Cancelled Indicator | JOBS.STATE_VALUE | Populated based on STATE_VALUE. if set to 1024, then the indicator is populated with 1. Else, with 0. |
SRC_PREMISE_ID | Premise ID | CD_PREM.SRC_PREM_ ID | |
SRC_PERSON_ID | Person ID | CD_PER.SRC_PER_ID | |
SRC_METER_ID | Meter ID | CD_METER.SRC_ METER_ID | |
DEVICE_ID | Device ID | CD_DEVICE.DEVICE_ ID | |
Foreign Keys | |||
EVENT_KEY | Event Key | JOBS.NUMB | Populated from CD_EVENT’s surrogate key using NUMB column in the JOBS table. |
EVENT_STATUS_KEY | Event Status Key | JOBS.STATUS | Populated based on the jobs status. |
ACCT_KEY | Account Key | CD_ACCT.SRC_ACCT_ID INCIDENTS.ACCOUNT_NUM CES_CUSTOMERS.ACCOUNT_NUMBER | Populated based on the account ID retrieved from the CES_CUSTOMER_ HISTORY table. |
PREM_KEY | Premise Key | CD_PREM.SRC_PREM_ ID | Populated based on the service location retrieved from CES_CUSTOMER_ HISTORY. |
PER_KEY | Person Key | CD_PER.SRC_PER_ID | Populated based on the CUST_ID retrieved from CES_CUSTOMER_ HISTORY. |
METER_KEY | Meter Key | CD_METER.SRC_ METER_ID | Populated based on METER_ID retrieved from CES_CUSTOMER_ HISTORY. |
ADDR_KEY | Address Key | CD_ADDR.SRC_ADDR_ ID | Populated based on service location retrieved from CES_CUSTOMER_ HISTORY. |
BEGIN_DATE_KEY | Begin Date Key | SUPPLY_NODE_LOG. OUTAGE_TIME | Populated based on the outage begin date. |
BEGIN_TIME_KEY | Begin Time Key | SUPPLY_NODE_LOG. OUTAGE_TIME | Populated based on the outage begin time. |
RST_DATE_KEY | Restored Time Key | SUPPLY_NODE_LOG. RESTORE_TIME | Populated based on the restored date. |
RST_TIME_KEY | Restored Date Key | SUPPLY_NODE_LOG. RESTORE_TIME | Populated based on the restored time. |
EST_RST_TIME_KEY | Estimated Restored Time Key | JOBS.EST_REST_TIME | Populate based on the estimated restored date. |
EST_RST_DATE_KEY | Estimated Restored Date Key | JOBS.EST_REST_TIME | Populate based on the estimated restored time. |
CTRL_ZONE_KEY | Control Zone Key | SUPPLY_NODE_LOG. NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
CAUSE_CTRL_ZONE_ KEY | Cause Control Zone Key | JOBS.NCG | Populated based on jobs NCG. |
AFF_DEVICE_KEY | AFF Device Key | SUPPLY_NODES. DEVICE_IDX SUPPLY_NODES. DEVICE_CLS | If H_IDX and H_CLS are ‘0’, ‘0’ is populated. |
CAUSE_DEVICE_KEY | Cause Device Key | JOBS.H_IDX JOBS.H_CLS | If H_IDX and H_CLS are ‘0’, ‘0’ is populated. |
FEEDER_KEY | Feeder Key | SUPPLY_NODES.FEEDER | If FEEDER_IDX and FEEDER_CLS are ‘0’, ‘0’ is populated. |
SW_PLAN_KEY | Switch Plan Key | JOBS.SWSHEET_IDX JOBS.SWSHEET_CLS | If SWSHEET_IDX and SWSHEET_CLS are ‘0’, ‘0’ is populated. |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL_DETAIL_KEY | Excludable Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL2_DETAIL_KEY | Excludable 2 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL3_DETAIL_KEY | Excludable 3 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM_OUTG_TYPE_ KEY | Storm Outage Type Key | JOBS.STORMMAN_TYPE | Populated based on jobs STORMMAN_TYPE. |
PARENT_EVENT_KEY | Parent Event Key | PARENT_EVENT.NUMB | Populated based on PARENT_EVENT.NUMB. |
OUTG_DUR_KEY | Outage Duration Key | SUPPLY_NODE_LOG.OUTAGE_TIME SUPPLY_NODE_LOG.RESTORE_TIME | Populated with the difference between restore time and outage time. |
EST_RST_DUR_KEY | Estimated Restore Duration Key | JOBS.BEGIN_TIME JOBS.EST_REST_TIME | Populated with the difference between estimated restoration time and begin time. |
DURATION_DEVIATION_KEY | Duration Deviation Key | JOBS.RESTORE_TIME JOBS.EST_REST_TIME | Populated with the difference between restoration time and estimated restore time. |
CREW_KEY | CREW_DISPATCHES.CREW_KEY CREW_ASSIGNMENT.CREW_KEY | Lookup in CREW_DISPATCHES and populate the surrogate key. | |
CUST_OUTG_UDD1_ KEY | User Defined Dimension 1 Key | ||
CUST_OUTG_UDD2_ KEY | User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Damage Assessment
Captures information about the damage assessment (such as what parts are damaged).
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_DAMAGE_ASMT |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | DAMAGE_REPORT STROMMAN_STORMS |
ODI Package Name | B1_PKG_CF_DAMAGE_ASMT |
ETL View Name | B1_F_DAMAGE_ASMT_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
DAMAGE_ASMT_F_KEY | Damage Assessment Fact Surrogate Key | Populated with the sequence from B1_DAMAGE_ASMT_F_ SEQ. | |
Natural Keys | |||
SRC_DAMAGE_ASMT_ID | Damage Assessment ID | DAMAGE_REPORT.REPORT_ID | Populated with the primary key of the Damage Assessment Report. |
Measures | |||
EST_REPAIR_DUR | Estimated Repair Duration | DAMAGE_REPORT.REPAIR_MINUTES | Populated with the estimated repair duration. |
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
REPORT_DTTM | Reported Date/Time | DAMAGE_REPORT.REPORT_TIME | Populated with the reported date/time of the Damage Assessment Report. |
Foreign Keys | |||
REPORT_DATE_KEY | Reported Date Key | CD_DATE.DATE_KEY | Populated based on the date part of REPORT_DTTM. |
REPORT_TIME_KEY | Reported Time Key | CD_TIME.TIME_KEY | Populated based on the time part of REPORT_DTTM. |
DAMAGE_ASMT_KEY | Damage Assessment Dimension Surrogate Key | DAMAGE_REPORT.REPORT_ID | Populated based on the Damage Report ID. |
DAMAGE_ASMT_ STATUS_KEY | Damage Assessment Status Dimension Surrogate Key | DAMAGE_REPORT.STATE_KEY | Populated based on the Damage Report State Key Value. |
DEVICE_KEY | Device Dimension Surrogate Key | JOBS.H_CLS JOBS.H_IDX | Populated based on Jobs Device Class and Index. If the Damage Report is mapped to a non-electrical device, this field will be populated with 0. |
EVENT_KEY | Event Dimension Surrogate Key | JOBS.NUMB | Populated based on the Jobs Number. Jobs number is retrieved from event class and index from the incident based on the Damage Report incident number. |
EVENT_STATUS_KEY | Event Status Dimension Surrogate Key | JOBS.STATUS | Populated based on the Jobs Status. |
CTRL_ZONE_ KEY | Control Zone Dimension Surrogate Key | INCIDENTS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | INCIDENTS.NCG | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
STORM_KEY | Storm Dimension Surrogate Key | STORMMAN_STORMS. STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. Storm will affect zones at higher level. Damage Report mapped to NCG falls within the same zone and jobs begin time within the storm period will be populated with storm key. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
DAMAGE_ASMT_ DETAIL_UDD1_KEY | Damage Assessment Detail User Defined Dimension 1 Surrogate Key | ||
DAMAGE_ASMT_ DETAIL_UDD2_KEY | Damage Assessment Detail User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Damage Assessment Detail
Populated with a combination of the various components involved in a damage report, namely outage parts, damage types, and crew types.
Note: For a single damage report, there will be a mutually exclusive fact record for reach of its outage parts, damage types, and crew types. The fact records are loaded into this fact with the combination below:
1. Damage Assessment and Damage Part
2. Damage Assessment and Damage Type
3. Damage Assessment and Crew Type
The record for the damage part only contains that damage part information, and does not include information for damage types and crew types. Similarly, a record for the damage type only contains that damage type information, and not information for damage parts and crew types. A record for the crew type only includes information for that crew type, and not for damage parts or damage types.
Measures are also populated according to the combination mentioned above. For example: Crew count is populated for those whose Crew Type ID is not null. Otherwise, it will be set to 0.
The three tables listed below are replaced with DAMAGE_ASSETS table in Oracle Utilities Network Management System v2.4.0.1.
• DAMAGE_OUTAGE_TYPES
• DAMAGE_TYPES
• DAMAGE_OUTAGE_PARTS
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_DAMAGE_ASMT_DETAIL |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | For NMS versions below 2.4.0.1: DAMAGE_REPORT DAMAGE_OUTAGE_PARTS DAMAGE_OUTAGE_TYPES DAMAGE_CREW_TYPES STORMMAN_STORMS For NMS versions 2.4.0.1 or higher: DAMAGE_REPORT DAMAGE_ASSETS DAMAGE_CREW_TYPES STORMMAN_STORMS |
ODI Package Name | B1_PKG_CF_DAMAGE_ASMT_DETAIL |
ETL View Name | B1_F_DAMAGE_ASMT_DETAIL _VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
DAMAGE_ASMT_ DETAIL_KEY | Damage Assessment Detail Fact Surrogate Key | Populated with the sequence from B1_DAMAGE_ASMT_DETAIL_SEQ.NEXTVAL. | |
Natural Keys | |||
These fields are applicable only for NMS v2.4.0.0 or lower. | |||
SRC_DAMAGE_TYPE_ID | Damage Type ID | DAMAGE_OUTAGE_ TYPES.TYPE_ID | Populate this column by retrieving data from DAMAGE_OUTAGE_TYPES.TYPE_ID based on the REPORT_ID. Populate this column with -1 if null. |
SRC_DAMAGE_PART_ID | Damage Part ID | DAMAGE_OUTAGE_ PARTS.PART_ID | Populate this column by retrieving data from DAMAGE_OUTAGE_PARTS.PART_ID based on the REPORT_ID. Populate this column with ‘***’ if null. |
SRC_DAMAGE_ASMT_ID | Damage Assessment ID | DAMAGE_REPORT.REPORT_ID | Populated with the primary key of the Damage Report. |
SRC_CREW_TYPE_ID | Crew Type | DAMAGE_CREW_TYPES.CREW_TYPE_ID | Populated with the primary key of the Crew Type, if the fact record is for crew types. Else, it is set to 0. |
These fields are applicable only for NMS v2.4.0.1 or higher. | |||
SRC_DAMAGE_TYPE_ID | Damage Assessment ID | DAMAGE_REPORT. REPORT_ID | As part of NMS v2.4.0.1, Damage Type ID is deprecated. This column will be populated with -1. |
SRC_DAMAGE_PART_ID | DAMAGE_ASSETS.PART_ID | Populate this column by retrieving data from DAMAGE_ASSETS.PART_ ID based on REPORT_ID identified above. Populate this column with ‘***’ in case null. | |
SRC_DAMAGE_ASMT_ID | Damage Assessment ID | DAMAGE_REPORT.REPORT_ID | Populated with the primary key of the Damage Report. |
SRC_CREW_TYPE_ID | Crew Type | DAMAGE_CREW_TYPES.CREW_TYPE_ID | Populated with the primary key of the Crew Type, if the fact record is for crew types. Else, it is set to 0. |
Measures | |||
These fields are applicable only for NMS v2.4.0.0 or lower. | |||
ACCESS_CNT | Accessible Count | DAMAGE_OUTAGE_ TYPES.NUM_ACC | Retrieves the number of damaged outage access count from DAMAGE_OUTAGE_ TYPES.NUM_ACC. |
INACCESS_CNT | Inaccessible Count | DAMAGE_OUTAGE_ TYPES.NUM_INACC | Retrieves the number of damaged outage in access count from DAMAGE_OUTAGE_ TYPES.NUM_INACC. |
PART_QUANTITY | Part Quantity | DAMAGE_OUTAGE_PARTS.QUANTITY | Retrieves the number of damaged outage parts from DAMAGE_OUTAGE_ PARTS.QUANTITY. |
CREW_NEEDED | Crews Needed | DAMAGE_CREW_TYPES.NUM_CREWS | Populated only if the fact record is for Crew Types. |
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
These fields are applicable only for NMS v2.4.0.1 or higher. | |||
ACCESS_CNT | Accessible Count | SUM(DAMAGE_ASSETS.QUANTITY) | Retrieve all records from DAMAGE_ASSETS table for current REPORT_ID, PART_ID with ACCESSIBLE ='Y'. Populate this column with SUM(DAMAGE_ASSETS.QUANTITY), only if Part ID is not '***'. Else, ‘0’. |
INACCESS_CNT | Inaccessible Count | SUM(DAMAGE_ASSETS.QUANTITY) | Retrieve all records from DAMAGE_ASSETS table for current REPORT_ID, PART_ID with ACCESSIBLE ='N'. Populate this column with SUM(DAMAGE_ASSETS.QUANTITY), only if Part ID is not '***'. Else ‘0’. |
PART_QUANTITY | ACCESS_CNT+INACCESS_CNT | Sum of ACCESS_CNT and INACCESS_CNT. Populate this column only if Part ID is not '***'. Else ‘0’. | |
CREW_NEEDED | Crews Needed | DAMAGE_CREW_TYPES.NUM_CREWS | Populated only if the fact record is for Crew Types. |
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
REPORT_DTTM | Reported Date/Time | DAMAGE_REPORT. REPORT_TIME | |
Foreign Keys | |||
REPORT_DATE_KEY | Reported Date Key | Populated based on the Damage Report date. | |
REPORT_TIME_KEY | Reported Time Key | Populated based on the Damage Report time. | |
DAMAGE_ASMT_KEY | Damage Assessment Dimension Surrogate Key | Populated based on the Damage Report ID. | |
DAMAGE_ASMT_ STATUS_KEY | Damage Assessment Status Dimension Surrogate Key | Populated based on the Damage Report State Key value. | |
DAMAGE_TYPE_KEY | Damage Type Dimension Surrogate Key | Populated from CD_DAMAGE_TYPE dimension surrogate key based on Damage Type identified for SRC_DAMAGE_TYPE_ID. | |
CREW_TYPE_KEY | Crew Type Dimension Surrogate Key | Populated based on the damage Crew Type's Crew Type ID. | |
DAMAGE_PART_KEY | Damage Part Dimension Surrogate Key | Populated from CD_DAMAGE_PART dimension surrogate key based on Damage Part ID identified for target column SRC_DAMAGE_PART_ID. | |
DEVICE_KEY | Device Dimension Surrogate Key | Populated based on Jobs Device Class and Index. If the damage report is mapped to a non-electrical device, then this field will be populated with 0. | |
EVENT_KEY | Event Dimension Surrogate Key | Populated based on the Jobs Number. Jobs number is retrieved from event class and index from incident based on damage report incident number. | |
EVENT_STATUS_KEY | Event Status Dimension Surrogate Key | Populated based on the Jobs Status. | |
CTRL_ZONE_ KEY | Control Zone Dimension Surrogate Key | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. | |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. | |
STORM_KEY | Storm Dimension Surrogate Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. Storm will affect zones at higher level. Damage report mapped to NCG fall within the same zone and jobs begin time within the storm period will be populated with storm key. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
DAMAGE_ASMT_ DETAIL_UDD1_KEY | Damage Assessment Detail User Defined Dimension 1 Surrogate Key | ||
DAMAGE_ASMT_ DETAIL_UDD2_KEY | Damage Assessment Detail User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Feeder Delivered Load
Extracts the hourly feeder delivered load information from Network Management System on a weekly or monthly schedule.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_FEEDER_DLVRD_LOAD |
Table Type | Fact |
Fact Type | Snapshot |
Driver Table | FLM_FDR_LOAD |
ODI Package Name | B1_PKG_CF_FEEDER_DLVRD_LOAD |
ETL View Name | B1_F_FEEDER_DLVRD_LOAD_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
FEEDER_DLVRD_LOAD_KEY | Feeder Delivered Load Snapshot Fact Surrogate Key | Populated with the sequence from SPL_FEEDER_DLVRD_LOAD_SEQ. | |
Natural Keys | |||
SRC_FEEDER_CLS | Source Feeder Class | FLM_FDR_LOAD.FDR_ CLS | |
SRC_FEEDER_IDX | Source Feeder Index | FLM_FDR_LOAD.FDR_ IDX | |
SRC_PHASE_ID | Source Phase ID | FLM_FDR_LOAD.ANALYSIS_DATE | |
Measures | |||
BRKR_AMP_LIMIT | Breaker Amp Limit | PF_SWITCHES.AMP_ LIMIT | |
AMP | Ampere | FLM_FDR_LOAD.AMP_A | |
KW | Kilo Watt | FLM_FDR_LOAD.KW_A | |
KVAR | KVAR | FLM_FDR_LOAD.KVAR_A | |
KVA | Kilo | FLM_FDR_LOAD.KW_A FLM_FDR_LOAD.KVAR_A | |
VOLTAGE | Voltage | FLM_FDR_LOAD.KV_A | |
POWER_FACTOR | Power Factor | ||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
SRC_DTTM | Source Date | ||
Foreign Keys | |||
FEEDER_KEY | Feeder Dimension Surrogate Key | FLM_FDR_LOAD.FDR_ CLS FLM_FDR_LOAD.FDR_ IDX | |
CTRL_ZONE_KEY | Control Zone Dimension Surrogate Key | NETWORK_COMPONENTS.NCG | |
BRKR_DEVICE_KEY | FLM_FDR_LOAD.CB_ CLS FLM_FDR_LOAD.CB_ IDX | ||
SUBSTN_DEVICE_KEY | FLM_FDR_LOAD.CB_ CLS FLM_FDR_LOAD.CB_ IDX | ||
SNAPSHOT_DATE_KEY | Snapshot Date Key | FLM_FDR_LOAD.ANALYSIS_DATE | |
SNAPSHOT_TIME_KEY | Snapshot Time Key | FLM_FDR_LOAD.ANALYSIS_DATE | |
PHASE_KEY | Source Phase Key | ||
FEEDER_DLVRD_LOAD_UDD1_KEY | Feeder Delivered Load User Defined Dimension 1 Surrogate Key | ||
FEEDER_DLVRD_LOAD_UDD2_KEY | Feeder Delivered Load User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Job
Stores job information from Network Management System. It populates key measures, such as outage duration, number of calls, and customers impacted. The data is stored for all restored and un-restored jobs.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_RST_JOB |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | JOBS PICKLIST_INFO_UPD_TR |
ODI Package Name | B1_PKG_CF_RST_JOB |
ETL View Name | B1_F_RECENT_JOB_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
RST_JOB_KEY | Restored Job Fact Surrogate Key | Populated with the sequence from SPL_RST_JOB_SEQ. | |
Natural Keys | |||
SRC_JOB_NBR | Source Job Number | JOBS.NUMB | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
OUTG_DURATION | Outage Duration | JOBS.RESTORE_TIME JOBS.BEGIN_TIME | Populated as the difference (in minutes) between begin date/time and the actual restoration date/time of the job. |
EST_RST_DUR | Estimated Restoration Duration | JOBS.EST_REST_TIME JOBS.BEGIN_TIME | Populated with the difference (in minutes) between the begin date/time and the estimated restoration date/time of the job. |
CUST_OUT | Customers Out | JOBS. NUM_CUST_OUT | |
CALL_RECVD | Calls Received | INCIDENTS. NUMB | Populated with the number of calls received by counting the number of incidents associated with the job. |
DURATION_DEVIATION | Duration Deviation | JOBS. RESTORE_TIME JOBS. EST_REST_TIME | Populated with the difference (in minutes) between the actual restoration date/time and the estimated restoration date/time of the job. |
CRIT_A | Critical Customer A Count | JOBS.CRIT_A | Populated with the total number of customers affected with type A criticality for the outage. |
CRIT_B | Critical Customer B Count | JOBS.CRIT_B | Populated with the total number of customers affected with type B criticality for the outage. |
CRIT_C | Critical Customer C Count | JOBS.CRIT_C | Populated with the total number of customers affected with type C criticality for the outage. |
CRIT_D | Critical Customer D Count | JOBS.CRIT_D | Populated with the total number of customers affected with type D criticality for the outage. |
CRIT_E | Critical Customer E Count | JOBS.CRIT_E | Populated with the total number of customers affected with type E criticality for the outage. |
CRIT_F | Critical Customer F Count | JOBS.CRIT_F | Populated with the total number of customers affected with type F criticality for the outage. |
CRIT_G | Critical Customer G Count | JOBS.CRIT_G | Populated with the total number of customers affected with type G criticality for the outage. |
CRIT_H | Critical Customer H Count | JOBS.CRIT_H | Populated with the total number of customers affected with type H criticality for the outage. |
CRIT_I | Critical Customer I Count | JOBS.CRIT_I | Populated with the total number of customers affected with type I criticality for the outage. |
CRIT_J | Critical Customer J Count | JOBS.CRIT_J | Populated with the total number of customers affected with type J criticality for the outage. |
CRIT_K | Critical Customer K Count | JOBS.CRIT_K | Populated with the total number of customers affected with type K criticality for the outage. |
CRIT_L | Critical Customer L Count | JOBS.CRIT_L | Populated with the total number of customers affected with type L criticality for the outage. |
CRIT_M | Critical Customer M Count | JOBS.CRIT_M | Populated with the total number of customers affected with type M criticality for the outage. |
CRIT_N | Critical Customer N Count | JOBS.CRIT_N | Populated with the total number of customers affected with type N criticality for the outage. |
CRIT_O | Critical Customer O Count | JOBS.CRIT_O | Populated with the total number of customers affected with type O criticality for the outage. |
CRIT_P | Critical Customer P Count | JOBS.CRIT_P | Populated with the total number of customers affected with type P criticality for the outage. |
CRIT_Q | Critical Customer Q Count | JOBS.CRIT_Q | Populated with the total number of customers affected with type Q criticality for the outage. |
CRIT_R | Critical Customer R Count | JOBS.CRIT_R | Populated with the total number of customers affected with type R criticality for the outage. |
CRIT_S | Critical Customer S Count | JOBS.CRIT_S | Populated with the total number of customers affected with type S criticality for the outage. |
CRIT_T | Critical Customer T Count | JOBS.CRIT_T | Populated with the total number of customers affected with type T criticality for the outage. |
CRIT_U | Critical Customer U Count | JOBS.CRIT_U | Populated with the total number of customers affected with type U criticality for the outage. |
CRIT_V | Critical Customer V Count | JOBS.CRIT_V | Populated with the total number of customers affected with type V criticality for the outage. |
CRIT_W | Critical Customer W Count | JOBS.CRIT_W | Populated with the total number of customers affected with type W criticality for the outage. |
CRIT_X | Critical Customer X Count | JOBS.CRIT_X | Populated with the total number of customers affected with type X criticality for the outage. |
CRIT_Y | Critical Customer Y Count | JOBS.CRIT_Y | Populated with the total number of customers affected with type Y criticality for the outage. |
CRIT_Z | Critical Customer Z Count | JOBS.CRIT_Z | Populated with the total number of customers affected with type Z criticality for the outage. |
Degenerate Dimensions | |||
RST_IND | Restored Indicator | JOBS.RESTORE_TIME | Populated based on JOBS.RESTORE_TIME. If the restored date/time exists, then this field is populated with 1. Else, with 0. |
PLANNED_IND | Planned Indicator | SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME | Populated based on SHEET_CATEGORY_NAME. If the sheet category is planned, then this field is populated with 1. Else, with 0. |
EXCLUDE_IND | Exclude Indicator | JOBS.STATE_VALUE PICKLIST_INFO_UPD_TR.NO_DTR_FLAG SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME | Populated based on STATE_VALUE '1024, NO_DTR_FLAG 'Y' and SHEET_CATEGORY_NAME 'Planned'. |
OMS_EXCLUDE_IND | OMS Exclude Indicator | PICKLIST_INFO_UPD_TR.NO_DTR_FLAG | Populated based on NO_DTR_FLAG. If set to 'Y,' then this field is populated with 1. Else, with 0. |
CANCELLED_IND | Cancelled Indicator | JOBS.STATE_VALUE | Populated based on STATE_VALUE. If set to 1024, then the indicator is populated with 1. Else, with 0. |
BEGIN_DTTM | Begin Date Time | JOBS.BEGIN_TIME | |
RST_DTTM | Restored Date Time | JOBS.RESTORE_TIME | |
COMPLETION_DTTM | Completion Time | JOBS.JOB_COMPLETE_ TIME | |
EXCL _START_DTTM | Exclusion Period Start Date/Time | STORMMAN_STORMS. EX_STORM_START | Populated with begin time only when there's excludable storm based on NCG. |
EXCL _END_DTTM | Exclusion Period End Date/Time | STORMMAN_STORMS. EX_STORM_END | Populated with end time only when there's excludable storm based on NCG. |
Foreign Keys | |||
EVENT_KEY | Event Key | JOBS.NUMB | Populated based on the event. |
EVENT_STATUS_KEY | Event Status Key | JOBS.STATUS | Populated based on the event status. |
BEGIN_DATE_KEY | Begin Date Key | JOBS.BEGIN_TIME | Populated based on the data part of the CD_DATE dimension. |
BEGIN_TIME_KEY | Begin Time Key | JOBS.BEGIN_TIME | Populated based on the time part of the CD_TIME dimension. |
RST_TIME_KEY | Restored Time Key | JOBS.RESTORE_TIME | Populated based on the time part of the CD_TIME dimension. |
RST_DATE_KEY | Restored Date Key | JOBS.RESTORE_TIME | Populated based on the date part of the CD_DATE dimension. |
EST_RST_TIME_KEY | Estimated Restored Time Key | JOBS.EST_REST_TIME | Populated based on the time part of the CD_TIME dimension. |
EST_RST_DATE_KEY | Estimated Restored Date Key | JOBS.EST_REST_TIME | Populated based on the date part of the CD_DATE dimension. |
CTRL_ZONE_KEY | Control Zone Key | JOBS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
DEVICE_KEY | Device Key | JOBS.H_IDX JOBS.H_CLS | If H_IDX and H_CLS is '0', then populate DEVICE_KEY as '0'. |
CMPL_USER_KEY | Completed User Key | WHO_COMPLETED.USER_NAME | Populated based on the users who completed the tasks. |
RESP_USER_KEY | Responsible User Key | WHO_RESPONSIBLE.USER_NAME | Populated based on the responsible user key in the CD_USER table. |
CREW_KEY | Crew Key | CREW_DISPATCHES.CREW_KEY CREW_ASSIGNMENT. CREW_KEY | Populated based on the crew details in the CREW_DISPATCHES table. |
FEEDER_KEY | Feeder Key | JOBS.FEEDER_CLS JOBS.FEEDER_IDX | Populated with 0 if FEEDER_IDX and FEEDER_CLS are 0. |
SW_PLAN_KEY | Switch Plan Key | JOBS.SWSHEET_IDX JOBS.SWSHEET_CLS | Populated with 0 if SWSHEET_IDX and SWSHEET_CLS are 0. |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM_OUTAGE_TYPE_KEY | Storm Outage Type Key | JOBS.STORMMAN_TYPE | Populated based on the storm outage type from the CD_STORM_OUTAGE_TYPE table. |
EXCL2_DETAIL_KEY | Excludable 2 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | |
EXCL3_DETAIL_KEY | Excludable 3 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | |
PARENT_EVENT_KEY | Parent Event Key | PARENT_EVENT.NUMB JOBS.NUMB | Populated with parent event from the CD_EVENT table. |
OUTG_DUR_KEY | Outage Duration | JOBS.RESTORE_TIME JOBS.BEGIN_TIME | Populated with the outage duration (different between start range and end range). |
EST_RST_DUR_KEY | Est Restoration Duration | JOBS.EST_REST_TIME JOBS.BEGIN_TIME | Populated with the estimated restoration duration. |
DURATION_DEVIATION_KEY | Duration Deviation | JOBS.RESTORE_TIME JOBS.EST_REST_TIME | Populated with duration deviation (outage duration is between start range and end range). |
CUST_OUT_KEY | Customers Out Key | JOBS. NUM_CUST_OUT | Populated with number of customers experiencing outage. |
COMPLETION_DATE_ KEY | Completion Date Key | JOBS.JOB_COMPLETE_ TIME | Populated with the completion date. |
COMPLETION_TIME_ KEY | Completion Time Key | JOBS.JOB_COMPLETE_ TIME | Populated with the completion time. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Key | JOBS.NCG | Populated from CD_CTRL_ZON_SEC dimension surrogate key based on NCG ID and modified date/time is between effective start and end date/times |
EXCL_DETAIL_KEY | Excludable Detail Key | STORMMAN_STORMS. ZONE_IDX STORMMAN_STORMS. STORM_NAME STORMMAN _STORMS. STORM_DATE | Populated based on the storm level NCG, storm name, and storm name suffix. |
EXCL_START_DATE_ KEY | Exclusion Period Start Date Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the date part in the CD_DATE table. |
EXCL _END_DATE_KEY | Exclusion Period End Date Key | STORMMAN_STORMS. EX_STORM_END | Populated based on the date part in the CD_DATE table. |
EXCL_START_TIME_ KEY | Exclusion Period Start Time Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the time part in the CD_TIME table. |
EXCL _END_TIME_KEY | Exclusion Period End Time Key | STORMMAN_STORMS. EX_STORM_END | Populated based on the time part in the CD_TIME table. |
JOB_UDD1_KEY | User Defined Dimension 1 Key | ||
JOB_UDD2_KEY | User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 21 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Outage
A snapshot fact (that can be configured hourly, weekly, monthly etc) and stores various measures at the control zone level.
This fact is unusual in the sense that its data is not populated directly from the source tables in the replication schema. It is a calculated fact whose data is derived from the data in the restored facts (Storm, Storm, Switch Plan, and Storm) and the recent facts (Recent Call, Recent Crew, Recent Job, and Storm).
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_OUTG |
Table Type | Fact |
Fact Type | Snapshot |
Driver Table | CF_RECENT_CALL CF_RECENT_CREW CF_RECENT_JOB JOB CF_CUST_RECENT_OUTG CUST |
Stage Table Name | STG_CF_OUTG |
ODI Package Name | B1_PKG_CF_OUTG |
ETL View Name | B1_F_CF_OUTG_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
OUTG_KEY | Outage Fact Surrogate Key | Populated with the sequence from SPL_OUTG_SEQ. | |
Natural Keys | |||
SNAP_TYPE_CD | Snapshot Type | Populated with ‘H’, ‘W’, or ‘M’ depending on whether the snapshot is configured as Hourly, Weekly, or Monthly. The out-of-the-box solution is Hourly. | |
SRC_NCG_ID | Control Zone ID | CD_CTRL_ZONE.SRC_NCG_ID | |
Measures | |||
NUM_CREW_ASSIGN | Number of Crew Assignments | CF_RECENT_CREW. FACT_CNT | Populated with the count of crew assignments during the hour. |
NUM_CREW_DISP | Number of Crew Dispatches | CF_RECENT_CREW. FACT_CNT | Populated with the count of crew dispatches during the hour. |
NUM_CUST_OUTG | Number of Customers Out | CF_CUST_RECENT_OUTG.FACT_CNT | Populated with the count of customers experiencing outage. |
NUM_CUST_RST | Number of Customers Restored | CF_CUST_RECENT_OUTG.FACT_CNT | Populated with the count of customers restored during the hour. |
NUM_CUST_NEW | Number of New Customers | CF_CUST_RECENT_OUTG.FACT_CNT | Populated with the count of new customers experiencing outages during the hour. |
NUM_CUST_MOM | Number of Customer Momentaries | CF_RECENT_JOB. FACT_CNT | Populated with the sum of fact count from the Recent Customer Job fact suffering momentary outages during that hour. |
NUM_EVENT | Number of Events | CF_RECENT_JOB. FACT_CNT | Populated with the number of events. |
NUM_NEW_EVENT | Number of New Events | CF_RECENT_JOB. FACT_CNT | Populated with the number of new events started during the hour. |
NUM_CLOSED_ EVENT | Number of Closed Events | CF_RECENT_JOB. FACT_CNT | Populated with the number of events closed during the hour. |
NUM_CANCELLED_ EVENT | Number of Cancelled Events | CF_RECENT_JOB. CANCELLED_IND | Populated with the number of events cancelled during the hour. |
NUM_CALL | Number of Calls | CF_RECENT_CALL. FACT_CNT | Populated with the number of calls received during the hour. |
NUM_CUST_EST_RST | Number of Customers Estimated to be Restored | CF_CUST_RECENT_ OUTG. FACT_CNT | Populated with the number of customers estimated to be restored during the hour. |
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
SNAPSHOT_DTTM | Snapshot Date/Time | B1_JOB_CONFIG.SLICE_END_DTTM | |
Foreign Keys | |||
CTRL_ZONE_KEY | Control Zone Key | CD_CTRL_ZONE.CTRL_ZONE_KEY | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Key | JOBS.NCG | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
SNAPSHOT_DATE_ KEY | Snapshot Date Type | Populated based on the date part of the snapshot type key. | |
SNAPSHOT_TIME_ KEY | Snapshot Time Type | Populated based on the time part of the snapshot type key. | |
OUTG_UDD1_KEY | Outage User Defined Dimension 1 Surrogate Key | ||
OUTG_UDD2_KEY | Outage User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Outage History
An accumulation fact that calculates various measures at control zone level for every hour.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_OUTG_HIST |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CD_CTRL_ZONE CF_RECENT_CALL CF_RECENT_CREW CF_RECENT_JOB CF_CUST_RECENT_OUTG CD_DATE CD_TIME CD_CTRL_ZONE_SEC CD_OUTG_HIST_UDD1 CD_OUTG_HIST_UDD2 |
Stage Table Name | STG_CF_OUTG_HIST |
ODI Package Name | B1_PKG_CF_OUTG_HIST |
ETL View Name | B1_F_CF_OUTG_HIST_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
OUTG_HIST_KEY | Outage Historical Fact Surrogate Key | Populated from sequence B1_OUTG_HIST_SEQ. | |
Natural Keys | |||
SRC_NCG_ID | Control Zone ID | CD_CTRL_ZONE.NCG_ ID | All active NCGs should be loaded into this for every statistics date/time. |
Measures | |||
NUM_CREW_ASSIGN | Number of Crews Assigned | SUM(CF_RECENT_CREW.FACT_CNT) | Populate this column from CF_RECENT_CREW. • To be calculated for each level NCG (Ctrl Zone) CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_CREW.CTRL_ZONE_KEY • For the STATISTICS_DTTM, lookup in the CD_DATE using its Date Part and get the Date Key (Say, V_DATE_KEY) • For the STATISTICS_DTTM, lookup in the CD_TIME using its Time Part and get the Time Key and Hour (Say, V_TIME_KEY and V_HOUR) • Get the records from CF_RECENT_CREW whose crew assignment time fall in the statistics date hour: • CF_RECENT_CREW.ASSIGN_DATE_KEY= V_DATE_KEY • CF_RECENT_CREW.ASSIGN_TIME_KEY=CD_TIME.TIME_KEY • and CD_TIME.HOUR = V_HOUR • Sum all the identified records FACT_CNT. SUM(FACT_CNT) |
NUM_CREW_DISP | Number of Crews Dispatched | SUM(CF_RECENT_CREW.FACT_CNT) | Populate this column from CF_RECENT_CREW: • To be calculated for Each level NCG (Ctrl Zone) CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_CREW.CTRL_ZONE_KEY • For the STATISTICS_DTTM, lookup in the CD_DATE using its Date Part and get the Date Key (Say, V_DATE_KEY) • For the STATISTICS_DTTM, lookup in the CD_TIME using its Time Part and get the Time Key and Hour (Say, V_TIME_KEY and V_HOUR) • Get the records from CF_RECENT_CREW whose crew dispatch(accept) time fall in the statistics date hour: • CF_RECENT_CREW.ACCEPT_DATE_KEY= V_DATE_KEY • CF_RECENT_CREW.ACCEPT_TIME_KEY = CD_TIME.TIME_KEY and CD_TIME.HOUR = V_HOUR • Sum all the identified records FACT_CNT • SUM(FACT_CNT) |
NUM_CUST_OUTG | Number of Customer impacted by Outage | SUM(CF_CUST_RECENT_OUTG. FACT_CNT) | Populate from CF_CUST _RECENT_OUTG: • To be calculated for Each level NCG (Ctrl Zone) • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_CUST_RECENT_OUTG.CTRL_ZONE_KEY • Identify the Records from CF_CUST_RECENT_OUTG which are not restored and the Outage begin before the statistics dttm: • RST_DTTM IS NULL and BEGIN_DTTM<=STATISTICS_DTTM • Also Identify the records from CF_CUST_RECENT_OUTG which begin before the statistics dttm and are restored after the statistics dttm: • BEGIN_DTTM<=STATISTICS_DTTM and RST_DTTM >= STATISTICS_DTTM • From all the records identified in Step 2 and 3, Sum the FACT_CNT |
NUM_CUST_RST | Number of Customers Restored | SUM(CF_CUST_RECENT_OUTG. FACT_CNT) | Populate from CF_CUST_ RECENT_OUTG: • To be calculated for Each level NCG (Ctrl Zone) CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_CUST_RECENT_OUTG.CTRL_ZONE_KEY • Identify the Records from CF_CUST_RECENT_OUTG which are restored in statistics dttm hour. TRUNC(RST_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') • Sum all the identified records FACT_CNT. SUM(FACT_CNT) |
NUM_CUST_NEW | Number of New Customers impacted by Outage | SUM(CF_CUST_RECENT_OUTG. FACT_CNT) | Populate from CF_CUST_ RECENT_OUTG • To be calculated for Each level NCG (Ctrl Zone) • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_CUST_RECENT_OUTG.CTRL_ZONE_KEY • Identify the Records from CF_CUST_RECENT_OUTG which Begin in statistics dttm hour • TRUNC(BEGIN_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') • Sum all the identified records FACT_CNT • SUM(FACT_CNT) |
NUM_CUST_EST_RST | Number of Customers Estimated to be Restored | SUM(CF_CUST_RECENT_OUTG. FACT_CNT) | Populate this column from CF_CUST_RECENT_OUTG • To be calculated for Each level NCG (Ctrl Zone) • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_CUST_RECENT_OUTG.CTRL_ZONE_KEY • Identify the Records from CF_CUST_RECENT_OUTG which are not restored and have estimated restoration in the statistics date hour • CF_CUST_RECENT_OUTG.RST_DTTM is null and • TRUNC(CF_CUST_RECENT_OUTG.EST_RST_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') • Sum all the identified records FACT_CNT • SUM(FACT_CNT) |
NUM_MOM_EVENT | Number of Momentary Events | SUM(CF_RECENT_JOB.FACT_CNT) | Populate this column from CF_RECENT_JOB • To be calculated for Each level NCG (Ctrl Zone) • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_JOB.CTRL_ZONE_KEY • Identify records whose Begin time and Restoration time fall in the statistics date hour and are less than X minutes apart. • TRUNC(BEGIN_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') AND TRUNC(RST_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') AND ((RST_DTTM - BEGIN_DTTM)*24*60) <= X (X is the value configured for the ETL parameter B1_MOM_OUTG_DUR) • Sum all the identified records FACT_CNT. • SUM(FACT_CNT) |
NUM_EVENT | Number of Events | SUM(CF_RECENT_JOB.FACT_CNT) | Populate this column from CF_RECENT_JOB • To be calculated for Each level NCG (Ctrl Zone) • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_JOB.CTRL_ZONE_KEY • Identify the Records from CF_RECENT_JOB which are not restored and the Outage begin before the statistics dttm. • RST_DTTM IS NULL and BEGIN_DTTM<=STATISTICS_DTTM • Also, identify the records from CF_RECENT_JOB that begin before the statistics dttm and are restored after the statistics dttm. • BEGIN_DTTM<=STATISTICS_DTTM and RST_DTTM >= STATISTICS_DTTM • From all the records identified in Step 2 and 3, sum the FACT_CNT. |
NUM_NEW_EVENT | Number of New Events | SUM(CF_RECENT_JOB.FACT_CNT) | Populate this column from CF_RECENT_JOB • To be calculated for each level NCG (Ctrl Zone) • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_JOB.CTRL_ZONE_KEY • Identify the records from CF_RECENT_JOB that begin in statistics dttm hour. • TRUNC(BEGIN_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') • Sum all the identified records FACT_CNT. • SUM(FACT_CNT) |
NUM_CLOSED_EVENT | Number of Closed Events | SUM(CF_RECENT_JOB.FACT_CNT) | Populate this column from CF_RECENT_JOB • To be calculated for each level NCG (Ctrl Zone) • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_JOB.CTRL_ZONE_KEY • Identify the records from CF_RECENT_JOB which are restored in statistics dttm hour. • TRUNC(RST_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') • Sum all the identified records FACT_CNT. • SUM(FACT_CNT) |
NUM_CANCELLED_EVENT | Number of Cancelled Events | SUM(CF_RECENT_JOB.CANCELLED_IND) | Populate this column from CF_RECENT_JOB • To be calculated for each level NCG (Ctrl Zone): • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_JOB.CTRL_ZONE_KEY • Identify the records from CF_RECENT_JOB which are restored in statistics dttm hour. • TRUNC(RST_DTTM,'HH24') = TRUNC(STATISTICS_DTTM, 'HH24') • Sum all the identified records CANCELLED_IND. • SUM(CANCELLED_ IND) |
NUM_CALL | Number of Calls | SUM(CF_RECENT_CALL.FACT_CNT) | Populate this column from CF_RECENT_CALL • To be calculated for each level NCG (Ctrl Zone): • CD_CTRL_ZONE.CTRL_ZONE_KEY = CF_RECENT_CALL.CTRL_ZONE_KEY • For the STATISTICS_DTTM, lookup in the CD_DATE using its Date Part and get the Date Key (Say, V_DATE_KEY) • For the STATISTICS_DTTM, lookup in the CD_TIME using its Time Part and get the Time Key and Hour (Say, V_TIME_KEY and V_HOUR) • Get the records from CF_RST_CALL whose call time fall in the statistics date hour • CF_RECENT_CALL.CALL_DATE_KEY = V_DATE_KEY and • CF_RECENT_CALL.CALL_TIME_KEY = CD_TIME.TIME_KEY and CD_TIME.HOUR = V_HOUR • Sum all the identified records FACT_CNT. • SUM(FACT_CNT) |
JOB_NBR | Job Number | B1_JOB_EXEC.JOB_EXEC_ID | ODI Job number |
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
SNAPSHOT_DTTM | Snapshot Date/Time | B1_JOB_CONFIG.SLICE_END_DTTM | |
STATISTICS_DTTM | Statistics Date/Time | ||
Foreign Keys | |||
OUTG_HIST_UDD1_KEY | Outage User Defined Dimension 1 Surrogate Key | CD_OUTG_HIST_UDD1.OUTG_HIST_UDD1_KEY | Lookup with CD_OUTG_HIST_UDD1 dimension and populate its key |
OUTG_HIST_UDD2_KEY | Outage User Defined Dimension 2 Surrogate Key | CD_OUTG_HIST_UDD2.OUTG_HIST_UDD2_KEY | Lookup with CD_OUTG_HIST_UDD2 dimension and populate its key |
CTRL_ZONE_KEY | Control Zone Key | CD_CTRL_ZONE.CTRL_ ZONE_KEY | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Key | CD_CTRL_ZONE_SEC.CTRL_ZONE_SEC_KEY | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
Recent Call
Stores the details of the incidents, including associated job details, which are created in the Network Management System. The fact stores details about all un-restored jobs, as well as jobs restored within the last 'X' number of days. The value of 'X' days has to be configured using the Oracle Utilities Administration Tool.
See the Configuring Oracle Utilities Network Management System (NMS) section in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide for more details.
The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.
The “recent fact” data store is not intended to be a long-term repository, and hence there is a purging mechanism based on a user configured number of days that is set in Oracle Utilities Administration Tool.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_RECENT_CALL |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | INCIDENTS |
ODI Package Name | B1_PKG_CF_RECENT_CALL |
ETL View Name | B1_F_RECENT_CALL_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
RECENT_CALL_KEY | Recent Call Fact Surrogate Key | Populated with the sequence from SPL_RECENT_CALL_SEQ. | |
Natural Keys | |||
SRC_INCIDENT_ID | Source Incident ID | INCIDENTS.DECODE(NVL(ASSOCIATE_IDX, 0), 0,NUMB,ASSOCIATE_IDX) | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
PRIORITY_IND | Priority Indicator | INCIDENTS.DECODE(SUBSTR(INCIDENTS.COMPLAINT, 7, 1), '1', '1', '2', '1', '0') | Use parameter B1_PRTY_BIT instead of 7. |
RST_IND | Restored Indicator | JOBS.RESTORE_TIME | Populated with 1 if restoration date/time is populated. Else, it is populated with 0. |
Foreign Keys | |||
EVENT_KEY | Event Key | JOBS.NUMB | Populated with the event key. |
ACCT_KEY | Account Key | INCIDENTS.ACCOUNT_NUM | Populated with the account key. |
CALL_DATE_KEY | Call Date Key | INCIDENTS.INPUT_TIME | Populated with the date part. |
CALL_TIME_KEY | Call Time Key | INCIDENTS.INPUT_TIME | Populated with the time part. |
CTRL_ZONE_KEY | Control Zone Key | INCIDENTS.NCG | Populated based on the incidents NCG. |
USER_KEY | User Key | CES_USER.USER_NAME | Populated based on the user key value. |
CALL_INFO_KEY | Call Info Key | INCIDENTS.DECODE( NVL( ASSOCIATE_IDX, 0 ), 0, NUMB, ASSOCIATE_IDX ) | Populated based on the call information. |
PREM_KEY | Premise Key | CES_CUSTOMERS.SERV_LOC_ID | Populated with the mailing premise ID. |
ADDR_KEY | Address Key | CES_CUSTOMERS.SERV_LOC_ID | Populated with the mailing address. |
DEVICE_KEY | Device Key | INCIDENTS.H_IDX INCIDENTS.H_CLS | Populated with the device key value. |
ACCT_TYPE_KEY | Account Type Key | CES_CUSTOMERS_HISTORY.ACCOUNT_TYPE | Populated with the account type value. |
METER_KEY | Meter Key | CES_CUSTOMERS_HISTORY.METER_ID | Populated with the meter key value. |
NETWORK_PREMISE_ KEY | Network Premise Key | CES_CUSTOMERS_HISTORY.SERV_LOC_ID | Populated with the network premise key value. |
CTRL_ZONE_SEC_KEY | Control Zone Secondary Key | INCIDENTS.NCG | Populated based on the incidents NCG. |
STORM_KEY | Storm Key | STORMAN_STORMS. STORM_NAME STORMAN_STORMS. STORM_DATE STORMMAN_STORMS. ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMAN_STORMS. STORM_NAME STORMAN_STORMS. STORM_DATE STORMMAN_STORMS. ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMAN_STORMS. STORM_NAME STORMAN_STORMS. STORM_DATE STORMMAN_STORMS. ZONE_IDX | Populated based on the storm name, date and control zone. |
CALL_UDD1_KEY | Call User Defined Dimension 1 Key | ||
CALL_UDD2_KEY | Call User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Recent Crew
For every job created in the source system, several activities (such as assignment, un-assignment, dispatch, arrival, and completion) are performed to complete the job. Each of these activities is carried out by the corresponding crew. This fact stores the summarized information of all such crew activities for every job created in the source system. The fact stores details about all un- restored jobs as well as jobs restored within the last 'X' number of days. The value of 'X' days has to be configured using the Oracle Utilities Administration Tool.
See Configuring Oracle Utilities Network Management System (NMS) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide for more details.
The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.
The “recent fact” data store is not intended to be a long-term repository and hence there is a purging mechanism based on a user configured number of days that is set in Oracle Utilities Administration Tool.
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_RECENT_CREW |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CREW_ASSIGNMENTS CREW_DISPATCHES STORMMAN_STORMS |
ODI Package Name | B1_PKG_CF_RECENT_CREW |
ETL View Name | n/a |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
RECENT_CREW_KEY | Recent Crew Fact Surrogate Key | Populated with the sequence from SPL_RECENT_CALL_SEQ. | |
Natural Keys | |||
SRC_EVENT_NBR | Source Event Number | JOBS.NUMB | |
SRC_CREW_ID | Source Crew ID | CREW_ASSIGNMENTS.CREW_KEY CREW_DISPATCHES.CREW_KEY | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
INROUTE_DURATION | En Route Duration | CREW_DISPATCHES.WHEN_HAPPENED | Populated with the total time spent by the crew in ‘Dispatch’ state for a particular event. |
WORK_DURATION | Work Duration | CREW_DISPATCHES.WHEN_HAPPENED | Populated with the total time spent by the crew in ‘Arrive’ state for a particular event. |
ASSIGN_DURATION | Assign Duration | CREW_ASSIGNMENTS.WHEN_HAPPENED | Populated with the total time spent by the crew in ‘Assign’ state for a particular event. |
DISPATCH_DURATION | Dispatch Duration | CREW_DISPATCHES.WHEN_HAPPENED CREW_ASSIGNMENTS.WHEN_HAPPENED | Populated with the total time spent by the crew while assigned, en route and working on the event. Dispatch Duration = En Route Duration + Work Duration + Assign Duration |
Degenerate Dimensions | |||
EXCL_PERIOD_START_DTTM | Exclusion Period End Date/Time | STORMMAN_STORMS. EX_STORM_START | If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period start date/time. |
EXCL_PERIOD_END_ DTTM | Exclusion Period Start Date/Time | STORMMAN_STORMS. EX_STORM_END | If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period end date/time. |
Foreign Keys | |||
EVENT_KEY | Event Key | JOBS.NUMB | Populated from CD_EVENT’s surrogate key using NUMB column in the JOBS table. |
ASSIGN_DATE_KEY | Last Assign Date Key | CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED | Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0. |
ASSIGN_TIME_KEY | Last Assign Time Key | CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED | Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0. |
CTRL_ZONE_KEY | Control Zone Key | JOBS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
ASSIGN_USER_KEY | Assign User Key | CREW_ASSIGNMENTS ASSIGN.CES_USER | Look up the CD_USER dimension with the last user who assigned the crew. |
CREW_KEY | Crew Key | CREW_ASSIGNMENTS.CREW_KEY CREW_DISPATCHES.CREW_KEY | Lookup in CD_CREW dimension to populate surrogate key. |
DEVICE_KEY | Device Key | JOBS.H_IDX JOBS.H_CLS | Populated based on H_IDX and H_CLS in the CD_DEVICE table. . |
UNASSIGN_USER_KEY | Unassign User Key | CREW_ASSIGNMENTS UNASSIGN.CES_USER | Look up the CD_USER dimension with the last user who unassigned the crew and populate with surrogate key. |
UNASSIGN_DATE_KEY | Unassign Date Key | CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED | Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0. |
UNASSIGN_TIME_KEY | Unassign Time Key | CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED | Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0. |
ACCEPT_USER_KEY | Accept User Key | CREW_DISPATCHES DISPATCH.CES_USER | Populate with the last user who dispatched the crew, by looking up the CD_USER dimension. |
ACCEPT_DATE_KEY | Accept Date Key | CREW_DISPATCHES DISPATCH.WHEN_HAPPENED | Populated based on the date part of the latest dispatch date of the crew. In case of no dispatches for the crew, populate as 0. |
ACCEPT_TIME_KEY | Accept Time Key | CREW_DISPATCHES DISPATCH.WHEN_HAPPENED | Populated based on the time part of the latest dispatch date of the crew and lookup the CD_TIME dimension. In case of no dispatches for the crew, populate as 0. |
ARRIVE_TIME_KEY | Arrive Time Key | CREW_DISPATCHES ARRIVE.WHEN_HAPPENED | Populated based on the time part of the latest arrival date of the crew and lookup the CD_TIME dimension. In case of no completions for the crew, populate as 0. |
ARRIVE_DATE_KEY | Arrive Date Key | CREW_DISPATCHES ARRIVE.WHEN_HAPPENED | Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest arrival date of the crew. In case of no completions for the crew, populate as 0. |
ARRIVE_USER_KEY | Arrive User Key | CREW_DISPATCHES ARRIVE.CES_USER | Populate with the last user who marked the crew activity as arrived, by looking up the CD_USER dimension. |
CMPL_TIME_KEY | Complete Time Key | CREW_DISPATCHES COMPLETE.WHEN_HAPPENED | Populated with the time part of the latest completion date of the crew. In case of no completions for the crew, populate as 0. |
CMPL_DATE_KEY | Completed Date Key | CREW_DISPATCHES COMPLETE.WHEN_HAPPENED | Populated with the date part of the latest completion date of the crew. In case of no completions for the crew, populate as 0. |
CMPL_USER_KEY | Completed User Key | CREW_DISPATCHES COMPLETE.CES_USER | Populate with the last user who marked the crew activity as completed, by looking up the CD_USER dimension. |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL2_DETAIL_KEY | Excludable 2 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL3_DETAIL_KEY | Excludable 3 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM_OUTAGE_TYPE_KEY | Storm Outage Type Key | JOBS.STORMMAN_TYPE | Populated based on the storm outage type in the Jobs table. |
CTRL_ZONE_SEC_KEY | Control Zone Key | JOBS.NCG | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
EXCL_DETAIL_KEY | Excludable Detail Key | STORMAN_STORMS.ZONE_IDX STORMMAN _STORMS. STORM_NAME STORMMAN _STORMS. STORM_DATE | Populated based on the storm level NCG, storm name, and storm name suffix from the CD_EXCL_DETAIL table. |
EXCL_START_DATE_ KEY | Exclusion Period Start Date Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the start date part of the exclusion period. |
EXCL _END_DATE_KEY | Exclusion Period End Date Key | STORMMAN_STORMS. EX_STORM_END | Populated with the end date part of the exclusion period. |
EXCL_START_TIME_ KEY | Exclusion Period Start Time Key | STORMMAN_STORMS. EX_STORM_START | Populated with the start time part of the exclusion period. |
EXCL _END_TIME_KEY | Exclusion Period End Time Key | STORMMAN_STORMS. EX_STORM_END | Populated with the end time part of the exclusion period. |
CURR_ACCEPT_TIME_KEY | Current Crew Accept Time Key | CREW_DISPATCHES.WHEN_HAPPENED | If the accept time is before the assign time, populate null. Else, populate accept time in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_ACCEPT_DATE_KEY | Current Crew Accept Date Key | CREW_DISPATCHES.WHEN_HAPPENED | If the accept date is before the assign date, populate null. Else, populate accept date in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_ARRIVE_TIME_KEY | Current Crew Arrive Time Key | CREW_DISPATCHES.WHEN_HAPPENED | If the arrive time is before the assign time, populate null or if arrive time is before the accept time, populate null. Else, populate the arrive time in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_ARRIVE_DATE_KEY | Current Crew Arrive Date Key | CREW_DISPATCHES.WHEN_HAPPENED | If the arrive date is before the assign date, populate null or if arrive date is before the accept date, populate null. Else, populate the arrive date in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_CMPL_TIME_KEY | Current Crew Completed Time Key | CREW_DISPATCHES.WHEN_HAPPENED | If the complete time is before the assign time, populate null, or if complete time is before the accept time, populate null, or complete time is before the arrive time, then populate null. Else, populate the complete time in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CURR_CMPL_DATE_KEY | Current Crew Completed Date Key | CREW_DISPATCHES.WHEN_HAPPENED | If the complete date is before the assign date, populate null, or if complete date is before the accept date, populate null, or complete date is before the arrive date, then populate null. Else, populate the complete date in the fact tables CF_RST_CREW and CF_RECENT_CREW. |
CREW_UDD1_KEY | Crew User Defined Dimension 1 Key | ||
CREW_UDD2_KEY | Crew User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Recent Customer Outage
This fact stores the details of customers experiencing service interruptions based on the supply node log information from the source system. The actual list of customers experiencing the outage is retrieved from CES Customer History. Some of the measures captured in this fact are outage duration, CMI, number of momentary outages, etc.
The fact stores details about all un-restored jobs as well as jobs restored within the last 'X' number of days. The value of 'X' days has to be configured using the Oracle Utilities Administration Tool.
See Configuring Oracle Utilities Network Management System (NMS) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide for more details.
The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.
The “recent fact” data store is not intended to be a long-term repository and hence the purging mechanism based on a user configured 'X' number of days that is set in Oracle Utilities Administration Tool.
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_CUST_RECENT_OUTG |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | JOBS J STORMMAN_STORMS |
ODI Package Name | B1_PKG_CF_CUST_RECENT_OUTG |
ETL View Name | B1_F_CUST_RECENT_OUTG_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
CUST_RECENT_OUTG_KEY | Recent Customer Outage Fact Surrogate Key | Populated with the sequence from SPL_CUST_RECENT_ OUTG_SEQ. | |
Natural Keys | |||
SRC_ID | Source Outage ID | SUPPLY_NODE_LOG.ID | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
OUTG_DURATION | Outage Duration | Populated with the difference between restored time and outage time, in minutes. | |
NUM_MOMENTARY | Number of Momentaries Covered | JOBS.NUM_MOMENTARIES | |
CMI | CMI | Populated with the difference between restored time and outage time, in minutes. | |
EST_RST_DUR | Estimated Restoration Duration | Populated with the difference between jobs estimated restored time and begin time, in minutes. | |
DURATION_DEVIATION | Duration Deviation | Populated with the difference between jobs restored time and estimated restored time, in minutes. | |
Degenerate Dimensions | |||
BEGIN_DTTM | Begin Date Time | SUPPLY_NODE_LOG.OUTAGE_TIME | |
RST_DTTM | Restored Date Time | SUPPLY_NODE_LOG.RESTORE_TIME | |
EST_RST_DTTM | Estimated Restoration Date/Time | JOBS.EST_REST_TIME | |
EXCL_START_DTTM | Exclusion Period Start Date/Time | STORMMAN_STORMS. EX_STORM_START | Populated based on NCG only when there is an excludable storm. |
EXCL_END_DTTM | Exclusion Period End Date/Time | STORMMAN_STORMS. EX_STORM_END | Populated based on NCG only when there is an excludable storm. |
RST_IND | Restored Indicator | Populated with 1 for all restored outages (supply node log restored time is not null). Else, populated with 0. | |
PLANNED_IND | Planned Indicator | Populated with 1 for all planned outages (SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME is not null). Else, it is populated with 0. | |
EXCLUDE_IND | Exclude Indicator | JOBS J.STATE_VALUE PICKLIST_INFO_UPD_TR P.NO_DTR_FLAG SWMAN_SHEET_CATEGORYSSCA.SHEET_CATEGORY_NAME | |
OMS_EXCLUDE_IND | OMS Exclude Indicator | Populated with 1 when no_dtr_flag value is 'Y' in PICKLIST_INFO_UPD_TR table. Else, 0. | |
CANCELLED_IND | Cancelled Indicator | Populated with 1 based on the jobs state value. Else, it is populated with 0. | |
SRC_PREMISE_ID | Outage Premise ID | CES_CUSTOMER_HISTORY.SERV_LOC_ID | |
SRC_PERSON_ID | Outage Person ID | CES_CUSTOMER_HISTORY.CUST_ID | |
SRC_METER_ID | Outage Meter ID | CES_CUSTOMER_HISTORY.METER_ID | |
DEVICE_ID | Outage Device ID | CES_CUSTOMER_HISTORY.DEVICE_ID | |
MOM_OUTG_IND | Momentary Outage Indicator | Populated with 1 if outage duration is less than the momentary outage duration configured parameter value. Else, it is populated with 0. | |
Foreign Keys | |||
EVENT_KEY | Event Key | Populated with the event key. | |
EVENT_STATUS_KEY | Event Status Key | Populated with the event status. | |
ACCT_KEY | Account Key | Populated with the account ID. | |
PREM_KEY | Premise Key | Populated with the service location. | |
PER_KEY | Person Key | Populated with the customer ID. | |
METER_KEY | Meter Key | Populated with the meter ID. | |
ADDR_KEY | Address Key | Populated based on the service location. | |
BEGIN_DATE_KEY | Begin Date Key | Populated based on the outage begin date. | |
BEGIN_TIME_KEY | Begin Time Key | Populated based on the outage begin time. | |
RST_DATE_KEY | Restored Time Key | Populated based on the restored date. | |
RST_TIME_KEY | Restored Date Key | Populated based on the restored time. | |
EST_RST_TIME_KEY | Estimated Restored Time Key | Populated based on the estimated restored time. | |
EST_RST_DATE_KEY | Estimated Restored Date Key | Populated based on the estimated restored date. | |
CTRL_ZONE_KEY | Control Zone Key | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. | |
CAUSE_CTRL_ZONE_ KEY | Cause Control Zone Key | Populated based on the jobs NCG. | |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. | |
CAUSE_CTRL_ZONE_ SEC_KEY | Control Zone Sec Dimension Surrogate Key | Populated based on the jobs NCG. | |
AFF_DEVICE_KEY | Device Dimension Surrogate Key | Populated based on the supply node device class and index. If the damage report is mapped to a non-electrical device, then this field is populated with 0. | |
CAUSE_DEVICE_KEY | Device Dimension Surrogate Key | Populated based on the jobs device class and index. If the damage report is mapped to a non-electrical device, then this field is populated with 0. | |
FEEDER_KEY | Feeder Key | Populated based on FEEDER_IDX and FEEDER_CLS. | |
SW_PLAN_KEY | Switch Plan Key | Populated based on jobs SWSHEET_IDX and SWSHEET_CLS. | |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. Storm affects the zones at higher level. Outages fall within the same zone and jobs begin time within the storm period is populated with the storm key. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL2_DETAIL_KEY | Excludable 2 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
EXCL3_DETAIL_KEY | Excludable 3 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM_OUTAGE_TYPE_KEY | Storm Outage Type Key | Populated based on the jobs’ STORMMAN_TYPE. | |
PARENT_EVENT_KEY | Event Dimension Surrogate Key | Populated based on the parent event number. | |
OUTG_DUR_KEY | Outage Duration Key | Populated with the outage duration based on the RANGE_START and RANGE_END period. | |
EST_RST_DUR_KEY | Est Restoration Duration Surrogate key | Populated with the estimated restoration duration based on the RANGE_START and RANGE_END. | |
DURATION_DEVIATION_KEY | Duration Deviation Surrogate Key | Populated with duration deviation based on the RANGE_START and RANGE_END. | |
NETWORK_PREM_KEY | Network Premise Dimension Surrogate Key | Populated with the service location ID based on the customer history. | |
CREW_KEY | Crew Dimension Surrogate Key | CREW_DISPATCHES.CREW_KEY CREW_ASSIGNMENT.CREW_KEY | Populated based on the crew dispatched crew key. |
ACCT_TYPE_KEY | Account Type Dimension Surrogate Key | Populated with account type based on the customer history. | |
EXCL_DETAIL_KEY | Excludable Detail Dimension Surrogate Key | Populated based on ZONE_IDX, NAME,DATE retrieved from STORMMAN_STORMS. | |
EXCL_START_DATE_ KEY | Exclusion Period Start Date Key | Populated based on the exclusion detail start date. | |
EXCL_END_DATE_KEY | Exclusion Period End Date Key | Populated based on the exclusion detail end time. | |
EXCL_START_TIME_ KEY | Exclusion Period Start Time Key | Populated based on the exclusion detail start time. | |
EXCL_END_TIME_KEY | Exclusion Period End Time Key | Populated based on the exclusion detail end time. | |
CUST_OUTG_UDD1_ KEY | User Defined Dimension 1 Key | ||
CUST_OUTG_UDD2_ KEY | User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
DATA_LOAD_DTTM | Data Load Date/Time | Populated with the load timestamp value. | |
Recent Job
Stores job information from the source system. This fact stores KPIs, such as number of events, outage duration, restoration duration, resolved event count, etc. It also stores details about all un-restored jobs, as well as jobs restored within the last 'X' number of days. Value of 'X' days has to be configured using Oracle Utilities Administration Tool.
See Configuring Oracle Utilities Network Management System (NMS) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide for more details.
The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.
The “recent fact” data store is not intended to be a long-term repository and hence there is a purging mechanism based on a user configured 'X' number of days set in the Oracle Utilities Administration Tool.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_RECENT_JOB |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | JOBS PICKLIST_INFO_UPD_TR |
ODI Package Name | B1_PKG_CF_RECENT_JOB |
ETL View Name | B1_F_RECENT_JOB_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
RECENT_JOB_KEY | Recent Job Fact Surrogate Key | Populated with the sequence from SPL_RECENT_JOB_SEQ. | |
Natural Keys | |||
SRC_JOB_NBR | Source Job Number | JOBS.NUMB | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
OUTG_DURATION | Outage Duration | JOBS.RESTORE_TIME JOBS.BEGIN_TIME | Populated with the difference (in minutes) between the begin date/time and the actual restoration date/time of the job. |
EST_RST_DUR | Outage Duration | JOBS.EST_REST_TIME JOBS.BEGIN_TIME | Populated with the difference (in minutes) between the begin date/time and the estimated restoration date/time of the job. |
CUST_OUT | Customers Out | JOBS. NUM_CUST_OUT | Populated with the customer count experiencing outage. |
CALL_RECVD | Calls Received | INCIDENTS. NUMB | Populated with the number of calls received by counting the number of incidents associated with the job. |
DURATION_DEVIATION | Duration Deviation | JOBS. RESTORE_TIME JOBS. EST_REST_TIME | Populated with the difference (in minutes) between the actual restoration date/time and the estimated restoration date/time of the job. |
CRIT_A | Critical Customer A Count | JOBS.CRIT_A | Populated with the total number of customers affected with type A criticality for the outage. |
CRIT_B | Critical Customer B Count | JOBS.CRIT_B | Populated with the total number of customers affected with type B criticality for the outage. |
CRIT_C | Critical Customer C Count | JOBS.CRIT_C | Populated with the total number of customers affected with type C criticality for the outage. |
CRIT_D | Critical Customer D Count | JOBS.CRIT_D | Populated with the total number of customers affected with type D criticality for the outage. |
CRIT_E | Critical Customer E Count | JOBS.CRIT_E | Populated with the total number of customers affected with type E criticality for the outage. |
CRIT_F | Critical Customer F Count | JOBS.CRIT_F | Populated with the total number of customers affected with type F criticality for the outage. |
CRIT_G | Critical Customer G Count | JOBS.CRIT_G | Populated with the total number of customers affected with type G criticality for the outage. |
CRIT_H | Critical Customer H Count | JOBS.CRIT_H | Populated with the total number of customers affected with type H criticality for the outage. |
CRIT_I | Critical Customer I Count | JOBS.CRIT_I | Populated with the total number of customers affected with type I criticality for the outage. |
CRIT_J | Critical Customer J Count | JOBS.CRIT_J | Populated with the total number of customers affected with type J criticality for the outage. |
CRIT_K | Critical Customer K Count | JOBS.CRIT_K | Populated with the total number of customers affected with type K criticality for the outage. |
CRIT_L | Critical Customer L Count | JOBS.CRIT_L | Populated with the total number of customers affected with type L criticality for the outage. |
CRIT_M | Critical Customer M Count | JOBS.CRIT_M | Populated with the total number of customers affected with type M criticality for the outage. |
CRIT_N | Critical Customer N Count | JOBS.CRIT_N | Populated with the total number of customers affected with type N criticality for the outage. |
CRIT_O | Critical Customer O Count | JOBS.CRIT_O | Populated with the total number of customers affected with type O criticality for the outage. |
CRIT_P | Critical Customer P Count | JOBS.CRIT_P | Populated with the total number of customers affected with type P criticality for the outage. |
CRIT_Q | Critical Customer Q Count | JOBS.CRIT_Q | Populated with the total number of customers affected with type Q criticality for the outage. |
CRIT_R | Critical Customer R Count | JOBS.CRIT_R | Populated with the total number of customers affected with type R criticality for the outage. |
CRIT_S | Critical Customer S Count | JOBS.CRIT_S | Populated with the total number of customers affected with type S criticality for the outage. |
CRIT_T | Critical Customer T Count | JOBS.CRIT_T | Populated with the total number of customers affected with type T criticality for the outage. |
CRIT_U | Critical Customer U Count | JOBS.CRIT_U | Populated with the total number of customers affected with type U criticality for the outage. |
CRIT_V | Critical Customer V Count | JOBS.CRIT_V | Populated with the total number of customers affected with type V criticality for the outage. |
CRIT_W | Critical Customer W Count | JOBS.CRIT_W | Populated with the total number of customers affected with type W criticality for the outage. |
CRIT_X | Critical Customer X Count | JOBS.CRIT_X | Populated with the total number of customers affected with type X criticality for the outage. |
CRIT_Y | Critical Customer Y Count | JOBS.CRIT_Y | Populated with the total number of customers affected with type Y criticality for the outage. |
CRIT_Z | Critical Customer Z Count | JOBS.CRIT_Z | Populated with the total number of customers affected with type Z criticality for the outage. |
Degenerate Dimensions | |||
RST_IND | Restored Indicator | JOBS.RESTORE_TIME | Populated with 1 for all restored outages (supply node log restored time is not null). Else, it is populated with 0. |
PLANNED_IND | Planned Indicator | SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME | Populated with 1 for all planned outages. Else, it is populated with 0. |
EXCLUDE_IND | Exclude Indicator | JOBS.STATE_VALUE PICKLIST_INFO_UPD_TR.NO_DTR_FLAG SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME | Populated with 1 for all excluded outages. Else, it is populated with 0. |
OMS_EXCLUDE_IND | OMS Exclude Indicator | PICKLIST_INFO_UPD_TR.NO_DTR_FLAG | Populated based on the NO_DTR_FLAG. If this is 'Y', the field is populated with '1'. Else, '0'. |
CANCELLED_IND | Cancelled Indicator | JOBS.STATE_VALUE | Populated based on the STATE_VALUE. If this is 1024, then the indicator is populated with '1'. Else, with '0. |
BEGIN_DTTM | Begin Date Time | JOBS.BEGIN_TIME | |
RST_DTTM | Restored Date Time | JOBS.RESTORE_TIME | |
COMPLETION_DTTM | Completion Time | JOBS.JOB_COMPLETE_ TIME | |
EXCL _START_DTTM | Exclusion Period Start Date/Time | STORMMAN_STORMS. EX_STORM_START | Populated with begin time only when there's excludable storm based on NCG. |
EXCL _END_DTTM | Exclusion Period End Date/Time | STORMMAN_STORMS. EX_STORM_END | Populated with end time only when there's excludable storm based on NCG. |
Foreign Keys | |||
EVENT_KEY | Event Key | JOBS.NUMB | Populated based on the event. |
EVENT_STATUS_KEY | Event Status Key | JOBS.STATUS | Populated based on the event status. |
BEGIN_DATE_KEY | Begin Date Key | JOBS.BEGIN_TIME | Populated based on the data part of the CD_DATE dimension. |
BEGIN_TIME_KEY | Begin Time Key | JOBS.BEGIN_TIME | Populated based on the time part of the CD_TIME dimension. |
RST_TIME_KEY | Restored Time Key | JOBS.RESTORE_TIME | Populated based on the time part of the CD_TIME dimension. |
RST_DATE_KEY | Restored Date Key | JOBS.RESTORE_TIME | Populated based on the date part of the CD_DATE dimension. |
EST_RST_TIME_KEY | Estimated Restored Time Key | JOBS.EST_REST_TIME | Populated based on the time part of the CD_TIME dimension. |
EST_RST_DATE_KEY | Estimated Restored Date Key | JOBS.EST_REST_TIME | Populated based on the date part of the CD_DATE dimension. |
CTRL_ZONE_KEY | Control Zone Key | JOBS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
DEVICE_KEY | Device Key | JOBS.H_IDX JOBS.H_CLS | If H_IDX and H_CLS is '0', then populate DEVICE_KEY as '0'. |
CMPL_USER_KEY | Completed User Key | WHO_COMPLETED.USER_NAME | Populated based on the users who completed the tasks. |
RESP_USER_KEY | Responsible User Key | WHO_RESPONSIBLE.USER_NAME | Populated based on the responsible user key in the CD_USER table. |
CREW_KEY | Crew Key | CREW_DISPATCHES.CREW_KEY CREW_ASSIGNMENT. CREW_KEY | Populated based on the crew details in the CREW_DISPATCHES table. |
FEEDER_KEY | Feeder Key | JOBS.FEEDER_CLS JOBS.FEEDER_IDX | Populated with 0 if FEEDER_IDX and FEEDER_CLS are 0. |
SW_PLAN_KEY | Switch Plan Key | JOBS.SWSHEET_IDX JOBS.SWSHEET_CLS | Populated with 0 if SWSHEET_IDX and SWSHEET_CLS are 0. |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM2_KEY | Storm 2 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM3_KEY | Storm 3 Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
STORM_OUTAGE_TYPE_KEY | Storm Outage Type Key | JOBS.STORMMAN_TYPE | Populated based on the storm outage type from the CD_STORM_OUTAGE_TYPE table. |
EXCL2_DETAIL_KEY | Excludable 2 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | |
EXCL3_DETAIL_KEY | Excludable 3 Detail Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | |
PARENT_EVENT_KEY | Parent Event Key | PARENT_EVENT.NUMB JOBS.NUMB | Populated with parent event from the CD_EVENT table. |
OUTG_DUR_KEY | Outage Duration | JOBS.RESTORE_TIME JOBS.BEGIN_TIME | Populated with the outage duration (different between start range and end range). |
EST_RST_DUR_KEY | Estimated Restoration Duration | JOBS.EST_REST_TIME JOBS.BEGIN_TIME | Populated with the estimated restoration duration. |
DURATION_DEVIATION_KEY | Duration Deviation | JOBS.RESTORE_TIME JOBS.EST_REST_TIME | Populated with duration deviation (outage duration is between start range and end range). |
CUST_OUT_KEY | Customers Out Key | JOBS. NUM_CUST_OUT | Populated with number of customers experiencing outage. |
COMPLETION_DATE_ KEY | Completion Date Key | JOBS.JOB_COMPLETE_ TIME | Populated with the completion date. |
COMPLETION_TIME_ KEY | Completion Time Key | JOBS.JOB_COMPLETE_ TIME | Populated with the completion time. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Key | JOBS.NCG | Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table. |
EXCL_DETAIL_KEY | Excludable Detail Key | STORMMAN_STORMS.ZONE_IDX STORMMAN_STORMS.STORM_NAME STORMMAN _STORMS. STORM_DATE | Populated based on the storm level NCG, storm name, and storm name suffix. |
EXCL_START_DATE_ KEY | Exclusion Period Start Date Key | STORMMAN_STORMS.EX_STORM_START | Populated based on the date part in the CD_DATE table. |
EXCL _END_DATE_KEY | Exclusion Period End Date Key | STORMMAN_STORMS.EX_STORM_END | Populated based on the date part in the CD_DATE table. |
EXCL_START_TIME_ KEY | Exclusion Period Start Time Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the time part in the CD_TIME table. |
EXCL _END_TIME_KEY | Exclusion Period End Time Key | STORMMAN_STORMS. EX_STORM_END | Populated based on the time part in the CD_TIME table. |
JOB_UDD1_KEY | User Defined Dimension 1 Key | ||
JOB_UDD2_KEY | User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Storm
Captures various metrics (such as CI, CMI, MCI, events, and damage assessments) for every hour a storm was active. This fact caters to the various KPI requirements in the Storm Analysis dashboard in the Oracle Utilities Analytics Warehouse application.
Note: Though this is an accumulation fact, all measures have to computed on an hourly basis till the end of the storm. This helps to update the computed measures for any changes in the source system. Measures are calculated for every storm at the beginning hour (based on the storm’s start date).
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_STORM |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | STORMMAN_STORMS |
ODI Package Name | B1_PKG_CF_STORM |
ETL View Name | B1_F_STORM_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
STORM_F_KEY | Storm Fact Surrogate Key | Populated with the sequence from B1_STORM_SEQ. | |
Natural Keys | |||
SRC_STORM_NAME | Storm Name | CD_STORM.SRC_STORM_NAME | |
SRC_STORM_NAME_SFX | Storm Name Suffix | CD_STORM.SRC_STORM_NAME_SFX | |
SRC_NCG_ID | Source NCG ID | STORMMAN_STORMS.ZONE_IDX | |
MAJ_EVT_SNAP_TYPE_ CD | Major Event Snapshot Type | CD_MJR_EVT_SNAP_TYPE.SNAP_TYPE_CD | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
CI | Customers Interrupted | CF_CUST_RST_OUTG.SUM(FACT_CNT) | Populated with the count of customers interrupted. |
CMI | Customer Minutes Interrupted | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with the customer minutes interrupted. |
MCI | Momentary Customers Interrupted | CF_CUST_RST_OUTG.SUM(FACT_CNT) | Populated with the count of momentary customer interruptions. |
SAIDI | SAIDI | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with customer minutes interrupted by number of customers served. |
SAIFI | SAIFI | Populated with the sum of customer interruptions divided by number of customer served. | |
MAIFI | MAIFI | Populated with the sum of momentary customer interruptions divided by number of customer served. | |
CAIDI | CAIDI | Populated with the number of customer minutes interrupted divided by the number of customer interruptions. | |
NUM_NEW_CUST_OUT | New Customers Out | Populated with count of customers that went out only during that hour (begin date/time fall in that hour). | |
NUM_ CUST_RST | Customers Restored | Populated with count of customers restored during that hour (the RST_DTTM fall in that hour). | |
NUM_CUST_REMAIN_ OUT | Customers Remaining Out | Populated with the remaining customers out. Total Number of Customer Out – Customers Restored in that hour. | |
NUM_NEW_EVENT | New Events | Populated with the count of jobs created during that hour. | |
NUM_CLOSED_EVENT | Closed Events | Populated with the count of jobs restored during that hour. | |
NUM_REMAIN_EVENT | Remaining Events | Populated with number of remaining events. Total Events - Closed Events | |
Degenerate Dimensions | |||
STATISTICS_DTTM | Statistics Date/Time | LEAST OF STORM_START AND EX_STORM_START TILL GREATEST OF STORM_END AND EX_STORM_END | Populate for every hour starting from least of (STORM_START and EX_STORM_START) till greatest of (STORM_END and EX_STORM_END) in the STORMMAN_STORMS table. In case of an active storm, load till SYSDATE and populate the hour end time. Example: For 9th hour, the statistics DTTM is populated as: 09:59:59. |
EXCL _START_DTTM | Exclusion Period Start Date/Time | STORMMAN_STORMS. EX_STORM_START | |
EXCL _END_DTTM | Exclusion Period End Date/Time | STORMMAN_STORMS. EX_STORM_END | |
Foreign Keys | |||
CTRL_ZONE_KEY | Control Zone Key | JOBS.NCG | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. |
STORM_KEY | Storm Key | STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE STORMMAN_STORMS.ZONE_IDX | Populated based on the storm name, date and control zone. |
CTRL_ZONE_SEC_KEY | Control Zone Sec Key | JOBS.NCG | Populated from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension. |
EXCL_DETAIL_KEY | Excludable Detail Key | STORMMAN_STORMS.ZONE_IDX STORMMAN_STORMS.STORM_NAME STORMMAN _STORMS. STORM_DATE | Populated based on the storm level NCG, storm name, and storm name suffix from the CD_EXCL_DETAIL table. |
EXCL_START_DATE_ KEY | Exclusion Period Start Date Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the exclusion detail start date. |
EXCL _END_DATE_KEY | Exclusion Period End Date Key | STORMMAN_STORMS. EX_STORM_END | Populated based on the exclusion detail end time. |
EXCL_START_TIME_ KEY | Exclusion Period Start Time Key | STORMMAN_STORMS. EX_STORM_START | Populated based on the exclusion detail start time. |
EXCL _END_TIME_KEY | Exclusion Period End Time Key | STORMMAN_STORMS. EX_STORM_END | Populated based on the exclusion detail end time. |
STORM_UDD1_KEY | Storm User Defined Dimension 1 Surrogate Key | ||
STORM_UDD2_KEY | Storm User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Switch Plan
Supports KPIs, such as number of steps, number of aborted/failed steps, and number of safety documents.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_SW_PLAN |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | SWMAN_SHEET S |
ODI Package Name | B1_PKG_CF_SW_PLAN |
ETL View Name | B1_F_SW_PLAN_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
SW_PLAN_F_KEY | Switch Plan Fact Surrogate Key | Populated with the sequence from SPL_SW_PLAN_SEQ. | |
Natural Keys | |||
SRC_SW_PLAN_CLS | Switch Plan Class | SWMAN_SHEET.SWITCH_SHEET_CLS | |
SRC_SW_PLAN_IDX | Switch Plan Index | SWMAN_SHEET.SWITCH_SHEET_IDX | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
DURATION | Duration | Populated with the difference (in minutes) between Switch Plan Begin Date and End Date. (Switch Plan End - Switch Plan Begin) * 1440 | |
NBR_OF_STEPS | Number of Steps | Populated with the number of steps present in SWMAN_STEP (SWMAN_STEP.COUNT(*)) for a switch plan class and index. | |
NBR_OF_FAILED_STEPS | Number of Failed Steps | Populated with the number of failed switching plan steps. | |
NBR_OF_ABORTED_ STEPS | Number of Aborted Steps | Populated with the number of aborted switching plan steps. | |
NBR_OF_SAFETY_DOCS | Number of Safety Documents | SWMAN_SAFETY_DOCS.ID | |
Degenerate Dimensions | |||
BEGIN_DTTM | Begin Date/Time | Switch Plan Begin.WHEN_ISSUED | |
END_DTTM | End Date/Time | Switch Plan End.WHEN_EXECUTED | |
FINISH_DTTM | Finish Date/Time | SWMAN_SHEET.FINISH_DATE | |
Foreign Keys | |||
BEGIN_DATE_KEY | Begin Date Key | Populated based on the date part of switch plan begin WHEN_ISSUED. | |
BEGIN_TIME_KEY | Begin Time Key | Populated based on the time part of switch plan begin WHEN_ISSUED. | |
END_DATE_KEY | End Date Key | Populated based on the date part of Switch Plan End.WHEN_EXECUTED. | |
END_TIME_KEY | End Time Key | Populated based on the time part of Switch Plan End.WHEN_EXECUTED. | |
FINISH_DATE_KEY | Finish Date Key | Populated based on the date part of SWMAN_SHEET.FINISH_DATE. | |
FINISH_TIME_KEY | Finish Time Key | Populated based on the time part of SWMAN_SHEET.FINISH_DATE. | |
SW_PLAN_KEY | Switch Plan Key | Populated based on: SWMAN_SHEET.SWITCH_SHEET_CLS SWMAN_SHEET.SWITCH_SHEET_IDX | |
SW_PLAN_STATE_KEY | Switch Plan State Key | Populated based on SWMAN_SHEET.STATE_KEY. | |
USER_KEY1 | User1 | Populated with ‘0’. | |
USER_KEY2 | User2 | Populated with ‘0’. | |
USER_KEY3 | User3 | Populated with ‘0’. | |
USER_KEY4 | User4 | Populated with ‘0’. | |
USER_KEY5 | User5 | Populated with ‘0’. | |
USER_KEY6 | User6 | Populated with ‘0’. | |
USER_KEY7 | User7 | Populated with ‘0’. | |
USER_KEY8 | User8 | Populated with ‘0’. | |
CTRL_ZONE_ KEY | Control Zone Dimension Surrogate Key | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. | |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | Populate from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension. | |
SW_PLAN_UDD1_KEY | Switch Plan User Defined Dimension 1 Surrogate Key | ||
SW_PLAN_UDD2_KEY | Switch Plan User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Switch Plan Segmentation
Captures the customer related outage information at a switch plan and feeder level for switch plan segments.
A switch plan is associated to multiple events (outage associated to the switch plan). It is a time period created using the earliest starting time of the outages associated with the switch plan and updated when the number of customers out due to an outage changes due to the outage restoration or due to a new outage associated to the switch plan.
This fact stores the outage metrics for switch plan computed for every such segment. It is dependent on the Customer Outage fact (CF_CUST_RST_OUTG). It should be processed when the Outage fact is processed. Since this fact stores information at a switch plan level and at a feeder level, all the measures need to be computed at the switch plan and feeder level.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_SW_PLAN_SEG |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | SWMAN_SHEET B1_BR_SW_PLAN_JOBS CF_CUST_RST_OUTG |
ODI Package Name | B1_PKG_CF_SW_PLAN_SEG |
ETL View Name | B1_F_SW_PLAN_SEG_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
SW_PLAN_SEG_KEY | Switch Plan Segmentation Key | Populated with the sequence from B1_SW_PLAN_SEG_SEQ. | |
Natural Keys | |||
SRC_SW_PLAN_CLS | Switch Plan Class | SWMAN_SHEET.SWITCH_SHEET_CLS | |
SRC_SW_PLAN_IDX | Switch Plan Index | SWMAN_SHEET.SWITCH_SHEET_IDX | |
SRC_NCG_ID | Control Zone ID | CD_CTRL_ZONE.SRC_ NCG_ID | Populated based on the FEEDER_KEY from Customer Outage fact. |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
SEG_SEQ | Segmentation Sequence | Populated as a sequence value (beginning with 1) for all segments of the switch plan. | |
NUM_NEW_CUST_OUT | Number of New Customers Out | Retrieves the customer count impacted due to outage associated with the switch plan from the Customer Outage table. | |
NUM_CUST_RST | Number of Customers Restored | Retrieves the total count of customers restored for the outage associated with the switch plan. | |
NUM_CUST_REMAIN_ OUT | Number of Customers Remaining Out | Populated with the difference in the running sum of Customer Out (NUM_NEW_CUST_OUT) and the running sum of Customer Restored (NUM_CUST_RST). | |
Degenerate Dimensions | |||
SEG_START_DTTM | Segmentation Start Date Time | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with the start date and time of the new outage. |
SEG_END_DTTM | Segmentation End Date Time | CF_CUST_RST_OUTG.BEGIN_DTTM CF_CUST_RST_OUTG.RST_DTTM | Populated with the end date and time of the new outage. |
SW_PLAN_BEGIN_ DTTM | Switch Plan Begin Date Time | SWMAN_SHEET.START_DATE | Populated with SWMAN_SHEET. START_DATE. |
SW_PLAN_END_DTTM | Switch Plan End Date Time | SWMAN_SHEET.COMPLETED_DATE | Populated with SWMAN_SHEET.COMPLETED_DATE. |
Foreign Keys | |||
SW_PLAN_KEY | Switch Plan Key | SWMAN_SHEET.SRC_ SWITCH_SHEET_CLS SWMAN_SHEET.SRC_ SWITCH_SHEET_IDX | Populated based on: SWMAN_SHEET.SRC_SWITCH_SHEET_CLS SWMAN_SHEET.SRC_SWITCH_SHEET_IDX |
FEEDER_KEY | Feeder Key | CF_CUST_RST_OUTG. FEEDER_KEY | Populated with FEEDER_KEY from the Customer Outage fact. |
CTRL_ZONE_KEY | Control Zone Key | CF_CUST_RST_OUTG.CTRL_ZONE_KEY | Populated with CTRL_ZONE_KEY from the Customer Outage fact. |
SW_PLAN_STATE_KEY | Switch Plan State Key | SWMAN_SHEET.STATE_KEY | Populated based on SWMAN_SHEET.STATE_KEY. |
SW_PLAN_BEGIN_DATE_KEY | Switch Plan Begin Date Key | Populated based on the date part of the start of the switch plan. | |
SW_PLAN_BEGIN_TIME_KEY | Switch Plan Begin Time Key | Populated based on the time part of the start of the switch plan. | |
SW_PLAN_END_DATE_KEY | Switch Plan End Date Key | Populated based on the date part of the end of the switch plan. | |
SW_PLAN_END_TIME_ KEY | Switch Plan End Time Key | Populated based on the time part of the start of the switch plan. | |
SEG_START_DATE_KEY | Segmentation Start Date Key | Populated based on the date part of segment start. | |
SEG_START_TIME_KEY | Segmentation Start Time Key | Populated based on the time part of segment start. | |
SEG_END_DATE_KEY | Segmentation End Date Key | Populated based on the date part of segment end. | |
SEG_END_TIME_KEY | Segmentation End Time Key | Populated based on the time part of segment end. | |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | CF_CUST_RST_OUTG.CTRL_ZONE_SEC_KEY | Populated with CTRL_ZONE_KEY from the Customer Outage fact. |
SW_PLAN_SEG_UDD1_ KEY | Switch Plan Segmentation User Defined Dimension 1 Surrogate Key | ||
SW_PLAN_SEG_UDD2_ KEY | Switch Plan Segmentation User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |
Switch Plan State
Captures details about the crews working on jobs with measures, such as in-route duration, work duration, and dispatch duration.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_SW_PLAN_STATE |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table Name | SWMAN_AUDIT_LOG SAL1 |
ODI Package Name | B1_PKG_CF_SW_PLAN_STATE |
ETL View Name | B1_F_SW_PLAN_STATE_VW |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
SW_PLAN_STATE_F_ KEY | Switch Plan State Fact Surrogate Key | Populated with the sequence from SPL_SW_PLAN_STATE_ SEQ. | |
Natural Keys | |||
SRC_LOG_ENTRY | Switch Plan Log Entry ID | SWMAN_AUDIT_LOG. SEQ_LOG_ID | |
Measures | |||
FACT_CNT | Count | Populated with the standard value of ‘1’. | |
STATE_DURATION | Duration of the Switch Plan State | ||
Degenerate Dimensions | |||
BEGIN_DTTM | Begin Date/Time | SWMAN_AUDIT_LOG. INSERT_TIME | |
END_DTTM | End Date/Time | SWMAN_AUDIT_LOG. INSERT_TIME | Populated with the difference between SWMAN_AUDIT_LOG.INSERT_TIME when state change happens * 1440. |
Foreign Keys | |||
BEGIN_DATE_KEY | Begin Date Key | Populated based on the switch plan begin date. | |
BEGIN_TIME_KEY | Begin Time Key | Populated based on the switch plan begin time. | |
END_DATE_KEY | End Date Key | Populated based on the switch plan end date. | |
END_TIME_KEY | End Time Key | Populated based on the switch plan end time. | |
SW_PLAN_KEY | Switch Plan Key | Populated based on jobs SWSHEET_IDX and SWSHEET_CLS. | |
SW_PLAN_STATE_KEY | Switch Plan State Key | Populated based on the switch plan state key. | |
USER_KEY | User Key | Populated based on the CES user name. | |
CTRL_ZONE_ KEY | Control Zone Dimension Surrogate Key | Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table. | |
CTRL_ZONE_SEC_KEY | Control Zone Sec Dimension Surrogate Key | Populated from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension. | |
SW_PLAN_STATE_UDD1_KEY | Switch Plan State User Defined Dimension 1 Surrogate Key | ||
SW_PLAN_STATE_UDD2_KEY | Switch Plan State User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CES_PARAMETER. VALUE | Populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the source system. |
JOB_NBR | Job Number | Populated with the ODI job execution session number. | |