Customer Model - Logical Data Model

This section provides an overview of the logical view of the Oracle Utilities Network Management System customer model. Where the MultiSpeak data model uses Customer, Service Location and Meter entities, the Oracle Utilities Network Management System model adds the notion of a Service Point to increase flexibility, and provide for improved performance of the physical implementation. Additionally, the Oracle Utilities Network Management System model extends beyond the basic MultiSpeak model in the following ways:

• Supports more than one meter per service location.

• MultiSpeak attributes not required for NMS purposes are not required, such as billing information (acRecvBal, acRecvCur, … ) and meterology information (kwh, multiplier, …)

• Provides model extensions to support important attributes not currently defined by MultiSpeak but necessary for NMS purposes.

• Supports customer-defined attributes for read-only purposes with no requirement for use in analysis.

This model, when joined with the Supply Node information in the Oracle Utilities Network Management System database (supply_nodes), results in the following E-R diagram:

Residential Model

In this extended model, it is recognized that the occurrence of multiple meters is reasonably common, where each meter may have different rate codes associated.

Although the occurrence of multiple transformers is much less frequent than multiple meters, there are also several possible configurations of meters and transformers, with different electrical arrangements. Often, multiple transformers will occur on (geographically) large sites (for example, factory, airport, shopping mall, and so forth), where it is appropriate and helpful (from the perspective of outage analysis) to have multiple service locations defined for the site which aid in readily locating the appropriate transformer.

The following pictures depict some simple examples of the usage of this customer model. The first example shows two service locations, each with a meter connected to a distribution transformer.

The second example is an account with a single location with two meters, which is described through the definition of a customer account, a service location and two meters. The service location is associated with a distribution transformer.

A third example would be a combination of the two previous examples, where a single customer account was responsible for the billing related to all of the above service locations. A more sophisticated example of residential metering is provided in the appendix.

Commercial and Industrial (C & I) Model

Many Commercial and Industrial situations are more complicated than residential metering. In these cases, a variety of configurations of meters, transformers and circuits must be addressed. The variations include:

• Primary metering, where the meter is placed on the high side of the transformer

• Internal buses, where two transformers can be used with two meters, feeding an internal bus

• Alternate transformers, where a meter can be switched to one of two transformers, each on a different circuit

• A single transformer feeding two meters, where different rates apply to each meter

The following diagram illustrates these examples.

Customer Model Database Schemas

The following section provides schema descriptions for the data and tables that are relevant to the Customer Model. It should be noted that the naming convention used internally is slightly different than the convention used in MultiSpeak or CIM exchange formats, due to the case-insensitive nature of Oracle RDBMS.

Customer Model Database Tables

The purpose of this section is to provide descriptions of the data and tables that support the implementation of the Oracle Utilities Network Management System customer model. These descriptions address only the data elements that are relevant to the customer model. The actual database tables may contain additional fields, but the other fields are not relevant to the customer model and are not described here. These additional fields can be project-specific.

Req Key Values	Meaning	Comment
N	Not required	Not needed for standard ces_customers table.
C	Configured in standard ces_customers table.	Not all columns referenced in the ces_customers table are required for a given implementation - inclusion of some columns can be project-specific.
Y	Required	Used in standard ces_customers table – still may not be 100% required. Actual requirements are generally project specific.

Customers Table

The cu_customers table is used to manage customer accounts. While the primary key is cust_id, this typically may have the same value as account_number.

Req	Column Name	Data Type	Description
Y,C	cust_id	NUMBER NOT NULL,	Primary key – may be generated.
N	cust_account_number	VARCHAR2(30) NOT NULL	Customer account number.
N	cust_billing_account	VARCHAR2(13) NULL	Customer billing account number.
Y,C	cust_name	VARCHAR2(90) NULL	Name of the customer; concatenation of last, first and middle names, or business name.
N	cust_last_name	VARCHAR2(30) NULL	Last name.
N	cust_first_name	VARCHAR2(30) NULL	First name. Typically, this is only populated for residential customers.
N	cust_middle_name	VARCHAR2(30) NULL	Middle name or initial.
Y,C	cust_home_ac	NUMBER(3) NULL	Phone area code for the home phone.
Y,C	cust_home_phone	NUMBER(7) NULL	Phone number for the home phone.
N	cust_day_ac	NUMBER(3) NULL	Phone area code for the work phone.
N	cust_day_phone	NUMBER(7) NULL	Phone number for the work phone.
N	cust_day_phone_ex	NUMBER(7) NULL	Typically, day phone numbers are related to customers’ work phone numbers, which generally include extensions.
N	cust_bill_addr_1	VARCHAR2(50) NULL	Street address of the billing address. Note that billing address fields are usually populated only if different from the address held in the cu_service_point table.
N	cust_bill_addr_2	VARCHAR2(50) NULL	Second line, if necessary, of street address of the billing address.
N	cust_bill_addr_3	VARCHAR2(50) NULL	Third line, if necessary, of street address of the billing address.
N	cust_bill_addr_4	VARCHAR2(50) NULL	Fourth line, if necessary, of street address of the billing address.
N	cust_bill_city	VARCHAR2(30) NULL,	City of the billing address.
N	cust_bill_state	VARCHAR2(30) NULL	State of the billing address.
N	cust_bill_postcode_1	VARCHAR2(10) NULL	First 5 zip code numbers for US.
N	cust_bill_postcode_2	VARCHAR2(10) NULL	Second 4 zip code numbers for US.
C	cust_name_initials	VARCHAR2(3) NULL	The customer initials. Possibly used for certain soundex type searching if a customer wants to enable it - not often. Not necessary.
N	cust_comment	VARCHAR2(255)NULL	General field provided to support additional information about the customer, such as 30ft ladder, assult-case, crit-pmp-station, etc.
N	cust_user_def_1	VARCHAR2(255) NULL	These user-defined fields support the inclusion of other desired data not covered in the core fields. These fields can be extracted for project specific reporting.
N	cust_user_def_2	VARCHAR2(255) NULL
N	cust_user_def_3	VARCHAR2(255) NULL
N	cust_user_def_4	VARCHAR2(255) NULL
N	last_update_time	DATE	Time of last update for record. Generally, set internally when a record is updated - not via external CIS.
Y	birth	DATE	Time the record was activated
Y	death	DATE	Time the record was deactivated
Y	to_die	VARCHAR(1)	(Internal use.) Used to flag whether the record is being given a death time.

Service Locations Table

The purpose of the cu_service_locations table is to manage locations (premises) at which a customer is served. A customer account may have multiple service locations.

Req	Column Name	Data Type	Description
Y,C	serv_loc_id	NUMBER NOT NULL	Primary key – may be generated.
N	serv_type	VARCHAR2(2) NULL	The type of service at this location. (electrical or gas). Only necessary for utilities that support multiple service types.
N	serv_status	VARCHAR2(50) NULL	Electrical service status of the service location. For example: INA – Inactive ACT – Active PDI – Pending Disconnect Can be used to coordinate business processes around how to handle customer disconnects (for example, update the day before). Each project needs to discuss these.
Y,C	serv_account_number	VARCHAR2(30) NOT NULL	The service account number which will be used for call entry purposes, and the account number used in createIncident XML.
Y,C	serv_revenue_class	VARCHAR2(30) NULL	Revenue class for the service location.
N	serv_load_mgmt	NUMBER NULL	Binary - whether or not there is load management at this Service Location
Y,C	serv_concat_address	VARCHAR2(200) NULL	Concatenated address of the service address 1, 2, 3, and 4.
N	serv_special_needs	VARCHAR2(1) NULL	Identifies any special needs of the customer.
N	serv_priority	VARCHAR2(32) NULL	Mapped to ces_customers.priority. This defines the meaningful customer type value the utility uses internally. This value will be displayed on troubleInfo as well.
N	serv_addr_1	VARCHAR2(50) NULL	First line of street address of the service address.
N	serv_addr_2	VARCHAR2(50) NULL	Second line, if necessary, of street address of the service address.
N	serv_addr_3	VARCHAR2(50) NULL	Third line, if necessary, of street address of the service address.
N	serv_addr_4	VARCHAR2 (50) NULL	Third line, if necessary, of street address of the service address.
N	serv_city	VARCHAR2(25) NULL	City of the service location.
N	serv_state	VARCHAR2(25) NULL	State of the service location.
Y,C	serv_city_state	VARCHAR2(50) NULL	This field contains the data that will appear in the ces_customers.CITY_STATE field.
Y,C	serv_postcode_1	VARCHAR2(10) NULL	First 5 Zip Code numbers for US.
N	serv_postcode_2	VARCHAR2(10) NULL	Second 4 Zip Code numbers for US.
N	serv_user_geog_1	VARCHAR2(25) NULL	User geo codes typically used for political areas, such as counties, tax districts, etc.
N	serv_user_geog_2	VARCHAR2(25) NULL
Y,C	serv_town	VARCHAR2(3) NULL	The town or county for the customer.
Y,C	serv_str_block	VARCHAR2(20) NULL	Block number - used in searches.
N	serv_str_pfix	VARCHAR2(10) NULL	The 'R' in R 321 Rolling Rd (R rear, F front, A adjacent, etc.)
Y,C	serv_str_struc	VARCHAR2(20) NULL	Structure relates to apartments, units, piers, docks, warehouse, slip, etc.
N	serv_str_name	VARCHAR2(30) NULL	Name of the street (Main Street).
N	serv_str_cdl_dir	VARCHAR2(10) NULL	Cardinal direction (N, S, E, W).
N	serv_str_sfix	VARCHAR2(10) NULL	ST, PKY, PLC, DR, RD, AVE, etc.
Y,C	serv_lot	VARCHAR2(10) NULL	Lot number – used in searches.
Y,C	serv_apt	VARCHAR2(8) NULL	Apartment number.
N	serv_elec_addr	VARCHAR2(50) NULL	Elec address used in searches.
N	serv_sic	VARCHAR2(8) NULL	Standard Industrial Code.
N	serv_comment	VARCHAR2(255) NULL	General comment about the service location.
Y,C	serv_cumulative_priority	NUMBER NULL	Summation of priority codes for this location.
Y,C	serv_life_support	NUMBER NULL	Indicates if this is a life-support customer.
Y,C	serv_a_priority	NUMBER NULL	A customer defined flag, 0 or 1
Y,C	serv_b_priority	NUMBER NULL	B customer defined flag, 0 or 1
Y,C	serv_c_priority	NUMBER NULL	C customer defined flag, 0 or 1 – often emergency customers.
Y,C	serv_d_priority	NUMBER NULL	D customer defined flag, 0 or 1 – often medical customers.
Y,C	serv_e_priority	NUMBER NULL	E customer defined flag, 0 or 1 – often entertainment customers.
Y,C	serv_f_priority	NUMBER NULL	F customer defined flag, 0 or 1 – often fire customers.
Y,C	serv_g_priority	NUMBER NULL	G customer defined flag, 0 or 1 – often government customers.
Y,C	serv_h_priority	NUMBER NULL	H customer defined flag, 0 or 1 – often hospital customers.
Y,C	serv_i_priority	NUMBER NULL	I customer defined flag, 0 or 1
Y,C	serv_j_priority	NUMBER NULL	J customer defined flag, 0 or 1
Y,C	serv_k_priority	NUMBER NULL	K customer defined flag, 0 or 1 – often keycustomers.
Y,C	serv_l_priority	NUMBER NULL	L customer defined flag, 0 or 1 – often life support customers.
Y,C	serv_m_priority	NUMBER NULL	M customer defined flag, 0 or 1 – often manufacturing customers.
Y,C	serv_n_priority	NUMBER NULL	N customer defined flag, 0 or 1 – often nursing home customers.
Y,C	serv_o_priority	NUMBER NULL	O customer defined flag, 0 or 1
Y,C	serv_p_priority	NUMBER NULL	P customer defined flag, 0 or 1 – often police customers.
Y,C	serv_q_priority	NUMBER NULL	Q customer defined flag, 0 or 1
Y,C	serv_r_priority	NUMBER NULL	R customer defined flag, 0 or 1
Y,C	serv_s_priority	NUMBER NULL	S customer defined flag, 0 or 1 – often sensitive customers.
Y,C	serv_t_priority	NUMBER NULL	T customer defined flag, 0 or 1 – often transportation customers.
Y,C	serv_u_priority	NUMBER NULL	U customer defined flag, 0 or 1
Y,C	serv_v_priority	NUMBER NULL	V customer defined flag, 0 or 1
Y,C	serv_w_priority	NUMBER NULL	W customer defined flag, 0 or 1
Y,C	serv_x_priority	NUMBER NULL	X customer defined flag, 0 or 1
Y,C	serv_y_priority	NUMBER NULL	Y customer defined flag, 0 or 1
Y,C	serv_z_priority	NUMBER NULL	Z customer defined flag, 0 or 1 – often 911 customers.
Y,C	serv_map_loc_x	NUMBER NULL	GPS lat/long or other mapping coordinates.
Y,C	serv_map_loc_y	NUMBER NULL	GPS lat/long or other mapping coordinates.
N	serv_user_def_1	VARCHAR2(255) NULL	These user-defined fields support other desired data not covered in the core fields. These fields can be extracted for project-specific reporting purposes.
N	serv_user_def_2	VARCHAR2(255) NULL
N	serv_user_def_3	VARCHAR2(255) NULL
N	serv_user_def_4	VARCHAR2(255) NULL
N	last_update_time	DATE	Time of last update for record.
Y	birth	DATE	Time the record was activated
Y	death	DATE	Time the record was deactivated
Y	to_die	VARCHAR(1)	(Internal use.) Used to flag whether the record is being given a death time.

Meters Table

The cu_meters table describes meters that might exist at a service location. The use of meters is optional (but increasingly common) within Oracle Utilities Network Management System. Meter information is required for a project which intends to utilize integration with an Automated Meter Reading Infrastructure.

The cu_service_points table tracks the relationship between a meter (cu_meters) and a customer account (cu_customers) and service location (cu_service_locations).

Req	Column Name	Data Type	Description
Y,C	meter_id	NUMBER NOT NULL	Primary key – may be generated.
Y,C	meter_no	VARCHAR2(20) NOT NULL	Meter number.
N	meter_serial_number	VARCHAR2(20) NULL	Serial number on the meter.
N	meter_type	VARCHAR2(20) NULL	Type of meter (gas, electric, water, etc.).
N	meter_manufacturer	VARCHAR2(20) NULL	Manufacturer of the meter.
N	meter_phases	VARCHAR2(1) NULL	Phase(s) connected to the meter (IE 1, 2, or 3).
N	meter_rate_code	VARCHAR2(65) NULL	Rate code for the meter.
N	meter_user_def_1	VARCHAR2(255) NULL	These user-defined fields support other desired data not covered in the core fields.These fields can be extracted for project-specific reporting purposes.
N	meter_user_def_2	VARCHAR2(255) NULL
N	meter_user_def_3	VARCHAR2(255) NULL
N	meter_user_def_4	VARCHAR2(255) NULL
N	last_update_time	DATE	Time of last update for record.
Y	birth	DATE	Time the record was activated
Y	death	DATE	Time the record was deactivated
Y	to_die	VARCHAR(1)	(Internal use.) Used to flag whether the record is being given a death time.
N	bellwether	VARCHAR2(1) NULL	Bellwether meter flag (Y/N).
N	meter_amr_enabled	VARCHAR2(1) NULL	AMI-enabled meter flag (Y/N).
N	voltage_threshold	NUMBER NULL	Low voltage threshold.
N	nominal_voltage	NUMBER NULL	Meter nominal voltage.

Account Type Table

The purpose of the cu_account_type table is to contain a configuration of the valid Account Types that can be specified for a Service Point record. The initial loading of customer data populates this table. There is often only one row in this table (for electrical service).

cu_account_type
Column Name	Data Type	Description
acctyp_account_type	VARCHAR2(10) NOT NULL	Electric, Gas, Propane, Appliance Repair, etc.
acctyp_user_def_1	VARCHAR2(255) NULL	These user-defined fields support other desired data not covered in the core fields. These fields can be extracted for project-specific reporting purposes.
acctyp_user_def_2	VARCHAR2(255) NULL
acctyp_user_def_3	VARCHAR2(255) NULL
acctyp_user_def_4	VARCHAR2(255) NULL

Service Points Table

The purpose of the cu_service_points table is to manage the linkages between the cu_customers, cu_service_locations, cu_meters, cu_account_type and supply_nodes tables.

Key indexes are placed on this table for performance. History can be tracked, by setting active_fl to 'N' to identify that a record is now historical. No timestamp is used to track when a service point went out of service and the cu_service_points table is not intended nor recommended as a long term repository for service point history.

Req	Column Name	Data Type	Description
Y,C	serv_point_id	VARCHAR2(64) NOT NULL	Primary key. If the CIS cannot provide a unique value, use a generated key (for example, by combining cust_id, serv_loc_id and meter_id columns). This is used for CIS-to-NMS integration. For Customer Care & Billing (CC&B) integration in Oracle Utilities Network Management System 1.10, this is the CC&B Service Point Id. (See below for related info on ces_customers).
Y,C	cust_id	NUMBER NOT NULL	Foreign key ref to the cu_customers table.
Y,C	serv_loc_id	NUMBER NOT NULL	Foreign key ref to the cu_service_locations table.
Y,C	meter_id	NUMBER NOT NULL	Foreign key ref to the cu_meters table.
Y,C	device_id	VARCHAR2(25) NOT NULL	Foreign key ref to the supply_nodes table. This field is critical and necessary, as it ties Oracle Utilities Network Management System to the CIS.
N	feeder_id	VARCHAR2(10) NULL	Foreign key ref to the supply nodes table. Note this field is non-critical and generally not necessary.
Y,C	active_fl	VARCHAR2(1) NOT NULL	Identifies currently active records. Generally, this is always ‘Y’ as there is little provision or need for inactive records in the system. Inactive records are generally removed from this table.
N	create_dttm	DATE NOT NULL,	Timestamp for the record's creation.
Y,C	account_type	VARCHAR2(10) NOT NULL	Foreign key to the cu_account_type table.
N	last_update_time	DATE	Time of last update.
Y	birth	DATE	Time the record was activated
Y	death	DATE	Time the record was deactivated
Y	to_die	VARCHAR(1)	(Internal use.) Used to flag whether the record is being given a death time.

DERs Table

The purpose of the cu_ders table is to manage the distributed energy resources (DERs) that may be connected to a meter. The use of this table is optional depending on whether distributed energy resources are present on the associated distribution network. The information stored within this table can be used within the Trouble Info Customer List to display relevant information related to DERs associated to a particular customer. If distributed energy resources do not exist within the GIS data, this table can be used by the model preprocessing to artificially inject these devices into the NMS model. The preprocessing can determine the service transformer associated to the DER and then add a DER of the indicated technology type to the NMS model. .

Req	Column Name	Data Type	Description
Y,C	der_id	NUMBER NOT NULL	Primary key – may be generated.
Y,C	der_name	VARCHAR2(80) NOT NULL	Name of the DER.
Y,C	meter_id	NUMBER NOT NULL	The corresponding ID of the connected meter.
N	der_type	VARCHAR2(30) NULL	Technology type for the DER (PV, wind, etc.).
N	der_phases	VARCHAR2(1) NULL	DER phasing.
N	rated_size	VARCHAR2(30) NULL	The size of the DER in kVA.
N	nominal_voltage	NUMBER NULL	The nominal voltage value of the DER.
N	der_user_def_1	VARCHAR2(255) NULL	These user-defined fields support other desired data not covered in the core fields.These fields can be extracted for project-specific reporting purposes.
N	der_user_def_2	VARCHAR2(255) NULL
N	der_user_def_3	VARCHAR2(255) NULL
N	der_user_def_4	VARCHAR2(255) NULL
N	last_update_time	DATE	Time of last update for record.
Y	birth	DATE	Time the record was activated
Y	death	DATE	Time the record was deactivated
Y	to_die	VARCHAR(1)	(Internal use.) Used to flag whether the record is being given a death time.

Linkages to Other Tables

The customer model has linkages to other tables in the Oracle Utilities Network Management System model. The primary linkage between utility customers and the Oracle Utilities Network Management System electrical network model is the device_id column. The definitive table linkage is between supply_nodes.device_id and cu_service_points.device_id. From the perspective of the cu_service_points table, the device_id field is used to uniquely identify the electrical network model element (supply node) which supplies power to a service point (customer).

In general, an Oracle Utilities Network Management System supply node is any place on the model where a utility customer can be connected to receive electrical power. For customers that wish to model secondary network, this supply point can be associated with a single customer/meter. For customers that are only interested in modeling primary distribution circuits, the supply node is often associated with a secondary transformer.

The Oracle Utilities Network Management System electrical data model is implemented under the assumption that the source for the electrical network model data (generally a Geographic Information System) and the source for the utility customer data (generally a Customer Information System) understand and maintain this customer-to-supply-node relationship. The accuracy of this linkage is critical for reliable trouble call handling and outage reporting. Without this linkage, customer trouble calls enter the system as fuzzy calls and outage reports have diminished accuracy.