Configurable Consumption Extracts
Configurable Consumption Extracts are measurement data extracts for a set of usage subscriptions optionally related to a specific market participant.
Configuration options for these extracts include:
• Type of Market Participant, based on Service Point Market Participant or Usage Subscription Market Participant tables and market relationship
• Usage Subscriptions Types associated with the market participant (typically a customer information system receiving bill determinants)
• Unique combinations of unit of measure (UOM), time of use (TOU), and service quantity identifier (SQI) associated with the usage subscriptions
• Specific identifiers used to identify the usage subscriptions, service points, and devices associated with extracted measurement data
• Date and time parameters and frequency of extract
• Extract file naming and compression options
Consumption extracts are exported to a file in a specified file location, either on an application server (in an on-premises implementation) or an Oracle Cloud Infrastructure Object Storage location.
Consumption extracts are created via Consumption Extract Requests. See
Consumption Extract Requests in the
Business User Guide.
This section describes the configuration needed for these types of consumption extracts.
Consumption Extract Types
Configuration for consumption extracts are defined via a Consumption Extract Type. See
Consumption Extract Type in the
Administrative User Guide for more information about creating these records.
Configurable consumption extract types should be created using the Extract US Consumption to File (D1-ExtractUSConsumptionToFile) business object. Refer to the embedded help on this business object for more details about the parameters defined for consumption extract types.
Extract US Consumption to File consumption extract types define the following parameters:
• The market participants for which measurement data will be extracted. You define market participants based on their type (Service Point Market Participant or Usage Subscription Market Participant) and market relationship type (retailer, distributor, supplier, and so on), and a specific market participant. Note: If Market Participant Specific is set to "No", the market participant used is based on the Usage Recipient on the Usage Subscription or its Usage Subscription Type.
• The consumption type (interval or scalar)
• One or more Usage Subscription Types and a related unique UOM/TOU/SQI combination
• The specific ID types to include in the extract file for the Usage Subscription, Service Point, child Service Point (if applicable), and Device
• Execution parameters, including:
◦ The date and time through which data has been extracted for this consumption extract type
◦ A Request Type. Request types used with consumption requests should be created using the Consumption Extract Request Type (D2-IntervalDataExtRepoType) business object, and should reference the Consumption Extract (D2-IntervalDataExtRepository) business object in the
Request Business Object field. (see
The Big Picture of Requests in the
Application Framework Administrative User Guide)
◦ The frequency of extracts for this type (adhoc or daily)
◦ Batch controls used for the extract process (see Batch Controls below)
• File parameters, including:
◦ The file location where extract files will be saved (see
External File Storage in the
Application Framework Administrative User Guide)
◦ An optional child folder in the file location
◦ File naming options (including an optional prefix and date/time stamp)
◦ Whether or not to compress the extract file (file compression is performed using gzip)
Batch Controls
The data extract process and creation of export files is performed by the following batch controls:
• Extract Current US Intervals to File (D1-ECUSI): Used for daily extracts of interval consumption. This process requires that Interval Snapshot Mapping data be generated for any of the interval sizes being extracted. This aides in the efficiency of transposing the interval data into daily interval columns. (see
Interval Snapshot Mapping for more information).
• Extract Replacement US Intervals to File (D1-ERUSI): Used for extracts of replacement historical interval consumption. This process runs against Change Data Capture records in the General Process table (see Initial Measurement Subsequent Correction Change Data Capture below).
• Extract Current US Scalar Reads to File (D1-ECUSS): Used for daily extracts of scalar consumption.
• Extract Replacement US Scalar Reads to File (D1-ERUSI): Used for extracts of replacement scalar interval consumption. This process runs against Change Data Capture records in the General Process table (see Initial Measurement Subsequent Correction Change Data Capture below).
These batch controls are run for a specific Consumption Extract Type and date range. Batch processing is threaded by on Usage Subscription ID.
Refer to
Batch Controls under
Consumption Extract in the
Oracle Utilities DataConnect section of the
Integrations chapter for general information about consumption extract batch processing,
Initial Measurement Subsequent Correction Change Data Capture
The follow algorithms maintain measuring component date/times based on the latest measurement date time for the measuring component. These algorithms also check for a Consumption Extract Type that applies to the current initial measurement's measuring component and identifies whether the initial measurement is for a date/time that has already been extracted.
Algorithm | Description | Consumption Type |
|---|
D1-UPD-DTMC | Update Latest Measurement Date/Time on MC with Consumption Sync | Interval |
D1-UDTSCMCRE | Update Latest Measurement Date/Time on Scalar MC with Consumption Sync | Scalar |
D1-UPD-DTMCF | Update Latest Date/Time on MC from a Future Measurement | Interval |
D1-UPDDTSCMC | Update Latest Date/Time on Scalar Measuring Component | Scalar |
If an initial measurement is for a date/time that has already been extracted, these algorithms create Change Data Capture records in the General Process table that capture the batch code, batch run number, and the initial measurement's ID. The "Replacement" batch processes run off of these records.
Note that the following parameter must be set to “true” on the above algorithms to enable historical change data capture:
• Capture CET Historical Measurement Changes: true
File Format - Interval
The interval extract process produces JavaScript Object Notation (JSON) files with the extract data described in the following table.
Note that interval extract files can store up to a maximum of 300 intervals. This means that export files can store approximately:
• 12.5 days worth of hourly data
• 3 days worth of 15 minute data
• 1 day worth of 5 minute data
Field | Sample Value | Comments |
|---|
usId | 995647003500 | Usage Subscription ID defined by the Consumption Extract Type |
usType | E-RES | Usage Subscription type |
pSpId | 458081 | Parent Service Point ID, Null when there is no parent service point. |
spId | 458081_001 | Child Service Point ID This is the Service Point ID defined by the Consumption Extract Type |
dvcId | SN823287793219 | Device ID defined by the Consumption Extract Type |
uomTouSqi | KWH// | UOM / TOU / SQI, separated by forward slashes ("/") |
tz | US/Eastern | Time zone of the service point. This is the time zone name from the CI_TIME_ZONE table not the time zone code. |
intPerDay | 288 | Number of Intervals for the day, will be 1 hour less or 1 hour more of intervals for DST transitions |
intSize | 300 | Length of each interval in seconds |
mktPart | DIRECT_ENERGY | Market Participant ID of the market participant (if applicable) |
stDttm | 2020-01-01T00:00:00-05:00 | Start of the day, similar to initial measurement, this represents the start of the first interval. |
q1 | 1.325 | Measurement Value for the first interval, the measurement date/time for this interval would be the stDttm plus the intSize |
c1 | 301000 | Measurement Condition for the first interval. Null when "Regular" (501000) |
q2 | 1.230 | |
c2 | 301000 | |
... | q/c 3 through 299 here | |
q300 | .9134 | |
c300 | 30100 | |
Example:
{"usId":"995647003500", "usType":"E-RES", "pSpId":"458081", "spId":"458081_001", "dvcId":"SN823287793219", "uomTouSqi":"KWH//",
"tz":"US/Eastern", "intPerDay":"288", "intSize":"300", "mktPart":"DIRECT_ENERGY",
"stDttm":"2020-01-01T00:00:00-05:00", "q1":"1.325", "c1":"301000", "q2":"1.325",
"c2":"301000" "q3":"1.325", "c3":"301000",
<intervening intervals>, "q300":"1.325", "c300":"301000"}
File Format - Scalar
The scalar extract process produces JavaScript Object Notation (JSON) files with the extract data described in the following table.
Name | Sample Value | Comments |
|---|
c | 301000 | Measurement Condition for the scalar reading Null when "Regular" (501000) |
dvcId | SN823287793219 | Device ID defined by the Consumption Extract Type |
enDttm | 2020-01-02T00:00:00-05:00 | End date/time of the reading |
mktPart | DIRECT_ENERGY | Market Participant ID of the market participant (if applicable) |
pSpId | 458081 | |
q | 1.325 | Measurement Value for scalar reading |
r | 678645 | Dial reading of the meter |
spId | 458081_001 | Service Point ID defined by the Consumption Extract Type |
stDttm | 2020-01-01T00:00:00-05:00 | Start date/time of the reading (the End Date/Time of the previous reading) |
tz | US/Eastern | Time zone of the service point. This is the time zone name from the CI_TIME_ZONE table not the time zone code. |
uomTouSqi | KWH// | UOM / TOU / SQI, separated by forward slashes ("/") |
usId | 995647003500 | Usage Subscription ID defined by the Consumption Extract Type |
usType | E-RES | Usage subscription type |
Example (3 records):
{"usId":"995647003500", "usType":"E-RES", "pSpId":"458081", "spId":"458081_001", "dvcId":"SN823287793219", "uomTouSqi":"KWH//",
"tz":"US/Eastern", "mktPart":"DIRECT_ENERGY", "stDttm":"2020-01-01T00:00:00-05:00",
"enDttm":"2020-01-02T00:00:00-05:00", "q":"1.325", "c":"301000", "r":"678645"}
{"usId":"995647003500", "usType":"E-RES", "pSpId":"458081", "spId":"458081_001", "dvcId":"SN823287793219", "uomTouSqi":"KWH//",
"tz":"US/Eastern", "mktPart":"DIRECT_ENERGY", "stDttm":"2020-01-01T00:00:00-05:00",
"enDttm":"2020-01-02T00:00:00-05:00", "q":"1.325", "c":"301000", "r":"678645"}
{"usId":"995647003500", "usType":"E-RES", "pSpId":"458081", "spId":"458081_001", "dvcId":"SN823287793219", "uomTouSqi":"KWH//",
"tz":"US/Eastern", "mktPart":"DIRECT_ENERGY", "stDttm":"2020-01-01T00:00:00-05:00",
"enDttm":"2020-01-02T00:00:00-05:00", "q":"1.325", "c":"301000", "r":"678645"}
{"usId":"995647003500", "usType":"E-RES", "pSpId":"458081", "spId":"458081_001", "dvcId":"SN823287793219", "uomTouSqi":"KWH//",
"tz":"US/Eastern", "mktPart":"DIRECT_ENERGY", "stDttm":"2020-01-01T00:00:00-05:00",
"enDttm":"2020-01-02T00:00:00-05:00", "q":"1.325", "c":"301000", "r":"678645"}
Extending Extract Processing
Some implementations may wish to extend and/or customize the extract process based on specific requirements.
Extending List of IDs To Use
Consumption Extract Types define specific types of IDs to use for Usage Subscriptions, Service Points, child Service Points (if applicable), and Devices. If an implementation wishes to extend the types of IDs available, they need to add appropriate values to the lookup flags used by each of ID type. The table below lists the lookup codes used by the "IDs to Use" fields.
ID Type | Lookup Flag |
|---|
Usage Subscription ID | Usage Subscription Identifier Type (US_ID_TYPE_FLG) |
Service Point ID (used for both parent and child service points) | Service Point Identifier Type (SP_ID_TYPE_FLG) |
Device ID | Device Identifier Type (DVC_ID_TYPE_FLG) |
Processing Records and Creating Output Files
The extract process leverages the generic
Plug-in Driven Background Processes functionality but works in a slightly different manner. Instead of using the Select Records algorithm to retrieve the IDs of the individual records to be processed, that algorithm returns a range of Usage Subscription IDs to be extracted and the Process Records algorithm performs the detailed query to extract the range of Usage Subscription IDs.
This provides two benefits:
1. There is no limitation on the length of the query in the Process Records algorithm.
2. Retrieving data for many Usage Subscriptions at a single time is significantly more efficient.
This means that any required customization can be achieved by creating a new custom (CM) batch control and duplicating the Process Records Groovy script. Changes can be made within the custom Groovy script to the extraction SQL to retrieve additional information or provide additional selection criteria. If any changes are needed to the file format that can be done within the record processing logic that populates the file.
Note:
The Process Records scripts include a number of steps each containing specific processing logic and SQL based on the configuration of the Market Participants on the Consumption Extract Type as well as whether or not the specified UOMs measure peak quantities or not. Be sure to edit each of these steps as appropriate when creating customized versions of these scripts.
Parent topic