LinkedIn
Twitter
Google Cloud Platform (GCP) offers a wide range of Artificial Intelligence (AI) and Machine Learning (ML) Products which can help businesses to automate tasks, improve decision-making, and personalize experiences. One such GCP offering is BigQuery Machine Learning (ML) models that support a variety of use cases such as Running Predictions and Demand Forecasting . In this blog post, we will explore how customers can natively consume predictions of BigQuery ML models from their SAP landscape by using ABAP SDK for Google Cloud, a new addition to Google Cloud SDK.
Problem Statement
A new online retailer uses SAP to enter and process customer orders. The retailer wants to optimize and streamline the order fulfillment process. Specifically, the retailer wants to select the most cost efficient “Order Routing” option among:
- Fulfillment from own warehouse
- Forward to a 3PL partner
- Direct shipping from OEM Manufacturer / Vendor
The decision depends on a variety of factors such as:
- Whether the item is in stock in own warehouse
- What is the Delivery date of the order
- Shipping distance and cost associated with each option
- Customer category — Enterprise, Small Business etc.
Imagine an order handling specialist sifting through multiple datasets and running complex queries to decide the optimal order routing. This would be a cumbersome process, often leading to errors, increased operational costs, and unhappy customers.
In the below sections, we will explore how the retailer can solve this problem by using BigQuery ML and ABAP SDK for Google Cloud.
Business Impact
By implementing the below solution using ABAP SDK for Google Cloud, the retailer can achieve the following efficiencies.
- The manual review process will be reduced thanks to the BigQuery ML model, which will do the heavy lifting of predicting order routing.
- A model that is continuously trained will provide more accurate and cost-effective predictions, which will lead to lower operating costs.
- By taking the delivery date into account, the model will ensure that orders are delivered on time, leading to increased customer satisfaction.
Setting up BigQuery ML
If you are new to BigQuery, please take a moment to familiarize yourself with:
- BigQuery Product Features and Capabilities
- Various methods to load data to BigQuery
Once the dataset is created and is ready, data can be loaded to BigQuery. Then, with just 2 SQL commands, the retailer can create and train a ML model, and run “Order Routing Predictions” on their dataset. As new data gets loaded to BigQuery, the model is continuously trained to provide better predictions.
Solution Overview
After setting up a BigQuery ML model in GCP, the retailer’s ABAP Developers can use ABAP SDK for Google Cloud to:
- Load data to BigQuery using Pub/Sub, and
- Read predictions using the BigQuery ML Model,
All from within the SAP landscape and using the programming language they are familiar with — SAP ABAP.
Solution Components and flow
Detailed Steps
In the sections below, we will dive deep into various steps in GCP and SAP.
Setting Up ABAP SDK for Google Cloud and Cloud Resources
Let’s take a look at some key configurations that should be completed on GCP and SAP.
- As the first step, the retailer’s administrators will install and configure ABAP SDK on the SAP Landscape.
- Then, their Cloud Administrator / Developer will create below resources on GCP.
- Create Tables to hold data from SAP
- Create and Run a BigQuery ML Model to predict routing.
CREATE OR REPLACE MODEL `bqml.zorder_routing`
OPTIONS (model_type='linear_reg',
input_label_cols=['delivery']) AS
SELECT * FROM `ORDER_ROUTING_DEMO.zrouting_sap_data`
WHERE delivery IS NOT NULL
SELECT * FROM
ML.PREDICT (
MODEL`bqml.zorder_routing`,
(SELECT * FROM`ORDER_ROUTING_DEMO.zrouting_sap_data`
WHERE delivery IS NOT NULL))
Note: Above are sample commands. Refer to the documentation for detailed instructions on how to implement BigQuery ML models based on your needs.
- Create an Avro schema for order data from SAP.
- Create a topic using the above schema
- Create a subscription for the topic, writing to BigQuery table
ABAP Development
Now that the configuration steps are complete, it’s time for an ABAP Developer to implement the solution in SAP by using ABAP SDK for Google Cloud.
First, SAP Outbound delivery data is published to a Pub/Sub topic by using ABAP SDK Pub/Sub API Client Stub class /GOOG/CL_PUBSUB_V1 within the implementation of SAP Outbound Delivery BADI LE_SHP_DELIVERY_PROC. The data published to Pub/Sub is replicated to BigQuery in real time, thanks to the subscription that was set up in GCP.
DATA:
ls_input_sap TYPE /goog/cl_pubsub_v1=>ty_023.
LOOP AT it_xlips REFERENCE INTO DATA(ls_lips).
DATA(lv_json_obj_sap) =
/ui2/cl_json=>serialize(
data = VALUE t_payload_sap(
delivery = ls_lips->vbeln
* ......... other attributes
item = ls_lips->posnr )
pretty_name = /ui2/cl_json=>pretty_mode-low_case ).
APPEND VALUE #(
data = cl_http_utility=>encode_base64( lv_json_obj_sap ) )
TO ls_input_sap-messages.
ENDLOOP.
TRY.
DATA: lo_client TYPE REF TO /goog/cl_pubsub_v1.
CREATE OBJECT lo_client
EXPORTING
iv_key_name = 'DEMO_PUBSUB'.
CALL METHOD lo_client->publish_topics
EXPORTING
iv_p_projects_id = CONV #( lo_client->gv_project_id )
iv_p_topics_id = 'DEMO_ORDER_DATA_SAP'
is_input = ls_input_sap
IMPORTING
ev_ret_code = DATA(lv_ret_code)
ev_err_text = DATA(lv_err_text).
CATCH /goog/cx_sdk INTO DATA(lo_exception).
"Appropriate Error Handling
ENDTRY.
Next, routing predictions are read from an SAP program by using BigQuery API Client Stub /GOOG/CL_BIGQUERY_V2 and subsequently updated in Outbound Delivery by calling SAP API.
TRY.
DATA: lo_client TYPE REF TO /goog/cl_bigquery_v2.
CREATE OBJECT lo_client
EXPORTING
iv_key_name = 'DEMO_BIGQUERY'.
CALL METHOD lo_client->query_jobs
EXPORTING
iv_p_project_id = CONV #( lo_client->project_id )
is_input =
VALUE #(
query = 'SELECT * FROM
ML.PREDICT (
MODEL`bqml.zorder_routing`,
(SELECT * FROM`ORDER_ROUTING_DEMO.zrouting_sap_data`
WHERE delivery IS NOT NULL))' )
IMPORTING
es_output = DATA(ls_output).
DATA(lt_result) = VALUE tt_prediction_result( ).
LOOP AT ls_output-rows REFERENCE INTO DATA(ls_rows).
DATA(ls_result) = VALUE t_prediction_result( ).
LOOP AT ls_rows->f ASSIGNING FIELD-SYMBOL(<ls_field>).
ASSIGN COMPONENT sy-index OF STRUCTURE ls_result
TO FIELD-SYMBOL(<ls_target_field>).
<ls_target_field> = <ls_field>-v->*.
ENDLOOP.
INSERT ls_result INTO TABLE lt_result.
ENDLOOP.
CATCH /goog/cx_sdk INTO DATA(lo_exception).
"Handle Error
ENDTRY.
LOOP AT lt_result REFERENCE INTO data(ls_result_upd).
" Call SAP API to update routing
ENDLOOP.
Sample Demo Flow
Below flow shows how data from SAP application lands in the BigQuery table.
The BigQuery ML model then predicts the routing with a certain level of accuracy, which is read directly from SAP by using ABAP SDK.
Routing prediction from ML Model
Below UI5 application provides a better visualization of the routing predictions from BigQuery ML Model.
This UI5 application can be used for “human in the loop” scenarios such as Ad Hoc validation of ML results or when the prediction accuracy percent is lower than a certain threshold. The user can override the routing based on their judgment and upon saving, routing value will be sent back to BigQuery via Pub/Sub. This process will continuously train the BigQuery ML model to provide more accurate predictions for future datasets.
These BigQuery ML Models can also be managed directly in Google Vertex AI Platform and what’s more, the inferences of Vertex AI models can be read from SAP by using the Vertex AI API Client Stub class /GOOG/CL_AIPLATFORM_V1.
Conclusion and Next Steps
As demonstrated in this use case, ABAP SDK for Google Cloud provides capabilities to use real-time inferences and continuously tune models, right from the SAP landscape.
Ready to start using ABAP SDK for Google Cloud?
Bookmark What’s new with the ABAP SDK for Google Cloud for the latest announcements and follow installation and configuration instructions.
