SAP BTP Showcase - Access the SAP HANA Cloud database underneath SAP Data Warehouse Cloud

[former_member182748]

Overview

This is the 2nd of 7 blog posts which are part of the SAP Business Technology Platform Showcase series of blogs and videos. We invite you to check this overall blog, so you can understand the full end-to-end story and the context involving multiple SAP BTP solutions.

Here you will see how to create an SAP HANA Deployment Infrastructure (aka HDI) container on SAP HANA Cloud, and persist actual sales data originated from an external system in the same SAP Data Warehouse Cloud’s existing persistence area. We will show how to provide bi-directional access between SAP Data Warehouse Cloud and SAP HANA Cloud’s managed datasets. You will also see how to expose SAP HANA Cloud & SAP Data Warehouse Cloud’s artifacts, like a table or a Graphical View, as oData services.

We encourage you to watch this quick introductory video, for getting acquainted on this scenario.

Introductory Video

Below you can see this is the 2nd step of the "Solution Map" prepared for the journey on the referred overall blog:

SAP BTP Showcase – Overall Technical Architecture

Introduction

Have you ever implemented an SQL data warehouse on top of the very same SAP HANA Cloud database tenant that serves as the platform for SAP Data Warehouse Cloud?

This is possible and referred as the "SAP SQL data warehouse Integration" approach.

In the picture below you can see the SAP HANA Cloud database tenant highlighted in blue:

SAP Business Application Studio can deploy an HDI container in the same SAP HANA Cloud database tenant where SAP Data Warehouse Cloud is also running on, as per the drawing below:

"Integrated SAP SQL data warehouse" approach

SAP Data Warehouse Cloud serves business users, so they can manage their data with autonomy from IT. IT manages high volume/complex datasets and deploys a ready-to-consume "HDI SAP SQL data warehouse" in order to meet business users expectations.

As all of this data is persisted on the same SAP HANA Cloud database tenant, we leverage an integrated approach that eliminates data duplication and data movement between the data provided by IT and data managed by business users. Think about it, this is a huge opportunity for simplification and optimization!

Depending on the scenario, this can be a much wiser architecture when compared to the hybrid approach, where a separate SAP HANA Cloud database tenant is necessary to store the SAP SQL data warehouse data, and agents, remote connections, network latency and etc. are also necessary  for communicating with SAP Data Warehouse Cloud. In the picture below, you can check the benefits of the "Integrated SAP SQL data warehouse" approach.

Benefits of the "Integrated SAP SQL data warehouse" approach

Scenario

SAP Data Warehouse Cloud provides the ability to assign the HDI container to an SAP Data Warehouse Cloud Space, which provides immediate access from the SAP Data Warehouse Cloud Editors to the objects and content of the Space assigned HDI container.

Let's follow a step-by-step implementation for the "SAP SQL data warehouse Integration" approach, developing a new SAP Cloud Application Programming Model project in SAP Business Application Studio.

Landscape

Although you will be able to watch and learn everything that is explained in this blog on a detailed technical video, we also consider you may want to implement this concept by yourself. In this case, let's guarantee you have provisioned everything you need for deploying the project.

If you still don't have an SAP account for starting your developments, don't worry... SAP is providing you with a completely free SAP trial account, so you can join our network and get access to all SAP BTP solutions (SAP HANA Cloud. SAP Data Warehouse Cloud, SAP Analytics Cloud) required for you to implement all projects & artifacts presented in the overall blog, except steps proposed in this specific blog, which require opening a ticket at SAP, what's not yet available for trial accounts.

We will use SAP Business Application Studio for developing this project. If you still do not have access, you can learn how to setup a new subscription here.

The project source code is available in the official Github SAP-samples and can be cloned in SAP Business Application Studio, so you can easily reproduce this deployment in your own landscape.

Concepts

We assume you already have SAP HANA Deployment Infrastructure (HDI) skills for deploying this project. You can watch this official SAP HANA Academy video if you want to improve your knowledge, and also take a look on the SAP Help Portal - HDI Reference documentation.

Detailed Scenario

Now, we will split the scenario in 5 main topics in order to facilitate your understanding:

1- Contextualization

In our end-to-end demonstration, we talk about an utility company that wants to optimize it's Energy Production, as already explained in the overall blog. Our data warehouse will have 4 main source tables/view:

1. Energy Consumption Actual - Quantity of energy consumed by people in Germany in MWH


2. Energy Production Actual - Quantity of energy produced by utilities


3. Energy Consumption Predicted (calculation view)- Applying a Machine Learning algorithm (presented in Blog 4: Run future sales prediction using SAP HANA Cloud Machine Learning algorithms), SAP HANA Cloud will generate predicted values for the quantity of energy that will be consumed by people in in the future


4. Energy Production Planned - Quantity of energy planned for production by utilities

2- SAP Datawarehouse Cloud accessing SAP HANA Cloud’s managed datasets

For this technical use-case, 5 years of Energy Consumption Actual data is being exported by a system of records (e.g. SAP ERP) on .csv format, and we will persist this data on an HDI container deployed on the same SAP HANA Cloud database tenant where SAP Data Warehouse Cloud is running on.

We understand that there are multiple ways to load data into SAP Data Warehouse Cloud, as already exposed in Blog 1: Load data into SAP Data Warehouse Cloud, and that we could use even more sophisticated alternatives for replicating data. However, the intention here is to stick to the technical example of populating the SAP SQL data warehouse integrated to SAP Data Warehouse Cloud.

A possible scenario, for example, is SAP Landscape Transformation Replication Server replicating ERP's data to the SAP SQL data warehouse, which is in fact, an HDI container deployed in the mentioned SAP HANA Cloud database tenant.

We will then create this table, populate it with historical data, and make it available for consumption directly from SAP Data Warehouse Cloud Editors, with no data movement at all.

3- SAP HANA Cloud accessing SAP Datawarehouse Cloud’s managed datasets

In SAP Data Warehouse Cloud, we loaded Energy Production Actual data, and created a "Graphical View" comparing Consumption and Production Actual values. This comparison provides business users with an interesting analysis.

Now, TI wants the SAP SQL data warehouse to consume the "Consumption vs Production Actual Graphical View", modeled with autonomy by business users on SAP Data Warehouse Cloud. Their intention is to expose this artifact as an API, so external applications can consume it.

You will see how to make SAP Data Warehouse Cloud's artifacts available for consumption in SAP SQL data warehouse.

4- Exposing artifacts as oData Services

The SAP HANA Cloud database tenant we use for the SAP SQL data warehouse is a powerful technical platform. In this example, we will show how to expose the Energy Consumption Actual table as an oData service, as well as expose the “Consumption vs Production Actual Graphical View” (which is managed by SAP Data Warehouse Cloud) as an oData service as well.

5- Consuming exposed oData services

We will now show how easy is to consume these oData service APIs from any browser, any SAP or non-SAP applications, SAP BTP components, or any analytic solution that provides oData support.

Hands-on demonstration

Now that you understand the complete scenario, you can follow-up on watching this detailed technical video, and check how this project can be implemented.

Technical Video

All source code and data are available in the official Github SAP-samples.

Summary

In this blog you could learn how to leverage SAP Data Warehouse Cloud's persistence area, extending it for integration with a pure SAP SQL data warehousing. This topic also demonstrated how IT can create and populate a source table that will be consumed by business users in SAP Data Warehouse Cloud, with no data duplication/movement.

All of your feedback is appreciated. Enjoy!

[pbaumann]

Hi Christiano,

thank you for put in the work to show such a scenario.

I tried to follow but was not sure at the point where to open a ticket at SAP. As I use a trial account I have this system not on my system list which is mandatory to provider. Additionally is SAP typically not supporting trial systems. So if I use DWC trial and SAP HANA Cloud trial is this scenario possible?

Thank you!

Peter

[former_member182748]

Dear Peter,

Thank you for your feedback!

I have adjusted the blog to reflect your input. This specific scenario is not yet available for trial accounts. SAP is continually improving its solutions and we will keep posting as new functionalities become available.

I hope you can exercise feeding the database using other approaches (as presented in the overall blog) and have the chance to implement this specific blog on another landscape.

I'm looking forward for hearing from you.

Regards, Chris

[pbaumann]

Thank you for your fast feedback!

I'm sure we will find a way 🙂

Nevertheless good to see at least videos how it works. I enjoy the blog series and am happy to see more. For classical on-premises people this is a wild ride to get in touch with all this cloud systems. It is getting more technical than I expected with the first steps of DWC.

But to open a ticket to connect 2 cloud systems here is a little bit odd in 2021.

Best regards,

Peter

 

[kennedydb]

Hi Christiano,

Thanks for this very useful information.

Let ask you, is this scenario also possible with the HANA database underneath SAP Analytics Cloud?

I need to connect to a table in HANA from a 3rd party tool, and store some additional data for a story in SAC. If this was possible it would be very easy and straight forward, and no additional licenses would be needed.

I look forward to your feedback.

Thanks in advance.

Regards,

David

[former_member182748]

Dear David,

Thank you for your feedback!

I'm not aware that this scenario is available for SAP Analytics Cloud.

Regarding your use case, would Data Acquisition mode meet your requirements?

Regards, Chris

[kennedydb]

Thanks for your reply Christiano,

Technically SAC has a HANA database underneath, as well as SAP BW Cloud. Right? If so, there must be a way to access that database.

BTW, I'm not sure what you mean with "Data Acquisition mode", are you referring to the Cloud Connector and Cloud Agent? We need to push data to sac more than a pulling data from SAC.

Thanks in advance for your feedback.

Regards,
David

 

[former_member182748]

Thanks for your question David,

SAP has implemented a specific feature in SAP Data Warehouse Cloud, so HDI containers can be deployed and accessed in SAP HANA Cloud underneath, as demonstrated in the video. I am not aware of such similar feature in SAP Analytics Cloud.

I was curious if acquiring data (screenshot below) would meet your requirements, as you could connect to one of the 36 data source technologies available, and technically the data retrieved is persisted in SAP HANA Cloud's underneath SAP Analytics Cloud. Does that work for your use-case?

Regards, Chris

[kennedydb]

Thanks Christiano for your quick response.

Yes, we are using the BigQuery and HANA Cloud connections, and we are trying to get the ODATA to work, as it's a more flexible option.

But, when it comes to an app to app connection, we are writing back to hana from a 3rd party app, and we have a live connection between hana cloud and SAC. It works, but you need to add a HANA Cloud license, when you already have a HANA database in SAC. That's the reason we were interested in exploring the option you mentioned for BW Cloud, but for SAC. Unfortunately, as you also mentioned, it does not seem to be available.

Regards,

David

[kennedydb]

Hi Christiano,

I'm trying to test this for a very specific scenario, to decide if using DW Cloud is the best approach.

Do you know if the limitation on trial accounts has changed since this first message, as I'm not able to create the ticket either.

Thanks in advance for your feedback.

Regards,

David

 

[former_member182748]

Hi David,

I'm aware that the process to connect DWC and SCP is planned to be simplified, but we still do not have a date to share. SAP is targeting to provide the mapping without opening a ticket, and then, this scenario potentially will be solved.

I will share new information as soon as I have them. I'm really sorry for not having an immediate solution for you at this moment.

Regards, Chris

[kennedydb]

Hi Chris, thanks for your quick response.

In the mean time, while we cant test it, let me ask your opinion on the scenario we envision for our customers.

We are a new Partner Edge Build partner, and we have a Simulation & Optimization tool that we are integrating with the customers ERP/MRP data, and with a planning model we build in SAC. For the data integration we are using tables in SAP HANA Cloud, with python and connecting HANA live to SAC. But by doing this we are limited on the planning functions on the hana views.

For this reason we were looking to replace SAP Hana Cloud with SAP Datawarehouse Cloud, and use the underlying hana component to create the tables we need for the sim & opt tool data integration using python, and use DWC and SAC for the planning process.

We need to be sure this can all be done under the same DWC tenant. Not by adding three different tenants, one for SHC, one for DWC and one for SAC..

I look forward to your feedback.

Thanks and regards,

David

[former_member182748]

Hi David,

Seems to be an interesting solution. There are details to be evaluated. We can have a quick call so you can clarify some questions I have, and we can discuss about the feasibility - or not - of the approach you are envisioning.

My email is christiano.hage@sap.com, drop me an email and we schedule a call if you want.

Regards, Chris

[jonathanhaun]

Support does not seam to know how to set this open. Ticket open all day long and the only reply was that they needed more information. 😞

[Aravind03]

Hi Christiano,

Thank you for the wonderful blog & video tutorial.

We have tried to replicate the same by following all the steps (using the same project from GIT Hub).

The project build was successful, but getting the below error upon deployment to CF sub-account:

Service plan hdi-shared not found.

But that's the whole point right, creating an HDI container on HANA DB within SAP DWC without another subscription on SAP BTP. Please note that our SAP DWC tenant is standalone and not a subscription service on SAP BTP.

Kindly advise, If we are missing something.

Thanks & Regards,

Aravind

[Aravind03]

Let me answer my own question.

 

HANA Cloud service is not required in Cloud Foundry sub-account, since we are making use of the HANA DB within SAP DWC.

Still 'HDI container' service plan must be subscribed on CF sub-account, which is free of cost.

 

Once again, thanks a lot for this wonderful tutorial. The interoperability between SAP DWC & HANA XSA application is very powerful - similar to BW/4HANA.

 

Regards,

Aravind