Technical Articles
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;
- 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
- 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
- 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
-
{ "role": { "name": "DWC_CONSUMPTION_ROLE", "object_privileges": [ { "name": "CUSTOMERS", "type": "TABLE", "privileges": [ "SELECT" ] } ] } }And:
-
{ "role": { "name": "DWC_CONSUMPTION_ROLE#", "object_privileges": [ { "name": "CUSTOMERS", "type": "TABLE", "privileges_with_grant_option": [ "SELECT" ] } ] } }
- 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.
- Once you have deployed the HDI container again, it should come up in the DWC as option to connect to:
-
- 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
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:
- unzip the file into a staging area
- Create a dpagent user for the DWC environment you are connecting to.
- Create the directory /usr/sap/dataprovagent_$username – read /write to the dpagent user.
- run the hdbinst, as the dpagent user, installing into the above directory.
- Accept the defaults for the install
- 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
- start the dpagent: /bin/agentcli.sh –configAgent
- exit the agent config
- start the HANA user config: /bin/agentcli.sh –setSecureProperty
- Set the XS username and password as given in the DPAgent config screen for the DWC
- restart the dpagent
- Validate there are no errors in the logfile : /log/framework.log
- If there is “no connection to DWC” – then you may need to get the IP address whitelisted.
- 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
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
Glad you found it useful! I should document what I do more.
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.
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.
Thank you.
Do i need to expose my DPA to internet to make connections between backend and DWC?
Thanks
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
Hi Jon, do you have an example of it? I mean, how to expose and what to expose? Reverse Proxy, Firewall? how?
thanks
MartinD
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
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 Connector
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