Measurement Data Export

Export of data from the Measurement maintenance object is supported via "Specialized" export.  See  Specialized Data Export  in the Application Framework Administrative User Guide for more information about specialized data export.

The Measurement maintenance object is configured to support data export via the following options:

Option Type

Option Value

Data Export Class

F1SE (Specialized)

Export Batch Control

D1-SMDEC (Specialized Measurement Data Extract Current)

Initial vs. Current vs. Historical Extract

There are three types of extracts used with the Measurement maintenance object:

  • Initial Extracts gather an initial extract of measurement data, controlled by providing a start and end date time of the measurements that should be extracted. This process should be used to perform the initial extract of the measurement data prior to using the daily current and daily historical extracts.
  • Daily Current Extracts gather measurement data for measurements within the last 30 days that have been either added or changed. This extract is based on the Measurement Date/Time and Last Update Timestamp fields on the Measurement table, as well as the Export Through Date/Time field on the Data Export Control record (see below) to include only changes since the last extract.
  • Daily Historical Extracts gather measurement data for measurements older than the last 30 days that have either been added or changed. To avoid having to scan the entire Measurement table, this extract is based on Change Data Capture.  During initial measurement data processing if an initial measurement has measurements that are older than 30 days, then a Change Data Capture record is created in the General Process table.
    Enabling historical change data capture requires configuration of the following algorithm parameters:
    • Capture Historical Measurement Changes: true
    • Historical Measurement Data Extract Batch Control: D1-SMDEH (Specialized Measurement Data Extract Historical)
    These parameters should be configured as described on the following algorithms:
    • Update Latest Measurement Date/Time on MC with Consumption Sync (D1-UPD-DTMC)
    • Update Latest Measurement Date/Time on Scalar MC with Consumption Sync (D1-UDTSCMCRE)
    • Update Latest Date/Time on MC from a Future Measurement (D1-UPD-DTMCF)
    • Update Latest Date/Time on Scalar Measuring Component (D1-UPDDTSCMC)
    • Re-derive other values (D1-REDEROVAL)
Note: This use of Change Data Capture is an exception to the general rule described in the  Specialized Data Export  section of the Application Framework Administrative User Guide.

Data Export Control Records

Data export from the Measurement maintenance object requires a Data Export Control record. This record designates that the Measurement maintenance object should be exported, and tracks the date and time through which data has been exported so far, allowing subsequent export processes to only export incremental changes.

See  Defining Data Export Control in the Application Framework Administrative User Guide for more information about creating Data Export Control records.

Batch Controls

Export of data and creation of export files is performed by the following batch controls: 

  • Specialized Measurement Initial Extract (D1-SMDIE): Used for initial extract of measurement data. Unlike the current extract this batch is controlled directly by providing a start and end date time of the measurements that should be extracted. This process should be used to perform the initial extract of the measurement data prior to using the current extract (D1-SMDEC) and the historical extract (D1-SMDEH). For best performance it is recommended to break up the initial extract period into multiple smaller periods and to use multiple threads for each period. The extract will retrieve all measurements with a measurement date/time that is greater than the input start date/time and less than or equal to the input end date/time.
  • Specialized Measurement Data Extract Current (D1-SMDEC): Used for daily extracts of current (new) measurement data. This process extracts data that has changed since the last extract process has run (based on the Export Through Date/Time field on the Data Export Control record. This process updates the Export Through Date/Time field at the start of batch processing to eliminate the possibility of not setting an initial measurement as historical by Change Data Capture during the processing window.
  • Specialized Measurement Data Extract Historical (D1-SMDEH): Used for daily extracts of historical measurement data. This process runs against Change Data Capture records in the General Process table.

Batch processing is threaded by on Measuring Component ID, and creates one or more files based on the number of threads processed and batch control parameters.

File Creation and Naming

The extract process uses two controls to determine the number of files to be created:

  1. Each thread will create at least 1 file (more threads result in smaller files).
  2. The File Size Limit parameter can be used to specify set the number of measurement rows to include in any given file. This can result in a single thread producing multiple files.

The data in each file is not ordered, each thread will retrieve measurement data for a set of Measuring Component IDs, but beyond that there will be no control for how the data is loaded into the file. 

File Naming

Since each thread can produce multiple files and the number of files per thread cannot be predicted the file names will also include an indicator for the first file in a thread and the last file in a thread.

  • MSRMT_​EXPORT_​<file prefix batch parameter>_​<batch number>_​<batch thread number>_​<total thread count>_​<thread file indicator>_​<process date time>.csv.gz

where:

  • <file prefix batch parameter> is an optional file prefix defined using the File Name Prefix batch control parameter
  • <batch number> is the batch number for the process
  • <batch thread number> is the thread number that created the file
  • <total thread count> is the total number of threads
  • <thread file indicator>: is an identified for the first and last files created by a thread, S for first file E for last file
  • <process date time>is the date and time at which the file was created, in YYYYMMDDhhmmss format (YYYY = year, MM = month, DD = day, hh = hour, mm = minute, ss = second)

Examples: For batch number 135, comprising 10 threads:

  • First file of 3rd out of 10 threads: MSRMT_​EXPORT_​PREFIX_​135_​3_​10_​S_​20191104173233.csv.gz
  • All files before the last file of 3rd out of 10 threads: MSRMT_​EXPORT_​PREFIX_​135_​3_​10_​20191104173233.csv.gz
  • Last file of 3rd out of 10 threads: MSRMT_​EXPORT_​PREFIX_​135_​3_​10_​E_​20191104173233.csv.gz

File Format

The extract process produces comma separated values (CSV) files with the data described in the following table. The field names in the Field column are included in the output file as a header row if the Include Header parameter on the batch controls is set to “Y”.
Note: All date/times will be converted from OUAF format to the appropriate ISO format in UTC with the time zone provided (as "Z").

Applicability

Field

Data Type

Sample Content

Description

Interval

Scalar

Subtractive Interval

MEASR_​COMP_​ID

CHAR(12)

000004769213

Primary Key of Channel from Meter (aka Device)

X

X

X

MSRMT_​DTTM

DATE

2019-01-01T00:00:00Z

End of the time period the measurement covers. For example, if this were an hourly interval it would cover 2018-12-31T23:00:00Z to 2019-01-01T00:00:00Z.

X

X

X

PREV_​MSRMT_​DTTM

DATE

2018-12-31T00:00:00Z

For scalar measurements this identifies the start of the period being measured. Note: there should be another measurement for the same MC with this date as the MSRMT_​DTTM.

X

MSRMT_​COND_​FLG

CHAR(6)

501000

Defines the quality of the measurement (e.g. estimated, regular, super).

Valid values defined on F1_​EXTENDABLE_​LOOKUP_​VAL for the BUS_​OBJ_​CD D1-MeasurementConditionLookup.

X

X

X

MSRMT_​USE_​FLG

CHAR(4)

D101

Indicates whether the measurement is usable.

Valid values defined on CI_​LOOKUP.

X

USER_​EDITED_​FLG

CHAR(4)

D1UE

Indicates whether the user has edited the measurement.

Valid values defined on CI_​LOOKUP.

X

X

X

READING_​VAL

NUMBER(16,6)

750000000

This is the Dial Reading which is used to calculate the consumed amount.

X

X

READING_​COND_​FLG

CHAR(6)

501000

Defines the quality of the Daily Reading (e.g. estimated, regular, super).

Valid values defined on F1_​EXTENDABLE_​LOOKUP_​VAL for the BUS_​OBJ_​CD D1-MeasurementConditionLookup.

X

COMBINED_​MULTIPLIER

NUMBER(18,6)

1

Contains the product of the Measuring Component Multiplier and the Installation Constant. The MSRMT_​VAL will have been multiplied by this number.

X

X

X

MSRMT_​VAL

NUMBER(16,6)

1.23

This is the consumption reported from the meter after going through VEE (aka billing ready).

X

X

X

MSRMT_​VAL1

NUMBER(16,6)

This and the subsequent measurement values (MSRMT_​VAL 1 through 10) are derived values that are computed from the core MSMRT_​VAL.

Examples of this are UOM conversion (CCF to THERM) or loss adjustment.

X

X

X

MSRMT_​VAL2

NUMBER(16,6)

X

X

X

MSRMT_​VAL3

NUMBER(16,6)

X

X

X

MSRMT_​VAL4

NUMBER(16,6)

X

X

X

MSRMT_​VAL5

NUMBER(16,6)

X

X

X

MSRMT_​VAL6

NUMBER(16,6)

X

X

X

MSRMT_​VAL7

NUMBER(16,6)

X

X

X

MSRMT_​VAL8

NUMBER(16,6)

X

X

X

MSRMT_​VAL9

NUMBER(16,6)

X

X

X

MSRMT_​VAL10

NUMBER(16,6)

X

X

X

CRE_​DTTM

DATE

2019-10-31T11:09:31Z

When the record was created.

X

X

X

LAST_​UPDATE_​DTTM

DATE

2019-11-09T16:35:02Z

When the record was last updated.

X

X

X

Sample File

The following is a sample export file for interval data spanning a six hour window. Note that this does not include labels or column headers, but the data is in the same order as the column descriptions above in the File Format section above.

000004769213,2019-01-01T08:00:00Z,,501000,,,,,1,1.3715,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:39:09Z,2019-08-09T20:39:09Z
000004769213,2019-01-01T09:00:00Z,,501000,,,,,1,1.882,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:39:09Z,2019-08-09T20:39:09Z
000004769213,2019-01-01T10:00:00Z,,501000,,,,,1,1.838,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:39:09Z,2019-08-09T20:39:09Z
000004769213,2019-01-01T11:00:00Z,,501000,,,,,1,1.8585,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:39:09Z,2019-08-09T20:39:09Z
000004769213,2019-01-01T12:00:00Z,,501000,,,,,1,1.836,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:39:09Z,2019-08-09T20:39:09Z
000004769213,2019-01-01T13:00:00Z,,501000,,,,,1,1.83,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:39:09Z,2019-08-09T20:39:09Z
000671510494,2019-01-01T08:00:00Z,,501000,,,,,1,0.486,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:56:47Z,2019-08-09T20:56:47Z
000671510494,2019-01-01T09:00:00Z,,501000,,,,,1,0.4965,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:56:47Z,2019-08-09T20:56:47Z
000671510494,2019-01-01T10:00:00Z,,501000,,,,,1,0.45,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:56:47Z,2019-08-09T20:56:47Z
000671510494,2019-01-01T11:00:00Z,,501000,,,,,1,0.4725,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:56:47Z,2019-08-09T20:56:47Z
000671510494,2019-01-01T12:00:00Z,,501000,,,,,1,0.4575,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:56:47Z,2019-08-09T20:56:47Z
000671510494,2019-01-01T13:00:00Z,,501000,,,,,1,0.447,0,0,0,0,0,0,0,0,0,0,2019-08-09T20:56:47Z,2019-08-09T20:56:47Z
020643301975,2019-01-01T08:00:00Z,,501000,,,,,1,0.015,0,0,0,0,0,0,0,0,0,0,2019-08-09T21:04:28Z,2019-08-09T21:04:28Z
020643301975,2019-01-01T09:00:00Z,,501000,,,,,1,0.015,0,0,0,0,0,0,0,0,0,0,2019-08-09T21:04:28Z,2019-08-09T21:04:28Z
020643301975,2019-01-01T10:00:00Z,,501000,,,,,1,0.015,0,0,0,0,0,0,0,0,0,0,2019-08-09T21:04:28Z,2019-08-09T21:04:28Z
020643301975,2019-01-01T11:00:00Z,,501000,,,,,1,0.015,0,0,0,0,0,0,0,0,0,0,2019-08-09T21:04:28Z,2019-08-09T21:04:28Z
020643301975,2019-01-01T12:00:00Z,,501000,,,,,1,0.0165,0,0,0,0,0,0,0,0,0,0,2019-08-09T21:04:28Z,2019-08-09T21:04:28Z
020643301975,2019-01-01T13:00:00Z,,501000,,,,,1,0.015,0,0,0,0,0,0,0,0,0,0,2019-08-09T21:04:28Z,2019-08-09T21:04:28Z