Pulling data from the onDemand SOP system back into ECC

[vikas_ohri2]

Pulling data from the SAP OnDemand S&OP system back into ECC

Target audience: IT specialists that do ETL (extract, transform, load) & ABAP Programming, business analysts, and project managers.

Overview:

Common prevalent approach with the S&OP tool is that there is a one way approach to data being provided from the source system (typically SAP/BW/etc.) to the S&OP tool. There is a desire amongst customers to have a more holistic approach wherein the data that is manipulated within the S&OP tool to flow back to the source system (ECC in this example).

REST (Representational State Transfer) based web services call can help achieve the business requirements from a technical perspective.

This document illustrates various options for Data Export from S&OP and covers the configuration and coding steps necessary in the ECC (ABAP) environment  for working with REST based services over HTTPS (e.g. consuming the REST based services of SAP Sales & Operation OnDemand Cloud Solution).

Purpose: Optimize the data from the S&OP tool in the native ECC environment by extracting key figure data from an S&OP system to transform the data to the ECC system/Use those in Analytics on a web based or Mobile application.

SAP Sales and Operation Planning (S&OP) Data Integration:

SAP Sales and Operation Planning (S&OP) Cloud Solution is a vital cog in the enterprise planning integrating  tactical execution of Sales Planning, Operational Planning & Financial Planning processes with Enterprise Strategic Planning to make strategy actionable.

S&OP increases the visibility of data to support decisions around sales forecasting, budgeting scenarios, replenishment, cost controls, customer service, and and finance impacts. It works with data from source systems such as SAP Enterprise Resource Planning (SAP ERP) and SAP Advanced Planning Optimization (SAP APO).

A Common approach when working with the S&OP tool is that data need to be provided from the source system (typically SAP/BW/etc.) and it can be achieved with

using Self service Web UI S&OP Tool for Data Integration for manual data loads or tools like HCI (Hana Cloud Integration) Data services automating the ETL ( Extract, Transform , Load) process.

S&OP collects data from the source systems (ECC, BW, APO, legacy, etc.) and then returns data to the source systems for manipulation and analysis.

For example at the end of Statistical forecasting process of Demand Phase, the demand units need to be used as input for Plan Independent requirements process in the ECC system

For Such Data Export requirements various technical approaches can be adopted.

• HCI ( Hana Cloud Integration ) tool provides write back to flat file  functionality . This Flat file residing on premise can be read using native ABAP Application Program or ETL Tool like BODS ( Business Object Data Services) to write back into on premise source systems.
• HCI tool can use Web services as Data Store type which can be used to execute BAPI calls. SAP provides Standard Business API’s (BAPIs) like BAPI_REQUIREMENTS_CREATE & BAPI_REQUIREMENTS_CHANGE ( for the example scenario requirement for creating independent requirements). These BAPI interfaces can be used as endpoints in SOAP/XML based Web services interface , via SAP Transactions SE37 and SOAMANAGER (Service Oriented Architecture – Replacing WSADMIN/WSCONFIG)  to create SOAP based Web services Interface & its operations accessible via WSDL ( Web services Descriptive Language). HCI Data flows can use Web services and XML Map transform types to transform data to interface with it.

The SAP Sales and Operations Planning 3.0 SP1 provides a data export API which is a RESTful web service. This is an SAP native, delivered api that is supported by SAP.

Resource URI of the API is  <http or https>://<server URI>/sap/sop/sopfnd/services/analytics/sopa.xsjs

Example: https://yourdomain.com/sap/sop/sopfnd/services/analytics/sopa.xsjs

Note:POST method is used & Requests & Responses are formatted as JSON (Javascript Object Notation) and responses by default by this web service.

This S&OP API enables the export of key figure data from the S&OP application so that the data can be consumed by ETL tools , integration applications (such as SAP Process Integration) , directly from operational systems (example but not limited to ECC.) So using it leads to further options.

• On Premise ETL tool like BODS can Invoke S&OP’s data export API  using REST (Reprsentational State Transfer) call to get data back to on premise  source system.

• If the requirement is to extract the S&OP data for analysis in operational (ECC) system and/or data manipulation needed is in external application then

Asynchronous JavaScript based constructs or Web server scripting like PHP constructs can easily invoke  RESTful web services that would include S&OP Data Extract API to pull Key figures data . Also, since ECC system comprises of Web Application server which supports HTML5 & Web application development including Javascript/JQuery and integration with other web server scripting technologies like PHP or NODE.js , and ECC could be used as platform to build Web or Mobile Application based on S&OP Key Figure Data Export API.

• If source system is ECC environment  we may use ABAP HTTP Client REST Web Service Calls to pull S&OP Data from On Demand Cloud Solution.

Earlier releases (prior to 3.01) of S&OP used SOAP (Simple Object Access Protocol) based API.

REST is similar to SOAP in that both are web services work over Internet Protocols HTTP / HTTPS as a communication channel.

SOAP uses WSDL document that describes the service Interface and a proxy class is generated  & Simple Object Access Protocol ( SOAP) defines the way methods are called. REST (Representational State Transfer) is considered Lighter weight as there are different URLs for each method & HTTP method commands like GET, POST or DELETE are used with URL to determine what method is being invoked. In REST input parameters can be passed as URL parameters , while in SOAP input parameters are passed in message body in XML format. REST service there’s flexibility to use XML or JSON or OData format.

Listed below are High Level Implementation steps to call REST Web services using HTTPS/SSL ( Secured Socket Layer) Communication Protocol .

• The source planning area should be configured and available in S&OP.
• The user id has security authorizations to view all data for the source planning area
• ECC(ABAP) System is running HTTPS service . All requests to OnDemand S&OP server uses secured communication channel.
• ECC (ABAP) System has been configured as Trusted System to Communicate with S&OP. SSL certificates need to be imported into ECC PSE
• ECC (ABAP) System is setup to communicate outside the System Network Firewall. Client Proxy , if required has been setup.
• S&OP On Demand Host is set up as External Server RFC Destination communicating using Internet Protocol.
• Custom ABAP application program or custom ABAP Class if it to be used  as handler class  to an ABAP based REST web service.

It can be written using following building blocks around ABAP Class CL_HTTP_CLIENT & Its Methods

CREATE_BY_DESTINATION

IF_HTTP_REQUEST~SET_HEADER_FIELD (  'Content-Type' = 'application/json' )

IF_HTTP_REQUEST~SET_METHOD (use POST)

IF_HTTP_REQUEST ~SET_CDATA

for example : if a user wants to obtains key figure data for Sales Forecast Quantity and Marketing Forecast Quantity between July 1st, 2013 and September 30th, 2013.

Or  the customer ID, product ID, period ID, sales forecast quantity, and marketing forecast quantity for all entries in the ?Phone (product family) in the SAPMODEL1 planning area

JSON string for the method shall be as follows – It Sets the HTTP body of this CL_HTTP_CLIENT entity to the given char. data

{ "ACTION":"getDataExport",

"plarea":"SAPMODEL1", "select":"SM1CUSTID,SM1PRDID,PERIODID0,SALESFORECASTQTY,MARKETINGFORECASTQTY", "orderby":"SM1CUSTID,SM1PRDID,PERIODID0", "filter":"IN(SM1PRDFAMILY,'x Phone')ANDBT(PERIODID0,'2013-07-01 00:00:00.0','2013-09-30 23:59:59.999')"}

IF_HTTP_CLIENT~SEND (Actual communication to the server shall be performed by invoking this client object method)

It puts the program in wait state until the server responds with exception or success. In this method call you can use a timeout parameter as attribute to specify how long could be the program wait state time.

IF_HTTP_RESPONSE~GET_STATUS

Status of Communication call to S&OP can be inquired. status = “200” is OK status  and considered communication success.

IF_HTTP_CLIENT~RECEIVE (Get the Server Response )

Note here you are not actually calling back to server to get data, but calling the ECC Internet communication Manager ICM.

IF_HTTP_RESPONSE~GET_CDATA  (Delivers the HTTP body of  Response entity as character data)

Response shall have response data in JSON format as indicated below

[{"SM1CUSTID":"101","SM1PRDID":"x107 Phone","PERIODID0":"1055","PERIOD_FROM":"2013-07-01 00:00:00.0000000","PERIOD_TO":"2013-07-31 23:59:59.9990000","SALESFORECASTQTY":"1234.136843","MARKETINGFORECASTQTY":"1221"}{"SM1CUSTID":"101","SM1PRDID":"x107 Phone","PERIODID0":"1056","PERIOD_FROM":"2013-08-01 00:00:00.0000000","PERIOD_TO":"2013-08-31 23:59:59.9990000","SALESFORECASTQTY":"1219.5","MARKETINGFORECASTQTY":"1219.5"}{"SM1CUSTID":"101","SM1PRDID":"x107 Phone","PERIODID0":"1057","PERIOD_FROM":"2013-09-01 00:00:00.0000000","PERIOD_TO":"2013-09-30 23:59:59.9990000","SALESFORECASTQTY":"1279.643158","MARKETINGFORECASTQTY":"1279.63"}{"SM1CUSTID":"102","SM1PRDID":"x102 Phone","PERIODID0":"1055","PERIOD_FROM":"2013-07-01 00:00:00.0000000","PERIOD_TO":"2013-07-31 23:59:59.9990000","SALESFORECASTQTY":"1234.136842","MARKETINGFORECASTQTY":"1221"}]

The Data Response string can be parsed further to read monthly Sales Forecast Quantity and Marketing Forecast Quantity between July 1st, 2013 and September 30th, 2013.

IF_HTTP_CLIENT~CLOSE (The connection to the external server closed)

Summary: Several Data integration approaches exist to Export Data from OnDemand S&OP solution. ECC(ABAP) system can be used to pull data from external server using REST web service. Possible use of it could be in cases of complex HCI data integration scenarios ,involving ABAP Query and Custom ABAP Data flow Transforms , when  source system (ECC)  needs to inquire target S&OP before updating the target. Consuming web services using  Asynchronous JavaScript or similar open technologies based constructs are well known methods, I will share one such example “here link to be added” of Mobile Application Executing  HCI Tasks or Jobs.

.