DUDETAILSUMMARY table refresh issue resulting in duplicate records

The DUDETAILSUMMARY table is a hybrid representation of data from various AEMO tables to define DUIDs. DUDETAILSUMMARY includes an END_DATE, which is the START_DATE of the next record as defined when the data set was created.

DUDETAILSUMMARY was created for Participants to assist in comprehension of the complex interrelations between the component tables. Such a representation is subject to changes in Registration. While the vast majority of registration changes are prospective, at times retrospective changes are required after an initial Registration.

The reporting model which supplies new and changed records can result in overlapping dates ranges for a DUID in a participants Data Model in the case of a retrospective change where a (DUID, START_DATE) combination is superceded by a new suite of dated records.

What is the problem?

DUDETAILSUMMARY table refresh issue resulting in duplicate records.

This is a consequence of retrospective changes to the Registration records resulting in AEMO reporting which generates duplicates records in the data model.

This creates ambiguity as Participants can now see two records for one or more effective dates indicating an overlap of registration attributes for the affected DUID.

The best interpretation of the representation is some Participants might wish to ignore the published END_DATE and use the START_DATE of the subsequent record.

Why is it a problem?

Duplicate records in DUDETAILSUMMARY can create erroneous data conditions in reporting queries within participant systems. This may result in participant applications producing incorrect results or in the case where there is a dependency on this table for an offer application, it may result in difficulties preparing a new and valid bid to submit to the market.

What do you need to do?

AEMO suggests Participants regularly monitor the contents of DUDETAILSUMMARY table for duplicate rows and if this condition is detected, truncate the table and load the latest PUBLIC_PARTICIPANT_REGISTRATION_*_BASELINE file from daily \MarketData\Baseline, compiled each night.

When it will be fixed?

The long term solution is rather than reporting incremental changes to DUDETAILSUMMARY, AEMO supplies the full history of DUDETAILSUMMARY each day and modify the pdrLoader TRANSACTION_TYPE for this table from INSERT-UPDATE to DELETE-INSERT. The operation to change the transaction type clears and reloads the DUDETAILSUMMARY table in a single database transaction, and therefore correctly represent retrospective registration changes. In the event the operation fails, the current data remains and allows participant systems to continue with an existing data set.

The next version of pdrLoader supports this new functionality. AEMO is planning the next Data Interchange software update to be made available later this year or early next year. AEMO will notify Participants via a technical specification document covering the Data Interchange release scope and timelines.