Retail Sales Forecasting with Discovery Hub® and Azure Machine Learning service
Published Aug 01 2019 05:20 PM 5,985 Views

Sales forecasting is an essential task for the management of almost any retail organization. Being able to estimate sales on the lowest granularity, per retail outlet is crucial for both inventory and cost optimization.


Although it can seem easy at first glance, building a sales forecasting model that is accurate and ready to run in a production environment is a complex challenge involving both technical skills, process skills, and domain knowledge. Couple that with a set of diverse new technologies and services in the AI space, massive data volumes and hundreds of different data sources and you can understand why it has become hard to identify and prepare the right data for analytics and artificial intelligence (AI). Add to that the need to eliminate the creation of data silos and integrate the forecasts from an AI routine with existing business applications and you can understand why many enterprise organizations dedicate vast teams to this, in order to differentiate themselves in a competitive market.


Many medium and corporate sized organizations that want to start leveraging the transformative benefits of AI and experimenting with machine learning models do not have the necessary resources, knowledge, or even data scientists to get started, leaving them behind the competitive curve.


In this blog post, we’ll describe an end-to-end solution for building a retail sales forecasting model with TimeXtender’s Discovery Hub (Data management platform for building a modern data estate) and the Azure Machine Learning service. The entire end-to-end solution, including Discovery Hub, the required Azure services as well as all the prerequisites is now available through a one-click Azure Marketplace deployment.


Before we dive into the architecture and the actual code, here is a quick video from TimeXtender that describes the solution and how to get started.


High Level Architecture

This solution uses TimeXtender’s Discovery Hub to prepare the historical sales forecasting data that the Azure ML’s Automated ML (Auto ML) functionality uses to train, select, and operationalize a time-series forecasting model for multiple time-series, all while integrating this output with the existing analytics business process. Below is a high-level architecture of the solution:



Solution Overview

When you deploy the solution through the Azure Marketplace, all the required services including the Discovery Hub, Azure Data Lake Storage Gen2, Azure SQL, Azure Analysis Services and the Azure Machine Learning Workspace are deployed in sequence, eliminating the need for any manual configuration. Once deployed, we run an Azure Jupyter Notebook (hosted in an Azure Notebook VM) to connect to the data, run AutoML to create and test the best model, and predict and store the forecast sales back into Azure SQL DB.


For more information on how Azure AutoML trains a multivariate regression with lagging features for forecasting please visit the links below:




A detailed step-by-step guide to complete the deployment has been made available by TimeXtender.


Read the input

TimeXtender’s Discovery Hub prepares and cleanses all the historical monthly aggregated sales data and stores it in an Azure SQL database. The: SalesQuantity, StoreName, ProductName, ProductCategory, and OrderDate fields are used to create a time-series sales forecasting model that predicts sales by Store, ProductCategory, and Product for the next three months. In this step we read the following user parameters to be used in further steps.



Setup prerequisites

Install all necessary prerequisites, including pyodbc and sqlalchemy to connect to Azure SQL.



Connect and read data from Azure SQL

We connect directly to Azure SQL to read the historical sales data. With Discovery Hub, the dataset is already aggregated by Store, Category, and Product thus there is no need for further data preparation/manipulation within the Azure Notebook (E.g remove duplicates, aggregation, cleansing etc.).



Data Splitting

We now split the data into a training and a testing set for later forecast evaluation. The splits should be stratified by series, so we use a group-by statement on the grain columns.




The AutoMLConfig object defines the settings and data for an AutoML training job. Here, we set necessary inputs like the task type, the number of AutoML iterations to try, the training data, and cross-validation parameters.

The forecast horizon is given in units of the time-series frequency; for instance, the sales forecasting series frequency is monthly, so a horizon of 3 means that a trained model will estimate sales up to 3 months beyond the latest date in the training data for each series.

Here is a summary of AutoMLConfig parameters used for training the retail sales forecasting model:



Retrieve the best model

Each run within an Experiment stores serialized (i.e. pickled) pipelines from the AutoML iterations. We can now retrieve the pipeline with the best performance on the validation dataset:



Predict and store results back in Azure SQL



The code for this blog post is a simplified version of this GitHub sample, which includes additional steps to evaluate and operationalize the sales forecasting model.  



Building an accurate and useful sales forecasting model can be a complex, time-consuming challenge that requires extensive resources. This blog post provided an overview of how to easily create a sales forecasting model with TimeXtender’s Discovery Hub and Azure Automated Machine Learning service. You can get started by deploying the Discovery Hub® and ADLS Gen2, SQL DB, AAS, and ML Azure Marketplace template.


Please let us know your thoughts/questions in the comments section below.

Further Resources

- TimeXtender: Reversing the 80-20 rule in data wrangling for AI and machine learning

TimeXtender: Tap into the transformative power of data with Discovery Hub and Azure Machine Learning...

- Azure Machine Learning service example notebooks

- Azure Machine Learning service

- Azure AutoML

- Getting AI/ML and DevOps working better together

1 Comment
Version history
Last update:
‎Aug 26 2019 10:27 AM
Updated by: