AWS for SAP

Sales forecasting in SAP with Amazon Forecast

Introduction

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.

Solution Overview

At a high level, the solution consists of three steps:

  1. 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.
SELECT P~MATNR P~ERDAT SUM( P~KWMENG )
       FROM VBAK AS K INNER JOIN VBAP AS P ON K~VBELN = P~VBELN INTO TABLE IT_SALES_DATA WHERE P~MATNR IN ('MZ-FG-C950','MZ-FG-C900') GROUP BY P~MATNR P~ERDAT.

LOOP AT IT_SALES_DATA.
  DATA S_DEMAND TYPE STRING.
  S_DEMAND = IT_SALES_DATA-KWMENG.
  DATA S_DATE TYPE STRING.
  CONCATENATE IT_SALES_DATA-ERDAT+0(4) IT_SALES_DATA-ERDAT+4(2) IT_SALES_DATA-ERDAT+6(2) INTO S_DATE SEPARATED BY '-'.
  CONCATENATE IT_SALES_DATA-MATNR ',' S_DEMAND ',' S_DATE  INTO LS_FILE_TABLE.
  APPEND LS_FILE_TABLE TO LT_FILE_TABLE.
ENDLOOP.
    • 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.
  2. 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.
  3. 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.

Data Flow

Figure 1 : Data Flow

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.

API Gateway GET Method configuration

Figure 2: API Gateway GET Method configuration

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.

if ('Authorization' not in event['headers']):
        # User ID Password not provided
        return { "statusCode": 401,"isBase64Encoded" : False }
else:
    secret_name = "<secret name>"
    region_name = "<region id>"
    # Create a Secrets Manager client
    session = boto3.session.Session()
    client=session.client(service_name='secretsmanager',region_name=region_name)

    # Get secret from secrets manager
    get_secret_value_response = client.get_secret_value(SecretId=secret_name)  
    secret = json.loads(get_secret_value_response['SecretString'])

    # Get credentials from authorization header and decode to string
    userdet= event['headers']['Authorization'].split()[1]
    userdet = base64.b64decode(userdet).decode("utf-8")

    # Compare credentials
    if (secret ["<UserName>"] != userdet.split(":")[1]):
       # Password Not Matched
       return {"statusCode": 401", isBase64Encoded" : False}
    else:
	     # Password Matched, Authorization successful

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.

itemid = event['queryStringParameters']['item_id']
fromdate = event['queryStringParameters']['from_date']
todate = event['queryStringParameters']['to_date']

response = forecastclient.query_forecast(
                  ForecastArn='<Replace with your forcastARN>',
                  StartDate = from_date+'T00:00:00',
                  EndDate = to_date+'T00:00:00',
                  Filters = {
                      'item_id' : item_id
                  }
)

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.

[
  {
    "I": "string",
    "D": "string",
    "ME": "string",
    "P10": "string",
    "P50": "string",
    "P90": "string"
  }
]

Here is the code snippet in Lambda for this conversion where variable response contains the raw JSON restored by Amazon Forecast.

result = [];
    for num, res in enumerate(response['Forecast']['Predictions']['mean']):
           data = {}
           data['I'] = itemid
           data['D'] = res['Timestamp'][0:10]
           data["ME"] =  str(round(response['Forecast']['Predictions']['mean'][num]['Value'],2))
           data["P10"] = str(round(response['Forecast']['Predictions']['p10'][num]['Value'],2))
           data["P50"] = str(round(response['Forecast']['Predictions']['p50'][num]['Value'],2))
           data["P90"] = str(round(response['Forecast']['Predictions']['p90'][num]['Value'],2))
           data["P99"] = str(round(response['Forecast']['Predictions']['p99'][num]['Value'],2))
           result.append(data)

Finally, we return the result from Lambda function.

return {
            "statusCode": 200,
            "isBase64Encoded" : False,
            "body": json.dumps(result),
            "headers" : { 'Content-Type' : 'application/json' },
            "multiValueHeaders" : {}
}

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).
    Figure 3: RFC Destination to invoke REST AP

    Figure 3: RFC Destination to invoke REST AP

    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.

    SSL enabled RFC destination with basic authentication

    Figure 4: SSL enabled RFC destination with basic authentication

    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.
    DATA HTTP_CLIENT TYPE REF TO IF_HTTP_CLIENT.

* HTTP Client
CL_HTTP_CLIENT=>CREATE_BY_DESTINATION(
      EXPORTING
        DESTINATION              = '<RFC Destination Created>'
      IMPORTING
        CLIENT                   = HTTP_CLIENT
      EXCEPTIONS
        ARGUMENT_NOT_FOUND       = 1
        DESTINATION_NOT_FOUND    = 2
        DESTINATION_NO_AUTHORITY = 3
        PLUGIN_NOT_ACTIVE        = 4
        INTERNAL_ERROR           = 5
        OTHERS                   = 6
        ).

DATA URI TYPE STRING.

* FROMDATE and TODATE in YYYY-MM-DD format
CONCATENATE '/<API Gateway stage>?item_id=' SELECTEDITEM 'andfrom_date=' FROMDATE 'andto_date=' TODATE INTO URI.
CONDENSE URI.

CL_HTTP_UTILITY=>SET_REQUEST_URI( EXPORTING REQUEST = HTTP_CLIENT->REQUEST URI = URI ).

DATA: REST_CLIENT TYPE REF TO CL_REST_HTTP_CLIENT.

CREATE OBJECT REST_CLIENT EXPORTING IO_HTTP_CLIENT = HTTP_CLIENT.

* Make GET call
  TRY.
    REST_CLIENT->IF_REST_CLIENT~GET( ).
  CATCH CX_REST_CLIENT_EXCEPTION INTO DATA(LO_EXCEPTION).
      DATA(LV_MSG) = `HTTP GET failed: ` Andand LO_EXCEPTION->GET_TEXT( ).
  ENDTRY.

* Get Response
  DATA(LO_RESPONSE) = REST_CLIENT->IF_REST_CLIENT~GET_RESPONSE_ENTITY( ).
  DATA(LV_HTTP_STATUS) = LO_RESPONSE->GET_HEADER_FIELD( '~status_code' ).

* Check status Code
  IF LV_HTTP_STATUS NE 200.
    DATA(LV_REASON) = LO_RESPONSE->GET_HEADER_FIELD( '~status_reason' ).
    WRITE: LV_HTTP_STATUS.
    EXIT.
  ENDIF.

* Get JSON Data from response
  DATA(LV_JSON_DATA) = LO_RESPONSE->GET_STRING_DATA( ).

    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

    TYPES : BEGIN OF INNERMOST,
            I   TYPE STRING,
            D   TYPE STRING,
            ME  TYPE STRING,
            P10 TYPE STRING,
            P50 TYPE STRING,
            P90 TYPE STRING,
            P99 TYPE STRING,
          END OF INNERMOST.

  TYPES : PREDICTIONS TYPE STANDARD TABLE OF INNERMOST WITH DEFAULT KEY.
  DATA PREDICT TYPE PREDICTIONS.
  DATA WA TYPE INNERMOST.

  /UI2/CL_JSON=>DESERIALIZE( EXPORTING JSON = LV_JSON_DATA PRETTY_NAME = /UI2/CL_JSON=>PRETTY_MODE-CAMEL_CASE CHANGING DATA = PREDICT ).

  LOOP AT PREDICT INTO WA .
    WRITE: / WA-I UNDER 'Material' , WA-D UNDER 'Date', WA-ME UNDER 'Mean', WA-P50 UNDER 'P50', WA-P90 UNDER 'P90', WA-P99 UNDER 'P99'.
  ENDLOOP.

    That’s it– pretty easy, right? Here is the result of execution in my SAP system.

    Result in SAP Report

    Figure 5: Result in SAP Report

    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.

    Clean up

    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:

    Next Steps

    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.