Preparing the NMS Model for Oracle Utilities Customer Self Service
The Oracle Utilities Customer Self Service application reads NMS materialized views to display NMS data. These materialized views are created in the [project]‑CSS‑setup script and refreshed using the [project]‑CSS‑refresh script. Note that the user_sdo_geom_metadata table needs to be updated for the new materialized views with the source table rows’ diminfo and srid values.
Materialized Views
GEOGRAPHIC_OUTAGES
The GEOGRAPHIC_OUTAGES materialized view is created from the JOBS, DIAGRAM_OBJECTS, and the NETWORK_COMPONENTS tables.
Column Name | Data Type | Description |
|---|---|---|
outage_type | VARCHAR | A description of the outage type. "Probable Service Outage", as mapped from the JOBS.status |
num_customers_out | NUMBER | The number of customers out, as mapped from the JOBS.user_cust_out. |
begin_time | DATE | The outage begin time, from JOBS.begin_time. |
est_rest_time | DATE | The outage estimated restore time, from JOBS.est_rest_time. |
last_update_time | DATE | The outage last updated time, from JOBS.last_update_time. |
cause | VARCHAR | The outage cause, if available. |
geometry | MDSYS.SDO_GEOMETRY | The geographic geometry, from DIAGRAM_OBJECTS.geo_geometry. |
GEOGRAPHIC_OUTAGE_AREAS
The GEOGRAPHIC_OUTAGE_AREAS materialized view is created from the zip_codes, jobs, supply_node_log, and customer_sum tables.
Column Name | Data Type | Description |
|---|---|---|
area | VARCHAR | The name of the outage area, from the CUSTOMER_SUM.zip_code, CUSTOMER_SUM.City_State, or CUSTOMER_SUM.User_Geographic_Loc. |
area_type | VARCHAR | Area type choice; for example, zip code, county, city. |
cust_served | NUMBER | The number of customers in the area, as summed from CUSTOMER_SUM.customer_count for that area. |
cust_out | NUMBER | The number of customers out in the area, as summed from CUSTOMER_SUM.customer_count for the supply_nodes with outage, |
num_outages | NUMBER | The number of distinct active JOBS table records. |
earliest_begin_time | DATE | The earliest outage begin time in the area, from the JOBS.begin_time. |
latest_est_rest_time | DATE | The last JOBS.est_rest_time for the area. |
last_update_time | DATE | The last JOBS.last_update_time for the area. |
geometry | MDSYS.SDO_GEOMETRY | The ZIP_CODES.geometry for the area. |
GEOGRAPHIC_OUTAGE_STATUS
The materialized view GEOGRAPHIC_OUTAGE_STATUS is created from the JOBS table.
Column Name | Data Type | Description |
|---|---|---|
report_date | DATE | The date this view was created – SYSDATE. |
cust_served | NUMBER | The sum of all CUSTOMER_SUM.customer_count records. |
cust_out | NUMBER | The sum of all active JOBS.user_cust_out records. |
num_outages | NUMBER | The number of distinct active JOBS table records. |
GEOGRAPHIC_OUTAGE_AREAS_D
The GEOGRAPHIC_OUTAGE_AREAS_D materialized view includes event details for each event in an area.
Column Name | Data Type | Description |
|---|---|---|
area | VARCHAR | The name of the outage area, from the CUSTOMER_SUM.zip_code, CUSTOMER_SUM.City_State, or CUSTOMER_SUM.User_Geographic_Loc. |
area_type | VARCHAR | Area type choice; for example, zip code, county, city. |
event_id | NUMBER | The event id. |
outage_type | VARCHAR | A description of the outage type. "Probable Service Outage", as mapped from the JOBS.status. |
cust_out | NUMBER | The sum of all active JOBS.user_cust_out records. |
begin_time | DATE | The outage begin time, from JOBS.begin_time. |
est_rest_time | DATE | The outage estimated restore time, from JOBS.est_rest_time. |
last_update_time | DATE | The last JOBS.last_update_time for the area. |
Adding Outage Summary Spatial Landbase to the NMS Viewer
The Viewer has the ability to display Oracle Spatial data behind the Viewer maps:
Prerequisites to supporting this functionality include:
1. Setting up the outage summary spatial tables in the NMS database. (See Preparing the NMS Model for Oracle Utilities Customer Self Service. for details) Oracle Utilities Customer Self (CSS) Service is not required to enable this outage summary in the NMS Viewer, however, the setup of the outage summary spatial table is required.
2. Create an Oracle Spatial MapBuilder Basemap containing and reference map themes (roads, water features, political boundaries) and include the spatial tables from the previous set in themes. In the CSS setup process, you create a GEOGRAPHIC_OUTAGES table for outage location points and GEOGRAPHIC_OUTAGE_AREAS for outage summary by locations (i.e., zip codes, cities, counties,…). You will want to make reference to one or both those tables in your map themes. In the image above, the viewer is showing outage locations as colored and sized dots and it is showing outage zip code areas as shaded polygons and annotating the number of customers out I the middle of the polygon.
In Map Builder, here is the definition of the GEOGRAPHIC_OUTAGES theme:
and the style rules to shade the polygons based on the cust_out and to label them with cust_out:
and here is the style rules for dots based on num_customers out:
and here is the theme definition for the GEOGRAPHIC_OUTAGE_AREAS:
and the style rules to shade the polygons based on the cust_out and to label them with cust_out:
create a Basemap that incorporates one or both of the above themes:
3. Add your Oracle Spatial Basemap to the NMS Viewer Hide/Display configuration. This is described in the section "Oracle Spatial Server Connection - (Optional)" of the Oracle Utilities Network Management System Installation Guide.
OPAL has an example of this in its configuration. Follow the installation instructions in the Oracle Utilities Network Management System Installation Guide and include the following sections:
• Oracle Utilities Network Management System Validation Model Setup
• Oracle Spatial Server Connection - (Optional)
• Spatial Outage Summary Installation (Optional)
You will then have a running example of this functionality.