Retail Forecasting Template with SQL Server R Services

By for May 17, 2016

Report Abuse
In this tutorial, we demonstrate how to develop and deploy end-to-end retail forecasting solutions with SQL Server 2016 R Services
In this template, we demonstrate how to develop and deploy end-to-end Retail Forecasting solutions with [SQL Server 2016 R Services]( Accurate and timely forecast in retail business drives success. It is an essential enabler of supply and inventory planning, product pricing, promotion, and placement. This template will demonstrate how to build a retail forecasting solution with SQL and Microsoft R services using the sales data from the retail industry. The sample data has been anonymized and transformed before being used in this sample. The template takes the following data as input. * Historical time series data * Retail related external information The template provides weekly sales data as time series data which contains two ID fields, which is associated with the store ID and product/SKU ID. In addition to the time series data, the template uses one economic index as the external information data: real disposable personal income. This index reveals economic trends that are influential to retail customer behaviors. The dataset of economic indices is collected at the national level, so every store can share the same information. (Real Disposable Personal Income, 11/01/2009 – 01/01/2014. Source is [here]( This template shows how to build and retail forecasting models using time series methods and regression machine learning methods. In this template with SQL Server R Services, we show two version of implementation: - **Model Development with Microsoft R Server in R IDE**. Run the code in R IDE (e.g., RStudio, R Tools for Visual Studio) with data in SQL Server, and execute the computation in SQL Server. - **Model Operationalization In SQL**. Deploy the modeling steps to SQL Stored Procedures, which can be run within SQL environment (such as SQL Server Management Studio) or called by applications to make predictions. A powershell script is provided to run the steps end-to-end. The following is the directory structure for this template: * **Data** This contains the provided sample data. * **R** This contains the R development code (Microsoft R Server). It runs in R IDE, with computation being done in-database (by setting compute context to SQL Server). * **SQLR** This contains the Stored SQL procedures from data processing to model deployment. It runs in SQL environment. A Powershell script is provided to invoke the modeling steps end-to-end. See Readme files in each directory for detailed instructions. This template with SQL Server R Services is equivalent to the [template]( in Cortana Analytics gallery.