Easily add Postgres to Data Warehouse Cloud (and non SAP Cloud Apps) using Open Connectors
One of the main Data Warehousing requirements from customers is accessing external non SAP Datasets. Yes, the SAP Data Warehouse Cloud (DWC) has many standard connectors, but in this example I used a lesser showcased feature of the DWC integration, that I think is very powerful – the SAP Open Connectors framework.
Below is a step by step process, of how I managed to get a Progres database connected, and how you can easily blend in 170 other commercial applications into the DWC.
Overall it took me about an hour – including learnings, in 4 simple steps:
- Access DWC environment for preparation of Open Connections.
- Setup Open Connectors on the BTP
- Configure Open Connectors to add Postgres instance
- Configure DWC to access configured Postgres instance.
Have Access to an DWC. I won’t cover the provisioning of DWC (that’s covered elsewhere), but you need to have access to the Space, and note the Open Connectors Connection:
Create access to Open Connectors in the SAP Business Technology Platform
This is the steps I went through:
Ensuring you have the right entitlements for the Open Connectors in your SAP BTP Account, select the appropriate subaccount to use. Then add the Open Connectors from the Entitlements
So now you should have the Open Connector in your entitlements:
Next create a subscription to the Open Connectors:
Noting your Provider and Region in the Subaccount Overview:
The Open Connectors is setup! Now it’s time to use it!
Logging into the Open Connectors interface (Launch it from the Subscriptions row, and go to Application)
From the Connectors page, select your choice of Integration:
For my scenario, I want to connect a Postgres database.
If you want to connect Postgres, some prep work I had to do:
- Ensure the firewall is open on the database port. I have a direct rule from the AWS postgres database to the Open Connectors servers (as our internal security rules disallow this open to all addresses). There is a range of IPs that the Open Connectors uses, so I put these into an AWS – Managed Prefix List. See the following link for the ip addresses https://help.openconnectors.ext.hana.ondemand.com/home/allow-ip-addresses
Selecting the Postgres tile gives:
These configuration option depends on the application, but are relatively straight forward, if you know your environment. You can also validate the connection.
Once validated, you can also run some queries, to validate the connectors:
Finally, you also need to capture the Organisation and User Secret
Connect the Open Connector framework to DWC and access the data.
- Provider and Region in the Subaccount Overview from Step 2,
- Organsational and User secret from Step 3
With the Open Connectors ready, we can add the appropriate connections to the Space:
Then, simply add the Connection. No additional details required
Note, that the connection type of Data Flow. So we use that to access the data.
My Postgres database only has 1 table, but can easily be acquired in to the DWC.
And we are off. I won’t do any further blending / visuals – you can find that in many other blogs!
Hope you found it useful, and if you have 1 of the 170 connections requiring integration – it’s a pretty easy solution!
If you do connect other systems, I’d be keen to hear about it in the comments below!