Energy Demand Forecasting Template Using SQL Server R Services

This post is by Hong Lu, a Data Scientist in Microsoft’s Data Group.

Demand forecasting is an important scenario encountered by companies across different industries. Being able to accurately forecast demand helps companies get better at production planning and resource allocation, and also supports their strategic business decision-making. In particular, in the energy sector, demand forecasting is a fundamental use case that allows utilities and power generation companies to respond appropriately to future demand. That has significant impact on reducing power outages, minimizing energy waste and controlling costs. Demand forecasting is also the foundation for other use cases in the energy domain including demand-supply balancing, energy trading and grid optimization.

The Cortana Intelligence Suite from Microsoft offers customers a rich cloud and on-premises platform for advanced analytics, as well as several solutions to facilitate the implementation of their use cases. We recently published the Demand Forecasting for Energy Solution Template that enables quick deployment of a simple solution under your own Azure subscription. Today, we are adding an on-premises version of the cloud-based energy demand forecasting template – utilities that prefer deploying on-premises solutions can take advantage of the same. The newly published template Energy Demand Forecasting Template With SQL Server R Services allows a simple deployment of a machine learning based solution for energy demand forecasting within your on-premises infrastructure. Furthermore, it takes advantage of and extends the capability of your currently deployed SQL Server.

The SQL Server R Services available in SQL Server 2016 offer customers new opportunities to perform in-database advanced analytics. With SQL Server R Services, both open source R scripts and the high performance analytics algorithms in Microsoft R Server can be executed within SQL Server. Furthermore, you can continue to develop using familiar R Integrated Development Environments (IDEs) such as R Tools for Visual Studio, RStudio etc., and benefit from interactive development and debugging. The development process can be done on your local computer, while performing in-database computation without moving data in or out of the database. The produced R scripts are easy to operationalize on SQL Server. Once the R script is finalized, it is embedded into a SQL stored procedure for operationalization. This stored procedure can be invoked by SQL queries to train, persist, and score models. For more instructions and guidance on how to develop and operationalize a solution with SQL Server R Services, see the tutorials here.

Data scientists and engineers can use the Energy Demand Forecasting Template With SQL Server R Services to get a quick introduction to building advanced analytics energy solutions with SQL Server R Services. The template demonstrates an end-to-end workflow (Figure 1), including a real time data simulator, feature engineering, model retraining, forecasting and visualization. The data simulator generates simulated electricity demand data every 15 minutes and temperature data every hour. A SQL Server Agent job is scheduled to run every 15 minutes to perform feature engineering on the latest data, re-train and persist a Random Forest Regression model, and generate new demand forecast for the next 6 hours. A PowerBI dashboard template is provided to visualize the forecasted and actual demand, as well as the forecasting accuracy.


Figure 1. Workflow of Energy Demand Forecasting with SQL Server R Services

The template includes sample data, the R scripts used in feature and model development, and SQL stored procedures used in the operationalized end-to-end solution. We provide a Windows PowerShell script to automatically deploy the entire template on your SQL Server. The GitHub repository has a detailed explanation of the database tables, stored procedures and SQL Server Agent jobs to help you understand different components in the template. Once deployed, you’ll immediately be able to see how the various components are functioning. You’ll also be able to see a visualization of the forecast on a PowerBI dashboard. Furthermore, this template allows you to ingest your own data sources as well as modify and enhance the data pipeline and ML algorithms. You can find the entire collection of advanced analytics templates using SQL Server R Services here.

We hope you find the template helpful and easy to use, and we look forward to your feedback.

Hong