The project
A Swiss energy producer active on European electricity markets receives forecasts from a market-data provider, Wattsight, now Volue Insight, through a REST API and a Server-Sent Events stream. Curves for consumption, renewable generation, temperature, plant availability and cross-border exchanges cover thirteen countries and are republished after each run of the ECMWF and GFS weather models. The analysts needed those curves in queryable local databases, with their history, and dashboards that follow each new publication.
Engineering approach
The event stream carries only the announcement of a modification. The listener re-reads the curve through the API before writing, which guarantees a complete series timestamped in the CET zone. Ensemble events are kept only for the average, so that writes are not multiplied, and consecutive events on the same range are grouped. Each country has its own processes and its own databases, so an outage on one country does not stop the others. The Python listener and the R library share the same JSON catalogues and the same table-naming convention; the .NET manager drives them without duplicating their logic.
Work delivered
Thirteen forecast databases and thirteen history databases in SQLite, kept up to date continuously, with one table per curve and its metadata, readable from R, from the Shiny application and from the manager. The dashboards re-read the API every 30 seconds and show new forecasts without any intervention.
My contribution
- Python event listener, one process per curve family and per country, subscribed to the API’s Server-Sent Events stream. On each modification of a followed curve, the new instance is read through the API and the series replaced in the country’s SQLite database, with OAuth2 token renewal, stream reconnection and a daily log
- R library, documented in an R Markdown manual, for authentication, curve search, download of series and instances, database creation from JSON catalogues, history backfill from October 2016 and Plotly charts
- Plotly Dash dashboard per country, served locally, comparing the latest wind, solar, consumption and temperature forecasts with previous runs and actual values, with the API re-read every 30 seconds
- Shiny application for browsing any curve of a database as a chart and a table
- Desktop manager in .NET and WPF embedding R through R.NET, used to edit curve catalogues, create or drop tables and launch the listeners and dashboards for each country, with corporate proxy support
Functional overview
Functional view of the toolkit. The provider’s API feeds one Python listener per curve family and an R library, which write to the country’s SQLite databases. Analysts use the data through the Dash dashboard, the Shiny application and the .NET manager.
.NET
Python
R
SQLite
Components and flows
- Server-Sent Events stream from the API to the Python listener, then each modified instance is read and the series replaced in the forecast database
- REST calls from the R library to create tables from the JSON catalogues and backfill history from 2016
- Dash dashboard re-reading the API every 30 seconds, Shiny application reading the SQLite databases
- .NET manager editing the catalogues, reading database metadata, creating or dropping tables through R.NET and launching the listeners and dashboards
Processing logic
Discuss your project
A discussion of your project’s design, development and production deployment.