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) |
Current vs. Historical Extract
There are two types of extracts used with the Measurement maintenance object:
• 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 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:
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 Dail 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
Parent topic