FC2: Import Amazon Forecast data

Integration between SAP and AWS to consume the Forecast data in SAP

➡️ 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.

Step1 : Private VPC setup for API Gateway configuration
  1. Follow this VPC and Subnet create steps listed in AWS documentation. Copy the VPC ID in the clipboard.
  2. In the next step. we will create an interface VPC endpoint (interface endpoint) allows you to create a private connection between your SAP instance and AWS API gateway.
  3. Choose Endpoint from the vpc navigation pane. Click Create Endpoint.

Preparation menu

  1. In the next screen choose the below option.
    • In Service Category - Choose AWS services
    • Service name - com.amazonaws.us-east-1.execute-api
    • VPC - Choose the VPC you created in the previous step.
    • Subnet - Choose the Subnet you created in the previous step.
    • Enable DNS name - Select –> To use private DNS names, ensure that the attributes ‘Enable DNS hostnames’ and ‘Enable DNS Support’ are set to ‘true’ for your VPC (vpc-*). Learn more.
    • Security group - Select a security group with https protocol.
    • Policy - Full access for this lab.
    • Click Create Endpoint.
    • Copy the Endpoint ID to your clipboard or notes.

Preparation menu

Step2: Storing SAP system against credentials in AWS Secrets Manager.

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.

  1. Creating and storing your secret from the console. Sign in to the AWS Secrets Manager console

  2. On either the service introduction page or the Secrets list page, choose Store a new secret.

  3. On the Store a new secret page, choose Other type of secret. You choose this type of secret because your secret doesn’t apply to a database. Enter the desired username(example: Forecast_user) and desired password(example:werrsfsdfsddf). Copy the this credentials for later steps.

Preparation menu

  1. Choose Next.

  2. Under Secret name, type a name for the secret in the text field. You must use only alphanumeric characters and the characters /_+=.@-. For this lab name the secret as SAP_Sales_Forecast_UserPass.

  3. In the Description field, type a desired description of the secret.

  4. Ignore the other updates and click choose Store to save your changes.

Step3: API Gateway configuration
  1. Sign in to the API Gateway console using this link

  2. If this is your first time using API Gateway, you see a page that introduces you to the features of the service. Under REST API, choose Build. When the Create Example API popup appears, choose OK.

  3. If this is not your first time using API Gateway, choose Create API. Choose the option Create a REST API that is only accessible from within a VPC.. Click Build.

Preparation menu

  1. Create an API as follows:

    • For API name, enter SAP_Sales_Forecast.

    • If desired, enter a description in the Description field; otherwise, leave it empty.

    • Endpoint Type set to Private.

    • Provide the Endpoint id you copied during the VPC endpoint creation. Example it starts with vpce-0bd6

    • Click Create API

Preparation menu

  1. Click Create Method.

Preparation menu

  1. Select Get Request.

Preparation menu

  1. Select Lambda function as integration type.

    • Select - Use Lambda proxy Integration.
    • Select the the Lambda function you created in the previous steps.
    • Select - Use Default Timeout.
    • Click - Save.

Preparation menu

  1. Click Resource policy from the navigation pane and update the below Json IAM policy snippet to provide access to the VPC. Modify the Json to input the id of the VPC in “aws:SourceVpc”: “vpc-**” and in resource arn update your AWS account number <> and the API Gateway id.
  • “Resource”: “arn:aws:execute-api:us-east-1:1234545555:uisdadsacol///*”,

Preparation menu

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": "*",
            "Action": "execute-api:Invoke",
            "Resource": "arn:aws:execute-api:us-east-1:<aws account number>:33gfjztln4/*/*/*",
            "Condition": {
                "StringEquals": {
                    "aws:SourceVpc": "vpc-09814f5143f82921d"
                }
            }
        }
    ]
}

  1. Click Save

  2. Go back to the Resource section, select the Get method and click Deploy API, choose New Stage, give stage name as dev, desired name for description and click deploy.

Preparation menu

  1. Go back to the Resource section, select the Get method and click on the Lambda proxy to verfiy the Lambda and API gateway integration.

Preparation menu

Preparation menu

  1. Go back to the API, click on the statge option and copy the gateway url.

Preparation menu

  1. Append the url with your VPC endpoint id as follows. The yellow highlighted section is where you will input your VPC endpoint that you copied as part of Step1 : Private VPC setup for API Gateway configuration.

Preparation menu

Step4: Generating API Gateway certificates.
  1. Amazon certificated uploaded in SAP: When the SAP application connects to the API Gateway endpoint, it presents a certificate. For the SAP application to trust this certificate, it needs to be uploaded to the SAP certificate store by using the transaction code STRUST.

Preparation menu

  1. For this Lab we will create create certificate for API region (us-east-1). If this is not your first time using API Gateway, choose Create API. Choose the option Create a REST API .. Click Build.

Preparation menu

  1. Create an API as follows:

    • For API name, enter Cert.

    • If desired, enter a description in the Description field; otherwise, leave it empty.

    • Endpoint Type set to Regional.

    • Click Create API

Preparation menu

  1. Click Create Method.

Preparation menu

  1. Select Get Request and click ok option.

Preparation menu

  1. Select Lambda function as integration type.

    • Select - Mock
    • Click - Save.

Preparation menu

  1. Click stages from the navigation pane. Click dec to open the settion and copy the invoke url into a brower.

Preparation menu

  1. Then click the paddle lock –> connection secure –> and More information.

Preparation menu

  1. Click Certififcates from the pop-up screen.

Preparation menu

  1. Download the Pem(cert) and Pem(chain). You wiill upload the certifcate in SAP.

Preparation menu

Step5: Uploading Amazon certificates in SAP.

1.If you are using your own SAP environment upload Amazon certificates are using the following steps:

  • Click on the change option in STRUST SAP Transaction

  • Select SSL client standard

  • Click on the import certificate option

  • Select the folder path where the Amazon certificates are saved(previous step)

  • Click on green tick mark(ok option)

  • Click on add to certificate list

  • Click Save

  1. Repeat the steps to upload all the Amazon certificates

Preparation menu

  1. Verify if the certificated are loaded correctly.

Preparation menu

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.

Step6: SAP Configuration to complete the API gateway integration.
  1. Go to SM49 to create an RFC destination for external connections. Click on create option and provide a desired destination name and choose G HTTP connection to external server ** and click ok button.

Preparation menu

  1. In the next screen, fill in the Description 1 with a desired name, update the Target Host with the API Gateway endpoint from the previous step, update the Service No as 443.

Preparation menu

  1. Click on the Logon and Security tab. Click Basic Authentication and update the user and password that you created in AWS secrets as part of the API gateway setup for basic authentication for this lab purpose. SSL certificate is Default SSL Client Standard. Click Save

Please make sure that RFC destination SSL certificate enabled (login & Security tab) is enabled.

Preparation menu

Step7: ABAP Program to call GET API

SAP provides a number of utility classes to invoke HTTP/S endpoint. For this LAB we will 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.

  1. In SE38 transaction, create a Z program – desired namee (Z_SAP_SALES_PREDICTION) to write an ABAP report.

Preparation menu

  1. We will use the following code snippet to invoke Amazon Forecast REST API. Here is some tips on this code. Review the below notes and copy and paste the code snippet.

➡️ Note this code is only for demonstration purpose.

  • In this code we are creating the parameters for users to input the SAP Material number and Extract the forecast values iinto SAP ABAP report.
  • We are using an ABAP class CL_HTTP_CLIENT to the get request using the RFC ‘‘Z_SALES_FORECAST’ destination you created in the previous steps.
  • In DATA URI TYPE STRING the code CONCATENATES ‘/dev/SAP_Sales_Forecast?item_id=’. For an example, in the below API configuration, you will take the dev stage and SAP_Sales_Forecast method to input in the line 90 of the ABAP code. You will also change the dates based on the historical data you used in Amazon Forecast.

Preparation menu

Please make sure you update the below code snippet Abap code with your concatenate values from API gateway and the proper dates based on your Forecasting history.

Preparation menu

  • Upon executing the code, you will call the API gateway enpoint to pass the API request to the LAMBDA where the create query string to pull the Forecast data into SAP.
Copy the below code snippet into the program and make the necessary changes outlined above:
*&---------------------------------------------------------------------*
*& Report Z_SAP_SALES_PREDICTION
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Z_SAP_SALES_PREDICTION.

TYPE-POOLS:VRM.

PARAMETERS: MATERIAL AS LISTBOX VISIBLE LENGTH 30.

DATA:NAME  TYPE VRM_ID,
     LIST  TYPE VRM_VALUES,
     VALUE LIKE LINE OF LIST,
     WA1   LIKE LINE OF LIST.

AT SELECTION-SCREEN OUTPUT.
  NAME = 'Material'.

  VALUE-KEY = '1'.
  VALUE-TEXT = 'MZ-FG-C900'.
  APPEND VALUE TO LIST.

  VALUE-KEY = '2'.
  VALUE-TEXT = 'MZ-FG-C950'.
  APPEND VALUE TO LIST.

  VALUE-KEY = '3'.
  VALUE-TEXT = 'MZ-FG-C990'.
  APPEND VALUE TO LIST.

  VALUE-KEY = '4'.
  VALUE-TEXT = 'MZ-FG-M500'.
  APPEND VALUE TO LIST.

  VALUE-KEY = '5'.
  VALUE-TEXT = 'MZ-TG-Y120'.
  APPEND VALUE TO LIST.



  CALL FUNCTION 'VRM_SET_VALUES'
    EXPORTING
      ID              = NAME
      VALUES          = LIST
    EXCEPTIONS
      ID_ILLEGAL_NAME = 1
      OTHERS          = 2.

START-OF-SELECTION.
  DATA SELECTEDITEM TYPE STRING.

  LOOP AT LIST INTO WA1.
    IF WA1-KEY = MATERIAL.
      SELECTEDITEM = WA1-TEXT.
      EXIT.
    ENDIF.
  ENDLOOP.


  DATA HTTP_CLIENT TYPE REF TO IF_HTTP_CLIENT.

* Create URL
*  CL_HTTP_CLIENT=>CREATE_BY_URL(
*          EXPORTING URL = 'https://spq6srsvh2-vpce-04db9a7c10c2eb05a.execute-api.ap-southeast-1.amazonaws.com'
*          IMPORTING CLIENT = HTTP_CLIENT
*          EXCEPTIONS
*              ARGUMENT_NOT_FOUND = 1
*              PLUGIN_NOT_ACTIVE = 2
*              INTERNAL_ERROR = 3
*              OTHERS = 4
*     ).


  CL_HTTP_CLIENT=>CREATE_BY_DESTINATION(
      EXPORTING
        DESTINATION              = 'Z_SALES_FORECAST'
      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.
  CONCATENATE '/dev/SAP_Sales_Forecast?item_id=' SELECTEDITEM '&from_date=2020-03-03&to_date=2020-03-10' 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: ` && 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 reponse
  DATA(LV_JSON_DATA) = LO_RESPONSE->GET_STRING_DATA( ).

* Display JSON Data in browser by converting to HTML
  CALL TRANSFORMATION SJSON2HTML SOURCE XML LV_JSON_DATA RESULT XML DATA(HTML).
  CL_ABAP_BROWSER=>SHOW_HTML( HTML_STRING = CL_ABAP_CODEPAGE=>CONVERT_FROM( HTML ) ).

*LV_JSON_DATA = '[{"i": "MZ-FG-C950","D": "2019-08-15","ME": "9.20","P10": "-1.59","P50": "9.59","P90": "10","P99": "10"},{"i": "MZ-FG-C900","D": "2019-08-15","ME": "9.20","P10": "-1.59","P50": "9.59","P90": "10"}]'.

  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 ).


  WRITE: 1 'Material', 20 'Date', 50 'Mean', 60 'P50', 70 'P90', 80 'P99'.
  ULINE.
  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.

  1. Align the code by clicking pretty printer & click check option to verify any errors.

Preparation menu

  1. Click Activate icon and click the green check mark and Save.

Preparation menu

  1. Execute the program. Select the material and close the document display to see your report.

Preparation menu

Congradulations!: 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.