Connecting to 10 different remote sources using Da...

JonGooding · ‎2020 Aug 26

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;

SAP ECC

SAP S/4HANA

SAP BW/4HANA

MSSQL Database

Oracle Database

HANA OnPrem SP5

HANA Cloud

ODATA

OpenSQL Access

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 EHP 8 Source System

DWC Configuration for ECC

2. SAP S/4

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

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 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 Source System

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

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 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 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 Source

The associated DWC connection:

DWC Configuration for HANA SP5

7. HANA Cloud

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 :

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:

Download the Windows or Linux version from https://tools.hana.ondemand.com/#cloudintegration

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