Skip to Content
Technical Articles
Author's profile photo Yuliya Reich
Yuliya Reich
August 3, 2020 2 minute read

Connection between SAP Data Warehouse Cloud and SAP Data Intelligence Cloud

Dear all,

this blog and any advice that comes with it is not part of an official product of SAP. I am hoping you will find this information useful and create your own connection between Data Warehouse Cloud and Data Intelligence without any problems.

I used SAP Data Warehouse Cloud (Version 2020.16.51) and SAP Data Intelligence Cloud (Version 2006.1.8).

Create Open SQL Schema in DWC

First of all, you need to create Open SQL Schema in SAP DWC. Here you will find the details how to do this. Then you must add DI IP address to DWC IP Allowlist. How to find your DI IP address you will find here.

Create connection in DI

On the DI side go to Connection Management. Click on Create Connection and choose HANA_DB as a type of the connection. You must provide information such as host, port, user and password of your Open SQL Schema in DWC. Before closing this window, test your connection. You should see “Test Connection Status: OK”. If you will see any errors, you should check your Open SQL Schema in DWC.

Next go to ML Scenario Management and create your scenario for this use case. In your ML Scenario go to pipelines and click on create. In this blog we will create the pipelines for two options:

  • The source system is Data Intelligence Cloud and the target system is Data Warehouse Cloud
  • The source system is Data Warehouse Cloud and the target system is Data Intelligence Cloud

Import data from SAP DI into SAP DWC

The two necessary operators are Structured File Consumer and Table Producer. The first one you need to define a file for importing into DWC and the second operator defines a target table in your Open SQL Schema.

For the operator Structured File Consumer choose a file that you want to import.

For the operator Table Producer choose the existing table or give a new name for the target table. I am going to create a new target table with the name “DI_TEST”.

Save and run this pipeline. Then go to your space in SAP DWC, select Data Builder and you will see the table that was imported from Data Intelligence:

 

Import data from SAP DWC into SAP DI

In the configuration for Table Consumer Operator choose a table that you want to import from DWC into DI. For Structured File Producer select where you want to storage the table. Then save and run the new pipeline. I want to save the table local, so I selected local as a storage type.

In order to find your table, go to System Management and the Files:

I hope you could follow along and can now use your connection between SAP Data Intelligence Cloud and SAP Data Warehouse Cloud.

Thank you, Laima Laka,  for giving me advice about DI.

Best,

Yuliya

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Karsten Krause
      Karsten Krause

      Hi Yuliya,

       

      thanks for this great guide on a topic which has not been covered extensivly elsewhere.

       

      Unfortunatly, when I try to connect DI Cloud and DWC the following message appears:

      Status code from service response: 400, Message: Failed to validate connection avantum_dwc.

      Code:
      1
      Message:
      Remote source is down!

       

      Maybe the OpenSQL Schema is not configured correctly? I´ve tried to find ressources explaining this process but unfortunatly all previous documentation is depreciated. In DWC I´ve created a space, turned on the exposure for consumption and created a database user which provided me with the credentials for the connection.

       

      Thank you for your help

      Best

      Karsten

      Author's profile photo Yuliya Reich
      Yuliya Reich
      Blog Post Author

      Hi Karsten,

      did you whitelisted your DI API on DWC tenant? If no, yo should do it.

      Regards,

      Yuliya

      Author's profile photo Karsten Krause
      Karsten Krause

      Hi Yuliya,

       

      thanks a lot. The connection is working now.

      Maybe you should add this step to the blogpost.

       

      Best

      Karsten

      Author's profile photo Richard Herrmann
      Richard Herrmann

      Thank you very much, Yuliya! Works smoothly.

      Author's profile photo Agus Jamaludin
      Agus Jamaludin

      Hi Yuliya,

       

      I have tried to write the data from Data Intelligence to SAP Data Warehouse Cloud and follow your step. But I got this kind of error

      SQL submitted to database <> resulted in error <General error;258 insufficient privilege: Detailed info for this error can be found with guid '28D57CD7CBE15E4981DB11DB40FC097B'>.

      Kindly advise.

       

      Thank you in advance for your help

       

      Regards,

      Author's profile photo Xavier Polo
      Xavier Polo

      Did you activate READ and WRITE access when you created the OpenSQL Schema in DWC?

      Author's profile photo Agus Jamaludin
      Agus Jamaludin

      Yes I did,

      Read:

       

      Write:

       

      Thank you in advance,

       

      Regards,

      Author's profile photo Yuliya Reich
      Yuliya Reich
      Blog Post Author

      Hi Agus,

       

      just ad hoc: try without HDI consumption. Is your DWC Connection in DI ok by checking in Conection Management?  If it still doesn't work, try to create another open SQL Schema.

      Regards,

      Yuliya

      Author's profile photo Agus Jamaludin
      Agus Jamaludin

      Hi Yulia,

       

      Thank you for your advise,

       

      Regards,

      Agus Jamaludin

      Author's profile photo Karsten Krause
      Karsten Krause

      Hi Agus,

       

      I´ve faced the same issue and found the solution. This problem has nothing to do with the user priviliges in DWC but rather with using the right user when configuring the table producer. I´ve found this solution with the help of this blog and people helping in the SAP DI Community.

       

      The symptom:

      Group messages:
      Group: default; Messages: Graph failure: tableproducer1 failed with the following error:
      General error;10 authentication failed
      Process(es) terminated with error(s). restartOnFailure==false

       

      The problem:

      When your schema is empty you can´t add a new target in the table producer. Therefore I´ve always selected the space above (without the #). But in the JSON file DI automatically uses the wrong user when selecting this space.

       

      The solution:

      Manually add the DWC user in the JSON view:

       

      Fun fact:

      After the first exection of this pipeline you´ll be able to add a target to this space using the diagram view.

       

      Hope this fixes your problem.

       

      Best

      Karsten

      Author's profile photo Agus Jamaludin
      Agus Jamaludin

      Hi Karsten,

       

      Thank you for your advise. It's working now.

       

      Regards,

      Agus Jamaludin

      Author's profile photo Yuliya Reich
      Yuliya Reich
      Blog Post Author

      Thanks Karsten for sharing!

       

      Regards,

      Yuliya