Skip to content

Datatypes

Datatype differences

General principles

  • AEMO column names are used exactly
  • All datetime fields are changed to datetime2(0)
  • varchar fields are reduced in length for consistency across tables
    • e.g. all DUIDs and Participant IDs are all made length 10
  • Numeric types are replaced with more appropriate SQL Server native datatypes, see below.

Specific datatype change examples

Original datatype Modified datatype Examples
numeric(1, 0) tinyint, or bit APEVENTREGION FLAGS
numeric(3, 0) tinyint, or smallint BILLING_RUN tables for WEEKNO, BILLRUNNO
numeric(4, 0) smallint BILLING_RUN tables for CONTRACTYEAR

Removed columns

Historically, we have removed certain columns from some MMS tables. These might be columns where the documentation suggests they are "not used" or if the value in the table is always a constant.

Specific reasons for removed columns

Column name Explanation
DISPATCHINTERAL Recreatable from other columns in the same table
PERIODID Recreatable from other columns in the same table
RUNNO Documentation says "always 1" or historically has always been "1"
EEP* Documentation says explicitly "not used"
UP / DOWNEPF Documentation says explicitly "not used"
TRADETYPE Documentation says explicitly "not used"

Full list of removed columns

Table Column Official schema key? Documentation comment
DISPATCH_INTERCONNECTION DISPATCHINTERVAL Dispatch period identifier, from 001 to 288 in format YYYYMMDDPPP
DISPATCHCONSTRAINT DISPATCHINTERVAL Y Dispatch period identifier, from 001 to 288 in format YYYYMMDDPPP
DISPATCHPRICE DISPATCHINTERVAL Y Dispatch interval identifier 001 to 288 in format YYYYMMDDPPP
DISPATCHINTERCONNECTORRES DISPATCHINTERVAL Y Dispatch period identifier, from 001 to 288 in format YYYYMMDDPPP
DISPATCHLOAD DISPATCHINTERVAL Dispatch period identifier, from 001 to 288 in format YYYYMMDDPPP
DISPATCHREGIONSUM DISPATCHINTERVAL Y Dispatch period identifier, from 001 to 288 in format YYYYMMDDPPP
DISPATCHLOAD DOWNEPF Not Used
PREDISPATCHPRICE EEP1 Not used
PREDISPATCHPRICE EEP2 Not used
PREDISPATCHPRICE EEP3 Not used
PREDISPATCHPRICE EEP4 Not used
PREDISPATCHPRICE EEP5 Not used
PREDISPATCHPRICE EEP6 Not used
PREDISPATCHPRICE EEP7 Not used
PREDISPATCHPRICE EEP8 Not used
PREDISPATCHLOAD PERIODID PERIODID is just a period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period.
PREDISPATCH_FCAS_REQ PERIODID Unique period identifier, in the format yyyymmddpp. The period (pp) is 01 to 48, with 01 corresponding to the half-hour ending at 04:30am.
PREDISPATCHOFFERTRK PERIODID Y PERIODID is just a period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period.
PREDISPATCHPRICE PERIODID PERIODID is just a period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period.
PREDISPATCH_LOCAL_PRICE PERIODID A period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period
PREDISPATCHPRICESENSITIVITIES PERIODID PERIODID is just a period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period.
PREDISPATCHCONSTRAINT PERIODID Unique period identifier, in the format yyyymmddpp. The period (pp) is 01 to 48, with 01 corresponding to the half-hour ending at 04:30am.
PREDISPATCHINTERCONNECTORRES PERIODID PERIODID is just a period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period.
PREDISPATCHREGIONSUM PERIODID PERIODID is just a period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period.
PREDISPATCHINTERSENSITIVITIES PERIODID PERIODID is just a period count, starting from 1 for each predispatch run. Use DATETIME to determine half hour period.
PREDISPATCHPRICE RRP1 Not used
PREDISPATCHPRICE RRP2 Not used
PREDISPATCHPRICE RRP3 Not used
PREDISPATCHPRICE RRP4 Not used
PREDISPATCHPRICE RRP5 Not used
PREDISPATCHPRICE RRP6 Not used
PREDISPATCHPRICE RRP7 Not used
PREDISPATCHPRICE RRP8 Not used
DISPATCH_INTERCONNECTION RUNNO Y Dispatch run no; always 1
DISPATCH_MNSPBIDTRK RUNNO Y Dispatch run no; always 1
DISPATCH_PRICE_REVISION RUNNO Y Dispatch run no; always 1
DISPATCHLOAD_BNC RUNNO Y Dispatch run no; always 1
DISPATCHBIDTRK RUNNO Y Dispatch run no from 1 to 288 (as per bid)
DISPATCHBLOCKEDCONSTRAINT RUNNO Y Dispatch run no; always 1
DISPATCHCASE_OCD RUNNO Y Dispatch run no; always 1
DISPATCHCASESOLUTION RUNNO Y Dispatch run no; always 1
DISPATCHCASESOLUTION_BNC RUNNO Y Dispatch run no; always 1
DISPATCHCONSTRAINT RUNNO Y Dispatch run no; always 1
DISPATCHPRICE RUNNO Y Dispatch run no; always 1
DISPATCHINTERCONNECTORRES RUNNO Y Dispatch run no; always 1
DISPATCHLOAD RUNNO Y Dispatch run no; always 1
DISPATCHREGIONSUM RUNNO Y Dispatch run no; always 1
DISPATCHTRK RUNNO Y Dispatch run no; always 1
PREDISPATCHLOAD RUNNO SPD Predispatch run no, typically 1. It increments if the case is re-run.
PREDISPATCH_FCAS_REQ RUNNO Case Run number
PREDISPATCHPRICE RUNNO LP Solver Predispatch run no, typically 1. It increments if the case is re-run.
PREDISPATCHPRICESENSITIVITIES RUNNO LP Solver Predispatch run no, typically 1. It increments if the case is re-run.
PREDISPATCHCONSTRAINT RUNNO SPD Predispatch run no, typically 1. It increments if the case is re-run.
PREDISPATCHINTERCONNECTORRES RUNNO SPD Predispatch run no, typically 1. It increments if the case is re-run.
PREDISPATCHREGIONSUM RUNNO LP Solver Predispatch run no, typically 1. It increments if the case is re-run.
PREDISPATCHINTERSENSITIVITIES RUNNO LP Solver Predispatch run no, typically 1. It increments if the case is re-run.
TRADINGINTERCONNECT RUNNO Y Dispatch run no.
TRADINGLOAD RUNNO Y Dispatch run no.
TRADINGREGIONSUM RUNNO Y Dispatch run no.
DISPATCHLOAD TRADETYPE Not used
PREDISPATCHLOAD TRADETYPE Not used
P5MIN_UNITSOLUTION TRADETYPE Generator or Load
TRADINGLOAD TRADETYPE Y Not used
DISPATCHLOAD UPEPF Not Used

We encourage customers to contact us at support@global-roam.com with any questions or requests regarding these omitted columns, we are more than happy to discuss.