Interval Data File Specifications

This section describes the distinct data elements that must be included in the interval data file that you send to Oracle Utilities. Additionally it describes how to handle time fields, daylight saving time, and file format requirements, and file transfer and size limits.

On this page:

Field Descriptions

The following table defines the elements that should be provided in the columns of the interval usage data file.

Field	Description
service_point_id	Utility’s unique identifier for the service point. This is the same as the `service_point_id` in the billing data files. See Interval Data Definitions for more information. Type: VARCHAR. Can Be Empty?: No. Default: Not applicable.
usage_value	Usage value amount based on meter read in units determined by meter's units. Type: Number. Can Be Empty?: No. Default: Not applicable.
date	End date of meter read interval. Type: VARCHAR (yyyyMMdd). Can Be Empty?: No. Default: Not applicable.
time	Time representation in 24-hour format in the local time zone. The timestamp represents the end of the read, not the beginning of the read. The following time intervals are supported: 5 minute, 15 minute, 30 minute, 60 minute, and 1440 minute (one day). Times should always be in fixed interval periods, and the intervals within the file should always represent the same length of time. For example, within an hourly interval data file, valid interval values are `0000` through `2300`, all ending in `00`. However, for daily interval files denoting an interval of 24 hours, the only valid interval value is `0000`. If you cannot provide ending times, contact your Delivery Team. For more examples on how you can specify the time field, see Time Field Examples below. For guidance on how to handle daylight saving time, see Daylight Saving Time below. Type: VARCHAR (HHmm). Can Be Empty?: No. Default: Not applicable.
units	Unit of measurement for the usage value. Supported Values: A Amps C Celsius CCF Hundred Cubic Feet CF Cubic Feet CGL Hundred Gallons CT Count F Fahrenheit GAL Galllons KL Kilolitres KVA Kilovolt-Ampere KVAH Kilovolt-Ampere Hours KVAR Kilovolt-Ampere Reactive KVARH Kilovolt-Ampere Reactive Hours KW Kilowatt KWH Kilowatt-Hours L Litres M3 Cubic Meter MCF Thousand Cubic Feet MGL Mega Gallons ML Megalitres MWH Megawatt-Hours PERC Percentage PF Power Factor TH Thermal Units THERM Thermal Units V Volts VA Volt-Ampere VAH Volt-Ampere Hours VAR Volt-Ampere Reactive VARH Volt-Ampere Reactive Hours W Watt WH Watt-Hours Type: VARCHAR. Can Be Empty?: No. Default: Not applicable.
is_estimate	A flag to indicate whether the usage read is estimated or not. Supported Values: A Actual read by utility. E Estimated read. Type: VARCHAR. Can Be Empty?: No. Default: E
UTC_offset	Provide the offset from UTC time for the `service_point_id` in +/-HHmm format. For example, send `-0800` for a `service_point_id` that is 8 hours and 0 minutes behind UTC, or `+0100` for a `service_point_id` that is one hour and 0 minutes ahead of UTC. For the two calendar days that are affected by daylight saving time (DST), send each interval with a DST offset to represent the amount of time lost or gained by the DST event. See Daylight Saving Time below for more information and examples. Type: VARCHAR. Can Be Empty?: No. Default: Not applicable.
service_type	The service type provided by service points of this type. Allowed Values: E Electric G Gas W Water M Miscellaneous WW Waste water O Other Type: VARCHAR. Can Be Empty?: Yes. Default: Not applicable.
service_quantity_identifier	An identifier for distinguishing between measured quantities that have identical unit of measure (UOM) combinations. Example Values: ADU Average Daily Usage COIN Coincident CONSUMED Consumed DELIVERED Delivered NET_USAGE Net Usage GENERATED Generation OFFDMD Contract Off Peak Demand RECEIVED Received Type: VARCHAR. Can Be Empty?: Yes. If this field is left blank, then it will be assumed that the usages represent `NET_USAGE` values. Default: `NET_USAGE`

Time Field Examples

The following table describes several timestamp scenarios that may be used with the time field in the interval usage data file. The scenarios described represent data from meters with 15 minute intervals:

The first interval of the day such as the interval between midnight and 00:15
A second interval during the day such as the interval between 11:45 and noon
The final interval in the day, for example the interval between 23:45 and midnight. (Note that intervals that cover the end of the day end at the beginning of the next day, or 00:00.)

service_point_id	usage_value	date	time	units	is_estimate	utc_offset
12345	10	20140101	0015	KWH	A	-0400
12345	5	20140101	1200	KWH	A	-0400
12345	7	20140102	0000	KWH	A	-0400

Daylight Saving Time

For utilities participating in daylight saving time (DST), two days in the calendar year require additional usage value rules: DST-forward when the time is reset one hour later, and DST-backward when the time is reset one hour earlier.

Warning: It is important to send daylight saving time and UTC offset data to Oracle Utilities as described. Otherwise, confusing information may display in the user interface of certain product features (most notably the Digital Self Service Data Browser) that depend on AMI data reads. If you have questions about daylight saving time, or if there are limitations in what your system can handle, contact your Delivery Team to discuss options.

Note: Oracle Utilities does not support user-configurable DST settings. DST is calculated using standardized calendars.

DST-Forward

In the US, on DST-forward days the 2 a.m. hour is skipped, and the hour after 1 a.m. is 3 a.m. For a 15-minute interval file, the end time should go from 0145 to 0300, and the utc_offset for 0300 should be reduced by one hour.

See the table below for an example of DST-forward. In the last row, the time value goes from 0145 to 0300 to indicate a shift forward due to daylight saving time. Notice that the utc_offset field value changes from -0600 to -0500 to adjust for this change.

service_point_id	usage_value	date	time	units	is_estimate	utc_offset
12345	10	20190310	0115	KWH	A	-0600
12345	9	20190310	0130	KWH	A	-0600
12345	9	20190310	0145	KWH	A	-0600
12345	5	20190310	0300	KWH	A	-0500

Note: In a standard 24-hour period, there are 96 reads of fifteen minutes. On DST-forward days, there will be 92 reads since the four reads in the 0200 hour will be skipped. These 92 reads will be displayed in the By day view of the Digital Self Service Data Browser.

DST-Backward

In the US, on DST-backward days the 1 a.m. hour is repeated, and there are two hours between 1 a.m. and 2 a.m. Therefore, there should be two sets of reads between 1 a.m. and 2 a.m. For a 15-minute interval file, the end time should fall back from 0145 to 0100, and the second utc_offset for 0100 should be increased by one hour.

See the table below for an example of DST-backward. In the sixth row, the time field value goes from 0145 to 0100 to indicate a fallback due to daylight savings time. Notice that the utc_offset field value changes from -0500 to -0600 to adjust for this change.

service_point_id	usage_value	date	time	units	is_estimate	utc_offset
12345	10	20181104	1245	KWH	A	-0500
12345	10	20181104	0100	KWH	A	-0500
12345	6	20181104	0115	KWH	A	-0500
12345	5	20181104	0130	KWH	A	-0500
12345	5	20181104	0145	KWH	A	-0500
12345	5	20181104	0100	KWH	A	-0600
12345	3	20181104	0115	KWH	A	-0600
12345	2	20181104	0130	KWH	A	-0600
12345	1	20181104	0145	KWH	A	-0600
12345	1	20181104	0200	KWH	A	-0600

Note: In a standard 24-hour period, there are 96 reads of fifteen minutes. On DST-backward days, there will be 100 reads since the four reads in the 0100 hour will be repeated. These 100 reads will be displayed in the By day view of the Digital Self Service Data Browser.

File Format

The following conventions apply to all files generated for transfer:

Oracle Utilities prefers to receive all data as tab-separated value (TSV) files. CSV format is also supported.
Files must use UTF-8 encoding so that both Latin and non-Latin characters can be supported.
Some fields must contain English values. These are fields that have an enumerated set of expected values. For example, the customer_type field must contain the values AGRICULTURAL, RESIDENTIAL, SMB, OTHER, or INDUSTRIAL. These values must not be translated as the Oracle Utilities Opower system expects these exact strings.
All files should include a header line containing column names from the tables specified in this document.
Rows can use "line feed" and "carriage return / line feed" as valid end-of-line separators.
All data fields specified in this document must be included in each file that is generated.
Reads within the files should be sorted according to service_point_id, date, and time for fastest processing.
Files and interval reads will be processed in the order that they are received. If multiple files are received within the same timeframe, the files will be sorted according to name prior to loading.
If duplicate records (same service_point_id, date, time) are received in the same file, Oracle Utilities will keep and use the last row received.
Do not remove columns or add columns to the middle of the file.
It is strongly recommended that files be compressed for transfer. Zip and gzip compression are supported, but gzip is strongly preferred. Files can be sent uncompressed, but compressing the files will greatly decrease the transfer time. Zip files must contain only one file each. There should be no directories in the zip file.

File Transfer Limit

There is a limit to the file size and file count that the Oracle Utilities Opower data ingest system can process in a day. See File Transfer Limits for details.