Integrating SAP Datasphere with SAP Integrated Business Planning, using SAP CI-DS - Part 3

[Domnic]

In my previous blogs, I did write about the same data exchange, but using SAP Integration Suite - SAP Cloud Integration as a middleware. In this blog, I like to highlight how we can achieve this using SAP Cloud Integration for data services (SAP CI-DS). For handling large data volumes, it is efficient to use WS-RFC as a protocol between SAP IBP and CI-DS. For sending or receiving data from a space in SAP Datasphere, we use a database connectivity

Fig. 1 Using SAP C-DS for bi-directional integration between SAP IBP and SAP Datasphere.

Prerequisites while using SAP CI-DS

In every integration, we do need to care about security, protocols data sources and targets. While using SAP CI-DS the following details have to be considered,

1. You probably have a planning area in IBP which has a key figure and associated root attributes. Identify these details. In my example I am using the CONSDEMAND as a key figure with root attributes PRDID, LOCID, CUSTID, UOMTOID and date.
2. SAP CI-DS comes with an on-premise agent. Make sure you have the PSE configured for your agent. You can refer to the SAP Note:- 3166596
3. SAP Datasphere uses SAP HANA Cloud database. This database also uses an ODBC connectivity. If you have set up the diver, it is fine. If not, you have to download it from the software center. To open this download file, you need a SAP CAR unzip utility. Make sure you have this utility.

User in SAP Datasphere

Database access in Datasphere is restricted to users with specific roles. Navigate to the Space management menu and create a new Space in Datasphere. Now open this new space or your existing one and click on the Database Access menu option. Create a new database user (see screenshot below). This action also creates an Open SQL Schema to which you can grant read and write access.

Fig. 2 User in SAP Datasphere with database access

Once this user is created, you can use the “Info” icon on the far right corner of your data base user to view the access credentials. Please 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. You can now create a Table using the SAP Hana Database explorer. I have created two tables – IBPDEMAND to read data from Datasphere and the IBPFORECAST to write forecast data from SAP IBP.

Configuration for ODBC connectivity on the CI-DS Agent

A SAP ODBC driver is available for the HANA Cloud database which is used by Datasphere. You can follow the steps outlined in this SAP Note:- 2970243 to download and install the ODBC driver In my configuration the driver version was 2.19.21. Once installed, you have to create a Data source name. I tried this on a Windows machine. In Microsoft Windows 10, there is a utility called:- ODBC Data Source Administrator. Use the 64 bit version of this tool to create a System Data Source Name. Below steps explain how to do this.

1. Open this tool – ODBC Data Source administrator (64-bit). Navigate to the System DSN. Press the Add button to create a new Data Source.
2. In the pop up window, select the HDBODBC driver and press finish. It will be visible only after you have installed the HANA ODBC driver on that machine.
3. In the new ODBC Configuration for SAP Hana window – enter a Data source name, a description, and the server Host – which is the url of the database schema which you would have got on the previous step.  Mine was looking like - XXXXXXXX-81YY-YYYY-1111-XXXXXXXXXXXXXXXXXXXXXXX.hna0.prod-eu20.hanacloud.ondemand.com:443  (the numerals and alphabets are masked for anonymity here).
4. As a database type – select SAP HANA Cloud from the drop down menu.
5. Click on the Advanced parameters button on the bottom left side and add the connection property - sslValidateCertificate with Value FALSE.

These steps are done on the same machine where the on-permise SAP Data Services Agent is installed. Press the test connection button to make sure that there is a connectivity between this on-premise agent and the HANA database from SAP Datasphere. Note down the Data source name as you would need this later. Also take a read that this SAP Note: - 3157902. Below is the screen shot of my configuration,

Fig. 3 Data Source name configuration for SAP HANA Cloud database

Configuring a data store in SAP CI-DS

Once the above steps are done, we can log-in to the SAP CI-DS cloud instance to create a new data store for SAP Datasphere. Here we are referring to the DSN name which you created on the machine where the CI-DS agent is running.

1. Login to the SAP Cloud Integration for data services for your Organization. Select the Datastores menu option.
2. Use the + button on the top left corner to create a new datastore. Enter a new name for this datastore and a description.
3. Select the type as Database and the database as SAP HANA from the drop down menus.
4. Select the name of your CI-DS Agent from the machine where you configured a Data source name in the previous step.
5. Under the credentials section – select the HANA version as HANA 2.x, select the option use Data soure (ODBC) as yes and enter the name of your DSN on that machine where your CI-DS agent is running.
6. Enter the username and the password. Under the Connection section, select the Use Client Certificate Authentication as No from the drop down menu.
7. Press SAVE and test the connection. The new datastore configuration looks like below for me,

   Fig. 4 New SAP Datasphere datastore configuration which uses ODBC

8. Once the testing works fine, take a look in to the tables tab to see the tables you have created in Datasphere. To do this, navigate to the tables tab. Press the Imports Object button. This would make a call to the on-premise agent which would use the DSN configured in your machine to get the metadata from the remote SAP Datasphere’s HANA Cloud database.  In my example, the two tables I created are now visible in CI-DS.

Fig. 5 Tables created in SAP Datasphere, visible in CI-DS. They can be used as source or targets

Reading and writing to SAP Datasphere

Using the above datastore one can use the tables listed a either a source or a target in a data flow. For this, you can create a new project, and use the tables from SAP Datasphere as a source to read data from the table. You can also select the IBP planning area as a target in that project. Once the Source and targets are done, then you can insert a new mapping between them. My data flow looks like the following,

Fig. 6 Dataflow in SAP CI-DS which uses a table from SAP Datasphere as a source.

In the above dataflow, columns from the table in Datasphere are mapped to the attributes and keyfigure names in the target staging table from a planning area. If there is any data transformation needed, we can do at this stage.

As stated before, the reason why I tried SAP CI-DS is that,

1. It is shipped with every SAP IBP sale as a bundle.
2. I observed that data exchange is much faster between the two applications IBP and SAP Datasphere compared to my previous approach
3. On the other side – it needs an on-premise agent. However, it also opens the door for many existing SAP IBP customers who want to leverage SAP Datasphere – they can do it even today using SAP CI-DS without further license costs for an integration tool.

Conclusion

In this blog series on integration between SAP IBP and Datasphere, we used two different middleware tools. Along the official integration roadmap, we will continue to share further examples as we explore the flexibility and more optimization of our solutions. Stay tuned.

Domnic Savio Benedict