Interaction of SAP Data Warehouse Cloud and KNIME

With SAP Data Warehouse Cloud (DWC), SAP offers a powerful modeling tool for business intelligence use cases. The platform can be extended with SAP Data Intelligence, for example, to orchestrate and optimize huge amounts of data through machine learning and integrate it with SAP DWC.

For reasons of cost, flexibility or innovation, many companies are increasingly turning to “free and open source software” (FOSS). For example, the KNIME (Konstanz Information Miner) application, among others, is becoming increasingly popular. Like SAP Data Intelligence, KNIME is a tool for data analysis. More than 1,000 modules that include connectors for databases, machine learning and mining algorithms, or wrappers with custom code fragments are integrated into KNIME. To make the best use of its functionalities, it is necessary to establish a connection between KNIME and SAP Data Warehouse Cloud. With the help of the JDBC adapter, 3rd party tools such as KNIME, but also other JDBC-enabled platforms, can be linked to SAP DWC.

Discover the possibilities of SAP Data Warehouse Cloud

Read more about SAP Data Warehouse Cloud

• How to connect SAP Data Warehouse Cloud with Power BI and Tableau

• Interaction of SAP Data Warehouse Cloud and KNIME

• Follow the traces: Audit logging in SAP Data Warehouse Cloud

• SAP Data Warehouse Cloud and Python: A winning combination

Preparations in SAP Data Warehouse Cloud

In order for the reading of data in KNIME and the subsequent re-import into SAP DWC to work, SAP Data Warehouse Cloud must first be prepared for the connection. The following steps are required for this:

• Creation of a database user

• Release of the public IP address for SAP Data Warehouse Cloud

• Release of the views for consumption

• Installation of SAP HANA Client 2.0 on the end device

Click here to read how exactly the preparations in SAP Data Warehouse Cloud proceed.

Driver installation in KNIME

Once the preparatory activities in SAP DWC have been completed, the next step is the connection to KNIME. First of all, the adapter has to be created in KNIME. The user navigates in KNIME to File/Preferences/KNIME/Databases and creates a new database. This opens a new input mask in which the ID and the name can be freely selected. The Database type and also the URL template should remain on “default”. Only the classpath has to be selected – by adding the ngdbc.jar file, which is delivered with the installation of SAP HANA Client 2.0 and is accordingly located in the installation folder. Finally, it is necessary to click on “Find driver classes” and select the appropriate driver.

Connecting the platforms

Establishing the connection of KNIME with SAP Data Warehouse Cloud is done with the module “DB Connector”. It must be dragged and dropped into the project and opened with a double click.

Now the created driver has to be selected in the tab “Connection Settings”. Additionally the database URL has to be entered. It is composed as follows: jdbc:sap://<host>:<port>/. For authentication, “Username & password” should be selected. The data to be entered can be found in the Space Management of SAP DWC. In the info dialog box of the database user, the host name, port, database username and the password can be read.

Reading views in KNIME

To see views in KNIME, it is important that they have been enabled for consumption in SAP Data Warehouse Cloud. Unfortunately, tables cannot be enabled for consumption and consequently are not recognized by KNIME. A view can be selected with the “DB Table Selector” module and displayed with the “DB Reader” module. This table is now usable in KNIME. It is important to mention that all modules are executed with the context menu.

Creating tables in SAP Data Warehouse Cloud

To integrate a table created with KNIME into SAP DWC works with the module “DB Writer”. On the one hand the desired table and on the other hand a “DB Connector” module must be connected to its input ports. With a double click on the “DB Writer” module the DWC schema can be specified, which can be found in the info dialog box of the database user. The table name is freely selectable.

After running “DB Writer” the imported table is visible in SAP Data Warehouse Cloud. To do this, the user creates a new view or opens an existing view and navigates to “Sources”. The view can be used in the view immediately. With these steps the interaction of SAP Data Warehouse Cloud with KNIME should work.