Loading data from non-data model tables

This procedure outlines the steps you can follow to allow the loading non-data model information into your local database.

Step 1 - Subscriptions

Navigate to Data Interchange > Data Subscription webpage on the Markets Portal Web applications for registered participants only. Requires access to MarketNet. to search and subscribe to the files. For more information, see Subscribe to Files

Step 2 - Prepare the local tables with your database

Collate the report specification. Some sources for this information might include:

•   The technical specification under which the report was described. This is available on Technical Specification Portal page on the AEMO Australian Energy Market Operator website.
• A sample of the report from your Data Interchange solution. Recently processed reports are found in the pdrLoader Software responsible for loading files to and from participant's local Data Interchange folders to the participant's DBMS. It can be installed in any environment supporting Java. Archive folder.
• A sample of the report from the NEMWeb.

If you are gathering this information from a sample CSV file, you need to capture details from the data sections of the report. These are denoted with an I record at the start of the line. For example, the report columns from the file PUBLIC_PD7DAY_GPG_*.zip are as follows:

For each data section, create an associated database table.

Naming guidelines for the tables

• Table name should have a common prefix to ensure the associated data is shown together in the database. Generally, the third part of the CSV file key is a suitable description to the data description. For example, the suggested names for the PD7DAY report are as follows:

  CSV file key	Table name
  I,GPG,CASESOLUTION,1	PD7DAY_CASESOLUTION
  I,GPG,MARKET_SUMMARY,1	PD7DAY_MARKET_SUMMARY
  I,GPG,CONSTRAINTSOLUTION,1	PD7DAY_CONSTRAINTSOLUTION
  I,GPG,INTERCONNECTORSOLUTION,1	PD7DAY_INTERCONNECTORSOLUTION
  I,GPG,PRICESOLUTION,1	PD7DAY_PRICESOLUTION

• Column names within a table must be an exact match to the CSV column names. While the order of the columns is not important, AEMO recommends you align the column order in the table to the CSV content.
• The primary key definition and column data types are generally included in the technical specification document. If this is not available, you may have to infer a suitable data type from the sample data.
• AEMO recommends you set only the primary key columns as NOT NULL. This ensures if a report data is discontinued in the future, the data continues to load assuming the primary key is satisfied.

For example, a sample definition of a table for the I,GPG,CASESOLUTION,1 CSV key would be:

CREATE TABLE PD7DAY_CASESOLUTION(
    RUN_DATETIME datetime NOT NULL,
    INTERVENTION numeric(2, 0) NULL,
    LASTCHANGED datetime NULL,
    CONSTRAINT PD7DAY_CASESOLUTION_PK PRIMARY KEY ( RUN_DATETIME )
)
GO

When you have created definitions for all required tables, run the script to create them in your local database.

Step 3 - Report mappings

The final step to allow data to load is to configure the report mappings. These mappings provide the required links between the CSV fil ekey and the table in your local database.

Option 1 - Using pdrMonitor

AEMO recommends you use the pdrMonitor application to create these mappings.

1. Let the sample report file pass through the Data Interchange system.

   If your system is not connected to a live environment, you can emulate this by copying your sample report file into the pdrLoader Reports folder and letting it process the file.

2. Log into your pdrMonitor installation and navigate to the Data Interchange menu option and setting tab.

   The default entry should already be created. For example, in the case of PD7DAY report, the entry iscalled PD7DAY_GPG, from the content in the first line of the CSV report file.

   

3. Double click on the highlighted row from the above image.

   A pop-up windown opens. this allows the configuration of the report to be applied. For the PD7DAY report, the pop-up looks as below:

   

4. For each data section, enter the associated DESTINATION_TABLE you created in Step 2 and tick the Is Active field if you want this data to load.
5. Once complete, click the Save button to save the final configuration.

   The updated configuration may take up to 2 minutes to take effect.

You can extract the configuration mapping rows created from this process as a suite of SQL INSERT statements and applied to different Data Interchange A set of cooperating applications used to replicate data between AEMO's energy market systems and a participant's DBMS conforming to the MMS Data Model. environment using option 2 - Manual configuration if desired.

Option 2 - Mnaual configuration

You can also complete the report mappings manually by editing data in the PDR_REPORT_TYPE_CONFIG and PDR_REPORT_RECORDS tables. The sample queries to insert data in the required fields in these tables for the PD7DAY example report are as follows:

INSERT INTO PDR_REPORT_TYPE_CONFIG(REPORT_TYPE,IS_ACTIVE,IS_NEW,SEQUENTIAL_PROCESS) VALUES('PD7DAY_GPG',1,0,0);

INSERT INTO PDR_REPORT_RECORDS(REPORT_NAME,REPORT_SUB_TYPE,VERSION,DESTINATION_TABLE,TRANSACTION_TYPE,ROW_FILTER_TYPE,IS_ACTIVE,IS_NEW) VALUES('GPG','CASESOLUTION',1,'PD7DAY_CASESOLUTION','INSERT',NULL,1,0);

You need to edit the parameters within these queries to suit the particular non-data model report you want to load. You must repeat the second query for each CSV File Key and the database table combination.

Step 4 - Confirmation

To confirm the end-to-end operation was successful:

1. Place the sample file back into the pdrLoader Reports folder and wait for it to process.
2. Confirm data is loaded in each table you set to active and all columns of the tables contain data.
3. In the event this check fails, double check the actions in steps 1 to 3. You can confirm any errors in the pdrLoader application log file.