NMS Data Quality

The NMS installation provides a data qualification script that can be run before attempting to load historical data to an external system. This script checks for some of the most common data issues and mismatches in the NMS system and lists the errors in your data.

To output the data validation to a file (dq.out), run:

ISQL < ${NMS_SQL_FILES}/OUA_Data_Qualification.sql 2>&1 | tee dq.out

Follow the output to correct any data issues, or they will potentially generate millions of errors and slow down your initial load.

Some of the most common errors are:

• Mismatches between Customers and events or calls.

This can happen if you ran your NMS before history was added to the customer records. The CES_CUSTOMERS_HISTORY defaults to a birth date of January 1, 2000, but is sometimes changed by the project. The CU_CUSTOMERS, CU_SERVICE_POINTS, CU_SERVICE_LOCATIONS, and CU_METERS may all have different birth times, since those are populated from an external system.

Event and call history, therefore, may reference customers that are not listed as active in one or more of these tables. Either import the correct historical customer data from an external system, or back-date the first CES_CUSTOMERS_HISTORY and CU_% table records' birth dates to the time of the earliest event or call.

• Mismatches between control zones and events or calls.

This can happen if you ran your NMS before history was added to the control zones tables. In the unlikely event you have control zone history in an external system, update inactive records in CONTROL_ZONES and CONTROL_ZONE_STRUCTURES to match, or back-date the birth dates for the first records.

• Mismatches between the CES_CUSTOMERS_HISTORY table and CU_CUSTOMERS and related tables.

This can happen if the CU_CUSTOMERS, CU_METERS, CU_SERVICE_POINTS, and the CU_SERVICE_LOCATIONS tables were created before or after the CES_CUSTOMERS_HISTORY records, or if the CES_CUSTOMERS table is created using unorthodox customer mapping.

It is expected that:

• CES_CUSTOMERS.cust_id matches CU_CUSTOMERS.cust_id

• CES_CUSTOMERS.meter_id matches CU_METERS.meter_id

• CES_CUSTOMERS.serv_loc_id matches CU_SERVICE_LOCATIONS.serv_loc_id

These three columns match the corresponding CU_SERVICE_POINTS fields.

• CES_CUSTOMERS.account_number matches CU_SERVICE_LOCATIONS.serv_account_number