
This blog series describe how we can exchange data relevant for planning between SAP IBP and a space in SAP Datasphere. I am using two SAP middleware tools – SAP Cloud Integration, which is a 100% cloud service on BTP and SAP Cloud Integration for data services which is a hybrid solution ( with an on-premise component ) to exchange data bidirectionally. This blog series contains three sections,
The last section describes how we could integrate bidirectionally using SAP Cloud Integration for data services and first two sections describe the data exchange using SAP Cloud Integration. To support large data volumes, we use SAP Remote Functions to extract data from SAP IBP into the middleware tools. SAP Datasphere also offer a JDBC interface which can be leveraged from Cloud Integration. The software components used for this integration are presented below in a high-level block diagram below:
Fig. 1 High level block diagram of this integration using a middleware
Let’s start with the following prerequisites in place:
From SAP Cloud Integration, the efficient way of communicating with SAP IBP is using web socket based remote function calls. In step 2 of our pre-requisite, we created the credentials for communicating with SAP IBP. They are using the pre-delivered interfaces which are exposed under the communication scenario: SAP_COM_0931. We can use these credentials to create a named destination in the SAP Business Technology Platform. For this, open your sub account landing page on the SAP BTP. Navigate to your Destination menu from the left and create a new destination using the above credentials. The configuration might look like this screen shot:
Fig.2 Screen shot of the SAP IBP destination in SAP BTP – destination service.
Here, the destination name is called IBP_G5R_100. We will use this name in our iFlows to make these WS-RFC calls to SAP IBP from Cloud Integration. A pre-delivered set of helper iFlows in SAP Cloud Integration are used for this integration. These iFlows take care of making the WS-RFC calls the creating the right payloads as well as waiting for the response from the backend. We create iFlows which are specific to business processes on top of these helper iFlows. We consume the helper iFlows using process direct mechanism in SAP Cloud Integration.
Reading from SAP IBP is done asynchronously. A select query is first initialized. Input parameters for this select query can be done either via a global parameter configuration or this iFlow can be triggered from an external application using a HTTPs call. In my example, I exposed the iFlows as HTTPS endpoint and trigger them via a POST call. A Screen shot of this iFlow is given below.
Fig.3 Initializing the select query in SAP IBP using helper iFlows.
I am using a content modifier adapter to prepare a payload that can be sent to the helper iFlows via a process direct. It can be a groovy or even an XSLT mapping that can prepare the payload for this call. Here is a sample payload which I am generating in that content modifier. The inputs for this payload are prepared from the HTTP request call which triggers this iFlow.
<IBPReads>
<IBPRead Key="${header.IBPQueryKey1}"
Destination="${header.IBPDestination}"
PackageSizeInRows="${header.IBPPackageSizeInRows}"
Select="${header.IBPQuerySelect}"
OrderBy="${header.IBPQueryOrderBy}"
Filter="${header.IBPQueryFilterString}"
TypeOfData="${header.IBPQueryTypeOfData}"
TimeAggregationLevel="2"/>
</IBPReads>
If the select query has a large data set, for example 5 million rows of data, the helper iFlows can be used to fetch pages of this data. You can define the page size using the XML attribute IBPPackageSizeInRows. The Offset attribute defines how many records you can skip. The following example payload can be sent to fetch data using the helper iFlows.
<IBPRead>
<xsl:attribute name="Key">
<xsl:value-of select="if (@Key) then else $IBPQueryKey1"/>
</xsl:attribute>
<xsl:attribute name="PackageSizeInRows">
<xsl:value-of select="if (@PackageSizeInRows) then @PackageSizeInRows else $IBPPackageSizeInRows"/>
</xsl:attribute>
<xsl:attribute name="ParallelThread">
<xsl:value-of select="if (@ParallelThread) then @ParallelThread else $IBPQueryParallelThread"/>
</xsl:attribute>
<xsl:attribute name="Offset">
<xsl:value-of select="number($IBPQueryOffset1)"/>
</xsl:attribute>
</IBPRead>
If there are more pages to read, the helper iFlows returns a Boolean message property called: - IBPhasMoreRecords. The fetch helper iFlow can be called multiple times in a loop. You can exit the loop when this message property IBPhasMoreRecords is false.
Fig. 4 Fetch data from IBP
Once the data is fetched from the backend, it can be transformed to the database schema that you defined while creating a table in the space on SAP Datasphere. After transformation, this data is then written to SAP Datasphere using the JDBC adapter.
Before we write to SAP Datasphere, we need to setup a few things. We will be using the JDBC protocol and Open SQL. We need a space, table, and a database user. Let’s do this step by step:
Navigate to the Space management menu and create a new Space. Open the space and click on the Database Access menu option. Create a new database user. Below is the screen shot of the user I have created. It also creates an Open SQL Schema to which you can grant read and write access.
Fig.5 Creating a JDBC User in SAP Datasphere.
Once created, press on the “info” button on the far right corner of the user you just created and note down the following information:
Database User Name:
Open SQL Schema
Host Name and Port
Password
You can also request a new password to start with.
Select the database username you just created and press the “Open Database Explorer” menu option to the left of the search input. This will open the SAP HANA Database Explorer in a new window. On the top left of the screen, just below the SAP logo – you will see the button to open an SQL Console. Click on that to open a SQL Console and enter the following SQL command to create a new table.
create TABLE IBPFORECAST (
PRODUCT VARCHAR(50),
CUSTOMER VARCHAR(50),
LOCATION VARCHAR(50),
CONSDEMAND DOUBLE,
KEYFIGUREDATE DATE,
UNITS VARCHAR(4),
COMMENTS VARCHAR(100)
);
You can adapt the statement to suite your needs. Once done, you can click on the green “Run” button to execute the SQL statement. This will create a new table which you can view under the Catalog –> Tables menu tree on the left. I created two tables. One was to read data from SAP Datasphere called IBPDEMAND and the other was to write data into Datasphere called IBPFORECAST. The idea was to read demand relevant data and write to SAP IBP, do the planning run in IBP and then write back forecast data into SAP Datasphere.
We need to use the SAP Datasphere - JDBC credentials that we created in the previous steps to create a connection between SAP Cloud Integration and SAP Datasphere. For this,
Fig.6 Creating a JDBC Data Source in SAP Cloud Integration.
Now, we can use this JDBC data source name in our iFlows.
We use the JDBC protocol to write data to SAP Datasphere from Cloud Integration. Once you have the data that you want to write to Datasphere, we create the Open SQL Insert statements using an XSLT mapping. The section of the iFlow would look like this,
Fig. 7 Using the JDBC Data source to write in SAP Datasphere
This section is exposed as a separate iFlow which is called via a Process Direct. This iFlow is then called from the main iFlow after we fetch the data from IBP. Writing to SAP Datasphere is done in a loop as we read data in batchs. Each batch is written directly as it is read and transformed.
The SQL INSERT statement is prepared in an XSLT file. The following code snippet, generates the insert statement as documented in this help
<root>
<Insert_Statement>
<dbTableName action="INSERT">
<table><xsl:value-of select="$SAPDSPTarget"/></table>
<xsl:for-each select="/IBPReadKeyFigures/item">
<access>
<PRODUCT hasQuot="Yes"><xsl:value-of select="./PRDID"></xsl:value-of></PRODUCT>
<CUSTOMER hasQuot="Yes"><xsl:value-of select="./CUSTID"></xsl:value-of></CUSTOMER>
<LOCATION hasQuot="Yes"><xsl:value-of select="./LOCID"></xsl:value-of></LOCATION>
<CONSDEMAND hasQuot="No"><xsl:value-of select="./CONSENSUSDEMANDQTY"></xsl:value-of></CONSDEMAND>
<KEYFIGUREDATE hasQuot="Yes">2024-03-01</KEYFIGUREDATE>
<UNITS hasQuot="Yes"><xsl:value-of select="./UOMID"></xsl:value-of></UNITS>
<COMMENTS hasQuot="Yes">Inserted from XSLT</COMMENTS>
</access>
</xsl:for-each>
</dbTableName>
</Insert_Statement>
</root>
This XSLT generates an XML payload which is then passed as a body for the JDBC request. This XML payload is generated for each batch as we fetch data from SAP IBP. In the request-reply adapter, we select the protocol as JDBC and we use the JDBC Data Source name as the Data Source Alias under the JDBC -> Connection screen as shown below,
Fig. 8 JDBC Connection Details
This Once data is written to the table in the SAP Datasphere space, the JDBC request sends the following response in XMl format.
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<root>
<Insert_Statement>
<table>IBPFORECAST</table>
<insert_count>200000</insert_count>
</Insert_Statement>
</root>
The above XML response shows that, in one call, 200K rows of data were inserted into the SAP Datasphere table called IBPFORECAST.
In this blog, I shared a few details on how I,
In the next blog we take a closer look into how we can read data from a space in SAP Datasphere, transform this data and write it into SAP Integrated Business Planning using SAP Cloud Integration.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
6 | |
4 | |
3 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 |