Profile Measurement Reporting

For Západoslovenská distribučná we designed and documented the architecture of a solution that lets business analysts browse, filter and visualize 15-minute profile readings from measurements. A key design priority was maximum reuse of the customer’s existing components and zero impact on the operation of critical production systems.

The brief

The customer holds a large volume of 15-minute profile measurements that are imported in real time into the operational system (ODS). Analysts needed a tool that would make this data available in a comprehensible, visual form — including aggregated overviews, filtering and export for further work.

The main challenges:

• No load on production. The solution must not affect the performance or stability of the master instance of the operational database.
• Large data volume. The raw 15-minute profiles need to be processed and aggregated so that they are suitable for fast visualization.
• Fitting into the existing environment. The solution should make the greatest possible use of components that already exist at the customer or are currently being built (for example, the new data warehouse).

The solution

We designed a modular architecture composed of four logical systems that together form a complete data flow from source to visualization.

A read-only replica instead of touching production

As the data source we chose a read-only replica of the operational system (ODS), into which all changes from the master instance are replicated in real time using native PostgreSQL streaming replication. As a result, all reading of raw profiles happens outside the production database and its operation remains completely untouched.

Data aggregation in the data warehouse (EDW)

In the enterprise data warehouse under construction, we proposed adding a separate datamart for 15-minute profiles. It contains pre-prepared aggregated overviews optimized precisely for fast browsing and visualization, so the web application does not have to compute aggregations on the fly.

ETL processes for preparing aggregates

A set of ETL processes reads the raw profiles from the read-only replica and writes the computed aggregates into the datamart in the data warehouse. The processes run on a schedule (cron) and take care of the continuous preparation of data in exactly the structure the visualization layer needs.

A web application for analysts

The web application is where the solution delivers its value to business analysts. It queries two sources at once — raw 15-minute profiles and code lists from the ODS replica, and aggregated overviews from the data warehouse. It lets users browse, filter and visualize the data, and also export it to CSV and SQLite formats for further processing. Access runs securely over HTTPS.

Technologies used

• PostgreSQL — the production database (master) as well as its HOT-standby read-only replica, connected by streaming replication.
• Go — ETL processes run via cron, connecting to the database through the pgx library.
• Spring Boot, jOOQ, Svelte — the backend and frontend of the web application for visualization.
• JDBC — connecting the visualization layer to both the data warehouse and the ODS replica.
• Exports to CSV and SQLite for users’ further analytical work.

Benefits

• Zero impact on production thanks to reading from a read-only replica.
• Fast visualization of large data volumes thanks to pre-computed aggregates in the data warehouse.
• High reuse of the customer’s existing infrastructure — the solution builds on the data warehouse under construction and uses systems that already exist.
• Modularity — the individual parts (replica, ETL, datamart, web application) are separated and can be developed or scaled independently.
• Analyst self-sufficiency — the ability to filter and export data without involving IT for every request.