First published on April 4, 2019.

Introduction

At Experis Ciber, we recently did some product development for following SAP system landscape: a Software-as-a-Service (SaaS) version of S/4HANA Cloud as transaction system and SAP Analytics Cloud (SAC) as reporting system on top. While doing so, we noticed that data modeling capabilities of SaaS S/4HANA and SAC are (still) quite limited. On S/4HANA Cloud side, one can work with the tile for “Custom CDS Views”. This comes down to putting multiple joins on top of each other. On SAC side one has the built-in aggregation that any reporting tool has. One can also do “data blending” by combining different “models” in a “story” and “linking dimensions”, which would be more like a union. However, this option is currently only available for data stored in SAC, something we wish to avoid.

Wouldn’t it be great if we could use the full potential of “Native HANA Modeling”, i.e. joins, unions and aggregations in any desired order plus the option to develop more advanced transformations with SQL? We can, if we squeeze the SAP Cloud Platform (SCP) between source and reporting system. SCP is a powerful environment for data storage, data modeling and app development.

To raise the bar a little higher: can this be done in a 100% virtual end-to-end scenario, which means that the data reported in SAC still originates from S/4HANA Cloud? Possibly. Let’s see how far we get.

And finally: can this be done with 100% free-of-charge demo systems, so poor freelancers can also do this? Challenging, but we will see.

System landscape

Below the system landscape used for this activity.

As a source system, we used an SAP demo system named “ES5”. Predecessors were not surprisingly named “ES1” to “ES4”. The following tutorial will show you how to create an account for ES5: https://developers.sap.com/tutorials/gateway-demo-signup.html. This blog gives some more info on ES5, e.g. which OData services are coming out of the system: https://blogs.sap.com/2017/12/05/new-sap-gateway-demo-system-available/.

For SCP, we used the individual trial account that any consultant can request and use. Go to following url to request this account: https://cloudplatform.sap.com/index.html. Use the “Neo Trial”, region “Europe (Rot) – Trial”, and create a database with Web Access, XS Engine and DP Server(!) switched on. Note that you will need to keep this database alive by starting it at least every 14 days or so. But then again, it is for free!

For SAC, we also used an individual trial account. For this account, no sap s-number or something of the sort is required, just a somewhat professionally looking e-mail address. Go to this url to request it: https://discover.sapanalytics.cloud/trialrequest-auto-1/. A message appears that the trial is for only 30 days. In practice, me and most of my colleagues have been able to use the system over a much longer period of time. Not quite sure how this works, so no guarantees from my side.

Connectivity

Now that you have your systems, it is time to connect them. Complexity of setting up the connections within the landscape varies strongly.

Easiest connection is between SCP and SAC. The connection type for virtual processing of data is called “Connect to Live Data”. Not all options are available for trial SAC, but option “HANA” is, and luckily our SCP is a HANA system. See below.

Trick is to give sufficient rights to the system user in SCP. Week 2 unit 3 and week 3 unit 2 of openSAP course “Analytics with SAP Cloud Platform” (cp6) provide all details on how to do this. Url: https://open.sap.com/courses/cp6. Once this live connection is up and running, connecting to a calculation view in SCP from SAC is a piece of cake. From model creation in SAC select a datasource, and a list of all reporting-capable calculation views appears.

The system landscape overview also shows a connection within SCP between calculation view and UI5 app. This is a connection based on OData technology, and is of “intermediate complexity”. More details coming in my next blog.

But then … how to connect the OData service generated by ES5 to the “bottom” of a calculation view in SCP? This was hell! Let’s deal with this step by step. First of all, what does an OData service generated by a source system look like? If your ES5 account is ok, you can go to following url … https://sapes5.sapdevcenter.com/sap/opu/odata/sap/EPM_REF_APPS_PO_APV_SRV/PurchaseOrderItems
… and your browser will display data in XML format like shown below.

Starting points for calculation views are tables, either with persistent data or virtual. The output shown above does not look at all like a table. To solve this mismatch, we need an “OData Adapter”. This can be created by using certain functions of SAP “Smart Data Integration” (SDI).

Let’s stop here for acknowledgements:

• Hồng Ngọc Vũ for putting in all the time & energy to crack this;
• Wim Snoep for pointing us in the right direction.

An OData Adapter for SCP is hard to make. The SCP itself does not seem to be able to do it. At least not in the Neo version of the trial account. Eclipse then. Much to our surprise, latest versions Eclipse with HANA Studio add-ons seem to have lost the option “Add Cloud System”, which is required to create the adapter. Fortunately, older software versions are still available, and do have this option. We were able to create the OData Adapter with Eclipse Neon.3. The procedure to accomplish this is nowhere neatly written down, but we can give a few pointers, and you can contact us if you are interested in all the details:

• Generic pointer: Use Google Chrome browser to access all SAP cloud systems.
• Another generic pointer: If responses are not what you expected, check if your HANA database on SCP is still running ? …
• … and check if you are logged on as the proper SCP-user:
  • System user for system activities (your s-number)
  • Admin database user for roles and privileges (user SYSTEM according to instructions in course cp6)
  • Special database user for connectivity to ES5 and SAC, and for all developments (user DEMO in cp6)
• During creation of the database, option “DP Server” (DP = Data Provisioning) should have been switched on. This is not default. If not, start over.
• Don’t be sparse with giving roles and privileges to the database user for connectivity. The more, the merrier. We don’t know what the minimum scenario is, but we do know that the connectivity user needs all privileges related to Adapters and Remote Sources.
• Export a certificate for the ES5 system, create a Trust Store in SCP and import the certificate.
• Import the “HANA_IM_DP” delivery unit, and after that add roles and privileges starting with “sap.HANA.im.dp”.
• Your first moment of triumph is when you have the option “Add Cloud System…” available in Eclipse. Connect to SCP by entering system credentials.

• Open the SQL console and enter following code: “CREATE ADAPTER “ODataAdapter” PROPERTIES ‘display_name=OData Adapter;description=OData Adapter’ AT LOCATION DPSERVER;”. In case it does not work, try typing it instead of copy/pasting it. Seriously? Yes, seriously. Don’t ask us why it makes a difference. It just does.
• Second moment of triumph is when you have successfully created a Remote Source for an OData service from ES5. Needs to be done for each OData service separately.
• Convert an item of the Remote Source to a Virtual Table to obtain your third and last moment of triumph.

The OData Adapter is now completed and tested. From now on, you don’t need Eclipse anymore, and you can continue developing on SCP in the “SAP HANA Web-Based Development Workbench”. Check the content of the virtual table to see how we somehow magically converted the XML-rubbish shown before into neatly ordered rows and columns.

Evaluation: Does anyone know a better way to do this? Somehow, it is hard to believe that it is necessary to install old software and grant 10 million roles and privileges to a database user to make this work.

HANA Modeling

HANA modeling in the web-based environment on SCP is somewhat less user-friendly than with Eclipse, but we better get used to it, as the WebIDE will stay and Eclipse probably won’t.

We tried some HANA modeling with ES5 data, e.g. joining Purchase Order Item data with Header data, but it all seemed quite fragile. Any joining in the calculation view led to error messages when previewing data. As we focused on the end-to-end scenario, we did not investigate further. Aggregations, however, went ok.

Report in SAC

Last step in the end-to-end scenario: a report in SAC. This consists of two parts: first create a “Model”, second create a “Story”.

Creating a model does not mean much when using a live connection. It is assumed that all data modeling is done in the system to which SAC is connected. On SAC side, it comes down to selecting the calculation view and pressing the “Save” button.

Creating a story leaves more room for creativity. One usually starts with a canvas page. Below a simple example with a table, a graph and a story filter.

I am not saying this is the best report in the world. But it is the outcome of the end-to-end scenario we promised at the beginning. As data is stored nowhere else but in the ES5 transaction system, it is a 100% virtual scenario! And using only free-of-charge systems.

Epilogue

In an upcoming blog, we will be doing more HANA modeling and we will compare graphical reporting in SAC with developing a data visualization app with UI5. Be patient for a week or so.

Two new openSAP courses are planned to start soon: “SAP Cloud Platform Essentials (Update Q2/2019)” (cp1-3), starting April 29th, and “Evolved Web Apps with SAPUI5” (ui52), starting May 8th. Programs of both courses appear to have quite some overlap with what we have been doing, so if you are interested in topics such as presented here you can enroll in these courses at following urls:
• https://open.sap.com/courses/cp1-3/
• https://open.sap.com/courses/ui52/

Feel free to comment or ask questions. You can also contact me via direct messaging. For questions regarding the OData Adapter you can contact Hong Ngoc Vu.