Skip to Content
Technical Articles
Author's profile photo Jon Gooding

Connecting to 10 different remote sources using DataWarehouse Cloud

In the spirit, of celebrating 10 years of HANA, I thought I would extend the 10 concept to DWC.

Having worked through quite a few Data Warehouse Cloud (DWC) connection requirements, I thought I would get this written down, and even better create a quick video of showing the connections being created.

Using the following to 10 connection types;

  1. SAP ECC
  2. SAP S/4HANA
  3. SAP BW/4HANA
  4. MSSQL Database
  5. Oracle Database
  6. HANA OnPrem SP5
  7. HANA Cloud
  8. ODATA
  9. OpenSQL Access
  10. HDI Containers

If you want to watch it all being built live (In 1 take) – please watch the video below. It shows each connection being made live. Otherwise if you want some screen shots, see the points under the video.

@SAP Employees, the un-blurred version is available internally.

The blog version:

Currently within DWC, the  adapter approach has these options for local connections:

I have included a note at the bottom about installing the DPAgent to allow to connect to non Internet facing systems.

1. SAP ECC

ECC6%20EHP%208%20Source%20System

ECC6 EHP 8 Source System

 

DWC%20Configuration%20for%20ECC

DWC Configuration for ECC

 

2. SAP S/4

Similarly for S/4 the connection requirements is the same as ECC:

S/4%20HANA%201709%20Source%20System

S/4 HANA 1709 Source System

In the DWC connection below, there is a DP Agent installed on the S/4HANA server allowing the internal host name to be used to connect to DWC.

DWC%20Configuration%20for%20S/4%20HANA

DWC Configuration for S/4 HANA

3. SAP BW/4 HANA

Similarly to ECC to S/4HANA the connection to BW/4HANA (or could be BW) uses the standard connection details

BW/4HANA%20Source%20System

BW/4HANA Source System

Using the ABAP Connector, and a DP Agent installed on the separate internal BW/4 server.

DWC%20Configuration%20for%20BW/4%20HANA

DWC Configuration for BW/4 HANA

 

4. MSSQL Database

In this scenario I am using Azure MSSQL, but the database could easily be an onpremise SQL Server environment.

Azure%20MSSQL%20Source

Azure MSSQL Source

The corresponding DWC Connection, using the reusable DPAgent:

5. Oracle Database

Using an AWS templated Oracle XE instance, I opened up the HR schema for testing the connection:

Oracle%20Source

Oracle Source

And the associated DWC Connection.

Note: The DPAgent install needs the Oracle instant client libraries installed in the lib directory of the agent

6. HANA OnPrem SP5

My main, traditional data source, HANA On Premise setting:

HANA%20Source

HANA Source

The associated DWC connection:

DWC%20Configuration%20for%20HANA%20SP5

DWC Configuration for HANA SP5

7. HANA Cloud

HANA%20Cloud%20Source

HANA Cloud Source

The associated DWC Connection:

DWC Configuration for HANA Cloud

8. odata – Success Factors

Using a datasource that we use often in joining Cloud HR related data to other SAP related data, Success Factors odata endpoints:

SuccessFactors as a source

The associated DWC connection:

9. OpenSQLAccess

An easy way to get external data to be made available within the Data Warehouse Cloud, is using the OpenSQL Access. Simply create the schema from with DWC:

And connect to the schema using any 3rd party ETL tool. We have populated this using a few tools including Data Services, DBeaver and SAP Data Intelligence. Happy to try others if required.

10. HDI Containers

The final one in my list, and probably the most configuration.

By default, this functionality is disabled and needs to be enabled and connected to a Cloud Foundry tenant where the HDI objects reside. This also needs to be the same CF as the DWC is available in.

The simple process is :

  1. Enable the functionality and link the Space where the deployed HDI exists (must be the same CF environment) – currently via ticket request
    1. You will need:Tenant ID of your SAP Data Warehouse Cloud We need this information to know where to map the HDI containers to.79ccxxxx-xxxx-xxxx-xxxx-xxxxx7b81

      ID of your SAP Cloud Platform Organization The SAP Cloud Platform organization where your HDI containers reside in.

      389xxxxx-xxxx-xxxx-xxxx-xxxxxxd5357

      ID of your SAP Cloud Platform Space The SAP Cloud Platform space that belongs to the SAP Cloud Platform organization.

      6d3xxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

       

    2. Once this above step has been completed. The HDI deployed object, needs the following roles, with the privs on the objects you want to allow the DWC to access
      1. {
          "role": {
            "name": "DWC_CONSUMPTION_ROLE",
            "object_privileges": [
              {
                "name": "CUSTOMERS",
                "type": "TABLE",
                "privileges": [
                  "SELECT"
                ]
              }
            ]
          }
        }And:
      2. {
          "role": {
            "name": "DWC_CONSUMPTION_ROLE#",
            "object_privileges": [
              {
                "name": "CUSTOMERS",
                "type": "TABLE",
                "privileges_with_grant_option": [
                  "SELECT"
                ]
              }
            ]
          }
        }
      3. As part of the mta.yaml:
        resources:
         - name: hdi_hdb
           parameters:
            config:
              database_id: c393xxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx

        Where the database ID is the hostname of the DWC server (can be found also in the OpenSQL access definition above.

      4. Once you have deployed the HDI container again, it should come up in the DWC as option to connect to:

Configuration of the Data Provisioning Agent:

  1. Download the Windows or Linux version from  https://tools.hana.ondemand.com/#cloudintegration
  2. I do nearly everything in Linux, so I will describe the steps here:
    1. unzip the file into a staging area
    2. Create a dpagent user for the DWC environment you are connecting to.
    3. Create the directory /usr/sap/dataprovagent_$username – read /write to the dpagent user.
    4. run the hdbinst, as the dpagent user, installing into the above directory.
    5. Accept the defaults for the install
    6. Modify the dpagentconfig.ini file
      agent.name=DPAGENT_APJ
      hana.port=443
      hana.onCloud=false
      hana.server=c393f67c-xxx-xxxx-xxxxx.hana.prod-ap10.hanacloud.ondemand.com
      jdbc.enabled=true
      jdbc.host=c393f67c-xxx-xxxx-xxxxx.hana.prod-ap10.hanacloud.ondemand.com
      jdbc.port=443
      jdbc.encrypt=true
      
    7. start the dpagent: /bin/agentcli.sh –configAgent
    8. exit the agent config
    9. start the HANA user config: /bin/agentcli.sh –setSecureProperty
      1. Set the XS username and password as given in the DPAgent config screen for the DWC
    10. restart the dpagent
    11. Validate there are no errors in the logfile : /log/framework.log
      1. If there is “no connection to DWC” – then you may need to get the IP address whitelisted.
    12. Retest the connection in the DWC DPAgent config screen – it can take a coffee break for it to go green so be patient!

Hopefully the above has given you some insights on how easy it is to create a variety of different data sources in DWC.

If you have any issues connecting to DWC, post your question below and I can try to help – when I have time.

Cheers

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Enio Terra
      Enio Terra

      Wow!! Great blog Jon! I'm following the evolution of DWC and with no doubt the solution getting better and will continue growing.

      BR.

      Enio Terra

      Author's profile photo Jon Gooding
      Jon Gooding
      Blog Post Author

      Glad you found it useful! I should document what I do more.

      Author's profile photo sandeep karnati
      sandeep karnati

      Hello Jon,

      in order to connect my backend systems like BW,BW4,ECC,S4 to DWC,do i need to use DPA? or can i  also use SP Cloud Connector for same.

       

      Author's profile photo Jon Gooding
      Jon Gooding
      Blog Post Author

      Hi Sandeep - yes you will need a DP Agent. As it is required for the back end connections

      It's relatively straight forward to configure.

      Author's profile photo sandeep karnati
      sandeep karnati

      Thank you.

      Do i need to expose my DPA to internet  to make connections between backend and DWC?

      Thanks

      Author's profile photo Jon Gooding
      Jon Gooding
      Blog Post Author

      The DWC instance needs to be able to connect to the DP Agent. So yes - but typically it gets secured appropriately as part of the process.

      Cheers

      Jon

      Author's profile photo Martin Dell'Isola
      Martin Dell'Isola

      Hi Jon, do you have an example of it? I mean, how to expose and what to expose? Reverse Proxy, Firewall? how?

      thanks

      MartinD

      Author's profile photo Paolo Rainone
      Paolo Rainone

      Hi, i have to connect DWC to S/4 Hana Private Cloud ... sold in RISE . I would to use the Cloud Connector but i can't configure the connection to S/4 ... i receive this error:

      - Data flows can't be used because of errors in the connection. - Remote tables can't be used because of errors in the connection.

       

      Could you help me how i can connect DWC to S/4 Hana in Private Cloud using Cloud Clonnector?

       

      Thanks

      Author's profile photo Jon Gooding
      Jon Gooding
      Blog Post Author

      Hi Paolo,

      I assume you have checked the latest compatibility prerequisites:

      Private Cloud -> from what you have said, you can use the On Premise capability. You may need some assistance getting these components installed on your Private Cloud Environment.

      Remote Sources -> Data Provisioning Agent

      Data Flow -> Cloud Connector

      Can you advise the errors for each?

      For the Cloud Connector, assuming you are following the installation guide  

      I have configured this from both a private server and a publically accessible S/4 HANA Server to DWC

      Also make sure the Cloud Connector is all Green as per screen shot:

      Cloud%20Connector

      Cloud Connector

      Author's profile photo Paolo Rainone
      Paolo Rainone

      Hi Jon, yes I've checked the Cloud Connector and it is ok. But when i try to create a connection in DWC i have this error (i try with SAP ABAP type and many other included S/4 On premise) :

       

      Also I don't see the DPA that SAP has installed when configuring the HEC.

      Honestly didn't expect to have to do so much work to connect environments sold in RISE :(.

      In any case, thank you very much for the support