Input Aggregations

Input aggregations are SQL-derived summary views of your input data. They display as a secondary chart below the main forecast chart in the pd4castr UI, giving you visibility into the input conditions that drove a particular forecast.

How Aggregations Execute

Aggregation queries run against model input files after a run is triggered. The results are stored alongside the forecast output and served in the pd4castr UI as supplementary charts.

Configuration

Define input aggregations in the inputAggregations array in .pd4castrrc.json:

FieldTypeRequiredDescription
namestringYesDisplay name for the aggregation chart.
keystringYesStable identifier tracked across revisions, so the name can change. Lowercase alphanumeric (with hyphens/underscores). Must be unique within the model.
querystringYesPath to a SQL file containing a query that aggregates the input data.
descriptionstringNoTooltip text shown in the platform when hovering over the chart title.
coloursstring[]NoArray of hex colour strings for chart series.

Here’s a configuration that shows demand and generation as secondary charts:

{
  "inputAggregations": [
    {
      "name": "Native Demand",
      "key": "native-demand",
      "query": "queries/input-aggregations/native-demand.sql",
      "description": "...",
      "colours": ["#008000", "#009900", "#00B300", "#00CC00", "#00E600"]
    },
    {
      "name": "Solar & Wind",
      "key": "solar-wind",
      "query": "queries/input-aggregations/solar-wind.sql",
      "description": "...",
      "colours": ["#008000", "#009900", "#00B300", "#00CC00", "#00E600"]
    }
  ]
}

The SQL file at queries/input-aggregations/native-demand.sql might look like this:

SELECT
    data_timestamp AS "datetime",
    region AS category_name,
    'Solar & Wind' AS input_name,
    (solar_generation + wind_generation) AS "value"
FROM input_data

Where the data in the table named input_data in this case would correspond to an input with the key of input_data.

Your query must return a table with the following columns:

ColumnTypeDescription
datetimetimestampThe timestamp of the data.
category_namestringThe name of the category. This is used to group the data into a chart series.
input_namestringThe name of the input. This is used to label the chart series.
valuefloatThe value of the input.

The column names must be lowercase, exactly as the table shows them. Every row must also meet these rules:

  • All four columns hold a value.
  • datetime parses as a timestamp.
  • value is a finite number.

Publish runs each aggregation against your local input files and checks the result before it uploads anything. A row that breaks one of these rules fails the publish in your terminal. A query that returns no rows only prints a warning, because your local files may not cover the dates the query filters on. On the platform, a run whose aggregation returns no rows still fails. When an input is partitioned by an axis, publish runs the aggregation once per axis value, against that value’s rows only, as each run does.

See SQL queries for the SQL dialect and how the query refers to an input.

Next Steps