How to Set Up a Dashboard for Global Energy Data Analytics (Real-World Use Case)

The intermittent energy project is a global energy grid analytics platform that processes and analyzes power grid data (a.k.a. time-series data) from over 40 countries. Created by Morgan Christiansson to test the assertion that “the wind is always blowing somewhere,” the project leverages publicly available energy data to shed light on energy transition discussions.

Christiansson began collecting data for intermittent energy in Elasticsearch, switched to InfluxDB 1.x (dropped it by 2.x), and experimented with QuestDB on the side before settling on TimescaleDB. “PostgreSQL with TimescaleDB is great for my primary and only datastore. It supports updating historical data, as several data providers change their historical data,” said the developer and Rubyist, who has used SQL for decades.

Based on his use case, this guide will help you build a real-time analytics dashboard using publicly available energy data, Ruby, and TimescaleDB-enhanced PostgreSQL. The stack is suitable for energy traders, utility companies, and curious energy consumers looking to transform grid data into actionable insights.

The project’s source code is open source, with data hosted on Timescale Cloud, which proudly sponsors the intermittent energy server.

Building a Dashboard for Billions of Energy Data Points

The global energy datasets that intermittent energy collects and analyzes are made of all energy production types, mainly intermittent wind and solar energy. For Christiansson, they are “interesting to look at in high resolution.”

With energy data from over 40 countries, including 48 U.S. states and five Australian regions, Christiansson acknowledges the scale and how overwhelming time-series data can be: “I'm a bit drowning in the data as there are billions of data points.”

TimescaleDB builds on PostgreSQL (a developer’s favorite) to deliver high-speed ingestion of new data while efficiently querying large datasets, enabling real-time analytics on time-series data. This capability allows organizations to forecast demand patterns and price trends, benchmark operations against market standards, and make data-driven decisions about energy procurement and usage.

This tutorial will use TimescaleDB—the core of our modern PostgreSQL data platform, Timescale Cloud—to build an efficient dashboard for analyzing global energy data in real time. We'll cover database architecture, schema design, and more.

Prerequisites

This is a hands-on tutorial, so you'll need to have some tools installed on your machine.

• Clone the intermittent-importers repository.
• Set up a local TimescaleDB database.
• Clone the repository and check out the main branch.
• Install the latest Ruby and the dependencies via bundler.

Database Architecture

Let's start with the system's core: the database schema that will power our real-time energy data analytics dashboard. This structure handles complex time-series relationships while maintaining query performance. “I opted for a narrow table, mainly because it works well with Grafana and is easy to query,” said Christiansson.

Schema Design

The database schema is designed to track and analyze global electricity markets through six core elements that work together to provide a complete picture of power systems worldwide:

The schema is designed around four core time-series tables (generation, load, transmission, prices) and several reference tables (areas, production_types, units). Each time-series table is implemented as a TimescaleDB hypertable with carefully tuned chunk intervals. By automatically partitioning your PostgreSQL tables into smaller, easier-to-scan tables, TimescaleDB’s hypertables help speed up queries.

Areas serve as the foundation, representing everything from entire countries to specific market zones or utility regions. Whether it's ENTSOE's European bidding zones or AEMO's Australian regions, areas provide the geographical context for all our data.

Production Types standardize how we classify power generation across different markets. This allows us to consistently track everything from traditional sources like coal and gas to renewables like wind and solar, even when markets use different terminology.

Generation tracks the actual power production over time, connecting areas with their production types to show how much electricity is being produced from each source at any given moment. This is crucial for understanding the energy mix in different regions.

Load represents electricity demand, helping us understand consumption patterns across different regions and timeframes. This data is essential for grid operators from Taipower in Taiwan to IESO in Ontario to manage their networks effectively.

Transmission monitors power flows between connected areas, tracking how electricity moves across borders and between regions. This is particularly important in interconnected markets like Europe's ENTSOE or Australia's National Electricity Market.

Prices captures the economic dimension of electricity markets, storing pricing data that varies by region and time. This helps us understand market dynamics from Europe’s ENTSOE and Australia’s AEMO spot prices to CAISO's locational marginal pricing.

Database stats: Reaching 5 billion rows

Before we dive into any details of how hypertables work, let’s share the current open stats of the project’s production database. Note how compression really shines for energy data. As you can see, the production database is almost five billion rows at this point.

Hypertable Configuration

Part of the mission to create a real-time dashboard is to have a fast database. Here's how the main time-series tables are configured:

-- Create the generation hypertable
SELECT create_hypertable('generation', 'time',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);
-- Set up compression with specific ordering
ALTER TABLE generation SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time DESC',
    timescaledb.compress_segmentby = 'area_id,production_type_id'
);
-- Create compression policy
SELECT add_compression_policy('generation', 
    compress_after => INTERVAL '7 days');

The code above creates a new chunk every day and compresses the data after seven days. This configuration balances compression efficiency with query performance. The compression parameters using time, area_id, and production_type_id columns provide optimal data organization for both historical analysis and backfilling operations.

Indexing Strategy

The indexing strategy is optimized for common query patterns:

-- Time-based lookups with area filtering
CREATE INDEX idx_generation_time_area 
ON generation(time DESC, area_id)
INCLUDE (value);
-- Production type aggregations
CREATE INDEX idx_generation_type 
ON generation(production_type_id, time DESC)
INCLUDE (value);

Getting Started: Building the Dashboard

Docker is a great way to get started with TimescaleDB. Here's how to run it on your local machine:

1. Install TimescaleDB.

docker run -d --rm -it \
    -e POSTGRES_HOST_AUTH_METHOD=trust \
    -e POSTGRES_USER=$USER \
    -e POSTGRES_DATABASE=$USER \
    -p 5432:5432 \
    timescale/timescaledb-ha:pg17

Note that timescale/timescaledb-ha:pg17 is the latest version of TimescaleDB for PostgreSQL. Check our docs for more information on installing TimescaleDB from a Docker container.

2. Set up the intermittent-importers database.

To import the data, you can follow the intermittent-importers repository depending on the data source you want to use. 

Set up a dashboard

Let's create a real-time dashboard using Sinatra, the TimescaleDB gem, and Vega-Lite for visualization. We'll build a simple web application that displays energy generation data by source.

First, set up your project dependencies in a Gemfile:

source 'https://rubygems.org'
gem 'sinatra'
gem 'sinatra-contrib'  # For development reloading
gem 'timescaledb'
gem 'activerecord'
gem 'vega'
gem 'semantic_logger'

Create a basic Sinatra application (app.rb):

require 'bundler/setup'
require 'sinatra'
require 'sinatra/reloader' if development?
require 'json'
require 'vega'
require 'timescaledb'
require 'active_record'
require 'semantic_logger'

# Enable TimescaleDB extensions
ActiveSupport.on_load(:active_record) { extend Timescaledb::ActsAsHypertable }

# Load intermittent-importers models
Dir["../intermittent-importers/app/models/*.rb"].each { |file| require file }

Note that the intermittent-importers models are not exposed as a library, but we're loading them with the proper dependencies and reusing the models. Now, we'll just override the Generation model to fetch the data for the dashboard.

class Report < Generation
  belongs_to :area
  belongs_to :production_type
  self.table_name = "generation"
  scope :production_by_type, -> do
    joins(:production_type)
      .select("time_bucket('1 hour', time) as time, name, sum(value) as value")
      .group(1, 2)
  end
end

To fetch the data for the dashboard, we'll use the generation_by_source scope.

def get_energy_data
  data = Report.production_by_type
  
  {
    values: data.map do |record|
      {
        date: record.time,
        value: record.value,
        source: record.name
      }
    end
  }
end

Now we'll create the Vega-Lite chart configuration.

def generation_mix_chart
  Vega.lite
    .data(get_energy_data)
    .mark(type: "area", tooltip: true)
    .encoding(
      x: {field: "date", type: "temporal", title: "Date"},
      y: {field: "value", type: "quantitative", title: "Generation (MW)", stack: "zero"},
      color: {field: "source", type: "nominal", title: "Energy Source"}
    )
end

And then we'll create the routes for the dashboard and data fetching.

get '/' do
  erb :dashboard
end
get '/api/energy_data' do
  content_type :json
  get_energy_data.to_json
end

Create a dashboard view (views/dashboard.erb), including the Vega-Lite chart.

<!DOCTYPE html>
<html>
<head>
  <title>Energy Dashboard</title>
  <script src="https://cdn.jsdelivr.net/npm/vega@5"></script>
  <script src="https://cdn.jsdelivr.net/npm/vega-lite@5"></script>
  <script src="https://cdn.jsdelivr.net/npm/vega-embed@6"></script>
  <style>
    body { 
      margin: 20px;
      background: #f5f5f5;
    }
    .dashboard {
      max-width: 1200px;      padding: 20px;
    }
  </style>
</head>
<body>
  <div class="dashboard">
    <h1>Energy Generation Dashboard</h1>
    <%= generation_mix_chart %> 
  </div>
</body>
</html>

This setup creates a stacked area chart showing energy generation by source over time. The chart is interactive, with tooltips showing exact values when hovering over data points.

To run the dashboard:

bundle install
ruby app.rb

Visit http://localhost:4567 to see your dashboard in action.

The Result: A Real-Time Energy Data Analytics Dashboard

The chart will display real-time data from your TimescaleDB database, with automatic updates as new data arrives.

This is what the result looks like when you have data from a few countries.

More data visualization ideas for your energy analysis 

For more inspiration to build your energy application, here are a few other dashboards and energy data visualizations on the intermittent energy project.

Maps are pretty helpful for understanding where the wind blows. Another great example from research in the dashboards is "the generation % of peak" filtering by wind. Click on the images to navigate to the official dashboards.

Prices are also very insightful on maps, especially over time. Check this pricing exposure to understand which regions are more expensive—it's incredible to see this key information displayed in clever data visualization.

Intermittent energy displays prices from Europe/ENTSOE and Australia/AEMO only. The U.S. uses nodal instead of zonal pricing, which is incompatible with the current data model. Here is the latest cool visualization of this blog to inspire you to animate prices correlating with energy demands.

Conclusion

TimescaleDB has proven to be an excellent choice for energy grid analytics platforms, handling petabytes of time-series data efficiently and allowing developers to analyze it in real time. The combination of features like hypertables, continuous aggregates, and a hybrid row-columnar storage engine provides the performance and flexibility needed for real-time energy market analysis.

More importantly, this open-source stack democratizes access to energy market intelligence. Companies of all sizes can now build sophisticated analytics capabilities that were once the exclusive domain of large utilities and trading houses. Installation instructions are on TimescaleDB’s GitHub repo.

If you need added scale, performance, and enterprise-ready features, Timescale Cloud is the obvious choice (there’s a 30-day free trial available).

And don’t forget to follow @IntermittentNRG for updates on database engineering and the transition to renewable energy. Oh, is the wind always blowing somewhere? “Sometimes it’s true, others it’s not,” said Christiansson. Mission accomplished.