I'd like to thank yuliyareich for her previous blog post on this topic which has informed my writing. Similarly, the work of virginie.boullery, axel.meier and bengt has influenced this blog post
SAP Data Intelligence Cloud has a wide array of functionality to help us extract, transform and load data with a wide variety of sources and targets. It only makes sense that we'll want to be able to move data into spaces within our Data Warehouse Cloud system - let's take a look at how it's done
First things first, we'll need to find the public IP address for our Data Intelligence system and add it to the allowlist within Data Warehouse Cloud.
From within Connection Management in Data Intelligence, we'll find a system created connection called INFO_NAT_GATEWAY_IP. Select this connection and then click edit
Note: If your Data Intelligence Cloud system is hosted on Azure environments, the INFO_NAT_GATEWAY_IP won't be pre-populated. In this case, you can get the NAT Gateway address by sending a support ticket to the DI Cloud Ops team (CA-DI-OPS)
We'll find an IP address in two places on this screen. This is the public IP address for our Data Intelligence system, and is the address which we'll have to add to our Allowlist within Data Warehouse Cloud. Take note of this address for later
In Data Warehouse Cloud, open the menu on the left, then click on System, and Configuration
Along the top, we want to select IP Allowlist, followed by Trusted IPs
From the IP Allowlist page, we click on the Add button
We're going to add the IP address we copied from our Data Intelligence System here. Enter the IP address into the CIDR box and click on Add
Note that adding the IP Address to the Allowlist may take a little while after we submit it. Once our Data Intelligence system has been added to the Allowlist, we can prepare a user for our Data Intelligence Connection
A Data Warehouse Cloud Space is a secure area created by an Administrator, where members can ingest data and do data modeling. Select Space Management from the menu on the left
From here we can select our Space, using the search bar to filter if required
From inside our Space we want to create a Database User which will have the necessary authorizations for use in Data Intelligence Cloud
We're going to use the suffix DIUSER and enable both Read Access (SQL) and Write Access (SQL, DDL & DML). Notice that the name of the Open SQL Schema changes to add the Database User Name Suffix.
Once the user has been created, we need to Save and Deploy the space. Then, we'll want to retrieve the password
From here, we'll want to click on Request Password to generate a new password
Once we've requested the password, we click on the eye icon to expose it before copying it and storing it for later. This is the only time we'll be able to view it without changing it, so be sure to note it down
While you're here, also note the database user name, host name, and port. We'll also need these to create our connection
Now that our user is created, we want to Save and then Deploy our Space
Now we're going to move into our Data Intelligence system and create our Connection. Within Connection Management, select Create Connection
Use the Select Connection Type button to select HANA_DB. Give your connection a name in the ID text box, then provide the details you copied earlier for Host, Port, User and Password
Once we've entered the connection details we want to click on the Test Connection button
A response of Test connection status: OK means we're good to create our connection using the Create button
We're now able to view our new connection in the Connection List
Throughout this blog post we've looked at how we can allow our Data Intelligence system to connect to our Data Warehouse Cloud and how to create a DI Connection to a space within DWC. Obviously, the next question is once we have the connection, what can we do with it?
In the next blog post, we'll be looking at how we can upload the contents of a .csv file into a Data Warehouse Cloud Space, and then keep the contents up to date as the file changes by using Data Intelligence's upsert feature
Note: While I am an employee of SAP, any views/thoughts are my own, and do not necessarily reflect those of my employer
SAP Data Intelligence Cloud has a wide array of functionality to help us extract, transform and load data with a wide variety of sources and targets. It only makes sense that we'll want to be able to move data into spaces within our Data Warehouse Cloud system - let's take a look at how it's done
Adding Data Intelligence to Data Warehouse Cloud IP Allowlist
First things first, we'll need to find the public IP address for our Data Intelligence system and add it to the allowlist within Data Warehouse Cloud.
From within Connection Management in Data Intelligence, we'll find a system created connection called INFO_NAT_GATEWAY_IP. Select this connection and then click edit
Locate INFO_NAT_GATEWAY_IP, then select Edit
Note: If your Data Intelligence Cloud system is hosted on Azure environments, the INFO_NAT_GATEWAY_IP won't be pre-populated. In this case, you can get the NAT Gateway address by sending a support ticket to the DI Cloud Ops team (CA-DI-OPS)
We'll find an IP address in two places on this screen. This is the public IP address for our Data Intelligence system, and is the address which we'll have to add to our Allowlist within Data Warehouse Cloud. Take note of this address for later
Public IP address can be found here
In Data Warehouse Cloud, open the menu on the left, then click on System, and Configuration
Navigate to System Configuration
Along the top, we want to select IP Allowlist, followed by Trusted IPs
Navigate to the IP Allowlist
From the IP Allowlist page, we click on the Add button
Select the Add button
We're going to add the IP address we copied from our Data Intelligence System here. Enter the IP address into the CIDR box and click on Add
Creating the Allowlist Entry
Note that adding the IP Address to the Allowlist may take a little while after we submit it. Once our Data Intelligence system has been added to the Allowlist, we can prepare a user for our Data Intelligence Connection
Preparing our Data Warehouse Cloud Space
A Data Warehouse Cloud Space is a secure area created by an Administrator, where members can ingest data and do data modeling. Select Space Management from the menu on the left
From here we can select our Space, using the search bar to filter if required
Select Space Management from the menu, then select your Space, using the search box in the top right if necessary
From inside our Space we want to create a Database User which will have the necessary authorizations for use in Data Intelligence Cloud
Navigate to Database Access and click on Create under Database Users
We're going to use the suffix DIUSER and enable both Read Access (SQL) and Write Access (SQL, DDL & DML). Notice that the name of the Open SQL Schema changes to add the Database User Name Suffix.
Enter the Database User Name Suffix then enable Read and Write Access as shown
Once the user has been created, we need to Save and Deploy the space. Then, we'll want to retrieve the password
Click on the info icon
From here, we'll want to click on Request Password to generate a new password
Request a new password
Once we've requested the password, we click on the eye icon to expose it before copying it and storing it for later. This is the only time we'll be able to view it without changing it, so be sure to note it down
While you're here, also note the database user name, host name, and port. We'll also need these to create our connection
Take this opportunity to note down the database user name, password, host name and port
Now that our user is created, we want to Save and then Deploy our Space
Saving and Deploying our Space
Creating DWC Connection in Data Intelligence
Now we're going to move into our Data Intelligence system and create our Connection. Within Connection Management, select Create Connection
Create a new connection
Use the Select Connection Type button to select HANA_DB. Give your connection a name in the ID text box, then provide the details you copied earlier for Host, Port, User and Password
Enter the connection details
Once we've entered the connection details we want to click on the Test Connection button
Testing our connection
A response of Test connection status: OK means we're good to create our connection using the Create button
Check the connection status then create our connection
We're now able to view our new connection in the Connection List
Our connection has been successfully created
Conclusion
Throughout this blog post we've looked at how we can allow our Data Intelligence system to connect to our Data Warehouse Cloud and how to create a DI Connection to a space within DWC. Obviously, the next question is once we have the connection, what can we do with it?
In the next blog post, we'll be looking at how we can upload the contents of a .csv file into a Data Warehouse Cloud Space, and then keep the contents up to date as the file changes by using Data Intelligence's upsert feature
Note: While I am an employee of SAP, any views/thoughts are my own, and do not necessarily reflect those of my employer
- SAP Managed Tags:
