Last modified: 13 March 2025
Database Tables
Entity-relationship diagram
Table descriptions
PDR_CONFIG
Description
The PDR_CONFIG table contains parameter data used for configuration of the pdrLoader.
The primary sources of data for this table are configuration files or SQL (for manual updates).
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
PARAM_NAME |
Varchar2 |
20 |
Unique identifier for the parameter |
|
DESCRIPTION |
Varchar2 |
200 |
Description of the parameter |
|
PARAM_GROUP |
Varchar2 |
20 |
The group the parameter belongs to |
|
PARAM_VALUE |
Varchar2 |
100 |
Value of the parameter |
Primary Key
PARAM_NAME
PDR_MANIFEST_LOG
Description
The PDR_MANIFEST_LOG table contains the state of all file-based transactions in the process of being reconciled.
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
EVENT_QUEUE_ID |
Integer |
|
The AEMO event queue identifier associated with the generation of this report |
|
FILE_ID |
Varchar2 |
40 |
The file identifier used in the AEMO reporting system to uniquely identify this report type |
|
CONFIDENTIALITY |
Varchar2 |
20 |
The confidentiality associated with this report |
|
REPORT_TYPE |
Varchar2 |
50 |
The identifying report type extracted from the file name and also located in the first row of file |
|
FILENAME |
Varchar2 |
200 |
The name of the file processed |
|
MANIFEST_REQUEST_ID |
Integer |
|
Unique identifier for the manifest request associated with this file |
|
ARCHIVE_REQUEST_ID |
Integer |
|
Unique identifier for the archive request associated with this file |
|
STATUS |
Varchar2 |
20 |
The current state of this file based transaction |
|
STATUS_DATE |
Date |
|
The date and time the status of this file was last updated |
|
REQUEST_COUNT |
Integer |
|
The number of times this report has been requested |
|
REJECTION_COUNT |
Integer |
|
The number of times a request fo thus report has been rejected by AEMO systems |
|
ERROR_MESSAGE |
Varchar2 |
1000 |
The reason for the failure to process this request, otherwise NULL |
|
REQUEST_LIMIT |
Integer |
|
The maximum number of times this report can be be requested before being ABANDONED |
|
PARTICIPANT_EVENT_DATETIME |
Date |
|
Event date time that the report was generated |
Primary Key
EVENT_QUEUE_ID, FILE_ID, CONFIDENTIALITY
PDR_MANIFEST_REQUEST
Description
The PDR_MANIFEST_REQUEST table contains the list of manifest requests per market replicated into this database.
This table is populated by the Loader as requests are generated and manifest response are received from AEMO's systems
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
MARKET |
Varchar2 |
20 |
Unique identifer for the market |
|
PDR_REQUEST_ID |
Integer |
|
Unique identifier for this file request operation |
|
MANIFEST_START_DATETIME |
Date |
|
The start event date of the manifest associated with this market |
|
MANIFEST_END_DATETIME |
Date |
|
The end event date of the manifest associated with this market |
|
STATUS |
Varchar2 |
20 |
The current state of this file based transaction |
|
STATUS_DATE |
Date |
|
The date and time the status of this file was last updated |
|
ERROR_MESSAGE |
Varchar2 |
1000 |
The reason for the failure to process this request, otherwise NULL |
Primary Key
MARKET, PDR_REQUEST_ID
PDR_MARKET_CONFIG
Description
The PDR_MARKET_CONFIG table contains the list of markets for which data is replicated into this database.
This table is populated by the Loader as data is recieved from AEMO's systems
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
MARKET |
Varchar2 |
20 |
Unique identifer for the market |
|
HEARTBEAT_DATETIME |
Date |
|
The timestamp at which heartbeat was last received for the market |
|
DATA_FEED_TIMEOUT |
Integer |
|
The elapsed time in minutes from the last heartbeat at which the data feed for this market is deemed to have ceased |
|
LASTCHANGED |
Date |
|
The last time this record was updated |
Primary Key
MARKET
PDR_REPORT_AUDIT
Description
The PDR_REPORT_AUDIT table contains the audit trail of files loaded into the local database (one row per file).
Contents
|
Field Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
PDR_REPORT_ID |
Number |
|
Unique identifier for this file processing operation |
|
FILENAME |
Varchar2 |
200 |
The name of the file processed |
|
THREAD_ID |
Integer |
|
The thread ID that processed this file, configured by file masking in the server properties file |
|
REPORT_SOURCE |
Varchar2 |
20 |
The report source from column 2 of the first C line in the file (being, at AEMO, the database where the data within the file originated) |
|
REPORT_TYPE |
Varchar2 |
50 |
The report type extracted from the file name |
|
FILE_GENERATE_TIME |
Date |
|
The date and time based on columns 6 and 7 of the first C row of the file (being, at AEMO, the creation date and time of the file) |
|
FILE_RECEIVE_TIME |
Date |
|
The time of receipt of the file at the local filesystem, determined as the last modified time of the file at load time |
|
FILE_PROCESS_START |
Date |
|
The time of commencement of the parsing and loading of data to the database |
|
FILE_PROCESS_END |
Date |
|
The time of completion of the parsing and loading of the file to the database or the time of an error occurring |
|
FILE_PROCESS_STATUS |
Varchar2 |
20 |
The process result of the file. Valid entries are: PROCESSING SUCCESS FAILED |
|
FILE_PROCESS_RECORDS |
Integer |
|
The number of records processed from the file, not necessarily being the same as the number of database rows loaded (since certain report records may be inactive or row filters may restrict the dataset) |
|
ERROR_MESSAGE |
Varchar2 |
255 |
The reason for the failure to load this file, otherwise NULL |
|
EVENT_QUEUE_ID |
Integer |
|
The AEMO event queue identifier associated with the generation of this report |
|
FILE_ID |
Varchar2 |
40 |
The FILE_ID which is an internal identifier within AEMO systems |
|
CONFIDENTIALITY |
Varchar2 |
20 |
The confidentiality associated with this report, valid values are PUBLIC or PRIVATE |
Primary Key
PDR_REPORT_ID
Index
EVENT_QUEUE_ID, REPORT_TYPE, CONFIDENTIALITY
FILE_PROCESS_STATUS
FILENAME
Note
The pdrLoader only adds records to the PDR_REPORT_AUDIT table. To avoid the table growing too large, arrange to truncate according to your own database management policy.
PDR_REPORT_AUDIT_SUMMARY
Description
The PDR_REPORT_AUDIT_SUMMARY table contains the latest status of data loads into the local database.
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
REPORT_TYPE |
Varchar2 |
50 |
The identifying report type extracted from the file name and also located in the first row of file |
|
REPORT_NAME |
Varchar2 |
40 |
The report name, extracted from column 2 of D and I records in the input files |
|
REPORT_SUB_TYPE |
Varchar2 |
30 |
The report sub type, extracted from column 3 of D and I records in the input files |
|
VERSION |
Integer |
|
The version, extracted from column 4 of D and I records in the input files |
|
DESTINATION_TABLE |
Varchar2 |
40 |
The table in the local database where this data is to load |
|
LAST_PROCESS_START |
Date |
|
The time of commencement of the parsing and loading of data to the database |
|
LAST_PROCESS_END |
Date |
|
The time of completion of the parsing and loading of the file to the database or the time of an error occurring |
|
LAST_PROCESS_RECORDS |
Integer |
|
The number of records processed from the file, not necessarily being the same as the number of database rows loaded (since certain report records may be inactive or row filters may restrict the dataset) |
|
PDR_REPORT_ID |
Number |
|
Unique identifier for this file processing operation |
Primary Key
REPORT_TYPE, REPORT_NAME, REPORT_SUB_TYPE, VERSION, DESTINATION_TABLE
PDR_REPORT_RECORDS
Description
The PDR_REPORT_RECORDS table is the main configuration table, mapping report types, subtypes and versions to destination tables.
This table is fully versioned and there may be multiple versions for each combination of report type and subtype.
The primary sources of data for this table are configuration files. If the pdrLoader encounters a combination of report type, subtype and version not contained within this table, the pdrLoader creates the row and sets the IS_NEW flag to true.
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
REPORT_NAME |
Varchar2 |
40 |
The report name, extracted from column 2 of D and I records in the input files |
|
REPORT_SUB_TYPE |
Varchar2 |
30 |
The report sub type, extracted from column 3 of D and I records in the input files |
|
VERSION |
Integer |
|
The version, extracted from column 4 of D and I records in the input files |
|
DESTINATION_SCHEMA |
Varchar2 |
20 |
The schema in the local database where this data is to load |
|
DESTINATION_TABLE |
Varchar2 |
40 |
The table in the local database where this data is to load |
|
TRANSACTION_TYPE |
Varchar2 |
20 |
The transaction type to apply and the order of application. Valid entries are: INSERT INSERT-UPDATE UPDATE-INSERT The second operation executes only if the row count from the first operation is zero. |
|
ROW_FILTER_TYPE |
Varchar2 |
20 |
The row filter to apply to this dataset, or NULL if no row filter is applicable |
|
IS_ACTIVE |
Integer |
|
Flag for enabling this report sub-type and version for loading into the database. Valid entries are: 1 (Enabled)) 0 (Disabled) |
|
IS_NEW |
Integer |
|
Flag indicating if there is no configuration in the database for a received and processed report type. Valid entries are: 1 (True) 0 (False) |
|
ROW_FILTER_TYPE_2 |
Varchar2 |
20 |
The cascaded row filter to apply to this dataset, or NULL if no cascading row filter is applicable |
|
ROW_FILTER_PARAM1 |
Varchar2 |
20 |
The first parameter applying to ROW_FILTER_TYPE |
|
ROW_FILTER_PARAM2 |
Varchar2 |
20 |
The second parameter applying to ROW_FILTER_TYPE |
Primary Key
REPORT_NAME, REPORT_SUB_TYPE, VERSION, DESTINATION_TABLE
Notes
For SQL Server, the DESTINATION_SCHEMA column contains the definitions of database and owner. The data format is DATABASE.OWNER (a period separates the two items). A single string is interpreted as being the OWNER.
PDR_REPORT_RECORD_MAPPING
Description
The PDR_REPORT_RECORD_MAPPING table is the configuration table mapping report columns to table columns.
This table is fully versioned and there may be multiple versions for each combination of report type and subtype.
The primary sources of data for this table are configuration files or SQL (for manual updates).
Columns are only mapped by exception, because the pdrLoader matches a source column with a table column with the same name (an implied mapping).
The pdrLoader ignores any superfluous mapping in this table (to columns not existing in the local database table).
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
REPORT_NAME |
Varchar2 |
40 |
The report name, extracted from column 2 of D and I records in the input files |
|
REPORT_SUB_TYPE |
Varchar2 |
30 |
The report sub type, extracted from column 3 of D and I records in the input files |
|
VERSION |
Integer |
|
The version, extracted from column 4 of D and I records in the input files |
|
DESTINATION_TABLE |
Varchar2 |
40 |
The table in the local database where this data is to load |
|
SOURCE_COLUMN |
Varchar2 |
40 |
The name of the column in the input file |
|
DESTINATION_COLUMN |
Varchar2 |
40 |
The name of the column in the destination table |
|
TRANSFORM_TYPE |
Varchar2 |
20 |
The data transform to apply to the file input data to get the data to insert into the local database |
|
TRANSFORM_PARAM1 |
Varchar2 |
40 |
The first parameter applying to TRANSFORM_TYPE |
|
TRANSFORM_PARAM2 |
Varchar2 |
40 |
The second parameter applying to TRANSFORM_TYPE |
Primary Key
REPORT_NAME, REPORT_SUB_TYPE, VERSION, DESTINATION_TABLE, DESTINATION_COLUMN
PDR_REPORT_ROW_FILTERS
Description
The PDR_REPORT_ROW_FILTERS table is the configuration of row filters allowing dataset reduction by a user-specified filtering function.
The primary sources of data for this table are configuration files or SQL (for manual updates).
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
ROW_FILTER_TYPE |
Varchar2 |
20 |
Identifier to this row filter, used by GUI and server |
|
DESCRIPTION |
Varchar2 |
200 |
The function of the row filter |
|
JAVA_CLASS |
Varchar2 |
100 |
The Java class including the full package specification implementing the row filter. The CLASSPATH environment variable on the server must include this class. |
Primary Key
ROW_FILTER_TYPE
PDR_REPORT_TRANSFORMS
Description
The PDR_REPORT_TRANSFORMS table is the lookup table listing the data transforms for the pdrLoader to apply to the data being loaded.
The primary sources of data for this table are configuration files or SQL (for manual updates).
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
TRANSFORM_TYPE |
Varchar2 |
20 |
Identifier for this column transform, used by GUI and server |
|
DESCRIPTION |
Varchar2 |
200 |
The function of the column transform |
|
JAVA_CLASS |
Varchar2 |
100 |
The Java class including the full package specification implementing the column transform. The CLASSPATH environment variable on the server must include this class. |
Primary Key
TRANSFORM_TYPE
PDR_REPORT_TYPE_CONFIG
Description
The PDR_REPORT_TYPE_CONFIG table contains configuration data at a Report Type level. The pdrLoader identifies the report type from the file name and verifies from the first row of the file.
The primary sources of data for this table are configuration files. If the pdrLoader processes a report type without an entry in this table, then the pdrLoader creates a row with the IS_NEW flag set to true.
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
REPORT_TYPE |
Varchar2 |
50 |
The identifying report type extracted from the file name and also located in the first row of file |
|
TRANSACTION_TYPE_OVERRIDE |
Varchar2 |
20 |
The transaction type to apply and the order of application. Valid entries are: INSERT INSERT-UPDATE UPDATE-INSERT The second operation executes only if the row count from the first operation is zero. |
|
IS_ACTIVE |
Integer |
|
Flag enabling this report type for loading into the database. Valid entries are: 1 (Enabled) 0 (Disabled) |
|
IS_NEW |
Integer |
|
Flag indicating if there is no configuration in the database for a received and processed report type. Valid entries are: 1 (True) 0 (False) |
|
SEQUENTIAL_PROCESS |
Integer |
|
Flag to force all reports of the same report type to process in time order, achieved by enforcing only one thread can be loading a report of this type at any one time |
|
PROCESS_PRIORITY |
Integer |
|
Priority to process report. Priority = 1 (or null) is highest priority |
|
ARCHIVE_PRIORITY |
Integer |
|
Priority to process archive report. Priority = 1 (or null) is highest priority |
|
MARKET |
Varchar2 |
20 |
The Market to which a given report applies. Reports used to supported data replication as designated as SYSTEM |
|
IS_PAUSED |
Integer |
|
Flag to indicate if loading of this report type into the database is paused. Valid entries are: 1 (Enabled) 0 (Disabled) |
|
SUBSCRIPTION_REQUESTED |
Integer |
|
Flag to indicate if associated subscriptions for this report type have been requested. Valid entries are: 1 (Enabled) 0 (Disabled) |
Primary Key
REPORT_TYPE
PDR_REQUEST_LOG
Description
The PDR_REQUEST_LOG table contains the audit trail of all requests made to the AEMO systems for transaction manifests or archive files.
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
PDR_REQUEST_ID |
Integer |
|
Unique identifier for this file request operation |
|
FILENAME |
Varchar2 |
80 |
The name of the request file generated |
|
REQUEST_TYPE |
Varchar2 |
20 |
The name of the file processed |
|
REQUEST_DATE |
Date |
|
The date this request was made |
|
STATUS |
Varchar2 |
20 |
The status of this request. |
|
STATUS_DATE |
Date |
|
The date and time the status of this request was last updated |
|
ERROR_MESSAGE |
Varchar2 |
1000 |
The reason for the failure to process this request, otherwise NULL |
Primary Key
PDR_REQUEST_ID
PDR_DATA_FILTER_CONFIG
Description
The PDR_DATA_FILTER_CONFIG table allows the definition of a data filter
Contents
|
Name |
Data Type |
Length |
Comment |
|---|---|---|---|
|
FILTER_GROUP_NAME |
Varchar2 |
40 |
The FILTER_GROUP_NAME column is used as the reference to the first parameter in PDR-REPORT_RECORDS when using a DATA_FILTER row filter |
|
COLUMN_NAME |
Varchar2 |
40 |
The COLUMN_NAME in the source data stream |
|
OPERATOR |
Varchar2 |
10 |
The operator to apply. Supported operators are:
|
|
FILTER_VALUE |
Varchar2 |
40 |
The filter value to compare to the source column data using the specified operator |
Primary Key
FILTER_GROUP_NAME, COLUMN_NAME, OPERATOR, FILTER_VALUE
Examples
Multiple target columns
A table in the target database can have multiple columns deriving from a single field in the CSV data file. Typically, one of the table columns is a transform based on the source data field.
In the following example, the SETTLEMENTDATE column is equivalent to the case identifier (DISPATCHINTERVAL) in each of two tables (DISPATCHPRICE and DISPATCHREGIONSUM). The column transform DATE2CASEID transforms a date-and-time into a case identifier.
Even if a source field and one of its destination columns have the same name, specifically include each of the multiple mappings.