Sales forecasting in SAP with Amazon Forecast
Sales forecasting is the process of predicting how much of something (a product or a service) someone (a company, a salesperson, a retailer etc.) will sell. To illustrate the importance of accurate forecasting, consider the case of a fashion e-retailer that sells products online. By predicting the demand correctly, they can maintain right level of inventory (reducing operational cost) at the right fulfillment location (reducing delivery expense) and deliver to their customer quickly (improving customer satisfaction). Reliable sales forecasting can play a major role in an organization’s success as it enables important downstream decisions like:
- Strategic forecasting: what is the projected growth in terms of total sales/revenue? Where should the business be (more) active geographically?
- Operational forecasting: How many units of each product should be purchased from the vendors and with what lead time, and also, in what region should they be placed? How much manpower is required to meet demand?
- Tactical forecasting: How should promotions be run? Should products be liquidated? Which geography they should run in?
Over the years, Amazon.com has remained a leader in sales forecasting. AWS built Amazon Forecast, using the same technology as used at Amazon.com to help our customers deliver accurate forecasts, even without machine learning experience. Amazon Forecast uses machine learning on time series data and metadata (e.g. product features, holiday calendars, etc.). In this blog post, I will discuss how SAP S/4HANA (or earlier releases of SAP ERP) can be integrated with Amazon Forecast to predict future sales. To keep this blog simple, I have only considered date of sale as the feature. However, you should consider all features like product attributes (color, shape, size etc.), time of the year, seasonality etc. that are relevant for your use case for reliable forecasting.
At a high level, the solution consists of three steps:
- Extracting Data from SAP S/4HANA: Sales line item data in SAP resides in table VBAP with header information in table VBAK. Once we understand the schema and format in which Amazon Forecast expects the data, we must:
- Write an ABAP report to extract data. Here is the code snippet that I used.
- Upload this data to an S3 bucket in the region where you want to consume Amazon Forecast: There are a couple of options to automate this process. For example, consume Amazon S3 REST API or call an external OS command (aws s3 cp) configured in SM49 via SAP function module SXPG_COMMAND_EXECUTE in the data extraction ABAP report.
- Configure Amazon Forecast: Once the data is in S3 bucket, all it takes is a few clicks to generate a forecast in Amazon Forecast console. You do not need to be a data scientist or machine learning expert; simply follow the detailed configuration steps in Amazon Forecast blog post.
- Consume predicted data in SAP S/4HANA or SAP Fiori for reporting: Once the forecast is ready for consumption, we must make it accessible from the SAP system. We use the following additional AWS services:
Let’s understand the flow first.
Amazon Forecast provides a QueryForecast API, which retrieves the forecast for a single item, filtered by the required criteria. We are calling this API on forecast created in Amazon Forecast via an AWS Lambda function and passing the results to the SAP application via API Gateway. API Gateway also validates the credentials passed by SAP against the credentials stored in AWS Secrets Manager before it allows a call to QueryForecast. Sounds simple, right? Let’s dive deep into the configuration steps.
API Gateway configuration
Follow the steps as mentioned as detailed at creation of REST API with Lambda proxy integration. Here is the snapshot of API Gateway GET operation.
To get the forecast for a specific item in a given time span, the SAP system calls GET operation on API Gateway with parameters item id, from date and to date. API Gateway is configured to invoke a Lambda function with proxy integration so these parameters are passed as-is to the Lambda function.
Code snippet in python below provides detail of how request and response are processed in lambda function attached to API Gateway. First we validate the credentials passed by the SAP system against credentials stored in AWS Secrets Manager.
After successful credential validation, we extract the parameters passed by SAP systems, such as item id, from date, to date.Then, invoke the QueryForecast API with these parameters.
On successful execution, Amazon Forecast returns data in a JSON format. You can format the result in Lambda before returning to SAP. For example, I converted them to the following schema for reduced complexity of conversion to the ABAP internal table format in SAP system. You can customise it to your needs or process it in the SAP system, if you’d like.
Here is the code snippet in Lambda for this conversion where variable response contains the raw JSON restored by Amazon Forecast.
Finally, we return the result from Lambda function.
That’s all for API Gateway. What about SAP? How is SAP calling GET method on API Gateway? Let’s look at that next.
SAP System configuration
- Create RFC Destination: In the SAP system, I have an RFC destination of type “G” with target host as invoke URL of API Gateway and service no 443 (HTTPS standard port).
To secure QueryForecast API, I am using basic authentication and I maintain user and password in RFC destination. In this solution, credentials passed by the SAP system (as stored in RFC destination) are matched by the Lambda function with the credentials stored in AWS Secrets Manager before access to QueryForecast API is allowed. You can also use API Gateway Lambda authorizer for custom authorization for example with Amazon Cognito or external authentication provider.
As the API Gateway endpoint is HTTPS enabled, we need to import the SSL client cert of Amazon API Gateway to the SAP system with transaction STRUST or STRUSTSSO2. Refer SAP KB 2521098 for configuration steps. Also ensure the SAP system is configured for TLS 1.2 (Refer SAP note 510007).
- ABAP Program to call GET API: SAP provides a number of utility classes to invoke HTTP/S endpoint. Here is one such example to invoke REST API using utility class CL_REST_HTTP_CLIENT for an external RFC Destination. Here is the code snippet to call this function in an ABAP Program.
On successful GET method invocation SAP will receive JSON response which can be converted to ABAP internal table with another utility class /UI2/CL_JSON. Refer to SDN post – One more ABAP to JSON Serializer and Deserializer.
For above mentioned JSON format returned by our Lambda function, the ABAP code snippet for conversion from JSON to an ABAP internal table format is as below
That’s it– pretty easy, right? Here is the result of execution in my SAP system.
We have extracted SAP sales data from the SAP application, created a sales forecast with Amazon Forecast, and retrieved it to SAP via a REST API using Amazon API gateway and an AWS Lambda function.
You’ve now successfully integrated SAP with Amazon Forecast. To clean up your AWS account and to avoid ongoing charges for resources you created in this blog you should delete following:
This was a simple example of how to integrate SAP and perform sales forecasting with Amazon Forecast. As for next steps:
- You can configure this solution to update sales forecasting on a periodic basis with similar steps as listed earlier.
- Consume this data and build dashboards in SAP Fiori for easy visualisation.
- Amazon Forecast can be used on any time series data, so you can explore usage for other scenarios including expense prediction, revenue prediction, and more.
Now Go Build.